mysql基于binlog实现数据备份与恢复(常用操作)

一、mysql binlog数据备份

1、my.ini配置 mysqld

#设置日志前缀
log-bin=mysql-bin
#设置日志保留天数
expire_logs_days=30
#设置日志文件最大大小
max_binlog_size=100M

二、mysql binlog数据恢复

查看是否开启binlog
show variables like 'log_bin';

#查看所有binlog日志
show master logs;

#查看最新binlog
show binlog events;

查看binlog开始时间和结束时间
./mysqlbinlog --base64-output=DECODE-ROWS --verbose mysql-bin.00000x | head -n20 
./mysqlbinlog --base64-output=DECODE-ROWS --verbose mysql-bin.000002 | tail -n20

linux binlog日志转sql日志模式 （方便查看执行sql语句位置）
./mysqlbinlog --no-defaults --base64-output=decode-rows -v 
 --start-datetime="2023-08-09 15:01:00" --stop-datetime="2023-08-12 09:51:53" 
 --database=test  /www/server/data/mysql-bin.000101
 > mysql-bin00100.sql
 
window binlog转sql
mysqlbinlog   F:/data/mysql-bin.000102  > mysql-bin000102.sql	 

binlog文件模糊查询带drop的语句
./mysqlbinlog  --no-defaults --base64-output=decode-rows -v /www/server/data/mysql-bin.000101|grep -i drop


恢复数据
1、按照时间段
./mysqlbinlog --no-defaults --start-datetime="2023-08-01 06:30:57" --stop-datetime="2023-08-05 15:13:59" /www/server/data/mysql-bin.000100 | mysql -u root -p test

2、按照定位
./mysqlbinlog --no-defaults --start-position=4 --stop-position=299 /www/server/data/mysql-bin.000100 | mysql -u root -p123456 test