Mysql 集群技术

1、mysql在服务器中的部署

[root@sql10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql #指定数据目录
socket=/data/mysql/mysql.sock #指定套接字
symbolic-links=0 #数据只能存放到数据目录中，禁止链接到数据目录

A、mysql源码编译安装

10配置

2、mysql的组从复制

B、mysql的组从复制

在slave中查看数据是否有同步过来

a、当有数据时添加slave2

注：生产环境中备份时需要锁表，保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;
备份后再解锁
mysql> UNLOCK TABLES;
mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句

#从master节点备份数据
[root@sql10 ~]# mysqldump -uroot -predhat ll > ll.sql
[root@sql10 ~]# scp ll.sql root@172.25.254.30:/mnt/
[root@sql30 ~]# cd /mnt/
[root@sql30 mnt]# ls
ll.sql
[root@sql30 mnt]# mysql -uroot -pll -e "create database ll;"
[root@sql30 mnt]# mysql -uroot -pll ll < ll.sql

b、延迟复制

延迟复制时用来控制 sql 线程的，和 i/o 线程无关

这个延迟复制不是 i/o 线程过段时间来复制， i/o 是正常工作的

是日志已经保存在 slave 端了，那个 sql 要等多久进行回放

c、慢查询日志

当执行 SQL 超过 long_query_time 参数设定的时间阈值（默认 10s ）时，就被认为是慢查询，这个

SQL语句就是需要优化的

慢查询被记录在慢查询日志里慢查询日志默认是不开启的

如果需要优化 SQL 语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

mysql> SHOW variables like "slow%";
mysql> SET GLOBAL slow_query_log=ON;
mysql> SET long_query_time=4;
mysql> SHOW VARIABLES like "long%";
mysql> SHOW VARIABLES like "slow%";
[root@mysql10 ~]# cat /data/mysql/mysql-node1-slow.log #慢查询日志
mysql> select sleep (10);
[root@mysql10 ~]# cat /data/mysql/mysql-node1-slow.log

3、mysql的并行复制（主从日志多线程回放）

（1）、原理：

三个线程：实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。

二进制日志转储线程（ Binlog dump thread ）是一个主库线程。当从库线程连接的时候，主库可以

将二进制日志发送给从库，当主库读取事件（ Event ）的时候，会在 Binlog 上加锁，读取完成之

后，再将锁释放掉。

从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog 。这时从库的 I/O 线程就可以读取到主库

的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（ Relay log ）。

从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同

步。

（2）、步骤：

a、复制

Master 将写操作记录到二进制日志（ binlog ）。

Slave 将 Master 的 binary log events 拷贝到它的中继日志（ relay log ）；

Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL 复制是异步的且串行化

的，而且重启后从接入点开始复制。

b、操作

slaves 端中设置了 master 端的 ip ，用户，日志，和日志的 Position ，通过这些信息取得 master 的认证及信息

master 端在设定好 binlog 启动后会开启 binlog dump 的线程

master 端的 binlog dump 把二进制的更新发送到 slave 端的

slave 端开启两个线程，一个是 I/O 线程，一个是 sql 线程，【i/o线程用于接收 master 端的二进制日志，此线程会在本地打开 relaylog 中继日志，并且保存到本地磁盘；sql线程读取本地 relog 中继日志进行回放】

（3）缺陷：

主从架构采用的是异步机制；master更新完成后直接发送二进制日志到 slave ，但是 slaves 是否真正保存了数据 master 端不会检测；master端直接保存二进制日志到磁盘；当master 端到 slave 端的网络出现问题时或者 master 端直接挂掉，二进制日志可能根本没有到达 slave ；master出现问题 slave 端接管 master ，这个过程中数据就丢失了；这样的问题出现就无法达到数据的强一致性，零数据丢失。

C、mysql的并行复制

slave-parallel-type=LOGICAL_CLOCK #基于组提交，
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录，默认记录
在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录，默认记录
在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启

4、半同步模式

MySQL从5.5开始引入了半同步复制，半同步复制介于异步复制和同步复制之间。主库在提交事务时先等待，必须确认至少一个从库收到了事件（从库将事件写入relaylog，不需要重放和提交，并向主库发送一个确认信息ACK），主库收到确认信息后才会正式commit。与同步复制相比，半同步复制速度快很多，因为他只需要至少1个从库确认写入relaylog，并不需要完成在从库上的事务提交，同时又比异步复制更安全，因为主库在提交时，事务至少已经存在2个地方（主库的binlog和从库的relaylog）。由于半同步复制在提交事务前，需要从库返还确认信息，所以这里涉及到网络的往返通信开销，因此半同步复制只适合在网络条件较好的且地理上距离不远的环境部署，否则可能会因为网络延迟大幅降低主库性能。

半同步复制的特点：

从库在连接主库时需要表明它是否支持半同步复制。
如果主库启用了半同步复制，且有一个支持半同步复制的从库，则主库上事务提交将等待至少一个从库确认已收到事务，或者直到发生超时。
默认只有在将事务写入其中继日志并刷新到磁盘后，主库才会提交事务（也可以配置成提交后等待确认）。
如果没有任何从库确认事务的情况下发生超时，则主库将退化为异步复制。当至少有一个半同步从库赶上时，主库恢复半同步复制。退化与恢复过程都是自动的。
必须在主库和从库上都启用半同步复制，否则使用异步复制。

（1）、gtid模式

在 master 端的写入时多用户读写，在 slave 端的复制时单线程日志回放，所以 slave 端一定会延迟与

master 端这种延迟在slave 端的延迟可能会不一致，当 master 挂掉后 slave 接管，一般会挑选一个和 master 延迟日志最接近的充当新的master 那么为接管master 的主机继续充当 slave 角色并会指向到新的 master 上，作为其 slave

当激活 GITD 之后

当 master 出现问题后， slave2 和 master 的数据最接近，会被作为新的 master slave1指向新的 master ，但是他不会去检测新的 master 的 pos id ，只需要继续读取自己 gtid_next 即可

D、gtid日志模式

（2）、半同步

E、半同步模式

5、mysql高可用之组复制 (MGR)

（1）、组复制

将多个节点共同组成一个复制组，在执行读写（ RW ）事务的时候，需要通过一致性协议层

（ Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里 “ 大多数人 ” （对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1 ），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO ）事务则不需要经过组内同意，直接提交即可

[root@mysql10 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1 #配置server唯一标识号
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储
引擎
gtid_mode=ON #启用全局事件标识
enforce_gtid_consistency=ON #强制gtid一致
master_info_repository=TABLE #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE #禁止对二进制日志校验
log_slave_updates=ON #打开数据库中继，
#当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog #重新指定log名称
binlog_format=ROW #使用行日志格式
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction=XXHASH64 #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正
式加入
#或创建的组
名
#名称为
uuid格式
group_replication_start_on_boot=off #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.10:33061" #指定插件接受其他成员的信息端口
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061" #本地地址允许访问成员列表
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #主机白名单
group_replication_bootstrap_group=off #不随系统自启而启动
group_replication_single_primary_mode=OFF #使用多主模式
group_replication_enforce_update_everywhere_checks=ON #组同步中有任何改变检测更新
group_replication_allow_local_disjoint_gtids_join=1 #放弃自己信息以master事件为主

（2）、组复制的单主和多主模式

single-primary mode( 单写或单主模式 )

单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主服务器，

multi-primary mode( 多写或多主模式 )

组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。

F、mysql组复制

[root@sql10 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@sql20 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@sql30 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
[root@sql10 mysql]# rm -rf /data/mysql/*
[root@sql20 mysql]# rm -rf /data/mysql/*
[root@sql30 mysql]# rm -rf /data/mysql/*

[root@sql10 mysql]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!

6、mysql-router（mysql路由）

Mysql路由器可以实现指定端口的读写分离，能够实现读写调度到Mysql组复制集群中的不同后端

MySQL Router

是一个对应用程序透明的 InnoDB Cluster 连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。

利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL 数据库服务器

G、mysql-router（mysql路由）

7、mysql高可用之MHA

（解决master的单点故障问题）

（1）、组成

MHA 由两部分组成 :MHAManager ( 管理节点 ) MHA Node ( 数据库节点 ),

MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。

MHA Manager 会定时探测集群中的 master 节点。

当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master ，然后将所有其他的

slave 重新指向新的 master 。

（2）、特点

自动故障切换过程中， MHA 从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失

使用半同步复制，可以大大降低数据丢失的风险，如果只有一个 slave 已经收到了最新的二进制日

志， MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数

据一致性

目前 MHA 支持一主多从架构，最少三台服务，即一主两从

（3）、工作原理

目前 MHA 主要支持一主多从的架构，要搭建 MHA, 要求一个复制集群必须最少有 3 台数据库服务器，一主二从，即一台充当Master ，台充当备用 Master ，另一台充当从库。

MHA Node 运行在每台 MySQL 服务器上

MHAManager 会定时探测集群中的 master 节点

当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master

然后将所有其他的 slave 重新指向新的 master ， VIP 自动漂移到新的 master 。

整个故障转移过程对应用程序完全透明。

H、mysql高可用集群

在软件中包含的工具包介绍
1.Manager工具包主要包括以下几个工具：
masterha_check_ssh #检查MHA的SSH配置状况
masterha_check_repl #检查MySQL复制状况
masterha_manger #启动MHA
masterha_check_status #检测当前MHA运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #控制故障转移（自动或者手动）
masterha_conf_host #添加或删除配置的server信息
2.Node工具包 （通常由masterHA主机直接调用，无需人为执行）
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs #清除中继日志（不会阻塞SQL线程）

[root@sql10 ~]# rm -rf /data/mysql/*
[root@sql20 ~]# rm -rf /data/mysql/*
[root@sql30 ~]# rm -rf /data/mysql/*
[root@sql10 mysql]# mysqld --user=mysql --initialize
[root@sql20 mysql]# mysqld --user=mysql --initialize
[root@sql30 mysql]# mysqld --user=mysql --initialize

[root@sql10 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@sql20 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@sql30 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

[root@sqlmha masterha]# vim app1.cnf[server default]
user=root #mysql管理员用户，因为需要做自动化配置
password=lee #mysql密码
ssh_user=root #ssh远程登陆用户
repl_user=repl #mysql主从复制中负责认证的用户
repl_password=lee #mysql主从复制中负责认证的用户密码
master_binlog_dir= /data/mysql #二进制日志目录
remote_workdir=/tmp #远程工作目录
#此参数使为了提供冗余检测，方式是mha主机网络自身的问题无法连接数据库节点，应为集群之外的主机
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s
172.25.254.11
ping_interval=3 #每隔3秒检测一次
#发生故障后调用的脚本，用来迁移vip
# master_ip_failover_script= /script/masterha/master_ip_failover
#电源管理脚本
# shutdown_script= /script/masterha/power_manager
#当发生故障后用此脚本发邮件或者告警通知
# report_script= /script/masterha/send_report
#在线切换时调用的vip迁移脚本，手动
# master_ip_online_change_script= /script/masterha/master_ip_online_change
manager_workdir=/etc/masterha #mha工作目录
manager_log=/var/etc/masterha/manager.log #mha日志
[server1]
hostname=172.25.254.10
candidate_master=1 #可能作为master的主机
check_repl_delay=0 ##默认情况下如果一个slave落后master 100M的relay logs的话
#MHA将不会选择该slave作为一个新的master
#因为对于这个slave的恢复需要花费很长时间
#通过设置check_repl_delay=0
#MHA触发切换在选择一个新的master的时候将会忽略复制延时
#这个参数对于设置了candidate_master=1的主机非常有用
#因为这个候选主在切换的过程中一定是新的master
[server2]
hostname=172.25.254.20
candidate_master=1 #可能作为master的主机
check_repl_delay=0
[server3]
hostname=172.25.254.30
no_master=1 #不会作为master的主机

I、 MHA的故障切换

#在master数据节点还在正常工作情况下
[root@sqlmha ~]# masterha_master_switch \
--conf=/etc/masterha/app1.cnf \ #指定配置文件
--master_state=alive \ #指定master节点状态
--new_master_host=172.25.254.20 \ #指定新master节点
--new_master_port=3306 \ #执行新master节点端口
--orig_master_is_new_slave \ #原始master会变成新的slave
--running_updates_limit=10000 #切换的超时时间

手动切换

恢复故障mysql节点

J、为MHA添加VIP功能

[root@sql10 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!
[root@sql10 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 
[root@sql10 ~]# mysql -uroot -pll

[root@sqlmha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000