mysql增量备份与修复

MySQL数据库增量恢复

1.一般恢复

将所有备份的二进制日志内容全部恢复

2.基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

3.基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

手动更新二进制文件

 mysql的增量备份第一步是完全备份

生成二进制文件有两种方法

1.设置max_binlog_size所设置的大小

2.接收flush logs 命令重新建立新的日志文件

第一步首先要在my.cnf中加入二进制文件

log-error=/usr/local/mysql/data/mysql_error.log
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
log-bin=mysql-bin
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

 

[root@localhost ~]#vim /etc/my.cnf
 
log-error=/usr/local/mysql/data/mysql_error.log
#错误日志  存放位置/usr/local/mysql/data/目录下  错误日志文件名为mysql_error.log
 
general_log=ON
#通用查询日志开启
 
general_log_file=/usr/local/mysql/data/mysql_general.log
#通用查询日志 保存位置在/usr/local/mysql/data目录下  通用查询日志文件名为mysql_general.log
 
log-bin=mysql-bin
#二进制日志（binlog）：用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认开启
 
slow_query_log=ON
#慢查询开启  慢查询：用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认关闭
 
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
#慢查询日志默认存放位置  /usr/local/mysql/data/  慢查询日志文件名为mysql_slow_query.log
 
long_query_time=5
#设置超过5秒执行的语句被记录  缺省时为10秒

随后重启，然后进入data里面查看

 如果要新生成一个二进制文件 要么等这个00001文件到1G，自动会产生新的二进制文件，要么手动创建一个新的二进制文件

 mysqladmin -uroot -p123123 flush-log

 

 第二种方式刷新二进制文件，直接重启

如果刷新完之后，新的二进制文件是00004，应该挪走00003，因为旧的日志是000003，新的会记录到000004文件中

二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)（记录事务性的操作 不不记录select 、show等操作）、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
 

 举例说明：1.statement格式

 随后，刷新重启

然后后进入到数据库中

 

 随后，给表中加入数据。一个是新增的数据，一个是修改的数据

 随后，退出，并且查看二进制文件，所有事务性的操作都会记录到新的000004二进制文件中， 因为是二进制文件里面的内容看不懂，因此要转话

直接查看二进制文件是看不懂的，因此要转化 

 

--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
-v: 显示详细内容
--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

 

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

 

举例说明：2. row基于行

随后重启 并且生成新的000003的二进制文件  

 随后，进入mysql 然后修改内容

随后，退出，再次去查看二进制文件（000003） 为什么是000003，因为是修改过配置文件刷新之后新产生的000003的二进制文件，之后修改的内容都在此

 

sow基于行，很精确，保存内容过多，占用空间多

 

 3.mixed模式 （集合于statement和row优点）

 

 

 增量备份   

二进制日志增量恢复

第一步，将库中的表备份 （完全备份）

第二步，在ky00表中新增数据

随后，打开另外一个相同的终端

，然后再去新增两条数据 11.12

随后，再去另一个相同的终端，进行生成新的二进制文件 11 12的新增数据在旧的文件中

 

 随后，将000006存在11 12文件的二进制文件移动到backup下面

如果不放心可以检查一下，000005 和000006里面的文件内容

 000005：

 

000006：

 

 

 随后，模拟删除表 然后恢复

 第一步 先完全备份恢复

 

第二步  增量恢复

 随后，再将000006的二进制数据增量备份的数据恢复到ky00表中

 

 基于时间点恢复

假设要恢复11 而不恢复12  可以使用基于位置恢复

 第一步将11 12删除 

11 12数据在mysl-bin.000006-20240326里面

所以查看这个二进制文件

 

随后，找到11这个文件的开始跟结束，

 随后执行

mysqlbinlog --no-defaults --start-position='296' --stop-position='439' /root/backup/mysql-bin.000006-20240326 |mysql -uroot -p'123123';

随后查看表 ，11数据恢复

 

基于时间点恢复

 接着上面，如果恢复最后id=12

 

ysqlbinlog --no-defaults --start-datetime='2024-03-26 16:09:09' --stop-datetime='2024-03-26 16:10:31' /root/backup/mysql-bin.000006-20240326 |mysql -uroot -p'123123';

 随后，再去查看一个表内容 12内容恢复过来了