主主配置
环境准备:
OS:CentOS7 master:192.168.30.129 backup:192.168.30.130 VIP:192.168.30.131
拷贝playbook安装mysql:
在master和backup上安装mysql
# cd ansible-playbook/mysql/ # ansible-playbook mysql.yml
安装完后自动启动,mysql root 密码为 123456789。
修改配置文件:
# 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
master上查看log bin日志和pos位置:
# mysql -uroot -p123456789 mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.30.%' IDENTIFIED BY 'replication'; 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 -p123456789 mysql> change master to -> master_host='192.168.30.130', #这里填backup的IP -> master_user='replication', -> master_password='replication', -> master_log_file='mysql-bin.000001', -> master_log_pos=618; mysql> start slave;
backup配置如下:
# mysql -uroot -p123456789 mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.30.%' IDENTIFIED BY 'replication'; mysql> flush privileges; mysql> change master to -> master_host='192.168.30.129', #这里填master的IP -> master_user='replication', -> master_password='replication', -> 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.30.130 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.30.129 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说明主主同步成功。
master上插入数据测试:
mysql> create database test; mysql> use test; mysql> create table user (number INT(10),name VARCHAR(255)); mysql> insert into user values(01,'lzx'); mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+
backup上查看:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ mysql> use test; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | user | +----------------+ mysql> select number,name from user; +--------+------+ | number | name | +--------+------+ | 1 | lzx | +--------+------+
可以看到已经成功同步过去,同样在backup插入到user表数据,一样同步过去,双主配置没有问题。
配置keepalived实现热备
master安装keepalived并配置:
# yum install -y keepalived # vim /etc/keepalived/keepalived.conf ! Configuration File for keepalived global_defs { notification_email { lzx@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 ens33 #根据实际网络接口进行更改 virtual_router_id 51 priority 100 #优先级,master设置为100 advert_int 1 nopreempt #不主动抢占资源,只在master上设置 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.30.131 } } virtual_server 192.168.30.131 3306 { delay_loop 2 #lb_algo rr #lb_kind NAT persistence_timeout 50 protocol TCP real_server 192.168.30.129 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 { lzx@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 ens33 #根据实际网络接口进行更改 virtual_router_id 51 priority 90 #优先级,backup设置为90 advert_int 1 #nopreempt #主动抢占资源 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.30.131 } } virtual_server 192.168.30.131 3306 { delay_loop 2 #lb_algo rr #lb_kind NAT persistence_timeout 50 protocol TCP real_server 192.168.30.130 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.30.%' identified by '123456789'; mysql> flush privileges;
测试高可用
通过mysql客户端通过VIP连接,看是否连接成功。
这里我用同网段的另一台机器,连接测试:
# mysql -h192.168.30.131 -uroot -p123456789 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 114 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> select * from test.user; +--------+------+ | number | name | +--------+------+ | 1 | lzx | +--------+------+ 1 row in set (0.01 sec)
可以看到,连接成功,且查询数据没有问题。
停止master上mysql服务,是否能正常切换到backup上。
可以使用ip addr命令来查看VIP在哪台服务器上。
master上查看是否有VIP:
# 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: ens33: <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.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet 192.168.30.131/32 scope global ens33 valid_lft forever preferred_lft forever inet6 fe80::fe8e:3c2f:4d32:e9fd/64 scope link noprefixroute valid_lft forever preferred_lft forever
可以看到VIP在master上。
停掉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: ens33: <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.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33 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:
# 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: ens33: <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.30.130/24 brd 192.168.30.255 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet 192.168.30.131/32 scope global ens33 valid_lft forever preferred_lft forever inet6 fe80::4b20:2e16:a957:f9a1/64 scope link noprefixroute valid_lft forever preferred_lft forever
可以看到VIP在backup上。
查看/var/log/messages日志,可以看到主备切换过程:
Apr 8 15:27:16 test2 systemd: Stopping MySQL Server... Apr 8 15:27:16 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed. Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: TCP connection to [192.168.30.129]:3306 failed. Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Check on service [192.168.30.129]:3306 failed after 1 retry. Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Removing service [192.168.30.129]:3306 from VS [192.168.30.131]:3306 Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1160, errno 2): No such destination Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Executing [/tmp/mysql.sh] for service [192.168.30.129]:3306 in VS [192.168.30.131]:3306 Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Lost quorum 1-0=1 > 0 for VS [192.168.30.131]:3306 Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Remote SMTP server [127.0.0.1]:25 connected. Apr 8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) sent 0 priority Apr 8 15:27:19 test2 Keepalived_vrrp[11049]: VRRP_Instance(VI_1) removing protocol VIPs. Apr 8 15:27:19 test2 Keepalived[11047]: Stopping Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: IPVS (cmd 1156, errno 2): No such file or directory Apr 8 15:27:19 test2 Keepalived_healthcheckers[11048]: Stopped Apr 8 15:27:20 test2 Keepalived_vrrp[11049]: Stopped Apr 8 15:27:20 test2 Keepalived[11047]: Stopped Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2 Apr 8 15:27:27 test2 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: ens33: <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.30.129/24 brd 192.168.30.255 scope global noprefixroute ens33 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切回来。