拓扑环境：
MySQL-master1：192.168.100.10 MySQL-master2：192.168.100.11 MySQL-VIP：192.168.100.12 MySQL-test: 192.168.100.13 OS版本：CentOS 7 MySQL版本：5.7.22 Keepalived版本：1.4.3
一、MySQL 双主配置
1. auto_increment_offset和auto_increment_increment配置
mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：
auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535
在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2。这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。
2.修改MySQL配置文件 master1配置：
[root@node10 ~]# vim /etc/my.cnf basedir=/usr/local/mysql datadir=/data/mysql port=3306 socket=/tmp/mysql.sock log_bin=mysql-bin server-id=1 #binlog_do_db=test binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto_increment_increment=2 auto_increment_offset=1 log-error=/data/mysql/mysql-error.log pid-file=/data/mysql/mysql.pid
master2配置：
[root@node10 ~]# vim /etc/my.cnf basedir=/usr/local/mysql datadir=/data/mysql port=3306 socket=/tmp/mysql.sock log_bin=mysql-bin server-id=2 #binlog_do_db=test binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto_increment_increment=2 auto_increment_offset=2 log-error=/data/mysql/mysql-error.log pid-file=/data/mysql/mysql.pid
注意：master1和master2中server-id和auto-increment-offset必须不能相同。
3. 将master1设为master2的主服务器
3.1 在master1上新建授权用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl'; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 154 | | | +------------------+----------+--------------+------------------+ 1 row in set (0,00 sec)
3.2 在master2上将master1设为自己的主服务器
mysql> change master to master_host='192.168.100.10',master_user='repl',master_password='repl',master_log_file='mysql-bin.000010',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin.000033
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 734
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: e0c32eb2-486f-11e8-ab13-080027de0e0e
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0,00 sec)
4. 将master2设为master1的主服务器
4.1 在master2上新建授权用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl'; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000014 | 436 | | | +------------------+----------+--------------+------------------+ 1 row in set (0,00 sec)
4.2 在master1上将master2设为自己的主服务器
mysql> change master to master_host='192.168.100.11',master_user='repl',master_password='repl',master_log_file='mysql-bin.000014',master_log_pos=436; mysql> start slave; Query OK, 0 rows affected (0.00 sec) MySQL> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.11 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000014 Read_Master_Log_Pos: 436 Relay_Log_File: relay-bin.000033 Relay_Log_Pos: 602 Relay_Master_Log_File: mysql-bin.000014 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 436 Relay_Log_Space: 803 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: ddd1ad82-4869-11e8-b6f0-080027de0e0e Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0,00 sec)
5. MySQL是否同步测试
如上述均正确配置，现在任何一台MySQL上更新数据都会同步到另一台MySQL，MySQL同步在此不再演示。
二、keepalived安装及配置
1. master1，master2服务器上安装keepalived（安装出现问题请点击查看）
[root@node10 vagrant]# wget http://www.keepalived.org/software/keepalived-1.4.3.tar.gz [root@node10 vagrant]# tar -zxvf keepalived-1.4.3.tar.gz [root@node10 vagrant]# cd keepalived-1.4.3 [root@node10 keepalived-1.4.3]# ./configure --prefix=/usr/local/keepalived [root@node10 keepalived-1.4.3]# make && make install #建立服务启动脚本，以便使用service命令控制之 (如果安装目录没有，就在解压目录里面) [root@node10 keepalived-1.4.3]# cp /home/vagrant/keepalived-1.4.3/keepalived/etc/init.d/keepalived /etc/init.d/ [root@node10 keepalived-1.4.3]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ #添加执行权限 [root@node10 keepalived-1.4.3]# chmod +x /etc/init.d/keepalived #将keepalived加入linux的服务中： [root@node10 keepalived-1.4.3]# chkconfig --add keepalived #开机启动 [root@node10 keepalived-1.4.3]# chkconfig keepalived on or systemctl enable keepalived.service [root@node10 keepalived-1.4.3]# mkdir /etc/keepalived [root@node10 keepalived-1.4.3]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
2. master1配置keepalived
注意：interface enp0s8 #指定HA监测网络的接口
通过ip addr show查看自己的网卡，我的网卡是enp0s8
[root@node10 vagrant]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:de:0e:0e brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
valid_lft 80919sec preferred_lft 80919sec
inet6 fe80::a00:27ff:fede:e0e/64 scope link
valid_lft forever preferred_lft forever
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:a7:ba:0f brd ff:ff:ff:ff:ff:ff
inet 192.168.100.10/24 brd 192.168.100.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fea7:ba0f/64 scope link
valid_lft forever preferred_lft forever
新建一个配置文件，默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
[root@node10 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
notification_email { #邮件报警
dwch49@163.com
}
notification_email_from dwch49@163.com #指定发件人
smtp_server 127.0.0.1 #指定smtp服务器地址
smtp_connect_timeout 30 #指定smtp连接超时时间
router_id mysql-1 #表示运行keepalived服务器的一个标识
}
vrrp_instance VI_1 {
state BACKUP #指定keepalived的角色,两台配置此处均是BACKUP,设为BACKUP将根据优先级决定主或从
interface enp0s8 #指定HA监测网络的接口
virtual_router_id 51 #虚拟路由标识，这个标识是一个数字(取值在0-255之间,用来区分多个instance 的VRRP组播)，同一个vrrp实例使用唯一的标识,确保和master2相同，同网内不同集群此项必须不同,否则发生冲突。
priority 100 #用来选举master的，要成为master，该项取值范围是1-255（在此范围之外会被识别成默认值100）,此处master2上设置为50
advert_int 1 #发VRRP包的时间间隔，即多久进行一次master选举（可以认为是健康查检时间间隔）
nopreempt #不抢占，即允许一个priority比较低的节点作为master，即使有priority更高的节点启动
authentication { #认证区域，认证类型有PASS和HA（IPSEC），推荐使用PASS（密码只识别前8位）
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { #VIP区域，指定vip地址
192.168.100.12
}
}
virtual_server 192.168.100.12 3306 { #设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
delay_loop 2 #设置运行情况检查时间，单位是秒
lb_algo rr #设置后端调度算法，这里设置为rr，即轮询算法
lb_kind DR #设置LVS实现负载均衡的机制，有NAT、TUN、DR三个模式可选
persistence_timeout 60 #会话保持时间，单位是秒。这个选项对动态网页是非常有用的，为集群系统中的session共享提供了一个很好的解决方案。有了这个会话保持功能，用户的请求会被一直分发到某个服务节点，直到超过这个会话的保持时间。
protocol TCP #指定转发协议类型，有TCP和UDP两种
real_server 192.168.100.10 3306 { #配置服务节点1，需要指定real server的真实IP地址和端口，IP与端口之间用空格隔开注：master2上此处改为192.168.100.1(即master2本机ip)
weight 3 #配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
notify_down /data/keepalived/shutdown.sh #检测到realserver的mysql服务down后执行的脚本
TCP_CHECK {
connect_timeout 3 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306#健康检查端口
}
}
}
3. master2配置keepalived
注意：不一样的地方
router_id mysql-2 #表示运行keepalived服务器的一个标识
nopreempt #不抢占模式，只有优先级高的机器上设置即可，优先级低的机器可不设置
real_server # 配置服务节点1，需要指定real server的真实IP地址和端口
新建一个配置文件，默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
[root@node10 ~]# vim /etc/keepalived/keepalived.conf
global_defs {
notification_email {
dwch49@163.com
}
notification_email_from dwch49@163.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
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.100.12
}
}
virtual_server 192.168.100.12 3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.100.11 3306 {
weight 3
notify_down /data/keepalived/shutdown.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
4. master1，master2编写检测服务down后所要执行的脚本
[root@node10 ~]# vim /data/keepalived/shutdown.sh #!/bin/bash pkill keepalived /sbin/ifdown enp0s8 && /sbin/ifup enp0s8
注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP。
5. 给master1，master2要执行的脚本权限
[root@node10 ~]# chmod +x /data/keepalived/shutdown.sh
6. master1，master2上启动keepalived并查看日志
[root@node10 ~]# /etc/init.d/keepalived start Starting keepalived (via systemctl): [ OK ] [root@node10 ~]# tail -f /var/log/messages
7. master1，master2上都要创建该脚本。
其作用是当mysql停止工作时自动关闭本机的keepalived，从而实现将故障机器踢出（因每台机器上keepalived只添加了本机为realserver）。
当mysqld正常启动起来后，要手动启动keepalived服务。
三、测试master1，master2对VIP（群集虚拟IP）的控制权
1. 在master1，master2上分别执行 ip addr show dev enp0s8 命令查看master1和master2对VIP（群集虚拟IP）的控制权。
1.1 master1上查看结果
[root@node10 ~]# ip addr show dev enp0s8
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:a7:ba:0f brd ff:ff:ff:ff:ff:ff
inet 192.168.100.10/24 brd 192.168.100.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet 192.168.100.12/32 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fea7:ba0f/64 scope link
valid_lft forever preferred_lft forever
1.2 master2上查看结果
[root@node11 ~]# ip addr show enp0s8
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:95:5a:44 brd ff:ff:ff:ff:ff:ff
inet 192.168.100.11/24 brd 192.168.100.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe95:5a44/64 scope link
valid_lft forever preferred_lft forever
从上可以看出master1是主服务器，master2为备用服务器。
2. 停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本
2.1 停止master1的mysql服务（当mysqld正常启动起来后，要手动启动keepalived服务）
[root@node10 ~]# service mysqld stop
2.2 master2上的查看结果
[root@node11 ~]# ip addr show enp0s8
3: enp0s8: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:95:5a:44 brd ff:ff:ff:ff:ff:ff
inet 192.168.100.11/24 brd 192.168.100.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet 192.168.100.12/32 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe95:5a44/64 scope link
valid_lft forever preferred_lft forever
在master2上看到了VIP地址，这说明在master1主服务上停止MySQL服务，触发了我们编写的脚本，进行自动故障切换。
四、测试数据是否同步
1. 在master1，master2两台MySQL服务器新建一个用户并授权允许从远程登录
mysql> grant all privileges on *.* to 'www'@'%' identified by '123456' WITH GRANT OPTION; mysql> flush privileges; mysql> exit;
2. 在master1，master2上打开iptables 3306端口、保存、查看
[root@node10 ~]# iptables -I INPUT 4 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT [root@node10 ~]# service iptables save [root@node10 ~]# iptables -nvL --line-number
3. 在test（192.168.100.13）远程登陆vip（192.168.100.12）进行测试
[root@node13 ~]# mysql -h 192.168.100.12 -u www -p 123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1168 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like "%hostname%"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | node10 | +---------------+--------+ 1 row in set (0,00 sec)
从sql输出结果看，通过vip登陆，并且登陆了master1服务器
接着创建一个数据库，并且创建一个表，然后插入数据
mysql> create database repldb; Query OK, 1 row affected (0.02 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | repldb | | sys | | test | +--------------------+ 6 rows in set (0.06 sec) mysql> use repldb; Database changed mysql> create table repl_table(id int,email varchar(80),password varchar(40) not null); Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------+ | Tables_in_repldb | +------------------+ | repldb | +------------------+ 1 row in set (0.01 sec) mysql> insert into repl_table(id,email,password) values(1,"master@163.com","qweasd"); Query OK, 1 row affected (0.00 sec)
4. 登陆master2主机的mysql，查看数据是否复制成功
[root@node11 ~] mysql -u www -p 123456 mysql> show variables like "%hostname%"; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | hostname | localhost.localdomain | +---------------+-----------------------+ row in set (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | repldb | | sys | | test | +--------------------+ 6 rows in set (0,04 sec) mysql> use repldb; Database changed mysql> show tables; +------------------+ | Tables_in_repldb | +------------------+ | repldb | +------------------+ 1 row in set (0.00 sec) mysql> select * from repl_table; +------+----------------+----------+ | id | email | password | +------+----------------+----------+ | 1 | master@163.com | qweasd | +------+----------------+----------+ 1 row in set (0.08 sec)
5. 停止master1上的mysql，查看故障是否自动转移（master1，mstaer2可自由切换测试）
[root@node10 ~] service mysqld stop Shutting down MySQL.. SUCCESS!
6. 在test（192.168.100.13）主机上查看故障是否转移
mysql> show variables like "%hostname%"; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 610 Current database: repldb +---------------+--------+ | Variable_name | Value | +---------------+--------+ | hostname | node11 | +---------------+--------+ 1 row in set (0.01 sec)
可以看到现在登陆的是master2主机，故障自动切换成功。
接着，插入数据，看master1是否能复制
mysql> insert into repl_table(id,email,password) values(2,"slave@163.com","qweasd"); Query OK, 1 row affected (0.06 sec) mysql> use repldb; Database changed mysql> select * from repl_table; +------+----------------+----------+ | id | email | password | +------+----------------+----------+ | 1 | master@163.com | qweasd | | 2 | slave@163.com | qweasd | +------+----------------+----------+ 2 rows in set (0.00 sec)
7. 登陆master1查看表数据是否复制成功
[root@node10 ~]# service mysqld start Starting MySQL. SUCCESS! [root@10 ~]# mysql -u www -p 123456 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.37-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use repldb; Database changed mysql> select * from repl_table; +------+----------------+----------+ | id | email | password | +------+----------------+----------+ | 1 | master@163.com | qweasd | | 2 | slave@163.com | qweasd | +------+----------------+----------+ 2 rows in set (0.02 sec)
成功复制！
五、总结
五、总结
Keepalived+mysql双主一般来说，中小型规模的时候，采用这种架构是最省事的。在master节点发生故障后，利用keepalived的高可用机制实现快速切换到备用节点。
在这个方案里，有几个需要注意的地方：
1.采用keepalived作为高可用方案时，两个节点最好都设置成BACKUP模式，并在优先级高的master上设置”nopreempt”,避免因为意外情况下（比如脑裂）相互抢占导致往两个节点写入相同数据而引发冲突；
2.把两个节点的auto_increment_increment（自增步长）和auto_increment_offset（自增起始值）设成不同值。其目的是为了避免master节点意外宕机时，可能会有部分binlog未能及时复制到slave上被应用，从而会导致slave新写入数据的自增值和原先master上冲突了，因此一开始就使其错开；当然了，如果有合适的容错机制能解决主从自增ID 冲突的话，也可以不这么做；
3.slave节点服务器配置不要太差，否则更容易导致复制延迟。作为热备节点的slave服务器，硬件配置不能低于master节点；
4.如果对延迟问题很敏感的话，可考虑使用MariaDB分支版本，或者直接上线MySQL 5.7最新版本，利用多线程复制的方式可以很大程度降低复制延迟。
您可以选择一种方式赞助本站