[root@dev_121_21 ~]# mysql--socket=/usr/local/mysql/mysql.sock --default-character-set=utf8</home/alldb_20160605.sql
ERROR 1142 (42000) at line 266079: SELECT,LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts'
[root@dev_121_21 ~]#
mysql> SELECT * FROM information_schema.`TABLES` t WHERE t.`TABLE_NAME`='accounts'\G; *************************** 1. ROW *************************** TABLE_CATALOG: def TABLE_SCHEMA: performance_schema TABLE_NAME: accounts TABLE_TYPE: BASE TABLE ENGINE: PERFORMANCE_SCHEMA VERSION: 10 ROW_FORMAT: FIXED TABLE_ROWS: 1000 AVG_ROW_LENGTH: 0 DATA_LENGTH: 0 MAX_DATA_LENGTH: 0 INDEX_LENGTH: 0 DATA_FREE: 0 AUTO_INCREMENT: NULL CREATE_TIME: NULL UPDATE_TIME: NULL CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci CHECKSUM: NULL CREATE_OPTIONS: TABLE_COMMENT: 1ROWINSET (0.00 sec)
ERROR: NOQUERY specified
mysql>
mysql> USE performance_schema DATABASE CHANGED mysql> SHOWCREATETABLE accounts; +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TABLE | CREATETABLE | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | accounts | CREATETABLE `accounts` ( `USER` CHAR(16) CHARACTERSET utf8 COLLATE utf8_bin DEFAULTNULL, `HOST` CHAR(60) CHARACTERSET utf8 COLLATE utf8_bin DEFAULTNULL, `CURRENT_CONNECTIONS` BIGINT(20) NOTNULL, `TOTAL_CONNECTIONS` BIGINT(20) NOTNULL ) ENGINE=PERFORMANCE_SCHEMA DEFAULTCHARSET=utf8 | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1ROWINSET (0.00 sec)
mysql>
|
看到ENGINE:PERFORMANCE_SCHEMA，accounts表不是正常的innodb存储引擎表，查了下资料，发现是由于5.5以后，mysql的performance_schema库用的是特殊的存储引擎PERFORMANCE_SCHEMA，在mysql运行的时候，这个表是不允许被锁定的；
而这个性能库不导入也没用关系，不影响数据一致性，那我们备份时跳过该库即可，需要重新备份，然后再重新导入。
（1）运用--skip-lock-tables导出的时候不lock tables，然后mysql导入：
mysql -uroot--password="root@plcc0716" -e 'show databases;'|grep -E -v"Database|information_schema|test" |xargs mysqldump -uroot--password="root@plcc0716" --opt --default-character-set=utf8--extended-insert=false --single-transaction -R --flush-logs --skip-lock-tablesperformance_schema --master-data=1 --databases>/home/alldb_20160605.sql
（2）忽略该性能库（本次操作选择这种方案）
mysqldjmp、mysql -uroot--password="root@plcc0716" -e 'show databases;'|grep -E -v"Database|information_schema|performance_schema" |xargs mysqldump -uroot --password="root@plcc0716"--opt --default-character-set=utf8 --extended-insert=false --single-transaction-R --flush-logs --skip-lock-tables --master-data=1--databases>/home/alldb_20160605.sql
[root@dev_121_21 ~]# time mysql--socket=/usr/local/mysql/mysql.sock --default-character-set=utf8 -uroot -p</home/alldb_20160605.sql
Enter password:
real 20m10.619s
user 2m35.341s
sys 1m13.020s
[root@dev_121_21 ~]#
然后mysql导入，导入成功，所以在备份的时候，记得忽略这个存储库表列基础信息的information_schema库和mysql运行信息的performance_schema性能库。