昨天我把MySQL 升级到了最新的开发版5.6.5,本来一切正常。直到今天发现我的数据库每夜备份挂了。
见下：
[root@PowerPC ~]# ls /home/ftp/xzx/sql -lh
total 7.3M
-rw------- 1 ftp web 796K Apr 9 00:00 mysql_1333900801.sql.bz2
-rw------- 1 ftp web 803K Apr 10 00:00 mysql_1333987201.sql.bz2
-rw------- 1 ftp web 809K Apr 11 00:00 mysql_1334073602.sql.bz2
-rw------- 1 ftp web 817K Apr 12 00:00 mysql_1334160001.sql.bz2
-rw------- 1 ftp web 822K Apr 13 00:00 mysql_1334246401.sql.bz2
-rw------- 1 ftp web 825K Apr 14 00:00 mysql_1334332802.sql.bz2
-rw------- 1 ftp web 831K Apr 15 00:00 mysql_1334419201.sql.bz2
-rw------- 1 ftp web 834K Apr 16 00:00 mysql_1334505601.sql.bz2
-rw------- 1 ftp web 14 Apr 17 00:00 mysql_1334592002.sql.bz2

最后明显不正常.

我忽然发现，是由于我没有给专门用来备份数据库的用户dumper 授权的原因~ 囧

赶紧按照my.cnf 里的配置，授予dumper 的 select 权限.

[mysqldump]
user = dumper
password = XXXXX
quick
max_allowed_packet = 16M

运行下crontab 里面的dump脚本,
[root@PowerPC ~]# /usr/local/bin/mysqldump -A -C -u dumper | bzip2 -9 > /home/ftp/xzx/sql/mysql_`date +\%s`.sql.bz2
mysqldump: Got error: 1044: Access denied for user 'dumper'@'localhost' to database 'bbpress' when using LOCK TABLES

还是报错。忘了给lock tables 权限了~

继续授权：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbpress |
| lighttpd |
| mysql |
| performance_schema |
| phpbb |
| typecho |
| vsftpd |
+--------------------+
8 rows in set (0.00 sec)

mysql> grant lock tables on vsftpd.* to dumper@localhost identified by 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> grant lock tables on typecho.* to dumper@localhost identified by 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> grant lock tables on phpbb.* to dumper@localhost identified by 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> grant lock tables on lighttpd.* to dumper@localhost identified by 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> grant lock tables on bbpress.* to dumper@localhost identified by 'XXXXX';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

再次测试脚本：
[root@PowerPC ~]# /usr/local/bin/mysqldump -A -C -u dumper | bzip2 -9 > /home/ftp/xzx/sql/mysql_`date +\%s`.sql.bz2

查看备份SQL文件：
[root@PowerPC ~]# ls /home/ftp/xzx/sql -lh
total 7.3M
-rw------- 1 ftp web 796K Apr 9 00:00 mysql_1333900801.sql.bz2
-rw------- 1 ftp web 803K Apr 10 00:00 mysql_1333987201.sql.bz2
-rw------- 1 ftp web 809K Apr 11 00:00 mysql_1334073602.sql.bz2
-rw------- 1 ftp web 817K Apr 12 00:00 mysql_1334160001.sql.bz2
-rw------- 1 ftp web 822K Apr 13 00:00 mysql_1334246401.sql.bz2
-rw------- 1 ftp web 825K Apr 14 00:00 mysql_1334332802.sql.bz2
-rw------- 1 ftp web 831K Apr 15 00:00 mysql_1334419201.sql.bz2
-rw------- 1 ftp web 834K Apr 16 00:00 mysql_1334505601.sql.bz2
-rw------- 1 ftp web 14 Apr 17 00:00 mysql_1334592002.sql.bz2
-rw-r--r-- 1 root root 504 Apr 17 16:19 mysql_1334650776.sql.bz2
-rw-r--r-- 1 root root 837K Apr 17 16:27 mysql_1334651252.sql.bz2

恩，这次正常了。以后可千万不能忘了。