如何解决MySQL中的问题

本指南旨在用作诊断MySQL设置时的故障排除资源和起点。我们将回顾许多MySQL用户遇到的一些问题,并提供解决特定问题的指导。我们还将包含指向DigitalOcean教程的链接以及在某些情况下可能有用的官方MySQL文档。

介绍

MySQL是一个开源的关系数据库管理系统(RDBMS),是世界上最受欢迎的。 与使用任何软件的情况一样,新手和有经验的用户都会遇到令人困惑的错误消息或难以诊断的问题。

在您诊断MySQL设置时,本指南将作为故障排除资源和起点。 我们将回顾许多MySQL用户遇到的一些问题,并提供解决特定问题的指导。 我们还将包含指向DigitalOcean教程的链接以及在某些情况下可能有用的官方MySQL文档。

请注意,本指南假定如何在Ubuntu 18.04上安装MySQL中描述的设置,并且整个指南中的链接教程反映了此配置。 但是,如果您的服务器正在运行其他发行版,则可以在链接教程顶部的“教程版本”菜单中找到特定于该发行版的指南。

如何开始使用MySQL

许多首次使用MySQL的用户遇到问题的地方是在安装和配置过程中。 我们关于如何在Ubuntu 18.04上安装MySQL的指南提供了有关如何设置基本配置的说明,可能对MySQL新手有用。

一些用户遇到问题的另一个原因是他们的应用程序需要仅存在于最新版本中的数据库功能,但某些Linux发行版(包括Ubuntu)的默认存储库中提供的MySQL版本不是最新版本。 出于这个原因,MySQL开发人员维护自己的软件存储库,您可以使用它来安装最新版本并使其保持最新。 我们的教程“ 如何在Ubuntu 18.04上安装最新的MySQL ”提供了有关如何执行此操作的说明。

如何访问MySQL错误日志

通常,可以通过分析其错误日志来确定MySQL中减速,崩溃或其他意外行为的根本原因。 在Ubuntu系统上,MySQL的默认位置是/var/log/mysql/error.log 在许多情况下,使用less程序最容易读取错误日志,这是一个命令行实用程序,允许您查看文件但不能编辑它们:

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

如果MySQL的行为不符合预期,则可以通过运行此命令并根据日志内容诊断错误来获取有关故障源的更多信息。

重置根MySQL用户密码

如果您为MySQL安装的root用户设置了密码但却忘记了密码,那么您可能会被锁定在数据库之外。 但是,只要您可以访问托管数据库的服务器,就应该可以重置它。

此过程与重置标准Linux用户名的密码不同。 查看我们关于如何重置MySQL或MariaDB Root密码的指南,以了解此过程并了解此过程。

查询麻烦

有时用户一旦开始对其数据发出查询就会遇到问题。 在某些数据库系统(包括MySQL)中,查询语句必须以分号( ; )结尾才能完成查询,如下例所示:

SHOW * FROM table_name;

如果在查询结尾处未能包含分号,则提示将在新行上继续,直到您通过输入分号并按ENTER完成查询。

有些用户可能会发现他们的查询非常慢。 查找哪个查询语句导致速度减慢的一种方法是启用和查看MySQL的慢查询日志。 为此,请打开mysqld.cnf文件,该文件用于配置MySQL服务器的选项。 该文件通常存储在/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

一旦你挑出导致速度减慢的查询,你可能会发现我们关于如何在VPS上优化MySQL和MariaDB中的查询和表的指南有助于优化它们。

此外,MySQL包含EXPLAIN语句,该语句提供有关MySQL如何执行查询的信息。 官方MySQL文档中的此页面提供了有关如何使用EXPLAIN突出显示低效查询的信息。

有关理解基本查询结构的帮助,请参阅MySQL查询简介

允许远程访问

许多网站和应用程序从他们的Web服务器和数据库后端开始托管在同一台机器上。 但是,随着时间的推移,这样的设置会变得很麻烦并且难以扩展。 常见的解决方案是通过设置远程数据库来分离这些功能,从而允许服务器和数据库在自己的计算机上按照自己的进度增长。

用户在尝试设置远程MySQL数据库时遇到的一个常见问题是,他们的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

更改此行后,保存并关闭该文件,然后重新启动MySQL服务:

sudo systemctl restart mysql

在此之后,尝试从另一台计算机远程访问您的数据库:

mysql -u user -h database_server_ip -p

如果您能够访问数据库,则会确认配置文件中的bind-address指令是问题所在。 但请注意,将bind-address设置为0.0.0.0是不安全的,因为它允许从任何IP地址连接到您的服务器。 另一方面,如果您仍然无法远程访问数据库,则可能会导致其他问题。 在任何一种情况下,您可能会发现在Ubuntu 18.04上按照我们的如何设置远程数据库以使用MySQL优化站点性能的指南来设置更安全的远程数据库配置会很有帮助。

MySQL意外停止或无法启动

MySQL崩溃的最常见原因是由于内存不足而导致其停止或无法启动。 要检查这一点,您需要在崩溃后查看MySQL错误日志。

首先,尝试键入以下命令启动MySQL服务器:

sudo systemctl start mysql

然后查看错误日志以查看导致MySQL崩溃的原因。 您可以使用less来查看日志,一次一页:

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

一些表示内存不足的常见消息是内存不足或mmap can't allocate

内存不足的潜在解决方案是:

  • 优化MySQL配置 一个很棒的开源工具是MySQLtuner 运行MySQLtuner脚本将输出一组建议的调整到MySQL配置文件( mysqld.cnf )。 请注意,在使用MySQLTuner之前服务器运行的时间越长,其建议就越准确。 要获得当前设置和MySQLTimer提议的内存使用估计值,请使用此MySQL计算器

  • 减少Web应用程序对页面加载的依赖 这通常可以通过向应用程序添加静态缓存来完成。 这方面的例子包括Joomla,它具有可作为内置功能的缓存 ,以及WP Super Cache ,这是一个添加此类功能的WordPress插件。

  • 升级到更大的VPS 至少,对于使用MySQL数据库的任何服务器,我们建议使用至少1GB RAM的服务器,但数据的大小和类型会显着影响内存要求。

请注意,尽管升级服务器是一种潜在的解决方案,但只有在您调查并权衡所有其他选项后才建议使用它。 具有更多资源的升级服务器同样会花费更多的钱,所以如果它真的最终成为您的最佳选择,您应该只进行调整大小。 另请注意,MySQL文档包含许多其他用于诊断和防止崩溃的建议

损坏的表格

有时,MySQL表可能会损坏,这意味着发生了错误,并且其中保存的数据不可读。 尝试从损坏的表中读取通常会导致服务器崩溃。

表损坏的一些常见原因是:

  • MySQL服务器在写入过程中停止。
  • 外部程序修改服务器同时修改的表。
  • 机器意外关闭。
  • 计算机硬件出现故障。
  • MySQL代码中有一个软件错误。

如果您怀疑其中一个表已损坏,则应在排除故障或尝试修复表之前备份数据目录。 这有助于最大限度地降低数据丢失的风险。

首先,停止MySQL服务:

sudo systemctl stop mysql

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

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

进行备份后,您就可以开始调查表实际上是否已损坏。 如果表使用MyISAM存储引擎 ,您可以通过从MySQL提示符运行CHECK TABLE语句来检查它是否已损坏:

CHECK TABLE table_name;

此语句的输出中将显示一条消息,通知您它是否已损坏。 如果MyISAM表确实已损坏,通常可以通过发出REPAIR TABLE语句来修复它:

REPAIR TABLE table_name;

假设修复成功,您将在输出中看到如下消息:

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

但是,如果表仍然存在损坏,那么MySQL文档提供了一些修复损坏表的替代方法

另一方面,如果损坏的表使用InnoDB存储引擎 ,那么修复它的过程将会有所不同。 从版本5.5开始,InnoDB是MySQL中的默认存储引擎,它具有自动损坏检查和修复操作。 InnoDB通过在读取的每个页面上执行校验和来检查损坏的页面,如果发现校验和差异,它将自动停止MySQL服务器。

很少需要修复InnoDB表,因为InnoDB具有崩溃恢复机制,可以在服务器重新启动时解决大多数问题。 但是,如果您确实遇到需要重建损坏的InnoDB表的情况,则MySQL文档建议使用“转储和重新加载”方法 这涉及重新获得对损坏表的访问,使用mysqldump实用程序创建表的逻辑备份 ,该表将保留表结构及其中的数据,然后将表重新加载回数据库。

考虑到这一点,尝试重新启动MySQL服务,看看这样做是否允许您访问服务器:

sudo systemctl restart mysql

如果服务器仍然崩溃或无法访问,那么启用InnoDB的force_recovery选项可能会有所帮助。 您可以通过编辑mysqld.cnf文件来完成此操作:

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

请注意,InnoDB存储引擎通常比旧的MyISAM引擎更具容错能力。 使用InnoDB的表仍然可以被破坏,但由于其自动恢复功能 ,表损坏和崩溃的风险明显降低。

套接字错误

MySQL通过使用套接字文件来管理与数据库服务器的连接, 套接字文件是一种促进不同进程之间通信的特殊文件。 MySQL服务器的套接字文件名为mysqld.sock ,在Ubuntu系统上,它通常存储在/var/run/mysqld/目录中。 该文件由MySQL服务自动创建。

有时,对系统或MySQL配置的更改可能导致MySQL无法读取套接字文件,从而使您无法访问数据库。 最常见的套接字错误如下所示:

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

可能会出现此错误的原因有几个,以及一些可能的解决方法。

导致此错误的一个常见原因是MySQL服务已停止或未开始启动,这意味着它无法首先创建套接字文件。 要确定这是否是您看到此错误的原因,请尝试使用systemctl启动该服务:

sudo systemctl start mysql

然后再次尝试访问MySQL提示符。 如果仍然收到套接字错误,请仔细检查MySQL安装查找套接字文件的位置。 这些信息可以在mysqld.cnf文件中找到:

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
. . .

关闭此文件,然后通过在MySQL期望找到它的目录上运行ls命令来确保mysqld.sock文件存在:

ls -a /var/run/mysqld/

如果套接字文件存在,您将在此命令的输出中看到它:

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

如果该文件不存在,原因可能是MySQL正在尝试创建它,但没有足够的权限来执行此操作。 您可以通过将目录的所有权更改为mysql用户和组来确保正确的权限到位:

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

然后确保mysql用户对目录具有适当的权限。 在大多数情况下,将这些设置为775将起作用:

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

最后,重新启动MySQL服务,以便它可以尝试再次创建套接字文件:

sudo systemctl restart mysql

然后再次尝试访问MySQL提示符。 如果您仍然遇到套接字错误,那么您的MySQL实例可能存在更深层次的问题,在这种情况下,您应该查看错误日志以查看它是否可以提供任何线索。

结论

MySQL是无数数据驱动的应用程序和网站的支柱。 有这么多用例,可能会有很多错误的潜在原因。 同样,还有许多不同的方法可以解决此类错误。 我们已经介绍了本指南中最常遇到的一些错误,但是根据您自己的应用程序如何与MySQL一起工作,可能会出现更多错误。

如果您无法找到特定问题的解决方案,我们希望本指南至少为您提供MySQL故障排除的背景知识,并帮助您找到错误的来源。 有关更多信息,您可以查看官方MySQL文档 ,其中包含我们在此讨论的主题以及其他故障排除策略。

此外,如果您的MySQL数据库托管在DigitalOcean Droplet上,您可以联系我们的支持团队以获得进一步的帮助。


分享按钮