MYSQL无备份情况下恢复误删除的user权限表

SEian.G

发布于 2021-03-03 10:04:23

2.5K0

文章被收录于专栏：SEian.G学习记录SEian.G学习记录

问题背景

前几天客户反馈，误删除了权限表，导致无法连接到实例中了，但是又没有备份，咨询要怎么去恢复；

针对上述的这种情况，下面给出具体的恢复方法；

（备份重于一切！备份重于一切！备份重于一切！！！重要的事情说三遍）

环境说明： MYSQL 5.7版本端口：3306和3309 说明：3309是故障的实例，3306是协助在没有备份的情况下做恢复

下面开始故障模拟和恢复：

1、查看一下目前user表中存在的用户

2、模拟用户误删除用户表

root@localhost [(none)]>drop table mysql.user;
Query OK, 0 rows affected (0.00 sec)
 
root@localhost [(none)]>select user,host from mysql.user;
ERROR 1146 (42S02): Table 'mysql.user' doesn't exist

。

。（重点来了）

。

3、换一个会话，其实依旧是可以登录（注意，这里的mysql进程目前依旧是启动的），因为这里考虑到了客户可能会杀掉mysql进程，所以下面我们分两种情况去讨论：

[root@VM_54_118_centos ~]# mysql -u root -pXXXXXXXX -S /tmp/mysql3309.sock

第一种情况：误删除了user表，进程是启动的（只要用户不手动的去kill进程，进程是运行的）第二种情况：误删除了user表，进程被杀掉了，mysql服务处理停机状态，那么重启实例，肯定会报错（后面可以看到）

第一种情况：MYSQL服务进程是启动的

恢复方法：

从其他的运行好的数据库或官方文档找到mysql5.7版本user表结构，然后手动的进行创建；

CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(32) 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',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_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',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_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',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_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',
`Create_user_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',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
`authentication_string` text COLLATE utf8_bin,
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`password_last_changed` timestamp NULL DEFAULT NULL,
`password_lifetime` smallint(5) unsigned DEFAULT NULL,
`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

然后插入初始化数据库之后，系统默认的存在的用户，如下：

INSERT INTO `user` VALUES ('%','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'mysql_native_password','*E94A9AEB5F3D9594EE8BFEBAE2E75B1E0694484A','N','2019-01-12 04:00:10',NULL,'N'),('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-14 05:18:03',NULL,'Y'),('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-14 05:18:03',NULL,'Y');

此时，user表恢复成功了，但是其他自建的用户就需要重新手动进行创建了，这就是不做备份的代价，出来混总是要还……

第二种情况：MYSQL服务进程停掉了，无法重启成功

恢复方法：

如果mysql进程已经听掉了，那么就会出现重启失败的问题，下面的错误日志中记录

2019-03-14T05:38:30.545238Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
2019-03-14T05:38:30.545296Z 0 [ERROR] Aborting

下面来操作恢复：

从一个运行完好的实例（也就是上述环境中3306端口的实例）中，将数据库文件下的user表的物理文件拷贝到故障实例的数据文件目录下，（5.7版本user表是myisam引擎，会存在三个物理文件）

[root@VM_54_118_centos ~]# ll /data/mysql/mysql_3306/data/mysql/user*
-rw-r----- 1 mysql mysql 10816 Nov 2 12:07 /data/mysql/mysql_3306/data/mysql/user.frm
-rw-r----- 1 mysql mysql 1072 Feb 24 14:26 /data/mysql/mysql_3306/data/mysql/user.MYD
-rw-r----- 1 mysql mysql 4096 Mar 4 17:16 /data/mysql/mysql_3306/data/mysql/user.MYI
 
[root@VM_54_118_centos ~]# cp /data/mysql/mysql_3306/data/mysql/user* /data/mysql/mysql_3309/data/mysql/
 
[root@VM_54_118_centos ~]# ll /data/mysql/mysql_3309/data/mysql/user* 
-rw-r----- 1 root root 10816 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.frm
-rw-r----- 1 root root 1072 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.MYD
-rw-r----- 1 root root 4096 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.MYI
 
# 注意：拷贝完成之后一定要修改文件的属主和属组
[root@VM_54_118_centos ~]# chown mysql:mysql /data/mysql/mysql_3309/data/mysql/user*
 
[root@VM_54_118_centos ~]# ll /data/mysql/mysql_3309/data/mysql/user* 
-rw-r----- 1 mysql mysql 10816 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.frm
-rw-r----- 1 mysql mysql 1072 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.MYD
-rw-r----- 1 mysql mysql 4096 Mar 14 13:47 /data/mysql/mysql_3309/data/mysql/user.MYI

复制完成之后，重新启动mysql进程

[root@VM_54_118_centos ~]# mysqld --datadir=/data/mysql/mysql_3309/data/ --socket=/tmp/mysql3309.sock --port=3309 --server_id=3309 --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3309/log/slow.log --log-error=/data/mysql/mysql_3309/log/error.log --binlog_format=row --log-bin=/data/mysql/mysql_3309/log/mysql3309.bin --gtid-mode=ON --enforce-gtid-consistency=ON --log-slave-updates=ON &
[1] 17339

mysql服务进程启动成功

[root@VM_54_118_centos ~]# ps -ef | grep 3309
root 7385 31429 0 13:02 pts/4 00:00:00 mysql -u root -px xxxxxxxxx -S /tmp/mysql3309.sock
mysql 17339 15178 3 13:49 pts/5 00:00:00 mysqld --datadir=/data/mysql/mysql_3309/data/ --socket=/tmp/mysql3309.sock --port=3309 --server_id=3309 --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3309/log/slow.log --log-error=/data/mysql/mysql_3309/log/error.log --binlog_format=row --log-bin=/data/mysql/mysql_3309/log/mysql3309.bin --gtid-mode=ON --enforce-gtid-consistency=ON --log-slave-updates=ON

但是可以发现，恢复成功后的user表是您拷贝实例（也就是3306端口实例）的user表哦。。。不是原来的；