MYSQL性能优化之Mysql数据库高可用架构设计之日志复制

Mysql复制功能提供分担读负载

• 复制基于主库上的二进制日志（异步）
• 同一时间点，备库上的数据和主库不一致（主复数据延迟也无法控制）

复制解决问题

mysql日志

show variables like 'binlog_format'
set session binlog_format = STATEMENT(直接记录修改时的sql段)
show binary_logs;
flush logs;
show binary logs;
cd /home/mysql/sql_log
ls -lh
mysqlbinlog 日志名

而且对于特定函数如UUID(),USER()非确定函数还是无法复制，而且如果有使用这样的函数，可能会造成MySQL复制的主备服务器数据不一致

STATEMENT日志格式

ROW日志格式（推荐）

mysql5.7默认基于行的日志格式，记录增删改查操作所修改行的信息

主从复制延时的主要原因就是从服务器存放主服务器二进制日志的效率

show variables like 'binlog_row_image';

full：默认值，记录所有的行信息
minimal:只记录要修改列的记录（推荐）
noblob:记录除了blog和text之外(如果blog或者text没有修改的话)的所有字段

row日志内容：

mysqlbinlog -vv mysql-bin.000000日志名

minimal，只记录了3的修改

noblob,c2是text的类型，c2（第三列）没有显示

SBR

• 逻辑复制，主库记录数据修改的语句，备库读取并且存放这些数据语句（重新执行一遍），基于段
  
  • 定义不同，数据类型可以兼容，或者是列的顺序不同，对大表结构进行修改可以先修改小表，然后主从切换（在线进行大表结构修改的一个很好的方法）
  • 优点
  • 
  • 缺点
  • 
  • 非确定事件，比如UUID

RBR

• 优点
• 
• 使用以上sql时，RBR就不再需要执行锁表的操作了
• 缺点
• 
• 当我们想在从服务器上使用触发器进行数据变更记录时，数据抽取（不行）

Mysql复制方式

• 启动二进制日志需要重启服务器，注意上线之前确保二进制日志已经开启

基于日志点的复制配置步骤

建议ip网段只对存在从服务器的网段来授权

配置主数据库服务器

配置从数据库服务器

- 启动mysql二进制文件(如果之前没有启动过，需要重启服务器)
- server_id 动态参数，也就是说可以通过set来设置（临时修改），配置文件（永久修改），唯一
- relay_log中继日志进行固定，因为默认是以主机名为中继日志名的
- log_slave_update链路复制(当该从服务器做为其他服务器的主服务器时，必须)

初始化从服务器数据

mysqldump 逻辑备份
如果是混合表的话，添加参数--lock-all-tables
备份对数据库需要加锁（影响并发性，在频繁系统，会造成大量的阻塞）
--master-data在备份时，主库当前的二进制文件拼音量的信息（只有使用了这个命令才能在从库使用change_master_to启动主从复制的链路）

第二种方式：
不阻塞服务器的操作，可以在不影响主库的操作下设置从库（InnoDb，最好的方式）
但是混合数据引擎时，同样会对表进行锁操作
--slave-info记录日志信息和日志偏移量信息

启动复制链路

基于日志点的复制配置演示：

more /etc/my.cnf

主服务器配置

从服务器配置

修改配置后，就开始备份了
主库：

mysqldump --single-transaction --master-data --tirggers --routines --all-databases  -uroot -p >> all.sql(前提，mysql版本号一致,如果不一致，就不要备份mysql数据库了)

日志名字和日志偏移量

从库：

yum install openssh-clients

主库：

备份到从库
scp .all.sql root@192.168.3.101:/root 传输

从库：

mysql -uroot -p < all.sql

启动复制链路

从库：

查看主从进程

start slave;启动复制链路。

主库：

基于日志点的复制

优点：

缺点：

因为每一个服务器的二进制文件都是独立的，很难找到日志点

基于GTID的复制（5.6，存在bug）

mysql5.6开始

基于GTID的复制和基于日志的复制对比

 - source_id执行事务主库的server UUID的值(server UUID是 mysql首次启动自动生成，保存在数据库中的数据目录中)
 - 每一个mysql事务的UUID都是不同的

- transaction_id从1开始逐级递增
- 事务：GTID = 1 : 1
- 不要手动在从服务器建立相同的账号，把所有没有在从服务器执行的事务都同步到从上去，会导致启动复制链路时出现错误

主DB Server 建立 复制账号

配置主数据库服务器

数据目录和存放二进制的log目录最好分开（最好分类磁盘分区，会提高磁盘io的提升）

• enforce-gtid-consiste 强制GTID的一致性，保证事务的安全
  
  • 不能存在create table … select 的操作
  • 不能存在create temporary table的操作
  • 不能在同一个事务中混合的更新事务表和非事务表
  • log-slave-updates 从服务器记录主服务器传输过来的修改日志（5.7之前必填，不过会增加从服务器IO负载）

配置从数据库服务器

- 从服务器连接主服务器的信息以及中继日志的存储方式，默认存储在文件中
- 存到表中，Innodb存储引擎，如果数据库崩溃，利用Innodb存储引擎的特点，进行崩溃恢复，以保证从服务器可以从正确的位置重新同步主服务器

初始化从数据库服务器

启动基于GTID的复制

基于GTID的复制配置演示：

主服务器配置

从服务器配置

全部重启数据库服务器
/etc/init.d/mysql restart

备份主数据库文件

转移主数据库文件

从数据库恢复传输过来的数据库文件

从服务器启动复制

show processlist
show slave status

基于GTID复制的优缺点

• 从主从切换来看，倾向于GTID
• mm（只基于日志点的复制）和mha(Master High Availability)（都支持）

MySQL复制拓扑

• MySQL5.7之前，一个从库只能有一个主库
• 5.7之后，支持一从多主架构

一主多从的复制拓扑

优点

• 方便主从切换，主（只读），从库同步完毕主数据，从库中选出一个充当新的主库
• 延迟相同（mysql复制异步，使数据差异达到最小）

用途

• （分隔）前台，数据量小，结果能比较快的返回
• （分隔）后台， 数据量大，查询速度要求不是很明显
• 建立不同的索引（前台查询分别指定到不同的从数据库上）
• 主库写还是会复制到从上面（解决：分库分表）
  

主-主（双主）复制拓扑

主备模式的主主复制（高可用）

配置注意事项

主主模式的主主复制

配置注意事项

• 一台按奇数生成ID，一台按偶数生成ID（自增ID）
• 尽量减少数据冲突(其他因素，比如两边同时对数据更新)

结合

• 每一个从都会在主上bin_log_dump线程
• 主上连接的从不要太多，否则会造成负载，增加网卡带宽

Mysql复制性能优化

• （异步）事务在主库上执行完成后，并记录到二进制文件中，从库才能在二进制文件中，读取完结的事务，并把事务放在从库的中继日志，从库sql线程读取相应的事件，进行存放。
• 所以，延迟是不可避免的
• 分批复制
• 减少二进制日志量
• mysql5.6的多线程复制（每个sql线程只能处理一个数据库上的sql存放）

影响主从延迟的因素

配置多线程复制（5.7）

1. 停止链路复制
2. 设置链路并发为逻辑时钟方式
3. 设置复制线程数量（并发线程处理数）
4. 重启复制

Mysql复制常见问题

• sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。(机器宕机，导致主从数据不一致)

• sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

  
  

• 服务器每次重启都会生成一个新的二进制日志文件

• 会造成数据差异

  

• read_only很重要
• 在多个主服务器上相同server_id,uuid,启动复制会出现错误
• 在多个从服务器上相同server_uuid,不容易发现，隐蔽，一旦生成不重新生成
  
  • 
• max_allow_packet不一致,无限制报错，重试，中继日志的损坏

Mysql复制无法解决的问题

• 分裤分表
• 引入组件