Mysql 通过binlog日志恢复数据

登录mysql查看binlog日志的状态

mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /www/server/data/mysql-bin       |
| log_bin_index                   | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+
6 rows in set (0.00 sec)

查询binlog日志名称 下一步需要用到

# 查询所有binlog日志
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000010 | 125978064 |
| mysql-bin.000011 |       177 |
| mysql-bin.000012 |    163467 |
+------------------+-----------+
3 rows in set (0.00 sec)

# 查询最新binlog日志
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 |   163467 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

使用mysqlbinlog 工具导出格式化后的日志

#查询最新binlog日志所在目录 PS:如果你知道binlog日志所在目录，可忽略(下同)
find / -name 'mysql-bin.000012'
# /www/server/data/mysql-bin.000012

#查看mysqlbinlog目录
find / -name 'mysqlbinlog'
# /www/server/mysql/bin/mysqlbinlog

/www/server/mysql/bin/mysqlbinlog -uroot -proot --base64-output=decode-rows /www/server/data/mysql-bin.000012 -d test_db > /www/backup/back.sql
# --base64-output=decode-rows 表示导出格式为row
# /www/backup/back.sql 表示输出路径
# -d test_db 指定导出 test_db 库的内容

使用vim查看刚刚导出的sql

确认要回滚的点,再次导出sql

# 这次不要加 –base64-output=decode-rows 参数，否则mysql无法识别这种格式导致无法恢复数据，此参数只是为了更方便的可视化mysqlbinlog的导出文件来寻找回滚点位置。
/www/server/mysql/bin/mysqlbinlog -uroot -proot --start-position="59487" --stop-position="60424" /www/server/data/mysql-bin.000012 -d test_db > /www/backup/backup.sql

# 恢复数据
mysql -uroot -proot test_db < /www/backup/backup.sql

{/card-list-item}