BINLOG与数据恢复

卷心菜

2021-10-22 16:55
关注

一、Binlog日志

Binlog日志是mysql的二进制日志重要日志,特别是项目中是必须要做好应用的;二进制日志,也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作,然后以二进制的形式记录日志在日志文件中,其中还包括没调语句所执行的时间和消耗的资源,以及相关的事务信息。

1、主要作用:

  • 以二进制形式记录更改数据库的SQL语句(insert,update,drop,alter等)。

  • 用于MySQL主从复制。

  • 增量数据备份及恢复。

2、配置方式

[mysqld]

server-id = 1 # 后续讲主从的时候解释

log-bin = mysql-bin # binlog日志文件名

log-bin-index = binlog.index # binlog.index日志文件的索引文件

启用该选项数据库性能降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整

3、Binlog日志三种形式

show variables like ‘%binlog%’; 如下,里面有binlog_format相关信息可以进行配置。

设置:binlog_format = mixed

  • Statement :每一条会修改数据的sql都会被记录在binlog中

  • ROW :不设置sql语句上下文相关信息,仅保存哪条记录被修改

  • MiXED :是以上两种level的混合使用。

一般的语句修改使用Statement格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用ROW格式保存binlog。mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是从Statement和ROW中之间选择一种。新版本的mysql中对ROW模式也做了优化,并不是所有的操作都会以ROW格式记录,像遇到表结构变更的时候就会以Statement模式来记录,至于update和delete等修改数据的语句还是会记录所有行的变更。

4、Binlog日志的常用命令

  • SHOW BINARY LOGS;【查看所有二进制文件信息】

  • SHOW MASTER STATUS; 【查看最新二进制文件

mysql每一次重启都会创建一个新的binlog日志,如下:

  • show binlog events。【用于在二进制日志中显示事件】

    help show binlog events; --获取帮助信息

    show binlog events\G;【默认是第一个binlog日志】

show binlog events in 'mysql-bin.00007'\G; 【查看指定binlog日志】

5、使用增量日志恢复数据

5.1、我们新建一个user表,且添加一些基本数据:

5.2 来查看数据库的binlog信息

用:show master status

5.3 查看binlog日志的使用情况

用show binlog events in 'mysql-bin.00008'\G;

5.4、先修改下的日志格式,且重启mysql后,重新进入,对数据进行重构。

vi /etc/my.cnf,一般修改为statement模式。因为这个是每一行都记录。另外,在同步恢复的多个机器之间,mysql 日志格式都保持一直,否则会报异常。

再次添加数据后,在看下mysql的binlog日志:

用show binlog events in 'mysql-bin.00009'\G;

删除ID大于5的记录后,再次查看binlog日志:ID大于5的节点是POS:554到1074,如下:

5.5、数据恢复

主体使用mysqlbinlog 命令进行相关操作。

mysqlbinlog grep | position

a)、通过日志节点进行恢复:

通过上面我么可知道,删除的节点为554到1074.

mysqlbinlog --start-position=554 --stop-position=1074 /var/lib/mysql/binlog.000009 | mysql -u root –p

b) 可以通过时间来恢复,命令如下

mysqlbinlog --start-datetime="2021-10-21 21:26:10" --stop-datetime="2021-10-21 21:31:25" --database=poempp /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p*** -v

举报/反馈