MySQLbinlog与增量备份的基本原理总结

定义

• binlog是记录所有数据库表结构变更&＃xff08;例如CREATE、ALTER TABLE…&＃xff09;以及表数据修改&＃xff08;INSERT、UPDATE即使update没有导致数据变化也会被记录、DELETE…&＃xff09;的二进制日志。
• binlog不会记录SELECT和SHOW这类操作&＃xff0c;因为这类操作对数据本身并没有修改&＃xff0c;但你可以通过查询通用日志来查看MySQL执行过的所有语句。
• 以事件形式记录&＃xff0c;还包含语句所执行的消耗的时间&＃xff0c;MySQL的二进制日志是事务安全型的。

作用

• 复制&＃xff1a;MySQL Replication在Master端开启binlog&＃xff0c;Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的

• 数据恢复&＃xff1a;通过mysqlbinlog工具恢复数据

• 增量备份&＃xff08;包括差异增量、累积增量&＃xff0c;主要利用flush logs配合mysqlbinlog来对binlog中的二进制数据进行分析&＃xff0c;将对应数据库的增量数据到出来&＃xff09;

  利用mysqlbinlog解析binlog来进行增量恢复时需要注意

  1. 不要使用 base64-output&＃61;decode-rows 参数
     –base64-output&＃61;decode-rows主要是解析 ROW 级别 binlog 日志时使用。
     我们解析日志的时候都会使用&＃xff1a;

     # mysqlbinlog -v --base64-output&＃61;decode-rows mysql-bin.0000XX
这是我们解析 binlog 日志时使用的命令&＃xff0c;我们可以很直观的分析 binlog 日志。
     但是我们想要恢复到数据库中的时候&＃xff0c;不能使用–base64-output&＃61;decode-rows 参数&＃xff0c;否则是无法恢复到数据库的。

  2. 是否使用–skip-gtids&＃61;true参数
     –skip-gtids&＃61;xxx的作用为&＃xff1a;mysqldump
     是否使用–skip-gtids&＃61;true 参数&＃xff0c;要根据情况来定&＃xff1b;
     第一种情况&＃xff1a;
     如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例&＃xff0c;那么就要使用该参数。如果不带该参数的话&＃xff0c;是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了&＃xff0c;根据 GTID 特性&＃xff0c;一个 GTID 信息在一个数据库只能执行一次&＃xff0c;所以不会恢复成功。

     # mysqlbinlog --skip-gtids&＃61;true mysql-bin.000012 |mysql -uroot -p
或者

     # mysqlbinlog --skip-gtids&＃61;true mysql-bin.000012 > backup.sql
mysql -uroot -p < backup.sql
第二种情况&＃xff1a;
     如果是恢复到其他实例的数据库并且不包含源实例的 GTID 信息&＃xff0c;那么可以不使用该参数&＃xff0c;使用或者不使用都可以恢复成功。

binlog的三种模式

• Row level
  日志中会记录每一行数据被修改的情况&＃xff0c;然后在slave端对相同的数据进行修改。
  优点&＃xff1a;能清楚的记录每一行数据修改的细节
  缺点&＃xff1a;数据量太大

• Statement level&＃xff08;默认&＃xff09;
  每一条被修改数据的sql都会记录到master的bin-log中&＃xff0c;slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
  优点&＃xff1a;解决了 Row level下的缺点&＃xff0c;不需要记录每一行的数据变化&＃xff0c;减少bin-log日志量&＃xff0c;节约磁盘IO&＃xff0c;提高新能
  缺点&＃xff1a;容易出现主从复制不一致

• Mixed&＃xff08;混合模式&＃xff09;
  结合了Row level和Statement level的优点

如何开启binlog

vim /etc/my.cnf(这个路径根据自己的安装查找吧)

[mysqld]
server_id&＃61;1#5.7以上必须设置这个参数&＃xff0c;否则重启服务会报错
log_bin &＃61; mysql-bin#第二个参数是binlog日志的基本文件名&＃xff0c;后面会追加标识来表示每一个文件&＃xff0c;默认在data目录
binlog_format &＃61; ROW
expire_logs_days &＃61; 5#binlog过期时间&＃xff0c;如果不配置&＃xff0c;则永不过期
max_binlog_size &＃61; 100M#单个binlog文件的大小&＃xff0c;超过了该大小&＃xff0c;自动切换到新的binlog文件


或者

log_bin&＃61;ON#打开binlog日志
server_id&＃61;1
log_bin_basename&＃61;/var/lib/mysql/mysql-bin#binlog日志的基本文件名&＃xff0c;后面会追加标识来表示每一个文件&＃xff0c;如果没有配置&＃xff0c;默认在data目录下
log_bin_index&＃61;/var/lib/mysql/mysql-bin.index#binlog文件的索引文件&＃xff0c;这个文件管理了所有的binlog文件的目录&＃xff0c;如果没有配置&＃xff0c;默认在data目录下


重启服务后即开启了binlog功能

binlog相关的命令

select &＃64;&＃64;log_bin#检查bin_log是否开启
select &＃64;&＃64;log_bin_basename#查看log_bin日志文件名前缀&＃xff0c;MySQL5.5以下不支持
select &＃64;&＃64;log_bin_index#存放log_bin日志文件的索引文件&＃xff0c;MySQL5.5以下不支持
reset master;#重置binlog&＃xff0c;删除所有binlog
show master logs;#查看所有binlog
show master status;#查看master状态&＃xff0c;即最后(最新)一个binlog日志的编号名称&＃xff0c;及其最后一个操作事件pos结束点(Position)值
flush logs;#刷新log日志&＃xff0c;自此刻开始产生一个新编号的binlog日志文件&＃xff0c;每当mysqld服务重启时&＃xff0c;会自动执行此命令&＃xff0c;刷新binlog日志&＃xff1b;在mysqldump备份数据时加 -F 选项也会刷新binlog日志&＃xff1b;


sql_log_bin临时关闭binlog开关

sql_log_bin用来临时关闭binlog&＃xff0c;仅仅对当前会话生效&＃xff0c;如果在当前会话设置了这个参数&＃xff0c;那么该会话中的增删改都不会记录到binlog日志中。

1. 所以在主从复制最好不要轻易使用该参数。
2. 如果对数据库利用binlog做了增量备份&＃xff0c;也最好别轻易使用该参数&＃xff0c;如果使用了会导致增量备份的数据不完整。
3. 当然了&＃xff0c;我们在对数据库进行恢复时&＃xff0c;可以在相应的恢复会话中设置该参数&＃xff0c;让恢复过程不走binlog&＃xff0c;从而加快恢复速度。

set sql_log_bin&＃61;0;#临时关闭当前会话的binlog
select &＃64;&＃64;session.sql_log_bin; #查询当前会话的sql_log_bin状态


binlog日志解析工具mysqlbinlog

1. 不加任何参数

mysqlbinlog xxxxxxx001 xxxxxxx002
#后面可以跟多个binlog日志文件&＃xff0c;利用这个特点我们可以将每个binlog日志文件的增量数据进行合并


2. 过滤指定数据库的binlog日志

mysqlbinlog -d test xxxxxx01 >test.sql


3.禁止恢复过程产生日志

mysqlbinlog mysqlbinlog -d test -D xxxxxx01 >test.sql
#或者----disable-log-bin


4.如果只想查看常规的SQL语句&＃xff0c;而不需要其他内容&＃xff0c;那么可以使用 -s 选项&＃xff0c;如下所示。
也可以使用 --short-form 选项&＃xff0c;效果相同。

5. 从远程服务器获取二进制日志