1、在全备中截取建表语句(路径根据自己的修改)
zcat backup.gz | sed -n '/CREATE TABLE `t1` /,/\;/p' > /data/backup/create.sql
2、在全备中提取插入数据语句(路径根据自己的修改)
zcat backup.gz | grep -i 'INSERT INTO `t1`' > /data/backup/insert.sql
3、在binlog备份中提取表数据修改的语句(路径根据自己的修改)
zcat backup.gz | head -n 25 | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=51893;
mysqlbinlog --no-defaults --database=db1 --start-position=51893 --base64-output=decode-rows -vv /var/lib/mysql/mysql-bin.000010
mysqlbinlog --skip-gtids --database=db1 --start-position=51893 --stop-position=1235082 /var/lib/mysql/mysql-bin.000010 > /data/backup/binlog.sql
4、恢复数据,进入mysql命令界面
use db1;
set sql_log_bin=0;
source /data/backup/create.sql
source /data/backup/insert.sql
commit;
source /data/backup/binlog.sql
set sql_log_bin=1;