MySQL数据库的维护处理

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
[email='fuhao'@'localhost']'fuhao'@'localhost'[/email]
(using password: NO)
081211 17:23:12       2 Connect     Access denied for user
[email='root'@'localhost']'root'@'localhost'[/email]
(using password: YES)
081211 17:23:19       3 Connect     
[email=root@localhost]root@localhost[/email]
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,....，这个命令的作用就好像母亲整理你的房间，除了抛弃旧的、被删除的文件以外，还可以排序有索引的文件，把变量表行的内容设置到连续空间中，更新统计信息，但是必须注意，这个表已经被锁定，在进行操作时不能被客户端访问。


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/87863/showart_1714377.html