高可用MySQL架构

There are one hundred ways to fix a problem. However, the ultimate solution is, don’t give it any chance to happen. (纠正一个错误的方法有很多，而解决一个错误的终极方法就是不给它任何发生的机会。)
知之为知之，不知为不知，是知也。
也许是天赋不足、也许是智商捉急，更可能是懒惰和拖延作祟。但是既然开始了，就不愿意停下来。
我本微末凡尘，却也心向天空。—— 我吃西红柿·《莽荒纪》
我在起点与终点之间两全其美,却无法禅定于一夜琴声。直至悠悠的琴声被暗香淹没，我才刚刚赶到岸边，片刻之间，已被一缕清风绣在水面。 ——仓央嘉措

数据库复制功能

数据库服务器由于事务性不能像web服务器那样简单增加机器的数量来进行拓展（事务）。MySQL的复制功能提供了分担读负载的办法。增加多个备库分担主服务器的读负载，为高可用、灾难恢复、备份提供更多选择。MySQL复制利用二进制日志进行增量异步复制，需要的带宽少。其原理是在备库上重放主库上的二进制日志文件。

MySQL二进制日志

MySQL日志分类

在二进制文件中记录了所有对数据库的修改事件，包括CRUD和对表结构的修改事件。有一点需要注意：二进制日志文件中记录的都是成功执行了的，已经回滚了的或者有语法错误的SQL是不在二进制日志文件中的。

二进制日志的格式

1	mysql> show variables like 'binlog_format';

基于段的格式：binlog_format=STATEMENT,日志记录量相对较小，节约磁盘和网络IO，缺点是：必须记录上下文信息，保证语句在从服务器上执行结果和主服务器上相同。对特定函数，例如：UUID(),USER()这样的非确定性函数无法复制（可能造成主从服务器数据不一致）。也称为基于SQL语句的复制，相比于基于行的复制会造成更多的行锁，因为基于行的复制仅仅是插入一条数据。

mysql> set session binlog_format=statement;
mysql> show binary logs;
mysql> flush logs; -- 会产生新的binlog文件
mysql> show binary logs;
mysql> create database crn;
mysql> use crn;
mysql> create table t(id int,c1 varchar(10));
mysql> insert t values(1,'a'),(2,'b');
mysql> update t set c1 = 'dd' where id = 1;

以上我们进行了数据的CRUD操作，接下来使用mysqlbinlog工具查看二进制日志文件（数据目录下）：

1	$ mysqlbinlog muke-49-468585-v52l3-bin.000004

在此日志文件中我们可以很清楚看到执行的SQL语句。

基于行的日志格式：binlog_format=ROW,该种格式可以避免MySQL主从复制中不一致的问题，是5.7之后的默认格式。和基于段的日志不同的是：同一语句修改了1000条数据，基于段的日志只会记录1条SQL，而基于行的复制会有1000条记录分别记录每一行的数据修改。如果修改了DB中的数据，同时又没有备份可以恢复的时候我们可以通过分析二进制日志，对日志中记录的操作进行反向处理来恢复数据。该种日志默认保存每一行，日志量大，但是可以通过binlog_row_image=MINIMAL（默认值为FULL,还可以是NOBLOB(与FULL相似，区别仅为不记录TEXT和BLOB)）来达到节约网络和磁盘IO的目的。

mysql> set session binlog_format=row;
mysql> show variables like 'binlog_row_image';
mysql> alter table t add t2 text; # 重用上面的t表并为其增加字段
mysql> insert t values(3,'hehe','hahahaha');
mysql> delete from t where id = 1;

使用mysqlbinlog命令查看二进制文件内容：

1	$ mysqlbinlog -vv muke-49-468585-v52l3-bin.000006 \| less # 注意基于ROW的二进制日志需要加入-vv参数

混合日志格式binlog_format=MIXED是基于段的和基于行的日志格式的一种折衷选择。根据SQL语句由系统决定在基于段和基于行的日志格式间进行选择，大部分会采用STATEMENT,而类似于UUID之类的会采用基于ROW的日志格式；数据量大小由所执行的SQL决定。

MySQL的复制功能必须依赖于二进制日志文件，其工作原理如下图：

MySQL复制

在实际应用中建议使用混合的或者基于ROW的日志格式。

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

1 2	mysql> CREATE USER 'repl'@'从Server IP段' identified by 'password'; -- 在主DB上建立复制账号 mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'从Server IP段';

主服务器上有2个参数需要配置：

1 2	log-bin=mysql-bin server-id=1

从服务器上配置对应的参数：

log-bin=mysql-bin
server-id=2
relay_log=mysql-relay-bin
log_slave_update=on # 可选
read_only=on # 可选，安全考虑

在从服务器上启动复制链路

1	mysql> CHANGE MASTER TO MASTER_HOST = 'master_host_ip',MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='master_log_file_name' MASTER_LOG_POS=4;

下面是实际操作：

主库：192.168.1.4(server-id=1)，从库:192.168.1.5(server-id=2)，IP地址的查看可以使用ip addr命令。

在主库上创建并授权用户

1 2	mysql> create user repl@'192.168.1.%' identified by '123456'; -- 主库创建用户 mysql> grant replication slave on . to repl@'192.168.1.%';

从主库导出数据并根据此数据初始化从库

1 2	$ mysqldump -uroot -p123456 --single-transaction --master-data --triggers --routines --all-databases >> all.sql $ scp all.sql root@192.168.1.5:/root

在从库上导入sql文件：

1	$ mysql -uroot -p123456 < all.sql

在从库上使用change master命令。

1	mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.4',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=429; -- 最后2个参数可以从all.sql中得到

以上就完成了主从备份的配置，我们可以在从库上使用start slave;启动备份，并使用show slave status\G;观察可以发现IO进程和SQL进程都已经起来了。在master和slaver上我们也可以分别执行show processlist;来查看mysql进程。

基于GDIT的复制

全局事务ID，保证为每一个在主库上提交的事务在复制集群中可以生成唯一ID。5.6版本开始起，新支持的复制方式，和基于日志的方式存在很大不同。基于日志的方式要求告诉从主库的哪个二进制文件的偏移量进行增量同步，如果指定错误会造成遗漏或者重复，从而造成数据一致性问题。

基于GDIT的复制

GTID=source_id:transaction_id，GTID由2部分组成。source_id是主库的server-uuid，在DB启动时自动生成（数据目录下的auto.cnf）,可以保证每个MySQL实例的server-uuid都是不同的。

具体步骤

首先在主库上建立账号，并授权（同基于日志点的复制）。主库和从库都需要配置log-bin和server-id。相比于基于日志点的复制，需要在主库上多设置如下内容：
是：

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on
master_info_repository=table
relay_log_info_repository=table

从库配置:

1
2
3

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=on

接下来导出主库数据并在从库上恢复：

1 2	$ mysqldump -uroot -p123456 --single-transaction --master-data=2 --triggers --routines --all-databases > all2.sql $ scp -P22 all2.sql root@192.168.1.5:/root

最后使用change master命令指定复制。

1
2
3

mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.4',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;
mysql>start slave;
mysql>show slave status\G;

我们可以在从库上执行stop slave;set global slave_parallel_workers=4;start slave;show processlist;进行多线程复制。

这种方式的优点是可以很方便地进行故障转移，从库不会丢失主库上的任何修改（建立在主库的二进制日志文件没有被删除）

mysql查看用户授权：use mysql;show grants for repl@'192.168.1.%';

基于GTID的复制方式在数据完整性和安全性上较高。

MySQL复制性能优化

MySQL复制是异步的，只有事务在主库上执行完并记录到二进制日志中之后，从库才能从二进制日志中读取并执行事务

高可用架构

HA(High Availability)指的是通过尽量缩短因日常维护操作和突发的系统崩溃导致的停机时间，以提高系统和应用的可用性。5个9的可用性要求全年只有5min对外不提供服务：3602460*(1-0.99999)=5.26.

MMM

Multi-Master Replication Manager。监控MySQL的主主复制拓扑，在当前主服务器失效时，进行主备服务器之间的主从切换和故障转移。

主库：192.168.1.4和5互为主从，192.168.4为主，6为从。首先使用基于日志点的复制方法导出主库数据。接下来在三个节点上都安装3M包：

1	$ apt-get install mysql-mmm-agent

在监控节点192.168.1.6上安装3M监控apt get install mysql-mmm-common mysql-mmm-monitor mysql-mmm-tools

在4上建立DB账号：

1 2	mysql>grant replication client on . to 'mmm_monitor'@'192.168.1.%' identified by '123456'; mysql>grant super,replication client,process on . to 'mmm_agent'@'192.168.1.%' identified by '123456';

MMM架构

/etc/mysql-mmm/mmm_common.conf是通用配置，所有集群节点中都是一致的。

active_master_role     	writer


<host default>
       	cluster_interface      		eth0

       	pid_path       				/var/run/mmm_agentd.pid
       	bin_path       				/usr/lib/mysql-mmm/

    # repl的用户名和密码
    replication_user        repl
    replication_password    123456

       	agent_user     				mmm_agent
       	agent_password 			123456
</host>

# 主库
<host db1>
       	ip     						192.168.1.4
       	mode   					master
       	peer   					db2
</host>

# 主备
<host db2>
       	ip     						192.168.1.5
       	mode   					master
       	peer   					db1
</host>

# 从
<host db3>
       	ip     						192.168.1.6
       	mode   					slave
</host>

# 主、主备，虚拟ip
<role writer>
       	hosts  					db1, db2
       	ips    						192.168.1.50
       	mode   					exclusive
</role>

<role reader>
       	hosts  					db1, db2, db3
       	ips    						192.168.1.51, 192.168.1.52, 192.168.1.53
       	mode   					balanced
</role>

将以上配置复制到各个节点的对应位置。

1 2	$ scp mmm_common.conf root@192.168.1.5:/etc/mysql-mmm/mmm_common.conf $ scp mmm_common.conf root@192.168.1.6:/etc/mysql-mmm/mmm_common.conf

分别配置3台节点上的mmm_agent.conf为db1,db2和db3

现在为止我们已经配置完成了db节点，接下来需要配置监控节点192.168.1.6,/etc/mysql-mmm/mmm_mon.conf:

include mmm_common.conf

<monitor>
        ip                                      192.168.1.6
        pid_path                                /var/run/mmm_mond.pid
        bin_path                                /usr/lib/mysql-mmm/
        status_path                             /var/lib/misc/mmm_mond.status
        ping_ips                                192.168.1.4, 192.168.1.5, 192.168.1.6
</monitor>

<host default>
        monitor_user                    mmm_monitor
        monitor_password                RepMonitor
</host>

debug 1

使用apt-get安装完3M后会在/etc/init.d/下生成2个脚本，分别表示代理和监控。在集群所有节点中启动所有的代理进程，可能会遇到下面的问题：mmm_agent_navy2.conf is world readable! at /usr/share/perl5/MMM/Common/Config.pm line 132，解决方案是：chmod 640 /etc/mysql-mmm/*。在最后一个节点启动监控服务。

1 2	$ /etc/init.d/mysql-mmm-agent start $ /etc/init.d/mysql-mmm-monitor start

以上即完成了3M集群，当一台主服务器宕机时将写操作转到另一个DB。它提供了读写VIP（虚拟IP），使服务器角色的变更对前端应用透明。在服务器出现大量的主从延迟或者主从链路中断时可以把这台服务器上的读的虚拟IP漂移到集群中其他正常的服务器上，提供了了从服务器的延迟监控，提供了主DB故障转移后从服务器对新主的重新同步功能。但是缺点也是明显的：发布时间较早，不支持MySQL新的复制功能（GDIT复制）；没有读负载均衡的能力；进行主从切换时容易造成数据丢失，由于3M监控服务器只有一台，因此容易造成单点故障。

MHA架构

全称是Master High Availability,由Perl脚本开发，可在30s内完成主从切换。集群中有3个节点都是192.168.1网段，分别是4(主)，5（从），6（从，监控节点）。3个节点上都运行着一个MySQL实例，并且启用了GTID_MODE(show variables like '%gtid_mode%';)。以4为主库，5，6为从库，使用change master命令建立基于GTID的复制。

配置各个节点SSH免认证登陆

使用ssh-keygen命令生成密钥（简单回车即可）,在4上执行以下命令：

$ ssh-keygen # 一路回车
$ ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.1.4
$ ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.1.5
$ ssh-copy-id -i /root/.ssh/id_rsa -p 22 root@192.168.1.6

在集群中所有节点进行类似操作。

mha软件包认为node和manager，前者需要安装在集群中的所有节点，后者只需要安装在监控节点(使用apt-get即可)。
主库节点（4）上建立用户

1	mysql> grant all privileges on . to mha@'192.168.1.%' identified by '123456';

在监控节点（6）上进行MHA配置：

$ mkdir -p /etc/mha;mkdir -p /home/mysql_mha # 建立工作目录和配置目录
$ vim /etc/mha/mysql_mha.conf # 配置文件如下
[server default]
user=mha
password=123456
manager_workdir=/home/mysql_mha
manager_log=/home/mysql_mha/manager.log
remote_workdir=/home/mysql_mha
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/var/log/mysql
[server1]
hostname=192.168.1.4
candidate_master=1
[server2]
hostname=192.168.1.5
candidate_master=1
[server3]
hostname=192.168.1.6
no_master=1

在监控节点(6)上进行基础环境的检测

1 2	$ masterha_check_ssh --conf=/etc/mha/mysql_mha.conf $ masterha_check_repl --conf=/etc/mha/mysql_mha.conf

步骤5的检测通过后使用nohup masterha_manager --conf=/etc/mha/mysql_mha.conf &在监控节点上启动mha。

读写分离和负载均衡

为什么要读写分离？

一般来说，写负载时不可分担的，而且只能在主上进行写操作，而读操作在主和从上都可以进行。为了分担主库的压力，将读操作分离到从库上。主从复制一般有2种：

程序实现读写分离
中间件实现读写分离，例如：mysql-proxy和maxScale。由中间件根据查询语法进行分析，自动完成读写分离，对应用程序透明，已有程序不需要做任何修改。缺点是查询效率降低（降低50%~70%的QPS），因此使用中间件需要进行必要的基准测试。

读写分离中间件maxScale

maxscale是mariadb公司开发的读写分离中间件。

高可用MySQL架构