MySQL Binlog（主从同步/数据恢复） 及 mysqldump：_mysql 主从同步, 数据还没同步 ,binglog已经被删除

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是主从复制和数据恢复。

Binlog日志的两个重要的使用场景:

1. MySQL主从复制：MySQL Replication在Master端开启binlog，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
   

根据这张图，把 mysql 的主备复制原理分解为如下流程：

• 总结来说，MySQL 的主从复制：异步单线程。

• Master上 1 个IO线程，负责向Slave传输 binary log（binlog）
• Slave上 2 个线程：IO 线程和执行SQL的线程，其中：
  • IO线程：将获取的日志信息，追加到relay log上；
  • 执行SQL的线程：检测到relay log中内容有更新，则在Slave上执行sql；

• 特别说明：MySQL 5.6.3 开始支持「多线程的主从复制」，一个数据库一个线程，多个数据库可多个线程     

MySQL 主从复制 / 增量同步（个人总结）：

1. master要开启binlog，并记录所有修改db数据的事件
2. slave会启动一个io线程，与master建立客户单连接
3. master也启动一个io线程（二进制转储线程）去读取master上的binlog日志，并向slave上的io线程发送"二进制数据"
4. slave接收到"二进制数据"并保存在relay log中
5. slave另启动sql线程，负责更新relay log中的数据到从库中（执行一遍relay log中的增量数据修改操作）

具体建立从库的方法参考：MySQL增加从库 - Jadear - 博客园

2. 数据恢复：通过使用 mysqlbinlog 工具来使恢复数据

mysql binlog有三种格式：

• statement（默认）：基于SQL语句的复制（statement-based replication, SBR）
• row（常用）：基于行的复制（row-based replication, RBR）
• mixed（前两者混合使用）：混合模式复制（mixed-based replication, MBR）

statement 和 row 格式的 优缺点比较：

• statement
  • 优点：生成的日志文件体积小，节约IO，存储的是sql执行语句
  • 缺点：搭建MySQL主从时，会出现一些问题。
    
    比如：master查询当前时间select now()，slave同步master的binlog后，同样查询当前时间，这时候就会出现主从二者时间不一致的问题。
• row
  • 优点：记录以行为结果的修改记录，确保主从数据的一致。
  • 缺点：
    • 由于是以每行记录的修改来写日志，故日志体积大（正常日志的3-5倍大小），占用大量 “io/网络” 资源（可能造成主从同步的延迟）。
      
      比如：有一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
    • 另外，mysql主从是异步的，倘若master突然挂了，那么有可能会导致slave数据的缺失，主从的数据不一致。

补充：

• 使用 show variables like '%binlog_format%'; 查看使用的格式       
• 将二进制日志格式设置为ROW时，有些更改仍然使用基于语句的格式，包括所有DDL语句，例如：CREATE TABLE， ALTER TABLE，或 DROP TABLE。

启用Binlog：

启用Binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：

在配置文件中加入 log-bin 配置，表示启用binlog，如果没有给定值，写成 log-bin=，则默认名称为主机名。（注：名称若带有小数点，则只取第一个小数点前的部分作为名称）

[mysqld]
log-bin=my-binlog-name

也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog，通过 SET SQL_LOG_BIN=0 命令停用 binlog。启用 binlog 之后须重启MySQL才能生效。
 

常用Binlog操作命令：

# 是否启用binlog日志
show variables like 'log_bin';
 
# 查看详细的日志配置信息
show global variables like '%log%';
 
# 查看所有二进制日志列表
show master logs; 或 show binary logs;
 
# 查看最新一个binlog日志文件名称和Position
show master status;
 
# 修改binlog的格式
set global binlog_format='mixed' 
 
 
# 查看 binlog 内容
show binlog events;
 
# 查看具体一个binlog文件的内容 （in 后面为binlog的文件名）
show binlog events in 'master.000003';
 
# 设置binlog文件保存时间，过期自动删除，单位: 天
set global expire_log_days=3; 
 
...

写 Binlog 的时机：

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

• 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
• 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
• 设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

注：在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

生成binlog日志文件(xxx.0000*)的触发条件:

当遇到以下3种情况时，MySQL会重新生成一个新的日志文件，文件序号递增：

• MySQL服务器停止或重启时
• 使用 flush logs 命令；
• 当 binlog 文件大小超过 max_binlog_size(最大1G) 变量的阈值时；

mysqlbinlog 命令的使用:

服务器以二进制格式将binlog日志写入binlog文件，如果要以文本格式显示其内容，可以使用 mysqlbinlog 命令。(--base64-output=decode-rows 命令用于对二进制binlog的解码)

# mysqlbinlog 的执行格式
mysqlbinlog [options] log_file ...
 
# 查看bin-log二进制文件（shell方式），二进制格式需要用 --base64-output=decode-rows 解码
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003
 
# 查看bin-log二进制文件（带查询条件）
mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \
    --start-datetime="2019-03-01 00:00:00"  \
    --stop-datetime="2019-03-10 00:00:00"   \
    --start-position="5000"    \
    --stop-position="20000"

设置日志格式为ROW时，输出信息示例：

# at 21019
#190308 10:10:09 server id 1  end_log_pos 21094 CRC32 0x7a405abc 	Query	thread_id=113	exec_time=0	error_code=0
SET TIMESTAMP=1552011009/*!*/;
BEGIN
/*!*/;

上面输出信息包括：

• position: 位于文件中的位置，即第一行的（# at 21019）,说明该事件记录从文件第21019个字节开始
• timestamp: 事件发生的时间戳，即第二行的（#190308 10:10:09）
• server id: 服务器标识（1）
• end_log_pos 表示下一个事件开始的位置（即当前事件的结束位置+1）
• thread_id: 执行该事件的线程id （thread_id=113）
• exec_time: 事件执行的花费时间
• error_code: 错误码，0意味着没有发生错误
• type:事件类型Query

如何定位用于恢复时的position：

参考文章：
mysql的数据备份和还原 - 如何使用mysqldump和mysqlbinlog（简单流程）
mysqldump全量备份+mysqlbinlog二进制日志增量备份（★★★★★ 详细流程：包含导入节点后的增量数据恢复操作）

既然了解到binlog在数据恢复下的使用场景，那么如何确定恢复数据的起止位置呢？两种方式：

(1) 可以按照 起止时间点 来恢复数据：mysqlbinlog  --start-date=... --stop-date=...

mysqlbinlog  --start-date="2015-09-09 15:23:40" --stop-date="2015-09-09 15:24:11" mysql-bin.000003 | mysql -uroot -pro 

(2) 也可以按照 position 来恢复数据...如下：

具体示例：

参考：mysql idb恢复删除之前的数据_恢复MySQL数据到误删的表之前的数据

（1）可通过 grep定位drop 表语句所在binglog文件的位置点来做恢复：

执行命令：

mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/binlog/mysql-bin.000005 | grep -i -C 15 drop

参数选项： 

• --base64-output=decode-rows 命令用于二进制日志文件的解码
• -i：搜索时忽略大小写
• -C：匹配行和它前后各n行。

### @1=10422

### @2='tomcat'

### @3='xiaohuahua'

### @4='2019-08-08 14:22:18'

# at 14987

#190808 14:22:18 server id 63306 end_log_pos 15018 CRC32 0x873943dd Xid = 20695

COMMIT/*!*/;

#at15018###################################

#190808 14:22:19 server id 63306 end_log_pos 15083 CRC32 0xcc8773ce GTID last_committed=34 sequence_number=35 rbr_only=no

SET @@SESSION.GTID_NEXT= 'bde7b592-b966-11e9-8c64-000c294f3e61:10445'/*!*/;

#at 15083

#190808 14:22:19 server id 63306 end_log_pos 15211 CRC32 0x8d445019 Query thread_id=7213 exec_time=0 error_code=0

use `testdb`/*!*/;

SET TIMESTAMP=1565245339/*!*/;

SET @@session.sql_auto_is_null=0/*!*/;

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

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

#End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

注意：这里的恢复数据的position或date只能是插入或修改的记录块。如果选择删除的记录块，则数据恢复不了。其原理跟普通的sql语句一样，日志只是把操作以sql语句的形式存储起来，恢复数据的时候执行一遍sql语句，而达到恢复数据的效果。因为只有insert 和 update 才会有数据存储。

binlog在 MySQL主从复制 中的应用原理：

对于数据库操作，应该注意如下问题：

1、要常备份（全备，增量备份），出了问题可以最快恢复数据；
2、操作数据库前，要把需要操作的数据库或者表dump出来；
3、需要把bin-log打开，就算没有做上面的两步，也可以通过日志恢复数据

补充：

1. 关于MySQL事务什么时候会触发写binlog的操作?

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录日志到binlog
 

2. mysqldump和mysqlbinlog 二者区别：

首先理解两个概念：

• 全量备份：每次备份都备份当前系统中的所有数据。
• 增量备份：备份当前时间点的数据与上次备份时间点数据的差异。

（1）mysqlbinlog 可以自动备份，mysqldump  是需要手动备份导出的；
（2）mysqlbinlog二进制日志常用于增量备份，mysqldump常用于全量备份；
策略：MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志增量备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

3. 什么场景下要关闭mysql binlog？

比如大批量导入数据的时候，没有必要去记录其binlog，浪费性能和空间，这时候可以短暂关掉。

4. 关于主从复制：

mysql 5.6之前是串行复制，5.6之后是并行复制。

5. 关于mysqldump的命令选项：--master-data=1

参考：Mysql使用mysqldump和mysqlbinlog进行备份

mysqldump会导致锁表，innodb需加上 --single-transaction 选项：

背景：
        在使用mysqldump备份数据库的时候发现数据无法查询了，查询资料后得知：在执行mysqldump时会默认执行FLUSH TABLES WITH READ LOCK，这会关闭所有打开的表，同时对于所有数据库中的表都加一个读锁，直到显示地执行unlock tables，该操作常常用于数据备份的时候。

方法：
        所以如果你的表引擎是innodb的话，你不想在备份的时候因为全局读锁而导致数据查询失败，你可以加上 --single-transaction。

原理：
        在mysqldump中指定 single-transaction 时，会使用 "可重复读(REPEATABLE READ)" 事务隔离级别来保证整个dump过程中数据一致性，该选项仅对InnoDB表生效，且不能与ALTER TABLE/CREATE TABLE/DROP TABLE/RENAME TABLE/TRUNCATE TABLE等DDL操作并行。
        其中，可重复读保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。

总结：
        如果不想阻塞同时表是innodb引擎可使用 single_transaction 取得一致性快照(取出的数据是导出开始时刻事务点的状态)
如果表不支持多版本特性，比如MyISAM，则只能使用 lock-all-tables 阻塞方式来保证一致性的导出数据。
        当然，如果能保证导出期间没有任何写操作，可不加或关闭 lock-all-tables

参考：mysqldump --master-data=2 --single-transaction_linuxheik的专栏-CSDN博客

更多可参考：

MySQL之mysqldump的使用

MySQL数据库备份与还原

MySQL mysqldump数据导出详解

MySQL主从复制的常见拓扑、原理分析以及如何提高效率（show processlist）

---