MySQL的二进制日志(binlog)以及主从配置

MySQL的二进制日志记录了所有对MySQL数据库的修改事件，包括增删改查事件和对表结构的修改事件。

1、binlog的格式

a、基于段的格式 binlog_format=STATEMENT ,日志记录量相对较小，节约磁盘以及网络IO，但必须要记录上下文信息，以保证语句在从服务器上执行结果和在主服务器上相同，一些非确定性函数无法正确复制，造成主从服务器数据不一致。
b、基于行的格式 binlog_format=ROW，可以避免MySQL复制中出现的主从不一致问题，对每一行数据的修改比基于段的复制高效，误操作时，可以分析ROW格式的日志，反向处理可以恢复数据；缺点是记录的日志量较大
c、混合日志格式 bin_format=MIXED，根据SQL语句由系统选择是基于段还是基于行，数据量的大小由所执行的SQL语句决定

2、日志格式对于复制的影响

a、基于SQL语句的复制(SBR) 使用STATEMENT格式的日志。优点是生成的日志量少，节约网络传输IO，不强制要求主从数据库表的定义完全相同，相比于基于行的复制方式更为灵活；缺点是对于非确定性事件，无法保证主从复制数据的一致性，对于存储过程、触发器、自定义函数进行的修改也可能导致数据不一致，相比于行的复制方式，在从上执行时需要更多的行锁。
b、基于行的复制(RBR) 使用基于行的日志格式。优点是可以应用于存储过程、触发器等，可以减少数据库锁的使用；缺点是要求主从数据库的表结构相同，否则可能会中断复制，无法在从上单独执行触发器，
推荐基于行的复制，对数据的一致性更有保证

3、MySQL复制的工作方式

a、主将变更写入二进制日志
b、从读取主的二进制日志变更并写入到relay_log
c、在从上重放relay_log中的日志

4、配置MySQL复制

基于日志点的复制配置步骤：
a、在主DB服务器上建立复制账号、并授权

CREATE USER 'repl'@'IP段' identified by 'password';
GRANT REPLICATION SLAVE ON *.* TO 'reply'@'IP段';

b、配置主数据服务器

log_bin=mysql-bin #默认为主机名字
server_id=100 #在整个复制集群中唯一

c、配置从服务器

log_bin=mysql-bin
server_id=101
relay_log=mysql-relay-bin
log_slave_update=on [可选] #重放操作是否加入本机的binlog
read_only=on [可选] #阻止没有super权限的用户对数据库写入

d、数据库dump

mysqldump --master-date=2 -single-transaction
xtrabackup --slave-info   #全部使用innodb的数据库，最好使用

e、
启动复制链路

CHANGE MASTER TO 
MASTER_HOST = 'master_host_ip'
MASTER_USER = '',
MASTER_PASSWORD ='',
MASTER_LOG_FILE = '',
MASTER_LOG_POS= 4;