MySQL5.7+keepalived双主Gtid复制同步
MySQL+keepalived双主Gtid复制同步
CentOS7环境：
master-1主机(192.168.1.10) master-2主机(192.168.1.12) keepalived virtual ip(192.168.1.100)
一、master-1主机配置
步骤：
①关闭防火墙或开启端口
[root@192 ~]# systemctl stop firewalld
②配置主配开启二进制日志，开启gtid模式
[root@192 ~]# vim /etc/my.cnf
[mysqld] basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 server_id = 1 socket = /usr/local/mysql/mysql.sock log-error=/usr/local/mysql/data/mysqld.err log-bin = mysql-bin gtid_mode = on enforce_gtid_consistency = on binlog_format = row log-slave-updates = 1 skip_slave_start = 1 auto-increment-increment = 2 auto-increment-offset = 1 [client] host = 127.0.0.1 user = root password = 123.com
[root@192 ~]# systemctl restart mysqld
③配置复制用户权限
mysql> grant replication slave on . to master1@'%' identified by '123.com';
二、mater-2主机配置与主从配置
步骤：
①关闭防火墙或开启端口
[root@192 ~]# systemctl stop firewalld
②配置主配开启二进制日志，开启gtid模式
[root@192 ~]# vim /etc/my.cnf
[mysqld] basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 server_id = 2 auto-increment-increment = 2 auto-increment-offset = 2 socket = /usr/local/mysql/mysql.sock log-error=/usr/local/mysql/data/mysqld.err log-bin = mysql-bin gtid_mode = on enforce_gtid_consistency = on binlog_format = row log-slave-updates = 1 skip_slave_start = 1 [client] host = 127.0.0.1 user = root password = 123.com
[root@192 ~]# systemctl restart mysqld
③配置复制用户权限
mysql> grant replication slave on . to master2@'%' identified by '123.com';
④开启master-2主机从同步
mysql> change master to master_host='192.168.1.10',master_user='master1',master_password='123.com',master_auto_position=1;
mysql> start slave ;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: master1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 736
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 949
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......⑤开启master-1主机从同步
mysql> change master to master_host='192.168.1.12',master_user='master2',master_password='123.com',master_auto_posittion=1;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.12
Master_User: master2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1318
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 996
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........三、keepalived配置
步骤：
①前往keepalived下载合适的版本，在两台master都与需要安装部署
[root@192 ~]# wget http://www.keepalived.org/software/keepalived-1.4.3.tar.gz
[root@192 ~]# yum -y install openssl openssl-devel
[root@192 ~]# cd keepalived-1.4.3/
[root@192 keepalived-1.4.3]# ./configure --prefix=/ && make && make install
[root@192 keepalived-1.4.3]# whereis keepalived
keepalived: /usr/sbin/keepalived /etc/keepalived
检查cent7启动脚本中执行程序位置
[root@192 ~]# vim /usr/lib/systemd/system/keepalived.service
[Unit] Description=LVS and VRRP High Availability Monitor After= network-online.target syslog.target Wants=network-online.target [Service] Type=forking PIDFile=/var/run/keepalived.pid KillMode=process EnvironmentFile=-//etc/sysconfig/keepalived ExecStart=/usr/sbin/keepalived $KEEPALIVED_OPTIONS ExecReload=/bin/kill -HUP $MAINPID [Install] WantedBy=multi-user.target
②master-1主机的keepalived主配
[root@192 ~]# setenforce 0
注意关闭selinx，否则可能导致notify_down脚本无法执行
[root@192 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql-1
}
vrrp_instance VI_1 {
state BACKUP
interface eno16777736
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100
}
}
virtual_server 192.168.1.100 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.1.10 3306 {
weight 1
notify_down /root/mysql.sh
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
connect_port 3306
}
}
}③master-2主机的keepalived主配
[root@192 ~]# setenforce 0
注意关闭selinx，否则可能导致notify_down脚本无法执行
[root@192 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id mysql-2
}
vrrp_instance VI_1 {
state BACKUP
interface eno16777736
virtual_router_id 51
priority 50
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100
}
}
virtual_server 192.168.1.100 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.1.12 3306 {
weight 1
notify_down /root/mysql.sh
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
connect_port 3306
}
}
}④notify_down脚本内容
[root@192 ~]# cat mysql.sh
#!/bin/bash pkill keepalived
[root@192 ~]# chmod +x mysql.sh
脚本内容的操作在两台主机都需要操作
四、测试
步骤：
①两台主机均启动keepalived，并且查看vip
[root@192 ~]# systemctl start keepalived
[root@192 ~]# ps -ef | grep keepalived
root 24528 1 0 17:35 ? 00:00:00 //sbin/keepalived root 24529 24528 0 17:35 ? 00:00:00 //sbin/keepalived root 24530 24528 0 17:35 ? 00:00:00 //sbin/keepalived root 25554 3223 0 17:39 pts/0 00:00:00 grep --color=auto keepalived
[root@192 ~]# ip a
......
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:4b:6a:1e brd ff:ff:ff:ff:ff:ff
inet 192.168.1.10/24 brd 192.168.1.255 scope global dynamic eno16777736
valid_lft 83035sec preferred_lft 83035sec
inet 192.168.1.100/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe4b:6a1e/64 scope link
valid_lft forever preferred_lft forever
......发现在master-1主机存在vip
②宕掉master-1的mysql服务再观察vip位置
[root@192 ~]# systemctl stop mysqld
[root@192 ~]# ps -ef | grep keepalived
root 28118 3222 0 17:38 pts/0 00:00:00 grep --color=auto keepalived
发现停止msyql服务后keepalived也被杀掉，说明脚本执行成功
去到master-2主机观察vip
[root@192 ~]# ip a
.......
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:db:f7:b8 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.12/24 brd 192.168.1.255 scope global dynamic eno16777736
valid_lft 82750sec preferred_lft 82750sec
inet 192.168.1.100/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fedb:f7b8/64 scope link
valid_lft forever preferred_lft forever
.......③重新启动master-1的msyql服务以及keepalived观察vip位置
[root@192 ~]# systemctl start mysqld
[root@192 ~]# systemctl start keepalived
......
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:4b:6a:1e brd ff:ff:ff:ff:ff:ff
inet 192.168.1.10/24 brd 192.168.1.255 scope global dynamic eno16777736
valid_lft 82554sec preferred_lft 82554sec
inet 192.168.1.100/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe4b:6a1e/64 scope link
valid_lft forever preferred_lft forever
.......五、总结
1)在配置双主互从过程中需要注意什么？
①keepalived+mysql双主一般来说，中小型规模是最省事的。master节点发生故障后，利用keepalived的高可用机制实现快速切换备用节点②在部署方案的过程中，两个节点的模式最好都为BACKUP模式，避免因为网络延迟，超过心跳检查时间，发生脑裂情况相互抢占MASTER导致写入相同数据引发的冲突
③两个节点的auto_increment_incremenet(自增步长)和auto_increment_offset(自增起始点)设为不同值。目的为了避免master意外宕机，可能会有部分binlog未能及时复制到slave上被应用，从而导致slave新写入数据的自增值和原先的master冲突，从offset起始点开始就错开了，避免了主键id的冲突，当然，如有合适的容错机制解决冲突话，也可以不这么设置
2)如果遇到主从延迟怎么解决？
①首先需要通过show slave statusG中 Seconds_Behind_Master观察主从之间延迟的状态
②slave节点服务器硬件配置不能与master节点相差太大，会大大导致复制的延迟
③如果对延迟问题很敏感，可以考虑更换mariadb分支版本，或者直接上线mysql5.7最新版本，利用多线程复制的方式可以很大程度降低复制延迟
mysql> show global variables like 'slave_paralle%';
| Variable_name | Value |
|---|---|
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
slave_parallel_workers：默认为0，表示为单线程
slave_parallel_type：默认多线程机制为一个线程处理一个DATABASE
mysql> set global slave_parallel_workers=4; #修改为四个线程操作
mysql> set global slave_parallel_type='logical_clock'; #修改为并行复制
④调整master节点服务器DDL速度还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit= 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave