MySQL数据库备份与恢复

1.备份数据库：
[root@localhost ~]# systemctl stop mysqld   
                     ###关闭数据库
[root@localhost ~]# mkdir /backup       
                    ###在根下新建个backup目录
[root@localhost ~]# tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/  
                  ###备份压缩

◆模拟故障
[root@localhost backup]# mkdir /bak            
                         ###在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/ 
                        ###将目录data数据移动到bak 里面去
[root@localhost backup]# ll /usr/local/mysql/  
                        ###查看mysql目录发现data被移走，模拟故障成功
total 64
drwxr-xr-x  2 mysql mysql  4096 Sep 10 16:01 bin
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING-test
drwxr-xr-x  2 mysql mysql    55 Sep 10 16:01 docs
drwxr-xr-x  3 mysql mysql  4096 Sep 10 16:01 include
drwxr-xr-x  4 mysql mysql   191 Sep 10 16:01 lib
drwxr-xr-x  4 mysql mysql    30 Sep 10 16:01 man
drwxr-xr-x 10 mysql mysql  4096 Sep 10 16:01 mysql-test
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README-test
drwxr-xr-x 28 mysql mysql  4096 Sep 10 16:01 share
drwxr-xr-x  2 mysql mysql    90 Sep 10 16:01 support-files
drwxr-xr-x  3 mysql mysql    17 Sep 10 16:01 usr

◆数据库恢复
[root@localhost mysql]# mkdir /restore     
                        ###在根目录新建restore 目录
[root@localhost mysql]#tar xzvf /backup/mysql_all-2020-09-13.tar.gz -C /restore/  
                      ####将备份数据库解压到 /restore目录下  注意：根据自己的时间节点解压
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/    
                      ###将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld     
                           ###启动mysql数据库
[root@localhost mysql]# systemctl status mysqld 
                        ###查看mysql数据库启动状态  ，日志文件显示正常。
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 16:30:44 CST; 24s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 29346 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 29327 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 29348 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─29348 /usr/local/mysql/bin/mysqld --daemonize --...

Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: Version:...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain mysqld[29346]: 2020-09-...
Sep 13 16:30:44 localhost.localdomain systemd[1]: Started MyS...
Hint: Some lines were ellipsized, use -l to show in full.



2.mysqldump备份与恢复
创建环境
[root@localhost ~]# mysql -uroot -p  
                     ###进入mysql数据库
mysql> create databases auth;                              
   	###创建auth 数据库
mysql> use auth;                              	
   	####进入auth 数据库
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
mysql> insert into users values('lisi', password('123456'));
退出数据库
[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql  
                  ###备份数据路mysql中的user表  ，文件保存在当前操作目录下面
Enter password:                  ###输入密码   
[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql    
  ####备份auth数据库     文件保存在当前操作目录下面
Enter password:                  ####输入密码

查看备份文件
[root@localhost /]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_name` char(16) NOT NULL,
  `user_passwd` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;

恢复表
[root@localhost /]# mysql -u root -p              ###进入数据库
Enter password:                                   ###输入密码

mysql> show databases;                            ###查看数据库

+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |

mysql> create database text ;                    ###新建数据库text
mysql>\q         ###退出

[root@localhost opt]# mysql -u root -p text < mysql-user.sql 
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show tables from text'    ###验证输出结果
Enter password:      
+----------------+
| Tables_in_text |
+----------------+
| user           |                                                                                   ###导进去的表
+----------------+


数据库恢复
[root@localhost /]# mysql -u root -p -e 'drop database auth'           
                    ###模拟故障删除auth数据库
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show databases'              
                    ######查看databases所有数据库有没有auth，发现没有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |

[root@localhost /]# mysql -u root -p < ./auth.sql                         
                    ###导入数据库auth
Enter password:                        ###输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'            
                   ###查看databases所有数据库有没有auth，发现有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |                                                                                  ###导进去的auth数据库
| bdqn               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+