ERROR 1142 (42000)
1.导入时报错ERROR 1142 (42000) at line 266079: SELECT,LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts'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: 128
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2021-12-14 15:19:29
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
mysql> show create table accounts;
| Table | Create Table
| accounts | CREATE TABLE `accounts` (
`USER` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL,
`CURRENT_CONNECTIONS` bigint NOT NULL,
`TOTAL_CONNECTIONS` bigint NOT NULL,
UNIQUE KEY `ACCOUNT` (`USER`,`HOST`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci5.5以后,mysql的performance_schema库用的是特殊的存储引擎PERFORMANCE_SCHEMA,在mysql运行的时候,这个表是不允许被锁定的;
而这个性能库不导入也没用关系,不影响数据一致性,那我们备份时跳过该库即可,需要重新备份,然后再重新导入。
3.解决方案
1.用--skip-lock-tables performance_schema
页:
[1]