mysql还原数据库怎么操作

前言如果mysql数据库系统自带的mysql数据库被误删了，应该如何恢复？其实操作方法比较简答，今天用实验的方式分享一下1、实验环境信息实验环境信息：mysql版本5.7.27实验系统 centos 7.9.2009mysql basedir /data/mysqlmysql datadir /data/mysqldata2、删除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> ｄｒｏｐ 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 ‘‘;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 stopShutting down MySQL.. SUCCESS!【3】数据恢复#将新数据目录中生成的 mysql performance_schemasys数据目录拷贝至 /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 Sep9 17:14 performance_schemadrwxr-x--- 2 mysql mysql 8192 Sep9 17:14 sys-rw-r----- 1 mysql mysql177 Sep9 17:14 mysql-bin.000001-rw-r----- 1 mysql mysql426 Sep9 17:22 mysql-bin.000002drwxr-x--- 2 mysql mysql 4096 Sep9 17:23 xxl_jobdrwxr-x--- 2 mysql mysql 4096 Sep9 17:24 server_info_collection-rw-r----- 1 mysql mysql177 Sep9 17:26 mysql-bin.000003-rw-r----- 1 mysql mysql56 Sep9 17:29 auto.cnfdrwxr-x--- 2 rootroot20 Sep9 17:29 test_12345-rw-r----- 1 rootroot136 Sep9 17:30 master.info-rw-r----- 1 rootroot61 Sep9 17:30 relay-log.info-rw-r----- 1 rootroot25 Sep9 17:30 db105-relay-bin.index-rw-r----- 1 rootroot154 Sep9 17:30 db105-relay-bin.000053-rw-r----- 1 mysql mysql154 Sep9 17:32 mysql-bin.000004-rw-r----- 1 mysql mysql844 Sep9 21:12 mysql-bin.000005-rw-r----- 1 mysql mysql177 Sep9 21:13 mysql-bin.000006-rw-r----- 1 mysql mysql177 Sep9 21:14 mysql-bin.000007-rw-r----- 1 mysql mysql177 Sep9 21:15 mysql-bin.000008-rw-r----- 1 mysql mysql177 Sep9 21:16 mysql-bin.000009-rw-r----- 1 mysql mysql177 Sep9 21:17 mysql-bin.000010-rw-r----- 1 mysql mysql177 Sep9 21:18 mysql-bin.000011-rw-r----- 1 mysql mysql177 Sep9 21:19 mysql-bin.000012-rw-r----- 1 mysql mysql177 Sep9 21:20 mysql-bin.000013-rw-r----- 1 mysql mysql177 Sep9 21:21 mysql-bin.000014-rw-r----- 1 mysql mysql177 Sep9 21:22 mysql-bin.000015-rw-r----- 1 mysql mysql177 Sep9 21:23 mysql-bin.000016-rw-r----- 1 mysql mysql177 Sep9 21:24 mysql-bin.000017-rw-r----- 1 mysql mysql177 Sep9 21:25 mysql-bin.000018-rw-r----- 1 mysql mysql177 Sep9 21:26 mysql-bin.000019-rw-r----- 1 mysql mysql177 Sep9 21:27 mysql-bin.000020-rw-r----- 1 mysql mysql177 Sep9 21:28 mysql-bin.000021-rw-r----- 1 mysql mysql418 Sep9 21:29 mysql-bin.index-rw-r----- 1 mysql mysql177 Sep9 21:29 mysql-bin.000022-rw-r----- 1 mysql mysql545 Sep9 21:29 ib_buffer_pool-rw-r----- 1 mysql mysql 67108864 Sep9 21:29 ib_logfile1-rw-r----- 1 mysql mysql 67108864 Sep9 21:29 ib_logfile0-rw-r----- 1 mysql mysql 79691776 Sep9 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-x5 rootroot58 Mar 102021 xtrabackupdrwxr-xr-x6 rootroot4096 Jun8 10:27 redisdrwxr-xr-x2 rootroot54 Jun8 11:00 xxl_appdrwxr-xr-x3 rootroot21 Jun8 11:05 applogsdrwxr-xr-x2 rootroot93 Jun9 18:02 server_appdrwxr-xr-x2 rootroot25 Jun 10 14:46 databackdrwxr-xr-x3 rootroot136 Jun 11 14:03 softwaredrwxr-xr-x 11 rootroot335 Jun 11 16:47 databacknewdrwxr-xr-x 12 mysql mysql256 Sep9 21:42 mysqldrwxr-xr-x6 mysql mysql318 Sep9 21:42 mysqldata[root@db105 data]# chown -R mysql:mysql mysqldata[root@db105 data]##启动数据库，验证数据是否正常使用[root@db105 data]# service mysqld startStarting 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> ｓｅｌｅｃｔ 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> ｓｅｌｅｃｔ * from xxl_job_user limit 1 \G*************************** 1. row ***************************id: 1username: adminpassword: 0571749e2ac330a7455809c6b0e7af90role: 1permission: NULL1 row in set (0.00 sec)mysql> #经过验证数据可用，mysql数据库可用【4】数据恢复注意事项#mysql数据库恢复后，需要重新创建数据库账号，原有数据库账户因数据库的删除已不存在