About mysqldump of MySQL 5.5 backup information_schema & performance_schema databases

MySQL 5.5多出了两个新数据库 information_schema &–all-databases–all-databases performance_schema

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+

但是用mysqldump去备份这两个数据库的时候都会报错

mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root’@’localhost’ for table ‘cond_instances’ when using LOCK TABLES

用–all-databases备份时也不会备份这两个数据库
官方文档里解释到:
http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html

总结一下,如果一定要用mysqldump去备份这两个数据库不是不可以,但是得disable lock tables。 我用的是:
–database information_schema –lock-tables=0

也可以把所有的数据库都列出在一个文件中,去掉information_schema和performance_schema。

以上内容转自http://www.blogjava.net/miaoyachun/archive/2012/05/11/377894.html

附备份脚本中的相关语句,摘自《猫大的最新版vps备份脚本
例如

/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PASS -B -N -e 'SHOW DATABASES' | xargs > mysqldata
sed -i 's/information_schema //g' mysqldata
sed -i 's/performance_schema //g' mysqldata
sed -i 's/mysql //g' mysqldata
for db in `cat mysqldata`; do
    (/usr/local/mysql/bin/mysqldump -u$MYSQL_USER -p$MYSQL_PASS --databases ${db}> ${db}.sql)
done
0 Comments
Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.