高性能数据库之三MySQL架构设计

一、MySQL复制功能
作用：
实现在不同服务器上数据分布
实现数据读取负载均衡（利用DNS轮询的方式或者用lvs）
增强了数据的安全性
实现数据库高可用和故障切换
实现数据库在线升级

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

二、二进制日志格式
基于段的格式：binlog_format=STATEMENT
优点：日志记录量相对较小，节约磁盘以及网络I/O
缺点：可能造成复制的主备服务器数据不一致

show variables like ‘binlog_format’; 查看日志格式
如果为row 则 set session binlog_format=statement;
show binary logs;
//刷新logs
flush logs;

基于行的日志格式：
binlog_format=ROW
优点：
使MySQL主从复制更加安全
对每一行数据的修改比基于段的复制高校
操作失误时可以反向操作处理
缺点：
记录日志量较大
binlog_row_image=[FULL|MINIMAL|NOBLOB]

混合日志格式：
binlog_format=MIXED
特点：
根据SQL语句由系统决定在基于段和基于行的日志格式中进行选择
数量的大笑由所执行的SQL语句决定

建议：
binlog_format=mixed或者
binlog_format=row
选择row时同时得设定参数binlog_row_image=minimal

三、二进制格式对复制的影响
1、基于行的复制（PBR）
优点：
可以应用于任何SQL的复制包括非确定喊叔，存储过程等
可以减少数据库锁的使用
insert into order_cnt（timestr,total,amount） select date(order_date),count(*),sum(amount) from order group by date(order_date);
缺点：
要求主从数据库表结构相同，否则可能会中断复制
无法在从服务器上单独执行触发器

2、工作原理
主服务器将变更写入二进制日志
从读取主服务器的二进制日志变更写入到relay_log中
在从服务器上重放relay_log中的日志

基于日志点的复制配置步骤
1、配置从数据库服务器
binlog = mysq-bin
server_id = 101
relay_log = mysql_relay_bin
log_slave_update = on(可选）(是否放到从服务器二进制日志中，如果从服务器当做其他服务器复制对象则必须)
read_only = on （可选）

2、初始化从服务器数据
mysqldump –master-date = 2 -single-transaction（容易造成阻塞）
xtrabackup –slave-info（不影响堵塞，非innodb表会阻塞）

3、启动复制连路
CHANGE MASTER TO MASTER_HOST =’master_host_ip’,MASTER_USER =’repl’,MASTER_PASSWORD=’PASSWORD’,MASTER_LOG_FILE=’mysql_log_file_name’,MASTER_LOG_POS=4

2、基于GTID的复制

MySQL复制拓扑
1、一主多从的复制拓扑
优点：配置简单、可以用多个从库分担读负载
用途：
为不同业务使用不同从库
将一台从库放到远程IDC，用作灾备恢复
分担主库的读负载
2、主主复制拓扑
注意事项：产生数据冲突而造成复制链路的中断，造成数据丢失
两个主中所操作的表最好能够分开
使用下面两个参数控制自增ID的生成
auto_increment_increment=2
auto_increment_offset=1|2（一台设为1一台设为2）

主备模式下的主主复制配置注意事项
确保两台服务器上的初始数据相同
确保两台服务器上已经启动binlog并且有不同sever_id
在两台服务器上启用log_slave_updates参数
在初始的备库上启用read_only

影响主从延迟的因素
1、主库写入二进制日志的时间
控制主库的事务大小，分割大事物
2、二进制日志传输的时间
使用mixed日志格式
或者设置setbinlog_row_image =minimal
3、 默认情况下从只有一个SQL线程，主上并发的修改在从上变成了串行
使用多线程复制
配置多线程复制：
stop slave
set global slave_parallel_type=’logical_clock’;
set global slave_parallel_workers=4;
start slave;

常见复制问题处理
1、由于数据损坏或丢失引起的主从复制错误
主库或从库意外宕机引起的错误
主库上二进制日志损坏
备库上的中继日志损坏
2、从库上数据修改造成的主从复制错误
3、不唯一的server_id或server_uuid
4、max_allow_packet设置引起的主从复制错误

主从复制作用：
分担主数据库的写负载
自动进行故障专一及主从切换
提供读写分离功能

四、高可用数据库架构
高可用性HA指通过尽量缩短因日常维护操作（计划）和突发的系统崩溃（非计划）所导致的停机时间，以提高系统和应用的可用性。

如何实现高可用：
1、避免导致系统不可用的因素，减少系统不可用的时间：
建立完善的监控报警系统，对备份数据
对备份数据进行恢复测试
正确配置数据库环境
对不需要的数据进行归档和清理
2、增加系统冗余，保证发生系统不可用时可以尽快恢复
避免存在单点故障
主从切换及故障转移
解决单点故障：
利用SUN共享存储或DRDB磁盘复制解决MySQL单点故障
利用多写集群或DNB集群来解决MySQL单点故障

MMM：监控和管理MySQL主从复制拓扑

MHA: