35. MySQL 常见问题和应用技巧

1. 忘记 MySQL 的 root 密码⚓

1）首先登录到数据库所在服务器，手工 kill 掉 MySQL 进程（MySQL 8.0没有该文件）：

kill `cat /mysql-data-directory/hostname.pid`

2）使用--skip-grant-tables 选项跳过权限表认证，重启 MySQL 服务：

mysqld_safe --skip-grant-tables --user=zzx &

3）无需指定root的密码即可登录，修改密码：

-- 此时不可执行该语句，也不能执行 “ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyPass';” 语句
mysql> set password = password('123');
-- 直接修改权限表
mysql> update user set password=password('123') where user='root' and host='localhost';

4）刷新权限表，使得权限认证重新生效：

mysql> FLUSH PRIVILEGES;

5) 重新用 root 登录时，必须输入新口令。

MySQL 8.0忘记密码修改方法:

1) 停止MySQL服务，用--skip-grant-tables 选项跳过权限表认证，重启 MySQL 服务：

$ systemctl stop mysqld
$ systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
$ systemctl start mysqld

2) 将密码字段设为空：

$ mysql -u root
mysql> UPDATE mysql.user SET authentication_string=null WHERE User='root';
mysql> FLUSH PRIVILEGES;

3）重置密码：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
mysql> exit;

4）重启MySQL服务：

$ systemctl stop mysqld
$ systemctl unset-environment MYSQLD_OPTS
$ systemctl start mysqld

2. 处理 MyISAM 存储引擎的表损坏⚓

一张损坏的表的症状通常是查询意外中断并且能看到下述错误：

“tbl_name.frm”被锁定不能更改；
不能找到文件“tbl_name.MYI”（Errcode：nnn）；
文件意外结束；
记录文件被毁坏；
从表处理器得到错误 nnn。

解决方法：

方法一

使用 MySQL 自带的 myisamchk 工具进行修复，是专门用来修复 MyISAM 的表的工具。

$ myisamchk -r tablename
# 如果不能修复则改为使用“-o”选项
# -o --safe-recover，可以进行更安全的修复
$ myisamchk -o tablename

方法二

使用MySQL的CHECK TABLE和REPAIR TABLE命令一起进行修复。

-- InnoDB, MyISAM, ARCHIVE, and CSV tables.
CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {
    FOR UPGRADE
  | QUICK
  | FAST
  | MEDIUM
  | EXTENDED
  | CHANGED
}

-- 该语句风险很大，最好不要执行。参考MySQL文档。
REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

3. MyISAM 表超过 4GB 无法访问⚓

在 MySQL 5.0 版本之前，MyISAM 存储引擎默认的表大小只支持到 4GB 。使用命令查看：

$ myisamchk -dv t1
MyISAM file:         t1
Record format:       Packed
Character set:       utf8mb4_0900_ai_ci (255)
File-version:        1
Creation time:       2019-07-23  4:07:39
Recover time:        2019-08-02  3:13:51
Status:              checked,analyzed
Auto increment key:              1  Last value:                    42
Data records:                   42  Deleted blocks:                 0
Datafile parts:                 42  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:               840  Keyfile length:              2048
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                   47

table description:
Key Start Len Index   Type                     Rec/key         Root  Blocksize
1   2     4   unique  long                           1         1024       1024

其中Max datafile length和Max keyfile length是最大数据和索引文件大小。

修改：

mysql> ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=15000;

此命令可以修改表的最大记录数和平均记录长度，因此可以修改数据文件的最大 size。

4. 数据目录磁盘空间不足⚓

4.1 MyISAM 存储引擎⚓

在建表时可以用如下选项分别指定数据目录和索引目录存储到不同的磁盘空间（实验没有成功），而默认会同时放在数据目录下：

DATA DIRECTORY = 'absolute path to directory
INDEX DIRECTORY = 'absolute path to directory'

表创建之后不能再使用ALTER TABLE修改路径。此时可以将表的数据文件和索引文件 mv 到磁盘充足的分区上，然后在原文件处创建符号链接即可。当然，mv 前必须要停机或者将表锁定，以防止表的更改。

4.2 InnoDB 存储引擎⚓

在MySQL服务启动参数 innodb_data_file_path中增加文件，路径写为新磁盘的绝对路径。在 /home1 下新增加一个可自动扩充数据文件：

innodb_data_file_path = /home/ibdata1:2000M;/home1/ibdata2:2000M:autoextend

重启数据库生效。 https://dev.mysql.com/doc/refman/8.0/en/innodb-system-tablespace.html

5. DNS 反向解析的问题⚓

在 MySQL 5.0 以前的版本中执行 show processlist 命令的时候，有时会出现很多进程，类似于以下情况：

unauthenticated user | 192.168.5.71:57857 | NULL | Connect | NULL | login | NULL

这些进程会累计得越来越多，并且不会消失，应用无法正常响应，导致系统瘫痪。

对于远程连接过来的 IP 地址，会进行域名的逆向解析，如果系统的 hosts 文件中没有与之对应的域名，MySQL 就会将此连接认为是无效用户，所以在进程中出现unauthenticated user并导致进程阻塞。在启动的时候加上 --skip-name-resolve 选项即可跳过。

在 MySQL 5.0 以后版本，默认都会跳过域名逆向解析。

6. mysql.sock 丢失后如何连接数据库⚓

如果指定 localhost 作为一个主机名，则 mysqladmin 默认使用 UNIX 套接字文件 mysql.sock 连接，而不是TCP/IP。

从 MySQL 4.1 开始，通过--protocol= {tcp| socket| pipe| memory}选项，用户可以显式地指定连接协议：

# TCP 连接：
[zzx@zzx mysql]$ mysql --protocol=TCP -uroot -p -P3306 -hlocalhost

7. 同一台服务器运行多个 MySQL 数据库⚓

参考https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

及《深入浅出MySQL》30.7章节。

下面是结合参考官网（https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html、https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html）实践的步骤：

# 先停止原先yum安装的MySQL
$ systemctl stop mysqld

$ useradd -g mysql mysql2
$ cd /home/mysql2
$ tar -zvxf mysql-8.0.17-el7-x86_64.tar.gz mysql
$ ln -s /home/mysql2/mysql/mysql-8.0.17-el7-x86_64 mysql
$ cd mysql
# 修改配置文件
$ cp /etc/my.cnf .
$ cat my.cnf
[mysqld]
basedir=/home/mysql2/mysql
datadir=/home/mysql2/mysql/var/lib/mysql
socket=/home/mysql2/mysql/var/lib/mysql/mysql.sock

log-error=/home/mysql2/mysql/var/log/mysqld.log
pid-file=/home/mysql2/mysql/var/run/mysqld/mysqld.pid

character-set-server=utf8mb4
secure-file-priv=
port=3307

[client]
socket=/home/mysql2/mysql/var/lib/mysql/mysql.sock
port=3307

# 建立对应的目录
$ mkdir var/log
$ mkdir -p var/run/mysqld
$ mkdir mysql-files
$ chown -R mysql2:mysql ../mysql-8.0.17-el7-x86_64 ; chown -R mysql2:mysql ../mysql
$ chmod 750 mysql-files

# 初始化MySQL
$ bin/mysqld --defaults-file=my.cnf --initialize --user=mysql2
# 查看是否成功及获取密码
$ vi var/log/mysqld.log
# 启动服务
$ bin/mysqld_safe --defaults-file=my.cnf --user=mysql2 &
# 可以查看MySQL后台进程
$ ps -ef | grep mysqld
# 客户端连接
$ bin/mysql --defaults-file=my.cnf -uroot -p
# 登录之后需要修改密码
mysql> ALTER USER root@localhost IDENTIFIED BY "xxx";

# 启动原先的MySQL服务
$ systemctl start mysqld

8. 客户端怎么访问内网数据库⚓

使用 secureCRT 客户端工具
使用 MySQL Proxy（MySQL 代理）工具

参考《深入浅出MySQL》30.8章节。