增量备份恢复

**

增量备份恢复案例

**1、配置mysql，设置日志文件
[root@localhost bak]# vim /etc/my.cnf
添加：

log-bin=mysql-bin

2、重启mysqld服务

systemctl restart mysqld

查看日志文件：

mysqlbinlog /usr/local/mysql/data/mysql-bin.000001

3、创建数据库，创建表，添加数据
[root@localhost ~]# mkdir /bak
[root@localhost ~]# mysql -uroot -p123.com
mysql> create database t666;
mysql> use t666;
mysql> create table user (id char(20),name char(20),sex char(4),user_id char(10),xiaofei int);
mysql> insert into user values(‘a001’,‘zhang1’,‘M’,‘0001’,120),(‘a002’,‘zhang2’,‘W’,‘0002’,100),
(‘a003’,‘zhang3’,‘M’,‘0003’,90),
(‘a004’,‘zhang4’,‘W’,‘0004’,160),
(‘a005’,‘zhang5’,‘M’,‘0005’,30),
(‘a006’,‘zhang6’,‘W’,‘0006’,80);
mysql> select * from user;

4、先进行一次完整备份
[root@localhost /]# mysqldump -u root -p123.com t666 user > /bak/t666_user-$(date +%F).sql
[root@localhost /]# ll /bak

生成新的二进制文件
[root@localhost bak]# mysqladmin -uroot -p123.com flush-logs
[root@localhost ~]# ll /usr/local/mysql/data/

5、添加新数据
[root@localhost bak]# mysql -u root -p123.com
mysql> use t666;
mysql> insert into user values(‘a007’,‘zhang7’,‘M’,‘0007’,120),(‘a008’,‘zhang8’,‘W’,‘0008’,100);
[root@localhost bak]# mysqladmin -uroot -p123.com flush-logs
[root@localhost bak]# ll /usr/local/mysql/data/

6、复制日志文件
[root@localhost bak]# cp /usr/local/mysql/data/mysql-bin.000002 /bak
删除表
[root@localhost bak]# mysql -u root -p123.com -e “drop table t666.user”
[root@localhost bak]# mysql -u root -p123.com -e “show tables from t666”
7、恢复数据库中的表和数据
执行完整恢复
[root@localhost ~]# mysql -u root -p123.com t666 < /bak/t666_user-2020-11-24.sql
[root@localhost bak]# mysql -u root -p123.com -e “show tables from t666”
[root@localhost bak]# mysql -u root -p123.com -e “select * from t666.user”
缺少后添加的数据
[root@localhost bak]# mysqlbinlog --no-defaults /bak/mysql-bin.000002 |mysql -u root -p123.com (再把后加的表内容 追加恢复过去)
[root@localhost bak]# mysql -u root -p123.com -e “select * from t666.user”

数据恢复完整

今天就分享这个 ，你们学会了吗？ 记得关注我哦！了解更多的数据库经验

本文地址：https://blog.csdn.net/m0_46219851/article/details/110679938