实验——MySQL数据库增量备份恢复

一、MySQL数据库增量备份恢复

1.1 物理冷备份，开启服务

[root@localhost ~]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data
[root@localhost ~]# ls /opt
mysql-2020-01-08.tar.xz  mysql-5.7.17  rh
[root@localhost ~]# systemctl start mysqld

1.2 开启二进制日志文件

[root@localhost mysql]# vim /etc/my.cnf                                               #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                                                                                                       #####找到这个模块,在[mysqld]项中加入配置 log-bin=mysql-bin
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin                            ####这个地方开启二进制日志功能

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
                                      
[root@localhost mysql]# systemctl restart mysqld                                           ####重启数据库

[root@localhost ~]# ll /usr/local/mysql/data/
总用量 122924
-rw-r----- 1 mysql mysql       56 8月   9 16:16 auto.cnf
-rw-r----- 1 mysql mysql      308 8月  15 05:50 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibdata1
-rw-r----- 1 mysql mysql 50331648 8月  15 05:50 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 8月   9 16:16 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 8月  15 05:50 ibtmp1
drwxr-x--- 2 mysql mysql     4096 8月   9 16:16 mysql
-rw-r----- 1 mysql mysql      154 8月  15 05:50 mysql-bin.000001                            ####二进制日志文件
-rw-r----- 1 mysql mysql       39 8月  15 05:50 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 performance_schema
drwxr-x--- 2 mysql mysql     8192 8月   9 16:16 sys

1.3 创建库和表，进行完全备份和增量备份

[root@localhost data]# mysql -uroot -p	
Enter password: 	###输入密码登陆
mysql> create database yiku;	###创建库
Query OK, 1 row affected (0.00 sec)

mysql> use yiku;	###使用库
Database changed

mysql> create table yibiao (id int(4) primary key,name char(10));	###创建表
Query OK, 0 rows affected (0.00 sec)

mysql> insert into yibiao values(1,'zhangsan'),(2,'lisi');	##插入记录
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from yibiao;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)
mysql>exit
[root@localhost data]# mysqldump -uroot -p yiku > /opt/yiku.sql	###进行完全备份
Enter password: 
[root@localhost data]# mysqladmin -uroot -p flush-logs	###进行增量备份
Enter password: 
[root@localhost data]# cd /usr/local/mysql/data/
[root@localhost data]# ls
 mysql-bin.000001   mysql-bin.000002  ...省略内容...
###发现已经有了增量备份文件，刚刚备份的操作记录都存放在 mysql-bin.000001中， mysql-bin.000002是准备存放后续操作的

1.4 进行正常操作和误操作，进行增量备份

mysql> insert into yibiao values(3,'wangwu');	###正常操作
Query OK, 1 row affected (0.00 sec)

mysql> delete from yibiao where name='wangwu';	###误操作
Query OK, 1 row affected (0.00 sec)

mysql> insert into yibiao values(4,'zhaoliu');	###正常操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from yibiao;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  4 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)
mysql>  exit
Bye
[root@localhost data]# mysqladmin -uroot -p flush-logs;	###再次增量备份
Enter password: 
[root@localhost data]# ls	###发现再次生成新的增量备份文件ysql-bin.000003
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 ###刚刚的正常操作和无操作都存放到了mysql-bin.000002中

1.5 查看增量备份文件

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt
[root@localhost data]# vim /opt/bak.txt 	###发现能够查看到刚刚的操作语句
...省略内容...
# at 343	###正常操作语句段
#200108  0:29:18 server id 1  end_log_pos 390 CRC32 0x9ada4ac9  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `yiku`.`yibiao`	
### SET
###   @1=3
###   @2='wangwu'
# at 390
...省略内容
# at 558	###误操作语句段，558为误操作语句的开始位置id，200108  0:29:56 为误操作语句的开始位置时间，也是上一语句的结束时间
#200108  0:29:56 server id 1  end_log_pos 610 CRC32 0xb990c664  Table_map: `yiku`.`yibiao` mapped to number 219
# at 610	
#200108  0:29:56 server id 1  end_log_pos 657 CRC32 0x1738ac5d  Delete_rows: table id 219 flags: STMT_END_F
### DELETE FROM `yiku`.`yibiao`
### WHERE
###   @1=3
###   @2='wangwu'
# at 657
...省略内容
# at 825	###正常操作语句段，825为正常操作语句的开始位置id，200108  0:30:11 为正常操作语句的开始位置时间，也是上一语句的结束时间
#200108  0:30:11 server id 1  end_log_pos 877 CRC32 0x89f71cba  Table_map: `yiku`.`yibiao` mapped to number 219
# at 877	
#200108  0:30:11 server id 1  end_log_pos 925 CRC32 0xae064160  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `yiku`.`yibiao`
### SET
###   @1=4
###   @2='zhaoliu'
# at 925
#200108  0:30:11 server id 1  end_log_pos 956 CRC32 0xe6213e68  Xid = 57
...省略内容...

1.6 模拟故障，删除表并进行恢复

[root@localhost data]# mysql -uroot -p 
Enter password: 
mysql> drop table yibiao;	###删除原有的表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /opt/yiku.sql;	###恢复库

mysql> show tables;	###恢复成功
+----------------+
| Tables_in_yiku |
+----------------+
| yibiao         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from yibiao;	###恢复成功
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

1.7 使用基于时间点的断点恢复

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-10-18  0:29:56' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p	###第一个正常操作的结尾时间是2020-10-18  00:29:56，所以此处用此时间，表示恢复到此时间的操作
###时间在4.5查看增量备份文件中有写
Enter password: 
[root@localhost data]# mysql -u root -p
Enter password: 
mysql> use yiku;
mysql> select * from yibiao;	###查看表，发现恢复成功了
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-10-18  00:30:11' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p	###第二个十误操作，我们要跳过。第三个正常操作的结尾时间是2020-10-18  00:30:11，所以此处用此时间，表示从此时间恢复到结尾的操作
###时间在4.5查看增量备份文件中有写
Enter password: 
[root@localhost data]# mysql -u root -p
Enter password: 
mysql> use yiku;
mysql> select * from yibiao;	###查看表，发现恢复成功了
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)

1.8 使用基于位置的断点恢复

'//先删除刚刚恢复的两个表记录'
mysql> delete from yibiao where id=3;
Query OK, 1 row affected (0.01 sec)

mysql> delete from yibiao where id=4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from yibiao;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)
mysql> exit
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='558' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p	###错误操作真正开始的位置是558，而不是610，所以要恢复到558位置结束
###位置在4.5查看增量备份文件中有写
Enter password: 
[root@localhost data]# mysql -u root -p
Enter password: 
mysql> use yiku;
mysql> select * from yibiao;	###查看表，发现恢复成功了
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)
[root@localhost data]# mysqlbinlog --no-defaults --start-position='825' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p	###正常操作真正开始的位置是825，而不是877，所以要恢复到825位置结束
###位置在4.5查看增量备份文件中有写
Enter password: 
[root@localhost data]# mysql -u root -p
Enter password: 
mysql> use yiku;
mysql> select * from yibiao;	###查看表，发现恢复成功了
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhaoliu  |
+----+----------+
4 rows in set (0.00 sec)