下你所需,载你所想!
IT技术源码资料下载网站

修复mysql.db系统表不能DDL/DML的问题

:其他软件 2020-07-30 23:31:40

修复mysql.db系统表不能DDL/DML的问题

修复 mysql.db 表相关问题(问题如下)
描述问题:系统表已经损坏,不能 DDL/DML
[uniapply@DBA0001 ~]$ sudo /use/sbin/mysql -uroot -p'**********'
mysql> create user 'biller'@'134.77.19.107' identified by '^B$WEF0Brqo%PZhI';
ERROR 1728 (HY000): Cannot load from mysql.db. The table is probably corrupted
mysql> quit;
[uniapply@DBA0001 ~]$
一、解决问题(思路)
[uniapply@DBA0001 ~]$ sudo /use/bin/mysqldump --all-databases --set-gtid-purged=OFF -uroot -p'**********' > /tmp/all.sql
[uniapply@DBA0001 ~]$ sudo /use/bin/mysqldump mysql user --set-gtid-purged=OFF -uroot -p'**********' > /tmp/mysql-user.sql
[root@itpuxdb01 ~]#
二、查看创建表的 DDL 语句
[uniapply@DBA0001 ~]$ sudo /use/sbin/mysql -uroot -p'**********'
mysql> show create table mysql.db;
mysql> drop table mysql.db;
mysql> commit;
mysql> quit;
[uniapply@DBA0001 ~]$
三、创建系统表 And 修复系统表
[uniapply@DBA0001 ~]$ sudo /use/sbin/mysql -uroot -p'**********'
CREATE TABLE `mysql`.`db (
-> `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
-> `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
-> `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
-> `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
-> PRIMARY KEY (`Host`,`Db`,`User`) USING BTREE,
-> KEY `User` (`User`) USING BTREE
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges';
Query OK, 0 rows affected (0.00 sec)
mysql> REPAIR TABLE mysql.db;
mysql> flush privileges;
mysql> quit;
[uniapply@DBA0001 ~]$
四、手动更新 MySQL 系统相关
[uniapply@DBA0001 ~]$ sudo /use/sbin/mysql_upgrade -uroot -p'*********'
五、备份恢复
[uniapply@DBA0001 ~]$ cd /tmp/
[uniapply@DBA0001 tmp]$ sudo cat mysql-user.sql | grep INSERT > /tmp/111.sql
[uniapply@DBA0001 tmp]$ sudo /use/sbin/mysql -uroot -p'**********'
mysql> source /tmp/111.sql
mysql> commit;
mysql> quit;
[uniapply@DBA0001 tmp]$
六、到此全部处理完成!

TAG:

相关阅读

最新推荐