1)全备份数据
[root@Centos7 ~]#mysqldump -A --master-data=2 > /data/all.sql
2)查看备份点
[root@Centos7 ~]#vim /data/all.sql ----记住此处这是我们从这里开始备份的
-- CHANGE MASTER TO MASTER_LOG_FILE='logbin.000003', MASTER_LOG_POS=522995;
3)模拟改变数据
MariaDB [hellodb]> insert into teachers(tid,name) values ('20','a');
MariaDB [hellodb]> insert into teachers(tid,name) values ('221','bb');
4)清空数据表
[root@Centos7 ~]#rm -rf /var/lib/mysql/*
5)vim /etc/my.cnf
[mysqld]
skip-networking
6)重启数据库生成初始化文件
[root@Centos7 ~]#systemctl restart mariadb
7)定位错误范围
[root@Centos7 ~]#mysqlbinlog --start-position=522885 /data/logs/logbin.000003 > /data/inc.sql
[root@Centos7 ~]#mysqlbinlog /data/logs/logbin.000005 >> /data/inc.sql
[root@Centos7 ~]#mysqlbinlog /data/logs/logbin.000006 >> /data/inc.sql
8)关闭 sql_log_bin
MariaDB [(none)]> set sql_log_bin=off
9)开始恢复
MariaDB [mysql]> source /data/all.sql
MariaDB [mysql]> source /data/inc.sql
10)开启 sql_log_bin
MariaDB [(none)]> set sql_log_bin=on
11)vim /etc/my.cnf
#skip-networking
mysql增量备份
最新推荐文章于 2023-08-15 10:27:17 发布