mysql高可用方案
galera集群
环境要求
galera集群至少需要三个节点的服务器硬件，以下操作在三个节点执行。安装后，在任意一个节点执行SQL，都是同步的。
安装
添加RPM源 vi etc/yum.repos.d/galera.repo
[galera] name = Galera baseurl = https://releases.galeracluster.com/galera-3/DIST/RELEASE/ARCH gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com gpgcheck = 1 [mysql-wsrep] name = MySQL-wsrep baseurl=https://releases.galeracluster.com/mysql-wsrep-VERSION/DIST/RELEASE/ARCH gpgkey = https://releases.galeracluster.com/GPG-KEY-galeracluster.com gpgcheck = 1yum 安装
yum install galera-3 mysql-wsrep-5.7 rsync配置开机自启动
systemctl enable mysqld启动mysql
systemctl start mysqld登录MySql命令行，修改密码
如果版本为5.7，系统为root设置了随机密码，需要修改配置文件 /etc/my.cnf,在最后添加如下配置，并重启mysql服务
skip-grant-tables=1 #跳过密码验证，等密码设置成功后，再将此配置删除掉登录mysql
mysql -uroot -p修改密码
update mysql.user set authentication_string=password('001!') where user='root'; flush privileges;设置远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '001!' WITH GRANT OPTION; flush privileges;如果执行出现如下错误
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 50649, now running 50730. Please use mysql_upgrade to fix this error.原因：用户在创建时选择的是MySQL5.7的版本，而导入的备份文件为MySQL5.6的，版本不一致导致MySQL系统表有差异所之后。执行如下命令解决:mysql_upgrade -uroot -p
服务器关闭selinux
setenforce 0 sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config服务器关闭防火墙或者添加端口允许
# 3306 MySQL client connections and mysqldump SST firewall-cmd --zone=public --add-port=3306/tcp --permanent # 4567 Galera Cluster replication traffic firewall-cmd --zone=public --add-port=4567/tcp --permanent # 4568 IST firewall-cmd --zone=public --add-port=4568/tcp --permanent
集群配置
修改 /etc/my.cnf 文件，添加配置
!includedir /etc/my.cnf.d/增加配置文件 /etc/my.cnf.d/galera.cnf
[mysqld] binlog_format=ROW #binlog文件格式：行 default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="fucloud_cluster" wsrep_cluster_address="gcomm://192.168.56.108,192.168.56.109,192.168.56.110" #整个集群的IP地址 # Galera Synchronization Configuration wsrep_sst_method=rsync #拷贝模式xtrabackup-v2 或者 rsync #wsrep_sst_method=xtrabackup-v2 # Galera Node Configuration 节点配置，每个节点只是这部分不同 wsrep_node_address="192.168.56.110" #本节点ip地址 wsrep_node_name="mysql3" #本节点名称
启动集群
随机选择一个节点，使用专用脚本 mysqld_bootstrap 初始化集群
mysqld_bootstrap在其他节点上启动 mysqld 服务
systemctl start mysqld
集群测试
确认集群启动成功（返回当前的集群节点数量）
mysql> show status like 'wsrep_cluster_size';查看galera状态
mysql> show status like 'wsrep%';
参考
https://www.cnblogs.com/weijie0717/p/8445167.html
https://my.oschina.net/colben/blog/1831527
https://blog.51cto.com/14089205/2477697
双主mysql+keepalived集群
两台mysql互为主备，使用keepalived监控mysql状态，进行自动failover。
安装
mysql
mysql安装参考以上。
keepalived
创建依赖环境
[root@localhost yum -y install openssl-devel gcc gcc-c++ [root@localhost mkdir /etc/keepalived [root@localhost wget https://www.keepalived.org/software/keepalived-2.0.18.tar.gz安装keepalived
[root@localhost]# tar -zxvf keepalived-2.0.18.tar.gz [root@localhost]# mv keepalived-2.0.18 /usr/local/keepalived [root@localhost]# cd /usr/local/keepalived [root@localhost]# ./configure && make && make install创建启动文件
[root@localhost]# cp -a /usr/local/etc/keepalived /etc/init.d/ [root@localhost]# cp -a /usr/local/etc/sysconfig /keepalived/etc/sysconfig/ [root@localhost]# cp -a /usr/local/sbin/keepalived /usr/sbin/设置开机启动
[root@localhost yum.repos.d]# systemctl enable keepalived
配置
mysql主备配置
配置服务器mysql1，修改/etc/my.cnf文件，增加如下配置：
[mysqld] server-id=1 #id唯一 log-bin=mysql-bin #开启binlog日志功能 auto-increment-increment=2 auto-increment-offset=1 log-slave-updates配置服务器mysql2，修改/etc/my.cnf文件，增加如下配置：
[mysqld] server-id=2 log-bin=mysql-bin #开启binlog日志功能 auto-increment-increment=2 auto-increment-offset=2 log-slave-updates重启两台服务器的mysql服务
systemctl restart mysqld在mysql1服务器上建立账户并授权
grant replication slave on *.* to 'itscmpsync'@'%' identified by 'dcits001!';注：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.56.109(mysql2服务器的ip地址)，加强安全
登录mysql1服务器的mysql，查询master的状态
mysql> show master status;
注：执行完此步骤后不要再操作master1服务器MYSQL，防止主服务器状态值变化
配置mysql2服务器
mysql>change master to master_host='101.200.56.108',master_user='itscmpsync',master_password='dcits001!',master_log_file='mysql-bin.000008',master_log_pos=154;注：master_log_file和master_log_pos的值应与mysql1服务器状态列出的值对应
启动mysql2服务器复制功能
mysql>start slave;检查mysql2服务器复制功能状态
mysql>show slave status\G
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
同样的操作，设置mysql1为mysql2的从服务器，在mysql2服务器上建立账户并授权
mysql>grant replication slave on *.* to 'itscmpsync'@'%' identified by 'dcits001!';注：一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.56.108(mysql1服务器的ip地址)，加强安全
登录mysql2服务器的mysql，查询master的状态
mysql>show master status;
注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
配置mysql1服务器
mysql>change master to master_host='192.168.56.109',master_user='itscmpsync',master_password='dcits001!',master_log_file='mysql-bin.000010',master_log_pos=154;注：master_log_file和master_log_pos的值应与mysql2服务器状态列出的值对应
启动mysql1服务器复制功能
mysql>show slave status\G检查mysql1服务器复制功能状态
mysql>show slave status\G
keepalived配置
编辑mysql1服务器keepalived配置文件，/etc/keepalived/keepalived.conf
global_defs { router_id mysql-1 #运行keepalived服务器标识 } vrrp_instance VI_1 { state BACKUP #指定keepalived的角色，两台配置此处均是BACKUP，设为BACKUP将根据优先级决定主或从 interface enp0s8 #指定检测网络的接口 #虚拟路由标示，这个标示是一个数字(取值在0-255之间，用来区分多个instance的VRRP组播)，同一个vrrp实例使用 唯一的标示，确保和mysql2相同，同网内不同集群此项必须不同，否则发生冲突 virtual_router_id 51 priority 100#用来选举master,该项取值范围是1-255（在此范围之外会被识别成默认值100），数值大的为master advert_int 1 #发vrrp包的时间间隔，即多久进行一次master选举（可以认为是健康检测时间间隔） #不抢占，允许优先级较低的作为master，即使有priority更高的节点启动，一般只在优先级高的mysql配置 Nopreempt authentication { #认证区域，认证类型有PASS和HA(IPSEC),推荐使用PASS(密码只识别前8位) auth_type PASS auth_pass 1111 } virtual_ipaddress { #VIP区域，指定vip地址 192.168.56.150 #虚拟vip } } #设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开 virtual_server 192.168.56.150 3306 { delay_loop 2 #每隔2秒检查一次real_server状态 lb_algo rr #设置后端调度算法，这里设置为rr,即轮询算法 lb_kind DR #设置LVS实现负载均衡的机制，有NAT、TUN、DR三个模式可选 #会话保持时间，单位为秒。这个选项对动态网页非常有用，为集群系统中的session共享提供了一个很好的解决方案，有了这个会话保持功能，用户的请求会被一只分发到某个服务节点，直到超过这个会话的保持时间 persistence_timeout 60 protocol TCP #指定转发协议类型，有TCP和UDP两种 real_server 192.168.56.108 3306 { #配置服务节点1，需要指定real server的真实IP地址和端口 weight 3 #配置服务的权重 notify_down /etc/keepalived/bin/mysql.sh #检测到服务down后执行的脚本 TCP_CHECK { connect_timeout 3 #连接超时时间 nb_get_retry 3 #重连次数 delay_before_retry 3 #重连间隔时间 connect_port 3306 #健康检查端口 } } }编辑mysql2服务器keepalived配置文件，/etc/keepalived/keepalived.conf
global_defs { router_id mysql-2 #服务器标识 } vrrp_instance VI_1 { state BACKUP interface enp0s8 virtual_router_id 51 priority 50 #优先级，用来选举 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.56.150 } } virtual_server 192.168.56.150 3306 { delay_loop 2 lb_algo rr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.56.109 3306 { weight 3 notify_down /etc/keepalived/bin/mysql.sh TCP_CHECK { connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } }分别在mysql1和mysql2上创建服务down后脚本 /etc/keepalived/bin/mysql.sh
#!/bin/bash pkill keepalived sleep 10 systemctl start keepalived配置运行权限
chmod +x mysql.sh启动keepalived
systemctl start keepalived
总结
- 采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，避免因为意外情况下（比如脑裂）相互抢占导致往两个节点写入相同数据而引发冲突；
- 把两个节点的auto_increment_increment（自增步长）和auto_increment_offset（自增起始值）设成不同值。其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原先master上冲突了，因此一开始就使其错开；当然了，如果有合适的容错机制能解决主从自增ID冲突的话，也可以不这么做；
- .slave节点服务器配置不要太差，否则更容易导致复制延迟。作为热备节点的slave服务器，硬件配置不能低于master节点；
- 如果对延迟问题很敏感的话，可考虑使用MariaDB分支版本，或者直接上线MySQL 5.7最新版本，利用多线程复制的方式可以很大程度降低复制延迟；
参考
https://cloud.tencent.com/developer/article/1139739
https://cloud.tencent.com/developer/article/1343127