Linux——MySQL备份与恢复

数据库备份概述

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。

1.数据备份的重要性

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难（如：火灾、地震、盗窃）

2.数据库备份类型

（1）从物理与逻辑的角度

• 冷备份：在数据库关闭状态下进行备份操作
• 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
  （2）从数据库的备份策略角度
• 完全备份
• 差异备份
• 增量备份

数据库完全备份

1.物理冷备份与恢复

执行冷备份需要提前关闭MySQL服务

（1）备份数据库

[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost ~]# mkdir /backup
[root@localhost ~]# [root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
tar: Removing leading `/' from member names
[root@localhost ~]# ls -l /backup/
total 4
-rw-r--r--. 1 root root 45 Jun  8 18:22 mysql_all-2020-06-08.tar.gz

（2）恢复数据库
将数据库文件移走模拟故障

[root@localhost ~]# mkdir bak
[root@localhost ~]# mv /usr/local/mysql/data/ bak/

从备份文件恢复数据

[root@localhost ~]#  mkdir restore
[root@localhost ~]# mv /usr/local/mysql/data/ bak/
[root@localhost ~]# tar zxf /backup/mysql_all-2020-06-08.tar.gz -C restore/
[root@localhost ~]# mv  restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld.service 

2.mysqldump备份与恢复

（1）备份数据库

“-u”，“-p”：分别指定于数据库用户名和密码

格式1：备份指定库中的部份表

[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:       //确认密码

格式2：备份一个或多个完整的库（并且包括了其中的表）

[root@localhost ~]# mysqldump -u root -p  --databases bdqn > bdqn.sql
Enter password: 

格式3：备份MySQL服务器中所有的库

[root@localhost ~]# mysqldump -u root -p  --opt --all-databases  > all-data.sql
Enter password: 

（2）查看备份文件

使用grep、less、cat等文本工具可以查看家脚本内容

[root@localhost ~]# grep -v "^--" bdqn.sql  | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bdqn` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `bdqn`;

（3）恢复数据库

-e：用于指定连接MySQL后执行的命令，命令执行完后自动退出

[root@localhost ~]# mysql -u root -p test < mysql-user.sql 
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'show tables from test;'
Enter password: 
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+

模拟故障恢复bdqn库

[root@localhost ~]# mysql -u root -p -e 'drop database bdqn;'
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wangfuping         |
+--------------------+
[root@localhost ~]# mysql -u root -p < ~/bdqn.sql 
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| mysql              |
| performance_schema |
| test               |
| wangfuping         |
+--------------------+
[root@localhost ~]# 

MySQL增量备份与恢复

进行MySQL的增量备份，需要开启二进制日志功能

[root@localhost ~]# vim /etc/my.cnf
……
[mysqld]

log-bin=/usr/local/mysql/mysql-bin
……
[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 120 Jun  8 18:48 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql  34 Jun  8 18:48 /usr/local/mysql/mysql-bin.index

MySQL增量恢复

常用的增量恢复方式有

• 一般恢复 ：将所有备份的二进制日志内容全部恢复
• 基于位置的恢复：通过位置进行恢复可以更加精准
• 基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复

一般恢复：

mysqlbinlog  --no-defaults   二进制文件 | mysql -u root -p

基于位置的恢复：
格式1：恢复数据到指定位置

[root@localhost ~]# mysqlbinlog --stop-position='操作 id'  二进制日志   | mysql -u root -p
Enter password: 

格式2：从指定的位置开始恢复数据

[root@localhost ~]# mysqlbinlog --start-position='操作 id'  二进制日志   | mysql -u root -p
Enter password: 

基于时间点的恢复
格式1：从日志开头截止到某个时间点的恢复

[root@localhost ~]# mysqlbinlog  --no-defaults --stop-datetime='年-月-日 小时：分钟：秒'  二进制日志 | mysql -u root -p
Enter password: 

格式2：从某个时间点到日志结尾的恢复

root@localhost ~]# mysqlbinlog  --no-defaults --start-datetime='年-月-日 小时：分钟：秒'  二进制日志 | mysql -u root -p
Enter password: 

格式3：从某个时间点到某个时间点的恢复

root@localhost ~]# mysqlbinlog  --no-defaults --start-datetime='年-月-日 小时：分钟：秒' --stop-datetime='年-月-日 小时：分钟：秒' 二进制日志 | mysql -u root -p
Enter password: 

案例

MySQL企业备份案例

身份证	姓名	性别	用户ID号	资费
06	张三	男	016	10
07	李四	女	017	91
08	王五	女	018	23
09	赵六	男	019	37
10	孙琪	男	0200	36

1.一般恢复

（1）添加数据库、表、录入信息

mysql> create database client;
Query OK, 1 row affected (0.00 sec)

mysql> use client;
Database changed
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into user_info values('06','张三','男','016','10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('07','李四','女','017','91');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user_info values('08','王五','女','018','23');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

（2）进行一次完全备份

[root@localhost ~]# mkdir /mysql_bak
[root@localhost ~]# mysqldump -u root -p client user_info > /mysql_bak/client_userinfo-$(date  +%F).sql
Enter password: 
[root@localhost ~]# ls /mysql_bak/
client_userinfo-2020-06-08.sql
[root@localhost ~]# mysqladmin  -uroot -p flush-logs
Enter password: 
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 1237 Jun  8 20:11 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql  120 Jun  8 20:11 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql   68 Jun  8 20:11 /usr/local/mysql/mysql-bin.index

（3）集需录入新的数据并进行增量备份

mysql> use client;
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> insert into  user_info  values('09','赵六','男','019','37');
Query OK, 1 row affected (0.01 sec)

mysql> insert into  user_info  values('10','孙琪','男','020','36');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 09        | 赵六   | 男     | 019         |     37 |
| 10        | 孙琪   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysqladmin  -uroot -p flush-logs
Enter password: 
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 1237 Jun  8 20:11 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql  675 Jun  8 20:13 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql  120 Jun  8 20:13 /usr/local/mysql/mysql-bin.000003
-rw-rw----. 1 mysql mysql  102 Jun  8 20:13 /usr/local/mysql/mysql-bin.index
[root@localhost ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_bak/

（4）模拟误删user_info表

[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

（5）恢复数据库

[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
[root@localhost ~]# mysqlbinlog  --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p    //  恢复增量备份
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 09        | 赵六   | 男     | 019         |     37 |
| 10        | 孙琪   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

2.基于位置恢复

模拟误删

[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 

实现基于位置和时间点恢复数据，必须先通过查看二进制日志文件确定恢复的位置或时间点。

查看二进制日志文件的具体内容

[root@localhost ~]# mysqlbinlog  --no-defaults /mysql_bak/mysql-bin.000002 
………………@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 203
#200608 20:13:11 server id 1  end_log_pos 343 CRC32 0xd6e852ce 	Query	thread_id=10	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1591618391/*!*/;
insert into  user_info  values('09','赵六','男','019','37')
/*!*/;
# at 343
#200608 20:13:11 server id 1  end_log_pos 374 CRC32 0x7a0f359f 	Xid = 80
COMMIT/*!*/;
# at 374
#200608 20:13:28 server id 1  end_log_pos 457 CRC32 0x876af585 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1591618408/*!*/;
BEGIN
/*!*/;
# at 457
#200608 20:13:28 server id 1  end_log_pos 597 CRC32 0x5fd128ba 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1591618408/*!*/;
insert into  user_info  values('10','孙琪','男','020','36')
/*!*/;
# at 597
#200608 20:13:28 server id 1  end_log_pos 628 CRC32 0x18cbf091 	Xid = 81
COMMIT/*!*/;
# at 628
#200608 20:13:44 server id 1  end_log_pos 675 CRC32 0x27b9671f 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过查看日志文件之后，会发现在每个操作前都有一个独特的编号，“# at 203 ” 称之为操作ID。所以要实现基于位置或时间点的恢复数据，需要分别依赖二进制日志文件中的 操作ID或者时间标记。

恢复457之前的数据

[root@localhost ~]# mysqlbinlog  --no-defaults --stop-position='457'   /mysql_bak/mysql-bin.000002 |  mysql -u root -p
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 09        | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+

–stop-position ：是停止的位置
– start-position：是指定开始恢复数据的位置

[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog  --no-defaults --start-position='457' /mysql_bak/mysql-bin.000002  | mysql -u root -p
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 10        | 孙琪   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+

3.基于时间点恢复

[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog  --no-defaults --stop-datetime='2020-06-08 20:13:18' /mysql_bak/mysql-bin.000002  | mysql -u root -p
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 09        | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+

跳过赵六直接恢复孙琪

[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password: 
[root@localhost ~]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog  --no-defaults --start-datetime='2020-06-08 20:13:18' /mysql_bak/mysql-bin.000002  | mysql -u root -p
Enter password: 
[root@localhost ~]# mysql -u root -p -e 'select * from  client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 06        | 张三   | 男     | 016         |     10 |
| 07        | 李四   | 女     | 017         |     91 |
| 08        | 王五   | 女     | 018         |     23 |
| 10        | 孙琪   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+