一、MySQL主从复制介绍
主从复制是一种MySQL自带的容灾备份方案，可以在一定程度上实现业务读写分离，如主从节点配置不同的域名或者使用中间件来实现)，还可以在一定程度上当做备份使用(配置一台从服务器为延迟从库的方式来实现)。传统的主从复制依赖于MySQL binlog实现。主节点通过Binlog记录了数据库所有DDL与DML操作(SELECT、SHOW等语句不对数据产生变化，不会有记录)，从库将binlog日志复制到自己的中继日志Relaylog中，然后根据Relaylog做相同的操作，实现了数据的同步。
主从结构通常可以分为一主一从、一主多从、级联复制等，如果是一主多从的架构需要注意从服务器的数量不能太多，否则会因为日志的传输给主库带来过多的带宽消耗(曾遇到主库网卡异常导致从库一直卡住的原因，当时一直思考的是大事务原因，结果是硬件导致)
二、Binlog日志介绍
1、binlog写入机制
事务执行过程中先把日志写到binlog cache中，等事务提交的时候再把cache写到binlog文件fsync落盘并清空cache，这一点和redolog很像。一个事务的 binlog不能被拆开，不论事务多大也要确保一次性写入binlog。每个线程都会申请一块binlog cache，内存空间大小由参数 binlog_cache_size控制。如果超过参数设置的大小就要暂存到磁盘。而write和fsync 的时机由参数sync_binlog决定(双1参数之1)
2、binlog格式介绍
· statement(标准模式，简称SBR)：保存的是每一条修改数据的SQL语句，然后在从服务器上会执行相同的语句来同步数据。优点是日志记录量比较少，缺点是数据一致性不是最高级别
· row(行模式，简称RBR)：推荐模式，将数据的具体改变记录在日志中，日志量大，安全性最高，不会因为某些产生随机数的SQL而让主从数据不一致，比如包含了时间函数的语句。row格式的二进制日志无法直接阅读，需要转码
· mixed(混合模式，简称MBR)：statement和row的结合模式，当基于语句无法精确复制时，就会采用行模式进行复制。实际上比较鸡肋，不考虑使用
delete * from test; #假设这条SQL语句删除了100万条数据
# STATEMENT语句模式仅记录这一条SQL语句
# ROW行模式会记录执行删除时的每一个语句，会记录100万次
三、MySQL主从复制配置
在部署主从架构前先确保主从节点的MySQL版本一致、时间一致
1、Master主库配置
· 主库开启binlog以及先关配置
[mysqld]
basedir = /usr/local/mysql
datadir = /mysqldata
port = 3306
socket = /tmp/mysql.sock
server_id = 1 #开启binlog的话必须配置它，建议设置为IP最后一段+端口的形式，如103306，该ID最大值为4294967295，主从不能一样。
innodb_file_per_table = ON
#### log set ###
log_bin = /data/mysql/log/master-bin #binlog日志路径与日志文件前缀
log_bin_index = /data/mysql/log/master-bin.index #binlog索引文件
log_bin_trust_function_creators = 1 #默认为0，开启后可以同步函数和存储过程
binlog_format = row #推荐用row模式
binlog_rows_query_log_events = on #将原始sql语句加上注释记录到日志中便于分析
expire_logs_days = 10 #二进制日志过期天数，通常设置为一个全备周期+1天，比如每周全备一次，这个时间就设置为8
max_binlog_size = 1024M #二进制日志大小，如果一个SQL事务产生的数据超过该限制，则突破该限制
binlog_cache_size = 2M #二进制日志缓存区大小，默认32K，建议1-4M
sync_binlog = 1 #binlog buffer刷盘策略。1代表每次提交事务之前将二进制日志同步到硬盘；N为有N次commit的时候才写入磁盘；0为关闭
innodb_flush_log_at_trx_commit=1 #默认为1，每次事务提交时会把buffer数据写入磁盘
master_info_repository = TABLE #将master-info信息保存到表中，减少开销的同时可以提升可靠性。默认是file
relay_log_info_repository = TABLE
log_slave_updates = 1
relay_log_recovery = 1 #如果从库IO线程崩溃，并且relaylog损坏，则放弃所有未执行的relaylog，重新从master获取日志保持完整
· 主库建立一个账号并给与数据复制权限
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'192.168.36.11' IDENTIFIED BY '123456789';
· 查看主库当前的position信息，从库会从该位置点进行复制
mysql > show master status \G
2、Slave从库配置(业务从库)
· 从库配置relaylog中继日志相关信息，默认情况下relaylog会在SQL线程执行完毕后自动删除
[mysqld]
basedir = /usr/local/mysql
datadir = /mysqldata
port = 3306
socket = /tmp/mysql.sock
server_id = 2 #和Master的ID不一样即可
relay-log = relay-log #中继日志路径和文件名，需注意主机名一旦更改主从就会出错
relay-log-recover = 1 #打开中继日志恢复模式，如果从库宕机导致当前中继日志损坏的话会放弃该日志并重新从master上获取
read_only = 1 #从库只读(该配置对root用户无效)，避免从库有写操作导致主从故障
super_read_only =1 #配置root用户也为只读
master_info_repository = table
relay_log_info_repository = table #relay-info信息写入数据库表中，默认是file
slave_parallel-type = LOGICAL_CLOCK #MySQL5.7引入的MTS并行复制机制，默认是database基于库进行并行复制，LOGICAL_CLOCK可以实现“master如何并行执行的语句，slave就如何并行回放语句”，在减少从库延迟的同时解决了并行复制时语句执行顺序问题。
slave_parallel_workers = 8 #从库并行复制的线程数，通常和CPU核数一致，可以一定程度上解决从库延迟问题
expire_logs_days = 10
max_binlog_size = 1024M
log_error = error.log
innodb_file_per_table=ON
skip_name_resolve=ON
###进行级联主从时从库也要配置binlog
log_slave_updates = 1 #从库也记录binlog日志
log_bin = slave-bin
binlog_format = row
· 从库使用主库上配置好的复制用户，并且指定好主库的position信息
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.145.85',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=0;
#MASTER_HOST：MASTER服务器的IP
#MASTER_USER：有slave权限的用户，就是GRANT所授权的用户
#MASTER_PASSWORD：从库用户的密码
#MASTER_LOG_FILE：在主库上执行show master status语句可以查看日志名
#MASTER_LOG_POS=333：这个位置决定了从库从哪个位置开始复制，实测写0也可以，从头复制
· 启动从库复制线程，需要为两个yes
mysql > start slave; #启动从库
mysql > show slave status \G #查看从库状态
3、延迟从库配置
在需要配置为延迟从库的客户端上执行以下命令
mysql > stop slave;
mysql > change master to master_delay = 3600; #SQL线程延迟1小时，IO线程不影响
mysql > start slave;
mysql > show slave status \G #查看SQL_Dely的值
四、主从状态查看
1、查看主库Position信息与从库状态
show master status \G #查看主库binlog position信息
show slave status \G #查看从库状态
2、从库状态关键指标
· Read_Master_Log_Pos：从库读取到主库二进制日志到哪个位置
·Exec_Master_Log_Pos：从库执行到主库二进制日志到哪个位置。如果和Read_Master_Log_Pos一致代表数据是同步的
·Relay_Log_File：从库中继日志记录到的位置，正常情况下中继日志内容的和主库binlog是一样的
·Seconds_behind_master：从服务器比主服务器慢了多少秒，为0代表没有延迟
·Slave_IO_Running、Slave_SQL_Running：如果有一项为No都表示主从异常。IO Thread的作用是从Master端请求二进制日志并存放到Slave端的中继日志中；SQL Thread的作用是将中继日志里的事件导入到SQL语句中，下图是启动slave之前的状态：
五、使用延迟从库恢复故障的思路
1、如果主库执行了错误的语句，比如drop database，现在需要使用延迟从库来恢复数据。首先就是在延迟从库上停止SQL线程，让它不再继续执行relay中的SQL
stop slave sql_thread;
2、查看延迟从库relaylog位置点，这个作为恢复的起点，这里假设为482
show slave status \G #查看relay_log_file和relay_log_pos信息
3、查看延迟从库relaylog中的故障点，通常是在最后一个relaylog中，这里需要注意Position信息只需要看左边一列的，它才是relaylog中的位置，而relaylog中的End_log_pos是主库中的位置，不需要关注。这里假设DROP操作的起始点是1402
mysql > show relaylog events in 'relay-bin.000005';
4、生成用于恢复数据的SQL，需要注意恢复的数据不仅是data1，在drop database data1后其他库可能还有操作，这部分也是要恢复的
mysqlbinlog --start-position=482 --stop-position=1402 /data/mysqllog/relay-bin.00005 > /tmp/relay.sql
