MySQL使用的常见问题解决与应用技巧汇总(mysql使用技巧)

发布时间：2024-01-29

推荐整理分享MySQL使用的常见问题解决与应用技巧汇总(mysql使用技巧)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:mysql主要用途,mysql的使用场景,mysql中常用的语句,mysql 常用,mysql 常用,mysql中常用的语句,mysql的基本使用,mysql中常用的语句,内容如对您有帮助，希望把文章链接给更多的朋友！

前言

在MySQL日常开发或者是维护中，有一些问题或是故障往往是难以避免的，如丢失密码、表损坏。在此总结一下常见的问题，以备今后所需。

一、忘记 MySQL 的 root 密码

1. 登录到数据库所在的服务器，手工 kill 掉 mysql 进程。

(1) 登录到数据库所在的服务器，手工 kill 掉 MySQL 进程：

其中，mysql.pid 指的是 MySQL 数据目录下的 pid 文件，它记录了 MySQL 服务的进程号。

(2) 使用 --skip-grant-tables 选项重启 MySQL 服务：

--skip-grant-tables 选项意思是启动 MySQL 服务时跳过权限表认证。启动后，连接到 MySQL 的 root 将不需要口令。

(3) 用空密码的 root 用户连接到 mysql ，并且更改 root 口令：

由于使用了 --skip-grant-tables 选项启动，使用 “set password” 命令更改密码失败，直接更新 user 表的 authentication_string(测试版本为5.7.,有的版本密码字段是 ‘password') 字段后，更改密码成功。刷新权限表，使权限认证重新生效。重新用 root 登录时，就可以使用刚刚修改后的口令了。

二、如何处理 myisam 存储引擎的表损坏

有的时候可能会遇到 myisam 表损坏的情况。一张损坏的表的症状通常是查询意外中断，并且能看到下述错误：

'table_name.frm' 被锁定不能更改不能找到文件 'tbl_name.MYYI' （errcode:nnn）文件意外结束记录文件被毁坏从表处理器得到错误 nnn。

通常有以下两种解决方法：

1. 使用 myisamchk 工具

使用 MySQL 自带的 myisamchk 工具进行修复：

其中 -r 参数的含义是 recover，上面的方法几乎能解决所有问题，如果不行，则使用命令：

其中 -o 参数的含义是 --safe-recover，可以进行更安全的修复。

2. 使用 sql 命令

使用 MySQL 的 check table 和 repair table 命令一起进行修复，check table 用来检查表是否有损坏；repair table 用来对坏表进行修复。

三、数据目录磁盘空间不足的问题

系统上线后，随着数据量的不断增加，会发现数据目录下的可用空间越来越小，从而给应用造成了安全隐患。

1. 对于 myisam 存储引擎的表

对于 myisam 存储引擎的表，在建表时可以用如下选项分别制定数据目录和索引目录存储到不同的磁盘空间，而默认会同时放在数据目录下：

如果表已经创建，只能先停机或者将表锁定，防止表的更改，然后将表的数据文件和索引文件 mv 到磁盘充足的分区上，然后在原文件处创建符号链接即可。

2. 对于 innodb 存储引擎的表

因为数据文件和索引文件是存放在一起的，所以无法将它们分离。当磁盘空间出现不足时，可以增加一个新的数据文件，这个文件放在充足空间的磁盘上。

具体实现方法是在参数 innodb_data_file_path 中增加此文件，路径写为新磁盘的绝对路径。

例如，如果 /home 下空间不足，希望在 /home1 下新增加一个可自动扩充数据的文件，那么参数可以这么写：

参数修改后，必须重启数据库才可以生效。

四、DNS反向解析的问题 (5.0 以后的版本默认跳过域名逆向解析)

在客户端执行 show processlist 命令，有时会出现很多进程，类似于：

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

MySQL 在默认情况下对于远程连接过来的 IP 地址会进行域名的逆向解析，如果系统的 hosts 文件中没有与之对应的域名，MySQL 就会将此连接认为是无效用户，所以下进程中出现 unauthenticated user 并导致进程阻塞。

解决的方法很简单，在启动时加上 --skip-name-resolve 选项，则 MySQL 就可以跳过域名解析过程，避免上述问题。

五、mysql.sock 丢失后如何连接数据库

在 MySQL 服务器本机上连接数据库时，经常会出现 mysql.sock 不存在，导致无法连接的问题。这是因为如果指定 localhost 作为一个主机名，则 mysqladmin 默认使用 Unix 套接字文件连接，而不是 tcp/ip。而这个套接字文件(一般命名为 mysql.sock)经常会因为各种原因而被删除。通过 --protocol=TCP|SOCKET|PIPE|MEMORY 选项，用户可以显式地指定连接协议，下面演示使用了 Unix 套接字失败后使用 tcp 协议连接成功的例子。

1. Unix 套接字连接：

2. tcp 连接

六、MyISAM表过大，无法访问问题

首先我们可以通过myisamchk命令来查看MyISAM表的情况。如下图，我查看admin表

datefile length代表当前文件大小 keyfile length代表索引文件大小 max datefile length 最大文件大小 max keyfile length 最大索引大小

可以通过如下命令来进行扩展数据文件大小

七、数据目录磁盘空间不足的问题

针对MyISAM存储引擎

可以将数据目录和索引目录存储到不同的磁盘空间。

针对InnoDB存储引擎

对于InnoDB存储引擎的表，因为数据文件和索引文件时存放在一起的。所以无法将他们分离。当磁盘空间出现不足时候，可以增加一个新的数据文件，这个文件放在有充足空间的磁盘上。具体实现是通过InnoDB_data_file_path中增加此文件。

参数修改之后，需要重启服务器，才可以生效。