• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 2年前 (2019-03-19) 1291次浏览 已收录 0个评论
文章目录[隐藏]

问题背景

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

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

(备份重于一切!备份重于一切!备份重于一切!!!重要的事情说三遍)

环境说明:

MYSQL 5.7版本

端口:3306和3309

说明:3309是故障的实例,3306是协助在没有备份的情况下做恢复

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

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

MYSQL无备份情况下恢复误删除的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');

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

此时,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表哦。。。不是原来的;

MYSQL无备份情况下恢复误删除的user权限表
画外音:没备份能恢复就不错了,其他的用户自己去创建吧….

 

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

 


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MYSQL无备份情况下恢复误删除的user权限表
喜欢 (3)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址