MySQL常见故障及处理办法

MySQL在运行过程中，经常会遇到各种各样的问题。一旦遇到问题，哪怕是一个很小的问题，也会对项目的正常运行造成致命的影响。如果能够找到MySQL产生问题的原因，那么处理起来就容易许多。而MySQL的错误日志，就给我们提供了一个快速找到MySQL错误产生原因的工具。

如何查看MySQL错误日志

MySQL的错误日志，路径如下所示：/var/log/mysql/error.log，我们可以通过less来查看该错误日志。

sudo less /var/log/mysql/error.log

MySQL的错误日志会详细记录MySQL运行的相关信息，当然也会将错误信息记录在内。

如何排除MySQL的故障

在MySQL数据库中，查询语句必须以分号（;）结尾，才能正常的执行，如以下示例所示：

SHOW * FROM table_name;

如果语句末尾没有分号，按回车后语句不会被执行，直到输入分号并按回车才能执行语句。

有时候我们的MySQL运行缓慢，我们可以通过配置mysqld.cnf文件，让MySQL可以将运行缓慢的查询记录在日志中。mysqld.cnf文件存储在/etc/mysql/mysql.conf.d/目录中：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

滚动文件到如下位置：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
#slow_query_log         = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
. . .

上述几行被注释掉的内容，是配置MySQL记录缓慢查询的指令，通过取消注释，我们就可以将较缓慢的查询记录到日志中了。下面我们来看一下每条内代码的含义：

• slow-query-log：将后面的值设置为1，可以启用缓慢查询日志。
• slow-query-log-file：这条指令定义了，MySQL缓慢查询日志文件的位置。如上面内容所示，缓慢查询日志的路径是：/var/log/mysql-slow.log。
• long_query_time：将此条指令的值设置为2，它让MySQL记录所有2秒以上才能完成的查询。
• log_queries_not_using_indexes：该条指令告诉MySQL，记录那些没有被记录在/var/log/mysql-slow.log日志的查询。对于缓慢查询日志来说，本条设置不是必须的，但它对于发现效率低下的查询很有帮助。

通过删除上面每一条指令前面的井号（#），可以取消每行的注释。现在，每条指令如下所示：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes
. . .

注意：如果正在运行的版本是MySQL 8+，这些被注释的行默认不在mysqld.cnf文件中。在这种情况下，需要将每一行手动添加到文件的底部：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes

启用缓慢查询日志后，保存并关闭文件。然后重启MySQL服务：

sudo systemctl restart mysql

使用这些设置后，我们就可以通过查看慢速查询日志，来查找有问题的查询了。使用less来查看缓慢查询日志，如下所示：

sudo less /var/log/mysql_slow.log

远程访问 MySQL

在许多网站和应用程序创建之初，都是将Web服务器和数据库部署在同一台主机上。但是，随着时间的流逝，我们对MySQL服务器的性能要求越来越高，我们可能需要通过远程数据库来提升项目的综合性能。

在大多数情况下，我们的MySQL服务器仅配置为侦听本地连接，如果要使用远程数据库，MySQL必须能够侦听服务器的外部IP地址。要启用此功能，请打开mysqld.cnf配置文件：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

导航到以bind-address指令开头的行：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
. . .

默认情况下，此处设置为127.0.0.1，这意味着服务器仅监听本地连接。需要更改此设置为外部IP地址。为了排除故障，可以先将此指令设置为通配符IP地址*，::或者0.0.0.0：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

注意：如果正在运行的版本为MySQL 8+，则默认情况下该bind-address指令行将不在mysqld.cnf文件中。在这种情况下，需要将该行内容手动添加到文件底部：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

改变这一行后，保存并关闭文件（如果使用nano，按CTRL + X，Y，然后ENTER）。

然后重启MySQL服务，以使对mysqld.cnf的更改生效：

sudo systemctl restart mysql

如果我们已经有了一个想要使用root用户或者拥有sudo权限的用户登录MySQL：

sudo mysql

如果为root用户启用了密码验证，则需要使用以下命令来登录MySQL：

mysql -u root -p

要更改用户的MySQL服务器，可以使用MySQL的RENAME USER命令。运行以下命令，将remote_server_ip更改为远程MySQL服务器的IP地址：

RENAME USER 'sammy'@'localhost' TO 'sammy'@'remote_server_ip';

或者，您可以使用以下命令创建一个仅从远程主机进行连接的新用户帐户：

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED BY 'password';

注意：此命令将创建一个新用户“sammy”，并使用MySQL的默认身份验证插件caching_sha2_password创建密码。但是，某些版本的PHP可能会导致此插件出现问题。

如果打算将此数据库与phpMyAdmin一起使用，则可能需要创建一个远程用户，该用户将通过mysql_native_password插件进行身份验证：

CREATE USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

如果不确定，则始可以创建一个使用caching_sha2_plugin验证的用户，然后使用“ALTER”命令更改验证方式：

ALTER USER 'sammy'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';

然后，根据需求为新用户授予适当的权限。下面的例子中，将授予用户全局权限，用户可以使用CREATE，ALTER和DROP等命令，对数据库、表和用户进行操作，当然也可以使用INSERT，UPDATE以及DELETE等命令对任何表的数据进行操作。它还授予用户使用查询数据命令SELECT，使用REFERENCES创建外键，以及使用RELOAD权限执行FLUSH操作。一定要记住，要根据需求授予用户权限，如果想调整用户的权限，我们也可以随时调整。

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;

由于之前使用了CREATE USER和GRANT语句，通过运行FLUSH PRIVILEGES，可以释放内存中的缓存，使配置生效：

FLUSH PRIVILEGES;

然后，退出MySQL客户端：

exit

最后，假设我们在数据库服务器上配置了防火墙，那么还需要打开3306端口（MySQL的默认端口）以允许访问MySQL。

如果我们只打算从一台特定计算机连接数据库服务器，则可以使用以下命令只授予该计算机远程连接数据库的权利。将remote_IP_address更改为需要授权的计算机IP地址：

sudo ufw allow from remote_IP_address to any port 3306

如果因为某些原因，将来需要从其他计算机访问数据库，则可以使用此命令临时授予它们访问权限。

另外，我们也可以使用以下命令允许从任何IP地址连接MySQL数据库：

警告：此命令允许任何人都可以访问MySQL数据库。

sudo ufw allow 3306

之后，可以尝试从另一台计算机远程访问数据库：

注意：如果添加了防火墙规则，仅允许来自特定的IP地址连接，设置后一定要使用计算机进行访问测试。

mysql -u user -h database_server_ip -p

如果能够访问数据库，则说明我们在配置文件bind-address中的配置生效。请注意，设置bind-address 的值为 0.0.0.0是不安全的，因为它允许从任何IP地址连接到数据库服务器。

如何解决MySQL崩溃的问题

MySQL崩溃的最常见原因，是由于内存不足而停止运行或启动失败。要验证这一点，需要在MySQL崩溃后查看其错误日志。

首先，尝试通过以下命令来启动MySQL服务器：

sudo systemctl start mysql

然后查看MySQL错误日志，以了解导致MySQL崩溃的原因。可以使用less命令，一次可查看一页日志：

sudo less /var/log/mysql/error.log

表示内存不足的日志信息包括：Out of memory 或 mmap can't allocate等。

内存不足的解决方案是：

• 优化MySQL配置。可以使用MySQLtuner这个开源工具来检测MySQL配置。运行MySQLtuner脚本后，会输出一组优化MySQL配置文件（mysqld.cnf）的建议。值得注意的是，在使用MySQLTuner之前，MySQL服务器运行的时间越长，积累的数据越多，其建议越准确。要获得MySQL当前内容使用情况和MySQLTimer建议的内存使用估算值，可以使用此MySQL计算器。
• 减少Web应用对MySQL的请求数量。通常可以通过使用静态缓存来完成此操作。例如，Joomla可启用内置的缓存功能；如果是wordpress可以使用WP Super Cache等插件。
• 升级VPS配置。对于使用MySQL数据库的服务器来说，我们建议至少具有1GB RAM的服务器。

虽然升级服务器的配置是最简单的方法，但是这种方法也是最费钱的。最好还是根据自己的实际需求情况来确定，如果不用升级配置就能稳定运行是最好的选择。

修复MySQL的表

有时，MySQL的数据表可能会损坏，并且无法读取其中的数据。尝试从损坏的数据表中读取数据，可能会导致MySQL服务器崩溃。

表损坏的常见原因：

• MySQL服务器在写入过程中停止运行。
• 外部程序修改表的时候，服务器也在修改表。
• 主机意外关闭。
• 计算机硬件出现故障。
• MySQL配置代码存在错误。

为了最大限度的降低风险，在尝试排除MySQL故障或者尝试修复数据表之前，我们先做好对数据库的备份。

首先，停止MySQL服务：

sudo systemctl stop mysql

然后将所有MySQL数据复制到新的备份目录中。在Ubuntu系统上，默认MySQL数据目录为/var/lib/mysql/：

cp -r /var/lib/mysql /var/lib/mysql_bkp

MyISAM 数据库引擎

如果MySQL使用的是 MyISAM 数据库引擎（MySQL 5.5 之前的版本），可以通过通过如下方式修复。

要检查某个表是否损坏，可以通过执行如下命令：

CHECK TABLE table_name;

通过输出内容，我们能够了解该表是否损坏。如果该表损坏，我们可以通过如下命令，尝试修复表：

REPAIR TABLE table_name;

如果修复成功，会在输出内容中看到类似以下内容的消息：

Output
+--------------------------+--------+----------+----------+
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status   | OK       |
+--------------------------+--------+----------+----------+

但是，如果表仍然是损坏的，则MySQL文档提供了一些其他修复损坏表的方法。

InnoDB数据库引擎

如果MySQL使用InnoDB数据库引擎（MySQL 5.5 之后的版本），则修复过程将有所不同。从MySQL 5.5版开始，InnoDB是默认的数据库引擎，它有自动检查和修复的功能。InnoDB在读取的每个页面执行校验来检查页面是否损坏，如果发现校验码不符，它将自动停止MySQL服务器运行。

因为InnoDB数据库引擎具有崩溃恢复机制，可以在重新启动服务器时解决大多数问题。但是，如果确实遇到需要对InnoDB表进行修复的情况，可参考“MySQL文档建议”。

如果有问题，可先尝试重新启动MySQL服务，检查是否可以恢复正常：

sudo systemctl restart mysql

如果MySQL服务器仍然崩溃或无法访问，可以通过编辑mysqld.cnf文件，启用force_recovery选项。

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在文件的[mysqld]部分，添加以下行：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
. . .
innodb_force_recovery=1

保存并关闭文件，然后尝试重新启动MySQL服务。如果重启后可以访问损坏的数据表，使用 mysqldump 实用程序将该表数据转储到新文件中。我们可以随意命名该文件，在这里我们将其命名为out.sql：

mysqldump database_name table_name > out.sql

然后，从数据库中删除该损坏的表。为了避免要重新打开MySQL提示符，可以使用以下语法：

mysql -u user -p --execute="DROP TABLE database_name.table_name"

然后，使用刚导出的备份文件还原该表：

mysql -u user -p < out.sql

解决MySQL的Socket错误

MySQL通过”socket”文件管理与数据库服务器的连接，帮助处理不同进程之间的通信。MySQL服务器的“socket”文件被命名为”mysqld.sock”，在Ubuntu系统通常被存储在/var/run/mysqld/目录中。该文件由MySQL服务自动创建。

有时，更改了MySQL的配置，可能导致MySQL无法读取“socket”文件，从而阻止访问数据库。常见的”socket”文件错误：

Output
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

导致此错误的一个常见原因，是MySQL服务已停止运行，尝试使用以下命令启动MySQL服务：

sudo systemctl start mysql

然后，再次尝试访问MySQL。如果仍然提示“socket”文件错误，可以在mysqld.cnf文件中查找“socket”文件的路径信息：

sudo nano /etc/mysql/mysql.conf.d/mysql.cnf

在此文件的[mysqld]部分查找socket参数。它看起来可能是这样：

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
. . .

关闭此文件，然后通过ls命令在配置文件所示目录中，确认“mysqld.sock”文件是否存在：

ls -a /var/run/mysqld/

如果“socket”文件存在，将在输出中看到如下所示：

Output.  ..  mysqld.pid  mysqld.sock  mysqld.sock.lock

如果该文件不存在，则可能是由于MySQL在尝试创建该文件时，没有足够的权限执行该操作，导致创建失败。将目录所有权更改为mysql用户和组：

sudo chown mysql:mysql /var/run/mysqld/

然后将目录权限设置为755：

sudo chmod -R 755 /var/run/mysqld/

最后，重新启动MySQL服务，以便MySQL可以再次尝试创建“socket”文件：

sudo systemctl restart mysql

尝试再次访问MySQL，如果仍然提示“socket”文件错误，我们就要继续查看错误日志，看看是否能够找到线索。

总结

本指南分享了一些常见的MySQL故障，以及解决MySQL故障问题的常见方法，不可能解决用户遇到的所有问题。遇到MySQL的问题，要仔细查看日志来查找线索，如果实在解决不了，可以尝试找其他高手帮忙解决。