MySQL双主配置
环境准备：
OS： CentOS7
master：192.168.1.10
backup：192.168.1.20
VIP：192.168.1.30
一、安装MySQL数据库.
在master 和 backup 上安装mysql,安装完后自动启动，mysql root密码为123456
二、修改MySQL配置文件：
1.master端配置文件如下：
1.master端配置文件如下：
# vim /etc/my.cnf #添加
server_id = 1 #backup上设置为2
log-bin = /data/mysql/mysql-bin
log-bin-index=/data/mysql/my-bin.index
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误
# systemctl restart mysqld
2. backup端配置文件如下：
master端和backup端配置只有server_id不一样，别的都一致.
三、创建数据同步用户并查看log bin日志和pos位置：
1.> master上创建 mysql 同步账号并查看log bin日志和pos位置：
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 618 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
master配置如下：
# mysql -uroot -p123456
mysql> change master to
-> master_host='192.168.1.20', #这里填backup的IP
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=618;
mysql> start slave;
2.> backup上创建mysql同步账号配置如下：
# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'repl';
mysql> flush privileges;
mysql> change master to
-> master_host='192.168.1.10', #这里填master的IP
-> master_user='repl',
-> master_password='repl',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=618;
mysql> start slave;
---------------------
分别查看同步状态：
master查看：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1082
Relay_Log_File: test2-relay-bin.000002
Relay_Log_Pos: 784
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------------
backup查看：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 618
Relay_Log_File: test3-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
---------------------
Slave_IO和Slave_SQL是YES说明主主同步成功。
四、MySQL主主同步测试
master上插入数据测试：
mysql> create database testdb;
mysql> use testdb;
mysql> create table user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'testid');
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
---------------------
backup上查看：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
mysql> use testdb;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
mysql> select number,name from user;
+--------+------+
| number | name |
+--------+------+
| 1 | testid |
+--------+------+
---------------------
可以看到已经成功同步过去，同样在backup插入到user表数据，一样同步过去，双主配置没有问题。
五、配置keepalived实现双机热备
1.master安装keepalived并配置：
# yum install -y keepalived
# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
admin@test.com
}
notification_email_from admin@test.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0 #根据实际网络接口进行更改
virtual_router_id 51
priority 100 #优先级，master设置为100
advert_int 1
nopreempt #不主动抢占资源，只在master上设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.30
}
}
virtual_server 192.168.1.30 3306 {
delay_loop 2
#lb_algo rr
#lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.1.10 3306 { #检测本地mysql
weight 3
notify_down /tmp/mysql.sh #当mysql服务down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
backup安装keepalived并配置：
# yum install -y keepalived
# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
admin@test.com
}
notification_email_from admin@test.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0 #根据实际网络接口进行更改
virtual_router_id 51
priority 90 #优先级，backup设置为90
advert_int 1
#nopreempt #主动抢占资源
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.30
}
}
virtual_server 192.168.1.30 3306 {
delay_loop 2
#lb_algo rr
#lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.1.20 3306 { #检测本地mysql
weight 3
notify_down /tmp/mysql.sh #当mysql服务down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
master 和 backup上编辑mysql.sh
# vim /tmp/mysql.sh
#!/bin/bash
pkill keepalived
# chmod +x !$
# systemctl start keepalived
两台mysql服务器授权允许root远程登录：
# mysql -uroot -p123456789
mysql> grant all on *.* to 'root'@'192.168.1.%' identified by '123456';
mysql> flush privileges;
测试高可用
通过mysql客户端通过VIP连接，看是否连接成功。
这里我用同网段的另一台机器，连接测试：
# mysql -h192.168.1.30 -uroot -p123456
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test.user;
+--------+------+
| number | name |
+--------+------+
| 1 | testid |
+--------+------+
1 row in set (0.01 sec)
---------------------
可以看到，连接成功，且查询数据没有问题，停止master上mysql服务，是否能正常切换到backup上，可以使用 ip addr命令来查看VIP在哪台服务器上。
master上查看是否有VIP,可以看到VIP在master上
# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 192.168.1.30/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
valid_lft forever preferred_lft forever
---------------------
停掉master上mysql服务：
# systemctl stop mysqld
# ps axu |grep keepalived
root 11074 0.0 0.0 112708 988 pts/1 S+ 15:28 0:00 grep --color=autokeepalived
# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
valid_lft forever preferred_lft forever
---------------------
可以看到，keepalived在mysql服务停掉之后也被停掉，VIP不在master上。
backup上查看是否有VIP，可以看到VIP在backup上。
# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:33:80:d5 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.20/24 brd 192.168.1.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 192.168.1.30/32 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::4b20:2e16:a957:f9a1/64 scope link noprefixroute
valid_lft forever preferred_lft forever
---------------------
查看/var/log/messages日志，可以看到主备切换过程：
Apr 8 15:27:16 hosts systemd: Stopping MySQL Server...
Apr 8 15:27:16 hosts Keepalived_healthcheckers[11048]: TCP connection to [192.168.1.10]:3306 failed.
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: TCP connection to [192.168.1.10]:3306 failed.
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Check on service [192.168.1.10]:3306 failed after 1 retry.
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Removing service [192.168.1.10]:3306 from VS [192.168.1.30]:3306
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: IPVS (cmd 1160, errno 2): No such destination
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Executing [/tmp/mysql.sh] for service [192.168.1.10]:3306 in VS [192.168.1.30]:3306
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Lost quorum 1-0=1 > 0 for VS [192.168.1.30]:3306
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Remote SMTP server [127.0.0.1]:25 connected.
Apr 8 15:27:19 hosts Keepalived_vrrp[11049]: VRRP_Instance(VI_1) sent 0 priority
Apr 8 15:27:19 hosts Keepalived_vrrp[11049]: VRRP_Instance(VI_1) removing protocol VIPs.
Apr 8 15:27:19 hosts Keepalived[11047]: Stopping
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: IPVS (cmd 1156, errno 2): No such file or directory
Apr 8 15:27:19 hosts Keepalived_healthcheckers[11048]: Stopped
Apr 8 15:27:20 hosts Keepalived_vrrp[11049]: Stopped
Apr 8 15:27:20 hosts Keepalived[11047]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2
Apr 8 15:27:27 hosts systemd: Stopped MySQL Server.
---------------------
恢复master服务器故障，看是否主动抢占资源，成为活动服务器。
master上启动mysql服务和keepalived服务：
# systemctl start mysqld
# systemctl start keepalived
# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cf:ab:c4 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.10/24 brd 192.168.1.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute
valid_lft forever preferred_lft forever
---------------------
可以看到，即使master故障恢复，也没有抢占资源，VIP仍然在backup上，这是因为之前已经配置了master为非抢占模式（nopreempt）。
不过需要注意的是：
nopreempt这个参数只能用于state为BACKUP的情况，所以在配置的时候要把master和backup的state都设置成BACKUP，这样才会实现keepalived的非抢占模式！
也就是说：
* 当state状态一个为MASTER，一个为BACKUP的时候，加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的，是抢占模式！
* 当state状态都设置成BACKUP，如果不配置nopreempt参数，那么也是看priority优先级决定谁抢占vip资源，即也是抢占模式。
* 当state状态都设置成BACKUP，如果配置nopreempt参数，那么就不会去考虑priority优先级了，是非抢占模式！即只有vip当前所在机器发生故障，另一台机器才能接管vip。
即使优先级高的那一台机器恢复正常后也不会主动抢回vip，只能等到对方发生故障，才会将vip切回来。