keepalive半同步双主一从
ip地址如下：
192.168.20.201 redis01 主 192.168.20.202 redis02 主 192.168.20.203 redis03 从 192.168.20.205 vip
修改my.cnf配置文件如下
[root@redis01 keepalived]# cat /etc/my.cnf [mysqld] server-id=1 log-bin=mysql-bin gtid_mode = ON enforce_gtid_consistency = ON master_info_repository =TABLE relay_log_info_repository=TABLE binlog_checksum=NONE log_slave_updates=ON binlog_format=ROW log_timestamps= SYSTEM #Group Replication Settings plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl-semi-sync-master-enabled = 1 rpl-semi-sync-slave-enabled = 1 slave_parallel_type=database slave_parallel_workers=3 #GENERAL datadir=/data/mysql socket=/data/mysql/mysql.sock pid-file=/data/mysqld.pid user=mysql port=3306 default_storage_engine=InnoDB #INNODB innodb_buffer_pool_size = 512M innodb_log_file_size=10M innodb_file_per_table=1 innodb_flush_method=O_DIRECT #MYISAM key_buffer_size=10M #LOGGING log-error=/data/log/mysql-error.log slow_query_log=1 #是否启用慢查询日志，1为启用，0为禁用 slow_query_log_file=/data/log/mysql-slow.log #指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datad #OTHER tmp_table_size=32M max_heap_table_size=32M #query_cache_type=0 #query_cache_size=0 max_connections=1000 thread_cache_size=100 #table_cache=100 open_files_limit=65535 [client] #socket=/var/lib/mysql/mysql.sock socket=/data/mysql/mysql.sock port=3306 user=root password=ocm123 其他节点修改server-id的值
创建复制用户及授权，3个节点都需要创建
create user 'repl'@'192.%' identified by 'ocm123'; GRANT replication slave ON *.* to repl@'192.%' IDENTIFIED BY 'ocm123';
安装及配置keepalived
yum install keepalived-1*
先备份配置文件
cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf_bak
修改配置文件如下,修改前保证防火墙已关掉，或者在/etc/sysconfig/iptables加入以下条目
-A INPUT -d 192.168.20.205 -j ACCEPT
配置keepalived为backup模式，master挂掉以后，vip切换到backup(redis02)主机，当master(redis01)主机启动以后不会抢回vip，从机redis03不会在切换一次
主机redis01
[root@redis01 keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MYSQL-HA
}
vrrp_script chk_mysql_port {
script "/etc/keepalived/check.sh"
interval 2
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
priority 150
nopreempt
interface eth1
virtual_router_id 51
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.20.205
}
track_script {
chk_mysql_port
}
}
主机redis02
[root@redis02 keepalived]# cat keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MYSQL-HA
}
vrrp_script chk_mysql_port {
script "/etc/keepalived/check.sh"
interval 2
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
priority 50
nopreempt
interface eth5
virtual_router_id 51
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.20.205
}
track_script {
chk_mysql_port
}
}
mysql监控脚本如下
[root@redis02 keepalived]# cat check.sh
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
/etc/init.d/keepalived stop
fi
配置复制
change master to master_host='redis01', master_user='repl', master_password='ocm123', master_auto_position=150; change master to master_host='redis02', master_user='repl', master_password='ocm123', master_auto_position=150; change master to master_host='192.168.20.205', master_user='repl', master_password='ocm123', master_auto_position=150;
验证vip切换
查看ip
[root@redis01 sysconfig]# ip addr
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:2b:cf:c4 brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe2b:cfc4/64 scope link
valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:ad:69:96 brd ff:ff:ff:ff:ff:ff
inet 192.168.20.201/24 brd 192.168.20.255 scope global eth1
valid_lft forever preferred_lft forever
inet 192.168.20.205/32 scope global eth1
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fead:6996/64 scope link
valid_lft forever preferred_lft forever
在redis03上查看vip在那个主机上
[root@redis03 ~]# ssh 192.168.20.205 hostname
redis01
关掉redis01上mysql
[root@redis01 sysconfig]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@redis01 sysconfig]# ip addr
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:2b:cf:c4 brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe2b:cfc4/64 scope link
valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:ad:69:96 brd ff:ff:ff:ff:ff:ff
inet 192.168.20.201/24 brd 192.168.20.255 scope global eth1
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fead:6996/64 scope link
valid_lft forever preferred_lft forever
[root@redis03 ~]# ssh 192.168.20.205 hostname
redis02
结论 redis01上mysql会自动关闭keepalived，vip切换到redis02上
启动redis01上mysql及keepalived看vip是否会切回redis01
[root@redis01 sysconfig]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
[root@redis01 sysconfig]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[root@redis01 sysconfig]# ip addr
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: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:2b:cf:c4 brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe2b:cfc4/64 scope link
valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:ad:69:96 brd ff:ff:ff:ff:ff:ff
inet 192.168.20.201/24 brd 192.168.20.255 scope global eth1
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fead:6996/64 scope link
valid_lft forever preferred_lft forever
[root@redis03 ~]# ssh 192.168.20.205 hostname
redis02
从上面得出结论,redis01启动mysql以后不会抢回vip
查看从机redis03上复制状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.205
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 230
Relay_Log_File: redis03-relay-bin.000074
Relay_Log_Pos: 435
Relay_Master_Log_File: mysql-bin.000005
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: 230
Relay_Log_Space: 921
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: e1ce6245-b4a3-11e8-9ecc-08002771e31b
Master_Info_File: mysql.slave_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: be2030ff-b4a3-11e8-8764-0800272bcfc4:1-54,
e1ce6245-b4a3-11e8-9ecc-08002771e31b:1
Executed_Gtid_Set: be2030ff-b4a3-11e8-8764-0800272bcfc4:1-54,
e1ce6245-b4a3-11e8-9ecc-08002771e31b:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> select * from mysql.slave_master_info \G
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000005
Master_log_pos: 230
Host: 192.168.20.205
User_name: repl
User_password: ocm123
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: e1ce6245-b4a3-11e8-9ecc-08002771e31b
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)
mysql> select * from mysql.slave_relay_log_info\G
*************************** 1. row ***************************
Number_of_lines: 7
Relay_log_name: ./redis03-relay-bin.000073
Relay_log_pos: 435
Master_log_name: mysql-bin.000016
Master_log_pos: 230
Sql_delay: 0
Number_of_workers: 3
Id: 1
Channel_name:
1 row in set (0.00 sec)
mysql> select * from mysql.slave_worker_info \G
*************************** 1. row ***************************
Id: 1
Relay_log_name: ./redis03-relay-bin.000005
Relay_log_pos: 730
Master_log_name: mysql-bin.000001
Master_log_pos: 1349594
Checkpoint_relay_log_name: ./redis03-relay-bin.000005
Checkpoint_relay_log_pos: 438
Checkpoint_master_log_name: mysql-bin.000001
Checkpoint_master_log_pos: 1349302
Checkpoint_seqno: 0
Checkpoint_group_size: 64
Checkpoint_group_bitmap:
Channel_name:
*************************** 2. row ***************************
Id: 2
Relay_log_name:
Relay_log_pos: 0
Master_log_name:
Master_log_pos: 0
Checkpoint_relay_log_name:
Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
Checkpoint_master_log_pos: 0
Checkpoint_seqno: 0
Checkpoint_group_size: 64
Checkpoint_group_bitmap:
Channel_name:
*************************** 3. row ***************************
Id: 3
Relay_log_name: ./redis03-relay-bin.000002
Relay_log_pos: 910010
Master_log_name: mysql-bin.000001
Master_log_pos: 909805
Checkpoint_relay_log_name: ./redis03-relay-bin.000002
Checkpoint_relay_log_pos: 555275
Checkpoint_master_log_name: mysql-bin.000001
Checkpoint_master_log_pos: 555070
Checkpoint_seqno: 0
Checkpoint_group_size: 64
Checkpoint_group_bitmap:
Channel_name:
3 rows in set (0.00 sec)
