mysql二进制日志

mysql的binlog(二进制日志)用于记录所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句，主要作用是实时备份数据库，以及主备库的同步。

开启Mysql binlog

默认binlog是不开启的，开启binlog毕竟会影响性能。如何开启binlog？
先在根目录查找一下mysql的默认配置文件：my.cnf
find / -name my.cnf
centos下可以找到/etc/my.cnf，修改这个文件:

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-bin=mysql-bin#慢查询  /var/log/mysqld-slow.loglog-slow-queries=/var/log/mysqld-slow.loglong_query_time=1       #慢查询阈值，单位 s[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysql]default-character-set=utf8

加上这句话：log-bin=mysql-bin，开启binLog，存储位置为：/var/lib/mysql/mysql-bin.index, /var/lib/mysql/mysql-bin.000001

my.cnf中，针对于binlog还有更多参数，如：

• binlog-do-db=test ：需要备份数据test，多个写多行，不写全部都备份
• binlog-ignore-db=mysql ：不需要备份的数据库，多个写多行

修改完my.cnf之后需要重启数据库

查看binlog是否已经开启

show binary logs

binlog的三种模式：

MySQL记录binlog的方式主要包括三种模式：row, statement 和mix。

• 基于行复制模式（row模式，RBR）
  
  • row模式下，binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.
  • 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。
• 基于SQL语句的模式(statement模式，SBR)
  
  • statement模式下，每一条会修改数据的sql都会记录到bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。
  • statement模式下的优点首先就是不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录所执行的语句的细节，以及执行语句时候的上下文的信息。
  • 缺点：由于statment是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。
• 混合模式（mixed模式，MBR）
  
  • mixed其实就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。目的是最大限度地记录数据库中每一行数据的变化，也能够减少binlog的日志量，节约空间。
• 需要注意的是
  新版本的row 模式已做了一定的优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

查看数据库是否已经开启binlog

show variables like “binlog_format”

修改当前binlog记录的模式， 执行：set binlog_format=xxx

binlog内容的查看

需要使用Mysql自带的工具 mysqlbinlog。
mysqlbinlog可能在/etc目录，也可能在/bin目录，centos下是在/etc目录，建议可以使用 find 命令查找一下。
./mysqlbinlog -v xxxx.bin 查看binlog内容

主备mysql同步

对于一个mysql服务器， 一般有两个线程来负责复制和被复制。这里只写原理，不讲具体配置。

当开启复制之后：

1. 作为主服务器Master，会把自己的每一次改动都记录到二进制日志binlog中，从服务器会负责来读取这个log，然后在自己那里再执行一遍。
2. 作为从服务器Slave，会用master上的账号登陆到master上，读取master的binlog，写入到自己的中继日志Relaylog，然后自己的sql线程会负责读取这个中继日志，并执行一遍。 到这里主服务器上的更改就同步到从服务器上了。
3. 在mysql上可以查看当前服务器的主从状态。 其实就是当前服务器的Binary（作为主服务器角色）状态和位置。 以及其RelayLog（作为从服务器）的复制进度。
4. 主从同步分为单向同步和双向同步，双向同步的时候，每一个mysql既是master又是slave，mysql-01需要使用mysql-02的账号登录去读mysql-02的binlog，而mysql-02又需要使用mysql-01的账号登录去读mysql-01的binlog，去做到双向同步。
5. 主从切换的时候，很可能的原因是mysql-01挂掉了或者断电了，这种情况可以自动化直接切到mysql-02，但是当mysql-01恢复的时候，需要有一段时间的数据恢复和同步，这就会涉及到一个问题：数据库的切回是不是自动化的，如果是自动化无人值守的，切回来的时候mysql-01是否已经完成了数据同步，否则就有数据不一致的风险。当然，双向同步的话，我理解是不需要自动切回去的，切到mysql-02的时候，mysql-02就是主库，不需要切回去。
6. 对于单向同步的读写分离，主库挂了，系统就只能读，不能写，不能把写迁移到备库上，因为备库数据不能同步回主库。备库挂了，可以再从主库读，这种行为是可以的，但是也涉及到备库恢复以后切回去是否是自动化的问题，如果是自动化的，是否也会涉及到备库数据未完成同步的问题。