MySQL数据库的维护处理
时间：2008-12-11 来源：fuowen20
1、维护，主要是日志记录在维护中的处理，日志记录文件通常保存在与数据文件相同的目录之中，比如：Unix系统中的/usr/local/mysql/var或是/usr/local/mysql/data，在Windows中则是c:\mysql\data目录，日志记录分为以下几类：
一、错误日志：保存了服务器上发生的每个错误的记录；如果需要激活错误日志，添加--log-error选项到服务器启动命令行或选项文件中，比如：
[root@host175 ~]# /etc/rc.d/init.d/mysqld start --log-error
Starting MySQL [ OK ]
这个日志的文件名一般是以.err为结尾的。比如我机器上的host175.err，如下蓝色字体标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB ibdata1 mysql-bin.000008 mysql-bin.000017
greedland mysql mysql-bin.000009 mysql-bin.000018
host.test.com.err mysql-bin.000001 mysql-bin.000010 mysql-bin.000019
host.test.com.pid mysql-bin.000002 mysql-bin.000011 mysql-bin.000020
host175.err mysql-bin.000003 mysql-bin.000012 mysql-bin.000021
host175.test.com.err mysql-bin.000004 mysql-bin.000013 mysql-bin.000022
host175.test.com.pid mysql-bin.000005 mysql-bin.000014 mysql-bin.000023
ib_logfile0 mysql-bin.000006 mysql-bin.000015 mysql-bin.index
ib_logfile1 mysql-bin.000007 mysql-bin.000016 test
具体内容如下，这是一部分： [root@host175 ~]# more /usr/local/mysql/var/host175.err
081211 17:03:35 InnoDB: Started; log sequence number 0 43665
081211 17:03:35 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.40-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
好像也记录了一些启动信息，当然我这里没有错误消息，而是一些指示性消息。 二、查询日志：这是另外一个有用的日志，因为这里保存了客户机发给服务器的每个查询的踪迹，还可以显示客户机连接服务器以及这些客户机所作操作的细节内容。若是希望监控以排错为目的的活动，就应该激活查询日志选项--log，也是加在服务器启动命令行或选项文件中，如下： [root@host175 ~]# /etc/rc.d/init.d/mysqld start --log
Starting MySQL [ OK ]
这个日志的文件名是主机名加.log扩展名的格式，比如我机器上的host175.log，下面蓝色字体标记的文件名： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile0 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.000017 mysql-bin.000021 mysql-bin.index greedland host175.log ib_logfile1 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 mysql-bin.000018 mysql-bin.000022 test
host.test.com.err host175.test.com.err ibdata1 mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 mysql-bin.000019 mysql-bin.000023 host.test.com.pid host175.test.com.pid mysql 查询日志部分内容示例如下： [root@host175 ~]# more /usr/local/mysql/var/host175.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
081211 17:22:52 1 Connect Access denied for user 'fuhao'@'localhost' (using password: NO)
081211 17:23:12 2 Connect Access denied for user 'root'@'localhost' (using password: YES)
081211 17:23:19 3 Connect root@localhost on WorkDB
3 Query select @@version_comment limit 1
081211 17:23:24 3 Query show tables
081211 17:23:31 3 Query select * from users
这里面对每一个操作记录的很详细。 三、慢查询日志：这文件中记录了超过预先设定时间量（根据long_query_time变量确定）的所有查询，任何超过这个值的查询都将列在这个日志文件中。若是需要查找优化性能的办法，可以查查这里的问题。 我们可以如下这样查看long_query_time变量的值： mysql> show variables like 'long_query_time';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
我这里的缓存查询时间限制设置是10秒，也就是说查询超过10秒的都会在这个慢查询日志中记录下来； 慢查询日志使用--log-slow-queries选项来激活，如下所示： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-slow-queries
Starting MySQL [ OK ]
该日志的默认文件名是主机名后带-slow的后缀以及.log扩展名，比如下面蓝色字体所标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 mysql-bin.000018 mysql-bin.000023
greedland host175.log ibdata1 mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 mysql-bin.000019 mysql-bin.000024
host.test.com.err host175.test.com.err mysql mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 mysql-bin.000020 mysql-bin.000025
host.test.com.pid host175.test.com.pid mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 mysql-bin.000021 mysql-bin.index
host175-slow.log ib_logfile0 具体的文件内容格式如下： [root@host175 ~]# more /usr/local/mysql/var/host175-slow.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
由于我这里不可能有超过10秒的查询所以下面就没数据了。 四、更新日志：记录了改变具体表的所有查询。与update、create table和drop table同时使用的insert、replace、delete、grant和revoke语句都属于改变具体表的查询，但是select语句不是。 要让服务器写更新日志必须在服务器启动时带选项--log-update，比如： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-update
Starting MySQL [ OK ]
如果需要重新构建一个表，但是该表在最近一次备份之后又进行了修改，这个日志可以发挥作用。在数据库受到破坏的情况下，可以根据备份进行恢复，然后按照更新日志重新创建记录到查询，这样可以把系统恢复到受破坏之前的状况。 五、二进制更新日志：在版本3.23.14以后版本支持，这是一种更为有效的保存日志数据的格式，它比标准更新日志记录得更详细，名为mysqlbinlog实用程序可以把二进制日志文件转换回文本文件格式从而可以进行阅读。 在启动MySQL时使用--log-bin选项可以创建二进制更新日志文件，比如： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-bin
Starting MySQL [ OK ]
这个日志文件的默认文件名是主机名加-bin后缀，然后有一个分辨日志顺序的数字。 发现个问题，我在5.0.40-log版本的MySQL中的二进制日志文件不是上面说的那样的文件名而是mysql-bin后面再带一个分辨日志顺序的数字，比如下面蓝色字体标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile1 mysql-bin.000003 可以使用mysql>flush logs;命令刷新日志，即关闭当前日志并使用一个新的顺序编号创建新日志。 特别注意：若是设置了主副服务器以便进行复制，必须启动二进制更新日志。 六、日志截止与循环：如果服务器很忙，日志很快就会变得很大甚至充满整个磁盘，所以日志必须通过截止日期和循环进行管理，确保日志成为你的助手而不是累赘。 日志循环是通过创建一个日志文件的有限数字实现，然后进行覆盖替换，这样每次都会剔除最旧的文件。常见的方式是每天创建新日志，在一个循环中使用7轮，每轮为一周中的一天。 如果使用的是数字编码的日志文件，编号后的日志可以设置截止日期来进行管理，截止日期一到，这些文件将被删除或归档，这就是日志截止。 以上两方法的具体实现可以参考MySQL手册。 2、检查和修理表： 一、检查表错误：使用命令myisamchk table-file-name，由于myisamchk需要专门访问该表所以最好在运行之前让服务器脱机或是停机；一个检查的具体例子： [root@host175 ~]# /usr/local/mysql/bin/myisamchk -e /usr/local/mysql/var/WorkDB/users
Checking MyISAM file: /usr/local/mysql/var/WorkDB/users
Data records: 1 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references
这是一个带-e参数的最慢速度、最完整的扩展选项检查，如果使用扩展选项检查没有发现错误就可以确定该表没有问题。 myisamchk的缺点是在进行检查时会锁定客户端，但是客户端却不能在运行myisamchk时锁住被检查表，在比较大的表中，这样的检查可能要花费数分钟的时间，这可能会导致一些问题。有两个替代方法： 第一，设置myisamchk可以使用很大的缓存空间，可以使用myisamchk --help查看修改不同缓存空间的选项； 第二，使用不同的方法检查表，比如：check table table-name，如下所示： mysql> check table users;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| WorkDB.users | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)
这种方法中还可以在check table命令中添加关键字FAST、MEDIUM、EXTENDED和CHANGED，以便执行希望使用的检查种类。 第三，常用myisamchk而不是check table命令的原因是，在使用check table命令是MySQL服务器承担了所有工作，若是服务器关闭，check table命令就没法使用了；而myisamchk在文件级工作，即使服务器关闭也能工作，并且不给MySQL服务器带去额外压力。myisamchk不能在InnoDB和BDB表上工作，在这些类型的表上面需要使用check table命令。 二、修理表： myisamchk工具还可以用来修理被毁坏的表，使用-r选项加表名就可以开始修复过程，即： myisamchk -r table-name 还有其他修复选项，其中-r是修理和恢复，属于标准恢复方法； -o是恢复的安全模式，属于慢速、完全恢复方法； -q是快速恢复方法，只检查索引，不检查数据文件。 由于myisamchk在文件级工作，所以在操作时需要所有锁被删除并且所有客户被排除才行。 在检查表时，首先尝试最快的选项-F，如果不行再使用比较慢的选项-m，最后再考虑最慢的完全的检查选项-e。如果在运行最强的修复可能性后问题仍然存在，那么将不得不从备份文件恢复该表了。 修复表可用的另一个选项是repair table命令，格式：repair table table-name,.... 还可以使用选项QUICK或EXTENDED关键字来设置修复类型，比如： mysql>repair table users QUICK; 三、优化表： 使用命令optimize table table-name,....，这个命令的作用就好像母亲整理你的房间，除了抛弃旧的、被删除的文件以外，还可以排序有索引的文件，把变量表行的内容设置到连续空间中，更新统计信息，但是必须注意，这个表已经被锁定，在进行操作时不能被客户端访问。
Starting MySQL [ OK ]
这个日志的文件名一般是以.err为结尾的。比如我机器上的host175.err，如下蓝色字体标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB ibdata1 mysql-bin.000008 mysql-bin.000017
greedland mysql mysql-bin.000009 mysql-bin.000018
host.test.com.err mysql-bin.000001 mysql-bin.000010 mysql-bin.000019
host.test.com.pid mysql-bin.000002 mysql-bin.000011 mysql-bin.000020
host175.err mysql-bin.000003 mysql-bin.000012 mysql-bin.000021
host175.test.com.err mysql-bin.000004 mysql-bin.000013 mysql-bin.000022
host175.test.com.pid mysql-bin.000005 mysql-bin.000014 mysql-bin.000023
ib_logfile0 mysql-bin.000006 mysql-bin.000015 mysql-bin.index
ib_logfile1 mysql-bin.000007 mysql-bin.000016 test
具体内容如下，这是一部分： [root@host175 ~]# more /usr/local/mysql/var/host175.err
081211 17:03:35 InnoDB: Started; log sequence number 0 43665
081211 17:03:35 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.0.40-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
好像也记录了一些启动信息，当然我这里没有错误消息，而是一些指示性消息。 二、查询日志：这是另外一个有用的日志，因为这里保存了客户机发给服务器的每个查询的踪迹，还可以显示客户机连接服务器以及这些客户机所作操作的细节内容。若是希望监控以排错为目的的活动，就应该激活查询日志选项--log，也是加在服务器启动命令行或选项文件中，如下： [root@host175 ~]# /etc/rc.d/init.d/mysqld start --log
Starting MySQL [ OK ]
这个日志的文件名是主机名加.log扩展名的格式，比如我机器上的host175.log，下面蓝色字体标记的文件名： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile0 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013 mysql-bin.000017 mysql-bin.000021 mysql-bin.index greedland host175.log ib_logfile1 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.000014 mysql-bin.000018 mysql-bin.000022 test
host.test.com.err host175.test.com.err ibdata1 mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql-bin.000015 mysql-bin.000019 mysql-bin.000023 host.test.com.pid host175.test.com.pid mysql 查询日志部分内容示例如下： [root@host175 ~]# more /usr/local/mysql/var/host175.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
081211 17:22:52 1 Connect Access denied for user 'fuhao'@'localhost' (using password: NO)
081211 17:23:12 2 Connect Access denied for user 'root'@'localhost' (using password: YES)
081211 17:23:19 3 Connect root@localhost on WorkDB
3 Query select @@version_comment limit 1
081211 17:23:24 3 Query show tables
081211 17:23:31 3 Query select * from users
这里面对每一个操作记录的很详细。 三、慢查询日志：这文件中记录了超过预先设定时间量（根据long_query_time变量确定）的所有查询，任何超过这个值的查询都将列在这个日志文件中。若是需要查找优化性能的办法，可以查查这里的问题。 我们可以如下这样查看long_query_time变量的值： mysql> show variables like 'long_query_time';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
我这里的缓存查询时间限制设置是10秒，也就是说查询超过10秒的都会在这个慢查询日志中记录下来； 慢查询日志使用--log-slow-queries选项来激活，如下所示： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-slow-queries
Starting MySQL [ OK ]
该日志的默认文件名是主机名后带-slow的后缀以及.log扩展名，比如下面蓝色字体所标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile1 mysql-bin.000003 mysql-bin.000008 mysql-bin.000013 mysql-bin.000018 mysql-bin.000023
greedland host175.log ibdata1 mysql-bin.000004 mysql-bin.000009 mysql-bin.000014 mysql-bin.000019 mysql-bin.000024
host.test.com.err host175.test.com.err mysql mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 mysql-bin.000020 mysql-bin.000025
host.test.com.pid host175.test.com.pid mysql-bin.000001 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016 mysql-bin.000021 mysql-bin.index
host175-slow.log ib_logfile0 具体的文件内容格式如下： [root@host175 ~]# more /usr/local/mysql/var/host175-slow.log
/usr/local/mysql/libexec/mysqld, Version: 5.0.40-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
由于我这里不可能有超过10秒的查询所以下面就没数据了。 四、更新日志：记录了改变具体表的所有查询。与update、create table和drop table同时使用的insert、replace、delete、grant和revoke语句都属于改变具体表的查询，但是select语句不是。 要让服务器写更新日志必须在服务器启动时带选项--log-update，比如： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-update
Starting MySQL [ OK ]
如果需要重新构建一个表，但是该表在最近一次备份之后又进行了修改，这个日志可以发挥作用。在数据库受到破坏的情况下，可以根据备份进行恢复，然后按照更新日志重新创建记录到查询，这样可以把系统恢复到受破坏之前的状况。 五、二进制更新日志：在版本3.23.14以后版本支持，这是一种更为有效的保存日志数据的格式，它比标准更新日志记录得更详细，名为mysqlbinlog实用程序可以把二进制日志文件转换回文本文件格式从而可以进行阅读。 在启动MySQL时使用--log-bin选项可以创建二进制更新日志文件，比如： [root@host175 ~]# /usr/local/mysql/share/mysql/mysql.server start --log-bin
Starting MySQL [ OK ]
这个日志文件的默认文件名是主机名加-bin后缀，然后有一个分辨日志顺序的数字。 发现个问题，我在5.0.40-log版本的MySQL中的二进制日志文件不是上面说的那样的文件名而是mysql-bin后面再带一个分辨日志顺序的数字，比如下面蓝色字体标记的： [root@host175 ~]# ls /usr/local/mysql/var/
WorkDB host175.err ib_logfile1 mysql-bin.000003 可以使用mysql>flush logs;命令刷新日志，即关闭当前日志并使用一个新的顺序编号创建新日志。 特别注意：若是设置了主副服务器以便进行复制，必须启动二进制更新日志。 六、日志截止与循环：如果服务器很忙，日志很快就会变得很大甚至充满整个磁盘，所以日志必须通过截止日期和循环进行管理，确保日志成为你的助手而不是累赘。 日志循环是通过创建一个日志文件的有限数字实现，然后进行覆盖替换，这样每次都会剔除最旧的文件。常见的方式是每天创建新日志，在一个循环中使用7轮，每轮为一周中的一天。 如果使用的是数字编码的日志文件，编号后的日志可以设置截止日期来进行管理，截止日期一到，这些文件将被删除或归档，这就是日志截止。 以上两方法的具体实现可以参考MySQL手册。 2、检查和修理表： 一、检查表错误：使用命令myisamchk table-file-name，由于myisamchk需要专门访问该表所以最好在运行之前让服务器脱机或是停机；一个检查的具体例子： [root@host175 ~]# /usr/local/mysql/bin/myisamchk -e /usr/local/mysql/var/WorkDB/users
Checking MyISAM file: /usr/local/mysql/var/WorkDB/users
Data records: 1 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references
这是一个带-e参数的最慢速度、最完整的扩展选项检查，如果使用扩展选项检查没有发现错误就可以确定该表没有问题。 myisamchk的缺点是在进行检查时会锁定客户端，但是客户端却不能在运行myisamchk时锁住被检查表，在比较大的表中，这样的检查可能要花费数分钟的时间，这可能会导致一些问题。有两个替代方法： 第一，设置myisamchk可以使用很大的缓存空间，可以使用myisamchk --help查看修改不同缓存空间的选项； 第二，使用不同的方法检查表，比如：check table table-name，如下所示： mysql> check table users;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| WorkDB.users | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)
这种方法中还可以在check table命令中添加关键字FAST、MEDIUM、EXTENDED和CHANGED，以便执行希望使用的检查种类。 第三，常用myisamchk而不是check table命令的原因是，在使用check table命令是MySQL服务器承担了所有工作，若是服务器关闭，check table命令就没法使用了；而myisamchk在文件级工作，即使服务器关闭也能工作，并且不给MySQL服务器带去额外压力。myisamchk不能在InnoDB和BDB表上工作，在这些类型的表上面需要使用check table命令。 二、修理表： myisamchk工具还可以用来修理被毁坏的表，使用-r选项加表名就可以开始修复过程，即： myisamchk -r table-name 还有其他修复选项，其中-r是修理和恢复，属于标准恢复方法； -o是恢复的安全模式，属于慢速、完全恢复方法； -q是快速恢复方法，只检查索引，不检查数据文件。 由于myisamchk在文件级工作，所以在操作时需要所有锁被删除并且所有客户被排除才行。 在检查表时，首先尝试最快的选项-F，如果不行再使用比较慢的选项-m，最后再考虑最慢的完全的检查选项-e。如果在运行最强的修复可能性后问题仍然存在，那么将不得不从备份文件恢复该表了。 修复表可用的另一个选项是repair table命令，格式：repair table table-name,.... 还可以使用选项QUICK或EXTENDED关键字来设置修复类型，比如： mysql>repair table users QUICK; 三、优化表： 使用命令optimize table table-name,....，这个命令的作用就好像母亲整理你的房间，除了抛弃旧的、被删除的文件以外，还可以排序有索引的文件，把变量表行的内容设置到连续空间中，更新统计信息，但是必须注意，这个表已经被锁定，在进行操作时不能被客户端访问。
相关阅读 更多 +
