前言
如果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数据库恢复后，需要重新创建数据库账号，原有数据库账户因数据库的删除已不存在
