mysqlbinlog 增量恢复

过程：

一、准备一台mysql5.7的数据库

 cat /usr/local/mysql/my.cnf |grep bin
	log-bin-index = master-bin.index
	log-bin = mysql-bin
	binlog-format = ROW
	sync_binlog = 500
	binlog_checksum = none

mysql -V
	mysql  Ver 14.14 Distrib 5.7.36, for linux-glibc2.12 (x86_64) using  EditLine wrapper

二、建库空testlog

CREATE DATABASE IF NOT EXISTS testlog;

三、开启增量，产生新的binlog日志

/usr/local/mysql/bin/mysql -uroot -p123456 -e 'flush logs'
#立即执行全量备份
/usr/local/mysql/bin/mysqldump -uroot -p123456 --single-transaction --master-data=2 testlog|gzip > /data/back/testlog`date +%Y-%m-%d_%H%M%S`.sql.gz

四、建表、删表

	CREATE TABLE t1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
   );
   插入一条数据
	INSERT INTO t1 (name, age) VALUES ('John', 25);

删表

mysql> show tables;
+-------------------+
| Tables_in_testlog |
+-------------------+
| t1                |
+-------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

五、恢复到删除表前

1、生成新的binlog文件

#生成新的binlog文件，旧数据从./mysql-bin.000003恢复
[root@VM-16-15-centos mysql]# /usr/local/mysql/bin/mysql -uroot -p123456 -e 'flush logs'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@VM-16-15-centos mysql]# cat master-bin.index 
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004

2、从全量备份中看pos点

cd /data/back/
gunzip -c testlog2023-12-01_093121.sql.gz >testlog2023-12-01_093121.sql
[root@VM-16-15-centos back]# head -n 100 testlog2023-12-01_093121.sql|grep -i pos
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=150;
#以上得出pos点为150

3、查看删除表前的pos点

mysqlbinlog --base64-output=decode-rows -v /data/mysql/mysql-bin.000003 >bin003.sql
或可以更明确的查看
 mysqlbinlog -v /data/mysql/mysql-bin.000003  \
 --start-position=xx  \
 --stop-position=xx  \
 --database=testlog  \
 --base64-output=decode-rows  (通过大概的pos区间 及指定库来查看，日志文件太大时使用)
 

# at 695
#231201  9:33:22 server id 2  end_log_pos 812   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1701394402/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
DROP TABLE `t1` /* generated by server */
/*!*/;

这里当文件太大时，可以先从base64密文看起
mysqlbinlog --set-charset=utf8 --database=testlog mysql-bin.000003 |grep -A 5 -B 5 t1


得到误操作的起始pos 695, 那么需要从mysql-bin.000003中恢复 150(全量备份) - (695-1)的pos点之间的数据 

4、恢复

#先全量恢复
gzip < /data/back/testlog2023-12-01_093121.sql.gz |mysql -uroot -p123456 testlog
或
gunzip -c testlog2023-12-01_093121.sql.gz >testlog2023-12-01_093121.sql
mysql -uroot -p123456 testlog <testlog2023-12-01_093121.sql

再增量恢复  pos点150 - 694
mysqlbinlog -v /data/mysql/mysql-bin.000003 --start-position=150 --stop-position=694 --database=testlog | mysql -uroot -p123456 testlog


查看是否已恢复testlog.t1表
[root@VM-16-15-centos back]# mysql -uroot -p123456 -e 'use testlog;select * from t1;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | John |   25 |
+----+------+------+

六、带一份日常数据备份

00 02 * * * /usr/bin/sh /data/sh/backup.sh
0 4 * * *  find /data/bakdb/mysqlbackup -mtime +7|xargs rm -rf

cat /data/sh/backup.sh

#!/usr/bin/env bash

backupPath="/data/bakdb/mysqlbackup"
exclude=('information_schema' 'mysql' 'performance_schema' 'test')
/usr/local/mysql/bin/mysql -uuname -ppassword -e 'flush logs'
for dbname in `/usr/local/mysql/bin/mysql -uuname -ppassword -e 'show databases;'|tail -n +2`
do
  flag=0
  for f in ${exclude[*]}
  do
    if [ $f = $dbname ];then
      flag=1
      break
    fi
  done
  if [ $flag = 0 ];then
    /usr/local/mysql/bin/mysqldump -uuname -ppassword --single-transaction --master-data=2 $dbname|gzip > $backupPath/$dbname`date +%Y-%m-%d_%H%M%S`.sql.gz
  fi
done

------------end