mysql ddl不阻塞dml_修复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]$

六、到此全部处理完成！