Linux运维笔记|自动化运维攻城狮

如何通过mysql binlog 恢复数据

数据库   mysqlbinlog  

    BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储。

一、开启 mysql binlog

  • 修改 /etc/my.cnf
    log-bin=mysql-bin # 去掉注释即可，等号的为binlog的名字
    expire_logs_days = 7 #设置日志保留时长,这个默认是0，也就是logs不过期
    service mysqld restart # 重新启动MySQL生效

二、查看自己的BINLOG的名字是什么

  • 命令(因为binlog过多，我这只看前五条)
show binlog events limit 5;  
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                            |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
| mysql-bin.000144 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.10-log, Binlog ver: 4                                           |
| mysql-bin.000144 | 120 | Query       |         1 |         217 | BEGIN                                                                           |
| mysql-bin.000144 | 217 | Query       |         1 |         367 | use `bbs_imdst_com`; DELETE FROM pw_tag_record  WHERE `update_time` <1436139958 |
| mysql-bin.000144 | 367 | Query       |         1 |         465 | COMMIT                                                                          |
| mysql-bin.000144 | 465 | Query       |         1 |         562 | BEGIN                                                                           |
+------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------------------------+
5 rows in set (0.00 sec)  
以后每次对表的相关操作时候,这个File_size都会增大。

三、使用mysqlbinlog工具将二进制结果导出到文本文件

  • 固定位置导出（例导出从4开始到120结束位置的binlog）
    mysqlbinlog --start-position=4 --stop-position=120 /data/mysql/mysql-bin.000144 > test.txt

  • 全部导出
    mysqlbinlog /data/mysql/mysql-bin.000144 > 144.sql

  • 如果有多个文件，空格格开并写上绝对路径
    mysqlbinlog /data/mysql/mysql-bin.000144 /data/mysql/mysql-bin.000145 > all.sql

  • 通过--start-date和--stop-date选项指定DATETIME格式的起止时间导出
    mysqlbinlog --start-datetime="2015-07-20 09:15:00" --stop-datetime="2015-07-20 09:40:00" /data/mysql/mysql-bin.000144 > 2009.sql

四、还原

  • 命令
    • 第一种： mysql -uroot -pxxxx dbname < all.sql
    • 第二种： mysql -uroot -pxxxx dbname
      mysql> source ./all.sql

五、通过指定pos结束点恢复数据

  • 登录mysql 查看日志6: show binlog events in ‘mysql-bin.000144 ’;
  • 查看日志发现，在备份数据后首先执行的是插入数据操作(在一个事务内)，此时可以通过指定pos结束点恢复(部分恢复)，如图，pos结束点位435(事务已提交表示结束)，执行命令如下, /usr/bin/mysqlbinlog --stop-position=435 --database=hello /var/lib/mysql/mysql-bin.000144 | /usr/bin/mysql -uroot -p密码 -v dbname (其中整个命令的含义是通过mysqlbinlog读取日志内容并通过管道传给mysql命令，-v表示执行此mysql命令)