High Availability of MySQL: Keepalived + MySQL (dual-master hot standby)

High Availability of MySQL: Keepalived + MySQL (dual-master hot standby)

High Availability of MySQL: Keepalived + MySQL (dual-master hot standby)

Environment Description:
OS: CentOS6.5 _ X64
MASTER: 192.168.0.202
BACKUP: 192.168.0.203
VIP: 192.168.0.204

1. Configure two Mysql master-master Synchronization
[Root @ master ~] # Yum install mysql-server mysql-y
[Root @ master ~] # Service mysqld start
[Root @ master ~] # Mysqladmin-u root password 123.com
[Root @ master ~] # Vi/etc/my. cnf # enable binary logs and set the id
[Mysqld]
Server-id = 1 # backup Settings 2
Log-bin = mysql-bin
Binlog-ignore-db = mysql, information_schema # ignore the database written to binlog
Auto-increment = 2 # field change increment Value
Auto-increment-offset = 1 # the initial field ID is 1
Slave-skip-errors = all # ignore all replication errors
[Root @ master ~] # Service mysqld restart

# First view the log bin log and pos Value Location

The master configuration is as follows:

[Root @ master ~] # Mysql-u root-p123.com
Mysql> grant replication slave on *. * TO 'replicase' @ '2017. 192.% 'identified by 'replicase ';
Mysql> flush privileges;
Mysql> change master
-> Master_host = '192. 168.0.203 ',
-> Master_user = 'replicase ',
-> Master_password = 'replicase ',
-> Master_log_file = 'mysql-bin.000002 ',
-> Master_log_pos = 106; # value of the peer status
Mysql> start slave; # start Synchronization

The backup configuration is as follows:

[Root @ backup ~] # Mysql-u root-p123.com
Mysql> grant replication slave on *. * TO 'replicase' @ '2017. 192.% 'identified by 'replicase ';
Mysql> flush privileges;
Mysql> change master
-> Master_host = '192. 168.0.202 ',
-> Master_user = 'replicase ',
-> Master_password = 'replicase ',
-> Master_log_file = 'mysql-bin.000002 ',
-> Master_log_pos = 106;
Mysql> start slave;

# After the master synchronization configuration is complete, view the synchronization status of Slave_IO and Slave_ SQL. YES indicates that the master synchronization is successful.

Test data insertion on the master:

Check whether synchronization is successful in backup:

We can see that the data has been successfully synchronized, and the data is also inserted to the user table in backup. In the same way, the dual-master operation is successful.

2. Configure keepalived for Hot Backup
[Root @ backup ~] # Yum install-y pcre-devel openssl-devel popt-devel # install the dependency package

[Root @ master ~] # Wget
[Root @ master ~] # Tar zxvf keepalived-1.2.7.tar.gz
[Root @ master ~] # Cd keepalived-1.2.7
[Root @ master ~] #./Configure -- prefix =/usr/local/keepalived
Make & make install

# Configure keepalived as a system service

[Root @ master ~] # Cp/usr/local/keepalived/etc/rc. d/init. d/keepalived/etc/init. d/
[Root @ master ~] # Cp/usr/local/keepalived/etc/sysconfig/
[Root @ master ~] # Mkdir/etc/keepalived/
[Root @ master ~] # Cp/usr/local/keepalived/etc/keepalived. conf/etc/keepalived/
[Root @ master ~] # Cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

[Root @ master ~] # Vi/etc/keepalived. conf
! Configuration File forkeepalived
Global_defs {
Notification_email {
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
Router_id MYSQL_HA # ID, the same as the dual-Master
}
Vrrp_instance VI_1 {
State BACKUP # BACKUP is set for both sets
Interface eth0
Virtual_router_id 51 # Same as master/backup
Priority 100 # priority, 90 for backup
Advert_int 1
Nopreempt # do not take the initiative to seize resources, only in the master priority settings, backup is not set
Authentication {
Auth_type PASS
Auth_pass 1111
}
Virtual_ipaddress {
192.168.0.204
}
}
Virtual_server 192.168.0.204 3306 {
Delay_loop 2
# Lb_algo rr # LVS algorithm. If it is not used, we close it.
# Lb_kind DR # LVS mode. If this mode is not disabled, the slave server cannot connect to the master MySQL instance through VIP.
Persistence_timeout 50 # connections from the same IP address are allocated to the same real server within 60 seconds.
Protocol TCP
Real_server 192.168.0.202 3306 {# Check local mysql, backup also needs to write to check local mysql
Weight 3
Notify_down/usr/local/keepalived/mysql. sh # execute this script when mysq server is down to kill keepalived for failover
TCP_CHECK {
Connect_timeout 3 # connection timeout
Nb_get_retry 3 # Number of Retries
Delay_before_retry 3 # Retry Interval
}
}