3306 MySQL client connections and mysqldump SST

galera集群

环境要求

galera集群至少需要三个节点的服务器硬件，以下操作在三个节点执行。安装后，在任意一个节点执行SQL，都是同步的。

安装

添加RPM源 vi etc/yum.repos.d/galera.repo

```shell [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 = 1 ```

yum 安装

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

修改密码

mysql update mysql.user set authentication_string=password('001!') where user='root'; flush privileges;

设置远程访问

mysql GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '001!' WITH GRANT OPTION; flush privileges;

如果执行出现如下错误

mysql 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

shell setenforce 0 sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

服务器关闭防火墙或者添加端口允许

```shell

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 文件，添加配置

shell !includedir /etc/my.cnf.d/

增加配置文件 /etc/my.cnf.d/galera.cnf

```shell [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

https://galeracluster.com/

双主mysql+keepalived集群

两台mysql互为主备，使用keepalived监控mysql状态，进行自动failover。

安装

mysql

mysql安装参考以上。

keepalived

创建依赖环境

shell [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

shell [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

创建启动文件

shell [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文件，增加如下配置：

shell [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文件，增加如下配置：

shell [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服务器

shell 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服务器

shell 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

```shell 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

```shell 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

shell #!/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

https://www.cnblogs.com/gered/p/11221702.html