MySQL集群篇
1 集群之主从复制
1.1 主从复制概述
MySQL主从复制也可以称为MySQL主从同步，它是构建数据库高可用集群架构的基础。它通过将一台主机的数据复制到其他一台或多台主机上，并重新应用relay log中的SQL语句来实现复制功能。MySQL支持单向、双向、链式级联、异步复制，5.5版本之后加入的半同步复制，5.6版本之后的GTID复制，MySQL5.7的多源复制、并行复制、loss-less复制。
常见的几种主从架构
1）单向主从模式：Master --> Slave
2）双向主从模式：Master <====> Master
3）级联主从模式：Master --> Slave1 --> Slave2
4）一主多从模式
5）多主一从模式
1.2 主从复制原理
1.2.1 异步复制
异步复制原理图
067.png
主从同步过程中主服务器有一个工作线程binlog dump thread，从服务器有两个工作线程I/O thread和SQL thread。主库把外界接收的SQL请求记录到自己的binlog中，从库的I/O thread去请求主库的binlog，主库通过binlog dump thread给从库I/O thread传送binlog，从库将binlog写到relaylog中，然后从库的SQL thread重做relaylog中的SQL语句。
主从复制的流程
068.png
1.主库db的更新事件(update、insert、delete)被写到binlog
2.从库启动并发起连接，连接到主库
3.主库创建一个binlog dump thread，把binlog的内容发送到从库
4.从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5.从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
注：上述流程为相对流程，并非绝对流程
在MySQL复制技术中，涉及到三个线程，分别为Dump线程，IO线程，SQL回放线程。
069.png
I/O线程
上图所示，IO线程位于从实例，其作用就是作为一个客户端，建立到master实例上的TCP链接，并且发送认证信息，bingbinlog同步协议信息，然后实时的去同步阻塞的读取master发送的binlog，并且设置有超时时间，为slave_net_timeout，如果在超过设置时间内没有收到master发送的日志信息，则认为主从之间的网络异常，或者master异常，进行网络重试。其生命周期开始于start slave或者start slave io_thread,结束于stop slave或者stop slave io_thread;
Dump线程
如上图所示，Dump线程位于master实例，此线程与普通线程类似，只不过接收到的是客户端发送的binlog dump命令，所以，会发送binlog，而不是其他的数据给客户端。其生命周期开始于slave的start slave或者start slave io_thread,结束于stop slave或者stop slave io_thread。
SQL线程
SQL回放线程位于slave实例，主要作用就是读取relay log，并且进行回放操作。其生命周期开始于start slave或者start slave sql_thread,结束于stop slave或者stop slave sql_thread;
1.2.2 半同步复制
半同步复制原理图：
070.png
MySQL默认的复制方式是异步复制，但是当主库宕机，在高可用架构准备切换时，就会造成新的主库丢失数据的现象。MySQL5.5版本之后引入了半同步复制，但是主从服务器必须同时安装半同步复制插件。在该功能下，确保从库接收主库传递过来的binlog内容已经写入到自己的relay log后才会通知主库上面的等待线程。如果等待超时(超时参数：rpl_semi_sync_master_timeout)，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。
半同步复制提升了主从之间数据的一致性，让复制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point参数，用来控制半同步模式下主库返回给session事务成功之前的事务提交方式。
该参数有两个值：
1）AFTER_COMMIT：5.6版本的默认值，主库将每个事务写入binlog，并传递给从库，刷新到中继日志中，同时主库提交事务。之后主库开始等待从库的反馈，只有收到从库的回复之后，master才将commit OK的结果反馈给客户端。
2）AFTER_SYNC：5.7版本新增，也是默认的半同步复制方式。主库将每个事务写入binlog并传递给从库，刷新到中继日志中，主库开始等待从库的反馈，接收到从库的回复之后，再提交事务并且返回commit OK结果给客户端。
注意：可以通过rpl_semi_sync_master_wait_for_slave_count参数来控制主库接收多少个从库写事务成功反馈，才返回成功给客户端。生产环境中使用半同步复制方式，当从库出现故障，等待超时的时间又很长，导致主库无法接收从库信息而无法正常写入时，可通过该参数剔除故障从库。另外rpl_semi_sync_master_timeout单位是毫秒，它表示如果主库等待从库回复消息的时间超过该值，就自动切换为异步复制模式，建议调整为很大，禁止向异步复制切换来保证数据复制的安全性。MySQL 5.7默认的半同步复制方式是after_sync模式。
在AFTER_SYNC模式下，即使主库宕机，所有在主库上已经提交的事务都能保证已经同步到从库的中继日志中，不会丢任何数据。
1.2.3 GTID复制
GTID又叫全局事务ID，是一个已提交事务的编号，并且是一个全局唯一的编号。GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。
server_uuid是数据库启动自动生成的，保存在auto.cnf文件下，transaction_id是事务提交时由系统顺序分配的一个不会重复的序列行。
GTID存在的价值：
1）GTID使用master_auto_position=1代替了基于binlog和position号的主从复制方式，更便于主从复制的搭建。
2）GTID可以知道事务在最开始是哪个实例上提交的。
3）GTID方便实现主从之间的failover，无须找position和binlog。
GTID限制条件：
1）不能使用create table table_name select * from table_name。
2）不支持CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE语句操作。
3）不支持sql_slave_skip_counter。
1.3 主从备份： mysqldump
关闭主从机器的防火墙
systemctl stop iptables（需要安装iptables服务）
systemctl stop firewalld（默认）
systemctl disable firewalld.service（设置开启不启动）
1.3.1 主服务器配置
第一步：修改my.cnf文件
[mysqld]
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=133
#指定复制的数据库(可选)
binlog-do-db=test
binlog-do-db=db_name，需开启二进制日志文件的数据库，多个数据库则要重复设置
binlog-ignore-db=db_name，无需开启二进制日志文件的数据库，多个数据库则要重复设置
binlog-do-db、binlog-ignore-db 为互斥关系，只需设置其中一项即可
binlog-do-db是在master实例中指定记录哪些库的二进制日志。
replicate-do-db则在slave实例中指定同步哪些库的二进制日志
第二步：重启MySQL服务
systemctl restart mysqld
第三步：主机授予从机备份权限(在主服务器上为从服务器建立一个用户)
# ip填写从服务器ip，root是创建的mysql用户名
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.159.135' identified by '123456';
#或
GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by '123456';
#或
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by '123456';
附：用户权限列表
071.png
第四步：刷新权限
FLUSH PRIVILEGES;
第五步：查询master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 | 871 | test | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1.3.2 从服务器配置
第一步：修改my.cnf文件
[mysqld]
server-id=135
#指定复制的数据库(可选)
replicate-do-db=test
replicate-do-db、replicate-ignore-db 为互斥关系，只需设置其中一项即可
第二步：重启并登录到MySQL进行从服务器配置
mysql>show slave status;
mysql>stop slave;
第三步：同步初始化
mysql>change master to
master_host='192.168.159.133',
master_port=3306,
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000009',
master_log_pos=871;
master_log_file 、 master_log_pos 以主机状态为主
第四步：启动从服务器复制功能
mysql>start slave;
第五步：检查从服务器复制功能状态
mysql>show slave status;
#……………………(省略部分)
Slave_IO_Running: Yes -- 此状态必须为YES
Slave_SQL_Running: Yes -- 此状态必须为YES
#……………………(省略部分)
1.3.3 测试
搭建成功之后，往主机中插入数据，看看从机中是否有数据
# 如果出现复制不成功，可以使用 set global sql_slave_skip_counter=1 忽略一个错误
mysql>set global sql_slave_skip_counter=1;
mysql>start slave
1.4 主从延时
1.4.1 延时判断
在从服务器上执行show slave satus可以看到很多同步的参数：
Master_Log_File： SLAVE中的I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos： 在当前的主服务器二进制日志中，SLAVE中的I/O线程已经读取的位置
Relay_Log_File： SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos： 在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File： 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Slave_IO_Running： I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running： SQL线程是否被启动
Seconds_Behind_Master： 从服务器SQL线程和从服务器I/O线程之间的时间差距，单位以秒计
Seconds_Behind_Master：0，表示主从复制良好；
Seconds_Behind_Master：正值，表示主从已经出现延时，数字越大表示从库延迟越严重；
Seconds_Behind_Master：NULL，表示I/O线程或是SQL线程发生故障；
或者建表时加时间戳(timestamp)，看时间差。
1.4.2 延时原因及解决方案
MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。
Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放，DML和DDL的IO操作是随机的不是顺序的，成本高很多。
另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。
1 主机的tps高于从机的Thread所能承受范围
因为SQL Thread和IO Thread默认是单线程，当主机的tps(每秒事务处理数)高于从机的Thread所能承受范围时，就会出现从机复制延时
解决方案： 将thread改成多线程模式（MySQL5.6改表，MySQL5.7改GTID）
2 网络延时
解决方案： 主和从在一个网内
3 IO延时
解决方案： slave server硬件升级
Slave调整参数，关闭binlog，修改innodb_flush_log_at_trx_commit参数值
当做以上调整后，仍有延时，利用分库分表中间件 Mycat、 sharding JDBC在一定的条件下强制读取主库。
2 集群之读写分离
MySQL的主从复制，只会保证主机对外提供服务，而从机是不对外提供服务的，只是在后台为主机进行备份。读写分离后，主负责写和部分读，从负责读，是一个高性能高可用的数据库集群。
072.png
073.png
074.png
2.1 安装lua脚本语言（mysql-proxy所需）
安装依赖
yum install cpp binutils glibc glibc-kernheaders glibc-common glibc-devel gcc make readline-devel -y
下载
wget http://www.lua.org/ftp/lua-5.3.5.tar.gz
解压
tar -zxvf lua-5.3.5.tar.gz
修改Makefile文件
cd /opt/lua-5.3.5/
vi Makefile
INSTALL_TOP= /usr/local/lua
编译
make linux && make install
添加环境变量
vim /etc/profile
export LUA_HOME=/usr/local/lua
export PATH=$PATH:$LUA_HOME/bin
source /etc/profile
2.2 MySQL-Proxy安装配置
下载
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6- x86-64bit.tar.gz
解压
tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /opt
ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
创建logs目录
mkdir /opt/mysql-proxy/logs
添加环境变量
vim /etc/profile
export MYSQL_PROXY_HOME=/opt/mysql-proxy
export PATH=$PATH:$MYSQL_PROXY_HOME/bin
source /etc/profile
修改mysql-proxy.cnf文件
[mysql-proxy]
# 运行mysql-proxy用户
user=root
# mysql-proxy连接后端mysql服务器的用户
admin-username=mysql_proxy_user
# mysql-proxy连接后端mysql服务器的密码
admin-password=123456
# 代理的监听地址端口，默认端口4040
proxy-address=192.168.159.137:4040
#指定后端主master写入数据
proxy-backend-addresses=192.168.159.133:3306
#指定后端从slave读取数据
proxy-read-only-backend-addresses=192.168.159.135:3306
#指定读写分离配置文件位置
proxy-lua-script=/opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
#日志位置
log-file=/opt/mysql-proxy/logs/mysql-proxy.log
#定义log日志级别，由高到低分别有(error|warning|info|message|debug)
log-level=debug
#以守护进程方式运行
keepalive=true
#mysql-proxy崩溃时，尝试重启
daemon=true
修改mysql-proxy.cnf文件的权限
chmod 660 mysql-proxy.cnf # 可读写
主库添加授权用户
grant all privileges on *.* to 'mysql_proxy_user'@'192.168.159.137' identified by '123456';
刷新权限
flush privileges;
修改控制读写分离的lua脚本
vim /opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
075.png
min_idle_connnections 表示最少多少个连接才开始读写分离
启动代理
./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
在其他客户端，通过mysql命令去连接MySQL Proxy机器
mysql>mysql -umysql_proxy_user -p123456 -h192.168.159.137 -P4040;
