删除mysql数据库能恢复吗误删除了自带的mysql数据库后该怎么恢复

导读如果mysql数据库系统自带的mysql数据库被误删了，应该如何恢复？其实操作方法比较简答，今天用实验的方式分享一下，接下来我们就来聊聊关于删除mysql数....

如果mysql数据库系统自带的mysql数据库被误删了，应该如何恢复？其实操作方法比较简答，今天用实验的方式分享一下，接下来我们就来聊聊关于删除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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2151Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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=457902021-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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.27-logCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 -lrttotal 262228drwxr-x--- 2 mysql mysql 8192 Sep 9 17:14 performance_schemadrwxr-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.000002drwxr-x--- 2 mysql mysql 4096 Sep 9 17:23 xxl_jobdrwxr-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.cnfdrwxr-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 -lrttotal 4drwxrwxr-x 5 root root 58 Mar 10 2021 xtrabackupdrwxr-xr-x 6 root root 4096 Jun 8 10:27 redisdrwxr-xr-x 2 root root 54 Jun 8 11:00 xxl_appdrwxr-xr-x 3 root root 21 Jun 8 11:05 applogsdrwxr-xr-x 2 root root 93 Jun 9 18:02 server_appdrwxr-xr-x 2 root root 25 Jun 10 14:46 databackdrwxr-xr-x 3 root root 136 Jun 11 14:03 softwaredrwxr-xr-x 11 root root 335 Jun 11 16:47 databacknewdrwxr-xr-x 12 mysql mysql 256 Sep 9 21:42 mysqldrwxr-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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: 1permission: NULL1 row in set (0.00 sec)mysql> #经过验证数据可用，mysql数据库可用

【4】数据恢复注意事项

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