MySQL二进制日志复制方法和高可用架构设计的理解

1. mysql二进制日志

　1.1记录了所有对mysql数据库的修改事件，包括增删改查事件对表结构的修改事件。（只记录成功执行的）

　1.2 二进制日志记录的格式

　（1） 基于段的格式 binlog_format = statement; #可以查看运行mysql语句，更新100行的sql记录在日志里只有一行 。
　　　优点：记录量相对较小，节约磁盘以及网络I/O，当然对于一条记录修改，基于row产生的日志量小于段的。
　　　缺点：必须要记录上下文信息来保证语句在从服务器上执行结果和在主服务器上相同；

无法记录特定函数如UUID(),user(),如果有数据使用了这些函数，可能造成mysql主从复制数据不一致；

　（2） 基于行的格式 binlog_format = ROW; #在mysql5.7版本默认记录日志格式，记录每一行数据的变化。
　　　优点：ROW格式可以避免mysql复制中出现的主从不一致问题；
　　　　　　使Mysql主从复制更加安全；
　　　　　　对每一行数据的修改比基于段的复制高效；
　　　　　　误操作修改的数据库数据可以分析日志来反向处理达到恢复数据。
　　　缺点：记录日志量较大。

　所以官方推出binlog_row_image参数来改善mysql行记录缺点，该值有三个可选项：
　　　#FULL记录一行数据所有列的所有数据（默认）；
　　　#MINIMAL 只记录被修改到的列（建议使用）；
　　　#NOBLOB和FULL相像，如果不修改text和blog列的数据，就不记录text和blog字段数据的日志。

show variables like 'binlog_format';　 #查看mysql记录日志的格式;

set session binlog format=statement;　 #修改mysql记录日志的格式;

show binary logs;　　　　　　　　　　　　　#查看记录mysql日志文件;

flush logs; 　　　　　　　　　　　　　　　　#刷新mysql日志，会产生新日志文件;

mysqlbinlog '文件名'; 　　　　　　　　　　 #查看mysql日志。

2. 数据库复制

　2.1 在主DB服务器上建立复制帐号并授权

CREATE USER repl @ 'IP段'  identified by 'password' ;  #添加帐号限制IP提升安全
GRANT REPLICATION SLAVE ON *.* TO 'repl' @ 'IP段';     #授权

　2.2 配置主数据库服务器

bin_log = mysql-bin  　#命名二进制日志名字
server_id = 100      　#复制唯一值 建议使用主机ip地址的后几段

　2.3 配置从数据库服务器

bin_log = mysql-bin;          # 命名二进制日志名字;
server_id = 101;              # 设置server_id和主服务器的;server_id不一样;
relay_log = mysql-relay-bin;  # 终极日志;
log_slave_update = on;        # [可选]记录复制更新的日志，如果以后要把从服务器作为主服务器给其他从服务器复制则要开启
read_only = on                #[可选]防止从服务器其他帐号进行写操作，提高安全性 

　2.4 初始化从服务器(备份数据库) 记录复制的偏移变量

　　（1）使用mysqldump

mysqldump --master-data=2 -single-transction  --triggers --rotines --all-databases -uroot -p>all.sql #mysql自带的备份工具，数据库时备份会进行大量的阻塞（在生产服务器上慎用）

参数解释 ：
–single-transction命令只用于Innodb，如果是混合引擎要加上#local-all-table命令；
–master-data=2必须使用,这样从库才能记录偏移量，来进行启动链路复制；
–triggers 备份触发器；
–rotines 备份自定义函数；
–all-databases 所有数据库；

　　（2）使用　xtrabackup --slave-info工具 （可以进行热备，需要所有数据表都为Innodb，建议使用）

　2.5 从库上启动复制链路

mysql>change master to master_host ='master_host_ip', aster_user='repl',master_password='password',master_log_file='mysql_log_file_name',master_log_pos = 4;#master_log_pos 日志偏移量，从什么节点进行复制

　最后使用start slave 启动复制链路 show slave查看是否启动

　优点：mysql最早支持的复制技术，bug相对较少；对sql查询没有任何限制；故障处理比较容易；
　缺点：故障转移时重新获取新主的日志点信息比较困难

　3.基于GTID的复制(比较适用于mysql5.7以后的版本)

　　！如果在事务中使用Create temmporary table建立临时表或者使用关联更新事务表和非事务表则不能使用该复制。

GTID即全局事务ID，其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。

操作步骤：

　3.1 在主DB服务器上建立复制帐号并授权（建立命令查看2.1）

　3.2 配置主数据库服务器

bin_log = /usr/local/mysql/log/mysql-bin
server_id = 100
gid_mode = on 
log_slave_updates = on #启动gtid模式，在mysql5.5和5.6版需要设置，5.7版本不用
enforce_git_consistency = on 
/etc/init.d/mysqld restart; ＃配置完之后要重启数据库服务器 

　3.3 配置从数据库服务器

server_id = 101
relay_log = /usr/local/mysql/log/relay_log
gid_mode = on
enforce_git_consistency = on 
log_slave_updates = on
read_only=on [建议]
master_info_repository = TABLE [建议]      # 这两个表记录主服务器到从服务器的复制信息
relay_log_info_repository =TABLE [建议]    # 作用是出现数据库奔溃时可以重新同步数据
/etc/init.d/mysqld restart; # 配置完之后重启数据库服务器生效

　3.4 初始化从服务器，记录GTID值（备份命令查看2.4）

　3.5 启动基于GTID的复制

change master to master_host = 'master_host_ip',aster_user = 'repl',master_password = 'password',master_auto_position = 1; #告诉从服务器是基于GTID复制(最主要)

优点：可以很方便的进行故障转移; 从库不会丢失主库上的任何修改;
缺点：故障处理比较复杂 ;对执行的sql有一定的限制

4.mysql的复制拓补

mysql5.7之前，一个从库只能有一个主库，mysql5.7之后支持一从多主架构。

　4.1 一主多从的复制拓补 （配置简单）。

　　用途：（1）不同业务使用不同的从库，比如前后台数据库操作分离在不同的从库；
　　　　　（2）将一台从库放到远程的IDC，用作灾备恢复；
　　　　　（3）分担主库的读负载。

　4.2 主-主复制拓补

不建议使用此方式来进行写负载，容易造成数据冲突

　下面是避免产生冲突的一些方法:
　（１）使用这种拓补，最好使两个主总所操作的表能分开，防止数据冲突。
　（２）使用下面两个参数控制自增ID的生成

auto_increment_increment = 2  #自动id的步长  
auto_increment_offset = 1|2   #从什么开始自增

　优点：可以用来做主备模式，比如第一台用于读写操作第二台就只作为只读，当第一台需要维护时，则第二台用来读写操作待维护完成后重新上线。
　
　配置注意事项：确保两台服务器上的初始数据相同;确保两台服务器上已经启动binlog并且有不同的server_id;在两台服务器上启动log_slave_updates 参数；在初始的备库上启用read_only。
　

　4.3 拥有备库的主-主复制的拓补

　　优点:可以减少主库的读负载，坏处是如果一个主库出现问题则会造成从库数据与其他主库的从库数据不一致，使业务出现问题。
　　

5.mysql复制性能的优化

　5.1 主库写入二进制日志的时间

　　解决方法：可以通过控制主库的事务大小，分割大事务。

　5.2 二进制日志传输时间（文件越大，传输时间越长）

　　解决方法：使用MIXED日志格式或设置set binlog_row_image = minimal。

　5.3 默认情况下从只有一个sql线程，主上并发的修改在从上变成了串行

虽然第一种方法说了分割大事务来控制写入日志的时间，但对于修改大表表结构的操作就无法做到分割事务的大小。

　　解决方法：使用多线程复制，在mysql5.7中可以按照逻辑时钟的方式来分配sql线程(适用于mysql5.7后的版本)
　　配置多线程复制：

stop slave  #停止链路复制
set global slave_parallel_type = 'logical_clock'; #开启mysql多线程复制  
set global slave_parallel_workers = 4; #设置复制的线程数
start slave;  #开始复制

6. MYSQL复制常见问题处理

　6.1 主库或从库意外宕机引起的错误

　　　解决方法：修复数据或者重新比对数据进行同步数据库。

　6.2 主库上的二进制日志损坏

　　　解决方法：同上

　6.3 备库上的中继日志损坏

　　　解决方法：通过change master命令来重新指定

　6.4 在从库上进行数据修改造成的主从复制错误 （切记设置从库read_only = on）

　6.5 不唯一的server_id 或 server_uuid

　6.6 max_allow_packet 设置引起的主从复制错误

MySQL复制无法解决的问题：
　　（1）分担主数据库的写负载；（解决可以通过分库分表）；
　　（2）自行进行故障转移及主从切换。（解决得依赖一些组件）；

7. 高可用

高可用性H.A. 指的是通过尽量缩短因日常维护操作（计划）和突发的系统奔溃（非计划）所导致的停机时间，以提高系统和应用的可用性。

　实现：
　　1.避免导致系统的不可用因素，减少系统不可用时间。
　　2.建立完善的监控以及报警系统；
　　3.对备份数据进行恢复测试；
　　4.正确配置数据库环境；
　　5.对不需要的数据进行归档和清理。

8. 读写分离和负载均衡

　8.1 由程序实现读写分离。

　　优点：比较灵活和性能损耗比较少。缺点：增加开发的工作量，使程序代码更加复杂，容易出现错误。
　　缺点：由于增加了中间层，所以对查询效率有损坏。对于延迟敏感业务无法自动在主库执行。
　　

　8.2 由中间件来实现读写分离。（软件：LVS、Haproxy、MaxScale。硬件:F5)

　　优点：由中间件根据查询语法分析，自动完成读写分离，对程序透明，对于已有程序不用做任何调整。
　　（1）mysql-proxy:性能稳定性在高并发下会比较差，不建议使用。
　　（2）maxScale:（建议使用该插件式中间件）
　　　　　有以下五个主要插件：
　　　　　　Authentication 认证插件;
　　　　　　Protocal 协议插件;
　　　　　　Router 路由插件，有readconnrote 连接路由和 readwritesplit 读写分离路由;
　　　　　　Monitor 监控插件;
　　　　　　Filter&Logging 日志和过滤插件。