MySQL增量备份与恢复

1、备份之前（进入数据库创建数据）：

mysql> create database test2;
#创建数据库
mysql> create table test2.t1 (id int,name varchar(32));
#创建表
mysql> insert into test2.t1 values (1,'test1');
mysql> insert into test2.t1 values (2,'test2');
#给test2添加数据
mysql> select * from test2.t1;
#查看数据

2、当前状态做一次全备： 

[root@mysql ~]#  mysqldump -uroot -hlocalhost -p'Liang.123com' -P3306 --all-databases --triggers --routines --events --single-transaction --master-data=1 --flush-logs --set-gtid-purged=OFF > /backup/mysql/$(date +%F%H)-mysql-all.sql
#--all-databases（备份mysql服务器上的所有数据库）
#--triggers（备份触发器）
#--routines（备份存储过程和存储函数）
#--events（备份事件调度器代码）
#--single-transaction（保证数据的一致性和服务的可用性）
#--master-data=1（记录binlog日志位置与文件名，追加至备份文件中）
#--flush-logs（备份之前刷新日志）
#--set-gtid-purged=OFF（主从复制的时候新建从库，避免主从的gtid不一致）

3、进入数据库再插入数据：

mysql> insert into test2.t1 values (3,'test3');
mysql> insert into test2.t1 values (4,'test4');
mysql> select * from test2.t1;

4、模拟数据库崩溃（重启初始化，启动数据库，更改默认密码）： 

[root@mysql ~]# systemctl stop mysqld
#停止数据库
[root@mysql ~]# rm -rf /var/lib/mysql/*
#删除数据库，模拟数据库崩溃
[root@mysql ~]# systemctl start mysqld
#初始化数据库
[root@mysql ~]# grep 'temporary password' /var/log/mysqld.log
#查密码
[root@mysql ~]# mysql -u root -p'VAeBojdk/9sG'
#登录数据库

mysql> alter user 'root'@'localhost' identified by 'Liang.123com';
#修改密码
mysql> \q
#退出

[root@mysql ~]# mysql -u root -p'Liang.123com'
#用新密码登录数据库

mysql> show databases;
#查看数据库

5、恢复全量数据：

[root@mysql ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-11-0122-mysql-all.sql 
#禁用当前会话的二进制日志记录
[root@mysql ~]# mysql -u root -p'Liang.123com' < /backup/mysql/2023-11-0122-mysql-all.sql 
#全备恢复
[root@mysql ~]# mysql -u root -p'Liang.123com' -e 'select * from test2.t1'
#查看数据库test2中t1表中的数据

6、恢复增量备份：

[root@mysql ~]# sed -n '22p' /backup/mysql/2023-11-0122-mysql-all.sql
#查看一下全量备份，备份到哪个点了 
CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000010', MASTER_LOG_POS=154;
#154这个点，000010这个日志文件

 7、根据 MASTER_LOG_POS 恢复增量的数据（全量仅备份到了154这个点，154后面的点全备文件里面就没有了，需要去000010以后的二进制文件里面找）：

[root@mysql ~]# ls /var/log/mysql
#查看二进制文件
[root@mysql mysql]# mysqlbinlog --start-position=154 bin-log.000010 bin-log.000011 bin-log.000012 bin-log.000013 bin-log.000014 |mysql -uroot -p'Liang.123com'
#恢复增量的数据
[root@mysql mysql]# mysql -u root -p'Liang.123com' -e 'select * from test2.t1'
#再次查看数据库test2中t1表中的数据

 

成功恢复！