mysql备份恢复(一)

 

 

 

 

 

mysqldump备份所有库

mysqldump -uroot -p -A -R --single-transaction >dump.sql

 

mysqldump备份指定库

mysqldump -uroot -p -B zhh -R --single-transaction >zhh.sql

 

恢复数据库

mysql -uroot  -p    < zhh.sql

或者

mysql> source /root/zhh.sql

mysqldump 不支持增量备份，若要实现增量备份 需要开启binlog。binlog会记录 所有对数据库更改的操作。

mysql开启binglog

vim /etc/my.cnf

server-id = 1
log-bin = mysql-bin
binlog_format = ROW

 

 

完整备份

mysqldump -uroot -p -R --single-transaction --flush-logs --master-data=2  -B  zhh > zhh.sql

-R   导出存储过程和自定义函数--routines

--single-transaction  保证导出的一致性

--flush-logs     关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1

--master-data   将当前服务器的binlog的位置和文件名追加到输出文件 相当于执行 show master status，状态（file,position)的值。

                         等于1 输出change master 命令   等于2  输出change master命令 并在前面加注释

vim zhh.sql

......

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

......

...

 

模拟备份完后新 更改一些数据

打开一张表查看现有数据

 

 

 

删除一条数据

 

 

新增一些数据

 

 

 

现在模拟数据库出现故障

 

 

mysqldump 做了完整备份 zhh.sql。但备份之后更新的数据需要通过binlog 做增量恢复

1  恢复完整备份

 

 

[root@zhh73 mysql]# mysql -uroot -p < zhh.sql 
Enter password: 

 

 

查看rt_airport_device表

 

 没有新增的数据

 

2 通过 binlog 恢复数据

查看binlog的内容

binlog 会对存储的内容进行base64编码的处理  ，通过base64解码  来查看binlog内容

[root@zhh73 mysql]# mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/mysql-bin.000002 > b.sql

 

 binlog会存储具体的sql语句  传入的参数通过@ 进行存储

 

 

在binlog里 找到最后的删除库的语句位置，和最近的position位置

 

 

 

 

 

基于二进制日志恢复 可以通过时间点 和position位置 两种方式

 

1）通过position位置来恢复

离 drop database zhh 最近的两个position是1669 和1580 。我们需要的是drop database zhh 语句 之前的所有操作。 所以 positon点里如果包含了drop database zhh 相当于通过这个点还原的数据 已经删除了zhh库

所以查看这两个点的sql语句，不含drop语句的 才是我们需要还原的位置。

root@zhh73 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=154 --stop-position=1669 >> bb.sql
[root@zhh73 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=154 --stop-position=1580 >> cc.sql

 

[root@zhh73 mysql]# grep "drop database zhh" bb.sql cc.sql 
bb.sql:drop database zhh

 

bb.sql里含有drop语句  说明我们要还原的positon点是1580

还原数据

[root@zhh73 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position=154 --stop-position=1580 |mysql -uroot -p
Enter password: 

 

再查看rt_airport_device表 数据已增量恢复

 

 

2）通过时间点来恢复

[root@zhh73 mysql]# mysqlbinlog --stop-datetime='2021-10-21 19:19:59' /var/lib/mysql/mysql-bin.000002 |mysql -uroot -p
Enter password: 

 

 

查看binlog日志   show binary  logs;

 

 

查看binlog文件内容  show binlog events in 'mysql-bin-000002' \G

 

 

参考 https://www.cnblogs.com/chenmh/p/5300370.html