mysql1142_转: MySQL5.7 ERROR 1142 (42000)问题[通俗易懂]

1，mysql全库导入报错

[root@dev_121_21 ~]# mysql–socket=/usr/local/mysql/mysql.sock –default-character-set=utf8

ERROR 1142 (42000) at line 266079: SELECT,LOCK TABLES command denied to user ‘root’@’localhost’ for table ‘accounts’

[root@dev_121_21 ~]#

2，查看accounts表

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运行的时候，这个表是不允许被锁定的；

而这个性能库不导入也没用关系，不影响数据一致性，那我们备份时跳过该库即可，需要重新备份，然后再重新导入。

3，重新备份的2种方案

(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

4，再次mysql导入成功

[root@dev_121_21 ~]# time mysql–socket=/usr/local/mysql/mysql.sock –default-character-set=utf8 -uroot -p

Enter password:

real         20m10.619s

user        2m35.341s

sys          1m13.020s

[root@dev_121_21 ~]#

然后mysql导入，导入成功，所以在备份的时候，记得忽略这个存储库表列基础信息的information_schema库和mysql运行信息的performance_schema性能库。

———————