大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE稳定放心使用
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性能库。
———————
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/187051.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...