mysql 从库基于主库binlog恢复_MySQL误操作后基于binlog恢复数据库

开启binlog

这是一个mysql配置文件示例

# 开启二进制日志

log-bin = mysql-bin

# 日志格式(mixed, statement, row)

binlog_format = mixed

# 设置过期时间

expire_logs_days = 30

# server id必须唯一

server-id = 1

查看日志命令

-- 列出二进制日志

show master logs;

-- 具体查看某个日志事件

show binlog events in 'mysql-bin.000003'

show binglog events

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

选项解析：

IN ‘log_name’ 指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] 偏移量(不指定就是0)

row_count 查询总条数(不指定就是所有行)

启动一个测试用的Docker

docker run --name test-mysql -d \

-v /home/fayfox/mysql/conf:/etc/mysql/mysql.conf.d/:ro \

-v /home/fayfox/mysql/data:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=123456 \

mysql

模拟数据

构建测试库

-- 建库

create database binlogtest;

-- 使用库

use binlogtest;

-- 建表

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL DEFAULT '',

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 初始测试数据

INSERT INTO `test`(`title`) VALUES ('a'), ('b'), ('c');

备份库

mysqldump -uroot -p -F binlogtest > ./bak.sql

-F 会在备份前先flush logs

编辑数据

UPDATE `test` SET `title` = 'dd' WHERE `id` = 3;

INSERT INTO `test`(`title`) VALUES ('e');

删表(模拟异常操作)

DROP TABLE `test`;

进行恢复

刷新日志索引

flush logs;

避免当前日志文件继续写入，使问题复杂化

导入之前的备份

利用mysql命令导入

mysql -uroot -p -D binlogtest < bak.sql

进入mysql客户端后用source命令导入

source /var/lib/mysql/bak.sql

查找问题语句位置

利用show binlog events查看

show binlog events in 'binlog.000003';

+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+

| binlog.000003 | 696 | Write_rows | 1 | 743 | table_id: 66 flags: STMT_END_F |

| binlog.000003 | 743 | Xid | 1 | 774 | COMMIT /* xid=76 */ |

| binlog.000003 | 774 | Anonymous_Gtid | 1 | 847 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| binlog.000003 | 847 | Query | 1 | 988 | use `binlogtest`; DROP TABLE `test` /* generated by server */ /* xid=78 */ |

| binlog.000003 | 988 | Rotate | 1 | 1032 | binlog.000004;pos=4 |

+---------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------------+

15 rows in set (0.00 sec)

可以看到，DROP语句从847位置开始

利用mysqlbinlog命令查看

mysqlbinlog binlog.000003

# at 774

#180717 14:59:38 server id 1 end_log_pos 847 CRC32 0x9fbb510b Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1531839578681339 immediate_commit_timestamp=1531839578681339 transaction_length=214

# original_commit_timestamp=1531839578681339 (2018-07-17 14:59:38.681339 UTC)

# immediate_commit_timestamp=1531839578681339 (2018-07-17 14:59:38.681339 UTC)

/*!80001 SET @@session.original_commit_timestamp=1531839578681339*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 847

#180717 14:59:38 server id 1 end_log_pos 988 CRC32 0x60ea3242 Query thread_id=8 exec_time=0 error_code=0 Xid = 78

use `binlogtest`/*!*/;

SET TIMESTAMP=1531839578/*!*/;

DROP TABLE `test` /* generated by server */

/*!*/;

同样可以看到，DROP语句从847位置开始

执行恢复

mysqlbinlog --stop-position=847 ./binlog.000003 | mysql -uroot -p -D binlogtest