MySQL常见故障及处理办法

MySQL常见故障及处理办法
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 + XY,然后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';

然后,根据需求为新用户授予适当的权限。下面的例子中,将授予用户全局权限,用户可以使用CREATEALTERDROP等命令,对数据库、表和用户进行操作,当然也可以使用INSERTUPDATE以及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 USERGRANT语句,通过运行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 memorymmap 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的问题,要仔细查看日志来查找线索,如果实在解决不了,可以尝试找其他高手帮忙解决。

原创文章,作者:牛奇网,如若转载,请注明出处:https://www.niuqi360.com/lamp-config/how-to-troubleshoot-issues-in-mysql/

发表评论

登录后才能评论