【mysql】误删除了自带的mysql数据库后该怎么恢复？

前言

如果mysql数据库系统自带的mysql数据库被误删了，应该如何恢复？其实操作方法比较简答，今天用实验的方式分享一下

1、实验环境信息

实验环境信息：
        mysql版本   5.7.27
        实验系统     centos 7.9.2009
        mysql basedir /data/mysql
        mysql datadir /data/mysqldata

2、删除mysql数据库

【1】测试环境基本信息

[root@db105 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2151
Server version: 5.7.27-log MySQL Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| mysql                  |
| performance_schema     |
| server_info_collection |
| sys                    |
| test_12345             |
| xxl_job                |
+------------------------+
7 rows in set (0.00 sec)

mysql>

【2】删除mysql数据库

mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.06 sec)

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| performance_schema     |
| server_info_collection |
| sys                    |
| test_12345             |
| xxl_job                |
+------------------------+
6 rows in set (0.00 sec)

mysql>

3、数据库恢复

【1】停止数据库

[root@db105 ~]# service mysqld stop 
Shutting down MySQL.. SUCCESS!

【2】备份原有数据目录并初始化数据库

#备份原有数据目录
[root@db105 data]# cd /data
[root@db105 data]# mv mysqldata mysqldata_bak
#创建新数据目录
[root@db105 data]# mkdir mysqldata
#初始化数据文件目录
[root@db105 data]# /data/mysql/bin/mysqld --initialize --basedir=/data/mysql/ --datadir=/data/mysqldata --user=mysql

#初始化完成，数据库默认密码会输出在error.log，日志目录见/etc/my.cnf文件中指定的位置
#获取默认密码  ri>vTtCtC5Lq
[root@db105 data]# tail -f /data/mysql/dbLog/error.log 
2021-09-09T13:31:19.927328Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-09T13:31:21.406026Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-09T13:31:21.488749Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-09T13:31:21.556779Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 389f8f31-1172-11ec-aea9-0800278d6ced.
2021-09-09T13:31:21.557710Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-09T13:31:21.558768Z 1 [Note] A temporary password is generated for root@localhost: ri>vTtCtC5Lq

#登录数据库，修改数据库密码
[root@db105 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by '<root_passwd>';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> 
#停止数据库
[root@db105 data]# service mysqld stop  
Shutting down MySQL.. SUCCESS!

【3】数据恢复

#将新数据目录中生成的 mysql performance_schema  sys数据目录拷贝至 /data/mysqldata_bak 目录，将/data/mysqldata_bak 重命名为/data/mysqldata  启动数据库，验证数据是否恢复
[root@db105 data]# cd mysqldata_bak/
[root@db105 mysqldata_bak]# ls -lrt
total 262228
drwxr-x--- 2 mysql mysql     8192 Sep  9 17:14 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep  9 17:14 sys
-rw-r----- 1 mysql mysql      177 Sep  9 17:14 mysql-bin.000001
-rw-r----- 1 mysql mysql      426 Sep  9 17:22 mysql-bin.000002
drwxr-x--- 2 mysql mysql     4096 Sep  9 17:23 xxl_job
drwxr-x--- 2 mysql mysql     4096 Sep  9 17:24 server_info_collection
-rw-r----- 1 mysql mysql      177 Sep  9 17:26 mysql-bin.000003
-rw-r----- 1 mysql mysql       56 Sep  9 17:29 auto.cnf
drwxr-x--- 2 root  root        20 Sep  9 17:29 test_12345
-rw-r----- 1 root  root       136 Sep  9 17:30 master.info
-rw-r----- 1 root  root        61 Sep  9 17:30 relay-log.info
-rw-r----- 1 root  root        25 Sep  9 17:30 db105-relay-bin.index
-rw-r----- 1 root  root       154 Sep  9 17:30 db105-relay-bin.000053
-rw-r----- 1 mysql mysql      154 Sep  9 17:32 mysql-bin.000004
-rw-r----- 1 mysql mysql      844 Sep  9 21:12 mysql-bin.000005
-rw-r----- 1 mysql mysql      177 Sep  9 21:13 mysql-bin.000006
-rw-r----- 1 mysql mysql      177 Sep  9 21:14 mysql-bin.000007
-rw-r----- 1 mysql mysql      177 Sep  9 21:15 mysql-bin.000008
-rw-r----- 1 mysql mysql      177 Sep  9 21:16 mysql-bin.000009
-rw-r----- 1 mysql mysql      177 Sep  9 21:17 mysql-bin.000010
-rw-r----- 1 mysql mysql      177 Sep  9 21:18 mysql-bin.000011
-rw-r----- 1 mysql mysql      177 Sep  9 21:19 mysql-bin.000012
-rw-r----- 1 mysql mysql      177 Sep  9 21:20 mysql-bin.000013
-rw-r----- 1 mysql mysql      177 Sep  9 21:21 mysql-bin.000014
-rw-r----- 1 mysql mysql      177 Sep  9 21:22 mysql-bin.000015
-rw-r----- 1 mysql mysql      177 Sep  9 21:23 mysql-bin.000016
-rw-r----- 1 mysql mysql      177 Sep  9 21:24 mysql-bin.000017
-rw-r----- 1 mysql mysql      177 Sep  9 21:25 mysql-bin.000018
-rw-r----- 1 mysql mysql      177 Sep  9 21:26 mysql-bin.000019
-rw-r----- 1 mysql mysql      177 Sep  9 21:27 mysql-bin.000020
-rw-r----- 1 mysql mysql      177 Sep  9 21:28 mysql-bin.000021
-rw-r----- 1 mysql mysql      418 Sep  9 21:29 mysql-bin.index
-rw-r----- 1 mysql mysql      177 Sep  9 21:29 mysql-bin.000022
-rw-r----- 1 mysql mysql      545 Sep  9 21:29 ib_buffer_pool
-rw-r----- 1 mysql mysql 67108864 Sep  9 21:29 ib_logfile1
-rw-r----- 1 mysql mysql 67108864 Sep  9 21:29 ib_logfile0
-rw-r----- 1 mysql mysql 79691776 Sep  9 21:29 ibdata1
[root@db105 mysqldata_bak]# rm -rf performance_schema sys
[root@db105 mysqldata_bak]# 
[root@db105 mysqldata_bak]# cp -r ../mysqldata/performance_schema ./
[root@db105 mysqldata_bak]# cp -r ../mysqldata/sys ./
[root@db105 mysqldata_bak]# cp -r ../mysqldata/mysql ./
[root@db105 mysqldata_bak]# cd ../
[root@db105 data]# rm -rf mysqldata
[root@db105 data]# mv mysqldata_bak mysqldata
[root@db105 data]# ls -lrt
total 4
drwxrwxr-x  5 root  root    58 Mar 10  2021 xtrabackup
drwxr-xr-x  6 root  root  4096 Jun  8 10:27 redis
drwxr-xr-x  2 root  root    54 Jun  8 11:00 xxl_app
drwxr-xr-x  3 root  root    21 Jun  8 11:05 applogs
drwxr-xr-x  2 root  root    93 Jun  9 18:02 server_app
drwxr-xr-x  2 root  root    25 Jun 10 14:46 databack
drwxr-xr-x  3 root  root   136 Jun 11 14:03 software
drwxr-xr-x 11 root  root   335 Jun 11 16:47 databacknew
drwxr-xr-x 12 mysql mysql  256 Sep  9 21:42 mysql
drwxr-xr-x  6 mysql mysql  318 Sep  9 21:42 mysqldata
[root@db105 data]# chown -R mysql:mysql mysqldata
[root@db105 data]#

#启动数据库，验证数据是否正常使用
[root@db105 data]# service mysqld start  
Starting MySQL... SUCCESS! 
[root@db105 data]# 
[root@db105 data]# 
[root@db105 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-log MySQL Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| information_schema     |
| mysql                  |
| performance_schema     |
| server_info_collection |
| sys                    |
| test_12345             |
| xxl_job                |
+------------------------+
7 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> use xxl_job;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_xxl_job  |
+--------------------+
| xxl_job_group      |
| xxl_job_info       |
| xxl_job_lock       |
| xxl_job_log        |
| xxl_job_log_report |
| xxl_job_logglue    |
| xxl_job_registry   |
| xxl_job_user       |
+--------------------+
8 rows in set (0.00 sec)

mysql> select * from xxl_job_user limit 1 \G
*************************** 1. row ***************************
        id: 1
  username: admin
  password: 0571749e2ac330a7455809c6b0e7af90
      role: 1
permission: NULL
1 row in set (0.00 sec)

mysql> 
#经过验证数据可用，mysql数据库可用

【4】数据恢复注意事项

#mysql数据库恢复后，需要重新创建数据库账号，原有数据库账户因数据库的删除已不存在

posted @ 2022-12-07 15:21 我爱编程到完阅读(972) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 数据库中间件MyCat之主键值生成方式详解

· Oracle迁移Mysql的sql语句修改需创建的function代码

· mysql误删库侯的恢复

· 转-MySQL 数据库误删除后的数据恢复操作说明

· mysql 被删除如何恢复

公告

昵称：我爱编程到完
园龄： 8年6个月
粉丝： 2
关注： 8

+加关注

2025年3月

日

一

二

三

四

五

六

【mysql】误删除了自带的mysql数据库后该怎么恢复？

前言

1、实验环境信息

2、删除mysql数据库

【1】测试环境基本信息

【2】删除mysql数据库

3、数据库恢复

【1】停止数据库

【2】备份原有数据目录并初始化数据库

【3】数据恢复

【4】数据恢复注意事项

公告

搜索

常用链接

随笔档案

阅读排行榜

评论排行榜

最新评论