Mysql dual-master replication and high availability using keepalived

1. Architecture
Vip 192. 168 .. 45.244
Mysql-1: 192.168.45.238
Mysql-2: 192.168.45.239

2. mysql dual-Master Settings
192.168.45.238
# Vim/etc/my. cnf
[Client]
Port = 3306
Socket =/tmp/mysql. sock

[Mysqld]
Port = 3306
Socket =/tmp/mysql. sock
Datadir =/usr/local/mysql/var/
Skip-locking
Skip-name-resolve
Key_buffer = 64 M
Max_allowed_packet = 64 M
Table_cache = 2048
Sort_buffer_size = 4 M
Net_buffer_length = 256 K
Read_buffer_size = 10 M
Read_rnd_buffer_size = 10 M
Myisam_sort_buffer_size = 16 M
Interactive_timeout = 240
Wait_timeout = 240
Max_connections = 800
Connect_timeout = 30
Open_files_limit = 8192
Query_cache_size = 1024 M
Thread_cache_size = 16
Thread_concurrency = 8
Long_query_time = 1
Log-slow-queries = slow. log

Innodb_additional_mem_pool_size = 8 M
Innodb_buffer_pool_size = 32 M
Innodb_log_buffer_size = 8 M
Innodb_log_file_size = 256 M
Innodb_log_files_in_group = 3
Innodb_file_io_threads = 8
Innodb_lock_wait_timeout = 50
Innodb_thread_concurrency = 16
Innodb_file_per_table

Log_slave_updates
Expire_logs_days = 7
Auto_increment_increment = 2
Auto_increment_offset = 2
Binlog_format = mixed
Log-bin = mysql-bin
Server-id = 8

[Mysqldump]
Quick
Max_allowed_packet = 16 M

[Mysql]
No-auto-rehash

[Isamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M

[Myisamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M

[Mysqlhotcopy]
Interactive-timeout
Set the mysql replication account
Mysql> grant replication slave on *. * TO 'repl' @ '192. 168.45.238 'identified by 'repl _ 100 ';
Mysql> grant replication slave on *. * TO 'repl' @ '192. 168.45.239 'identified by 'repl _ 100 ';
Export Database
# Mysqldump-uroot-p -- single-transaction -- flush-logs -- master-data = 2 -- all-databases> all. SQL
192.168.45.239

Mysql Configuration
# Vim/etc/my. cnf
[Client]
Port = 3306
Socket =/tmp/mysql. sock

[Mysqld]
Port = 3306
Socket =/tmp/mysql. sock
Datadir =/usr/local/mysql/var/
Skip-locking
Skip-name-resolve
Key_buffer = 64 M
Max_allowed_packet = 64 M
Table_cache = 128
Sort_buffer_size = 4 M
Net_buffer_length = 256 K
Read_buffer_size = 10 M
Read_rnd_buffer_size = 10 M
Myisam_sort_buffer_size = 64 M
Interactive_timeout = 240
Wait_timeout = 240
Max_connections = 800
Connect_timeout = 30
Open_files_limit = 8192
Query_cache_size = 1024 M
Thread_cache_size = 16
Thread_concurrency = 8
Long_query_time = 1
Log-slow-queries = slow. log

Innodb_additional_mem_pool_size = 8 M
Innodb_buffer_pool_size = 64 M
Innodb_log_buffer_size = 8 M
Innodb_log_file_size = 256 M
Innodb_log_files_in_group = 3
Innodb_file_io_threads = 8
Innodb_lock_wait_timeout = 50
Innodb_thread_concurrency = 16
Innodb_file_per_table

Log_slave_updates
Expire_logs_days = 7
Auto_increment_increment = 2
Auto_increment_offset = 1
Binlog_format = mixed
Log-bin = mysql-bin
Server-id = 9

[Mysqldump]
Quick
Max_allowed_packet = 16 M

[Mysql]
No-auto-rehash

[Isamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M

[Myisamchk]
Key_buffer = 20 M
Sort_buffer_size = 20 M
Read_buffer = 2 M
Write_buffer = 2 M

[Mysqlhotcopy]
Interactive-timeout
Import master database data
# Mysql-uroot-p <all. SQL
Set Synchronization
Mysql> change master to MASTER_HOST = '2017. 168.45.238 ', MASTER_PORT = '000000', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl _ 000000', MASTER_LOG_FILE = 'mysql-bin.000004', MASTER_LOG_POS = 3306;
Mysql> start slave;
Set synchronization on 192.168.45.238
Mysql> change master TOMASTER_HOST = '2017. 168.45.239 ', MASTER_PORT = '000000', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl _ 000000', MASTER_LOG_FILE = 'mysql-bin.000008', MASTER_LOG_POS = 3306;
Mysql> start slave;
3. Configure keepalived
192.168.45.238
192.168.45.239
Install keepalived
Wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz
Tar zxvf keepalived-1.2.2.tar.gz
Cd keepalived-1.2.2
./Configure -- prefix =/
Make
Make install
192.168.45.238 keepalived Configuration
6> # vim/etc/keepalived. conf
! Configuration File for keepalived
Global_defs {
Router_id yuangnag.com
}
Vrrp_script check_run {
Script "/root/keepalived_check_mysql.sh"
Interval 5
}
Vrrp_sync_group VG1 {
Group {
VI_1
}
}
Vrrp_instance VI_1 {
State MASTER
Interface eth0
Virtual_router_id 88
Priority100
Advert_int 1
Nopreempt
Authentication {
Auth_type PASS
Auth_pass yuangang.net
}
Track_script {
Check_run
}
Virtual_ipaddress {
192.168.45.244
}
}
Check mysql script configuration (the same configuration for two mysql instances)
# Vim #! /Bin/bash
MYSQL =/usr/local/mysql/bin/mysql
MYSQL_HOST = localhost
MYSQL_USER = root
MYSQL_PASSWORD =
CHECK_TIME = 3
# Mysql is working MYSQL_ OK is 1, mysql down MYSQL_ OK is 0
MYSQL_ OK = 1
Function check_mysql_helth (){
$ MYSQL-h $ MYSQL_HOST-u $ MYSQL_USER-e "show status;">/dev/null 2> & 1
If [$? = 0]; then
MYSQL_ OK = 1
Else
MYSQL_ OK = 0
Fi
Return $ MYSQL_ OK
}
While [$ CHECK_TIME-ne 0]
Do
Let "CHECK_TIME-= 1"
Check_mysql_helth
If [$ MYSQL_ OK = 1]; then
CHECK_TIME = 0
Exit 0
Fi

If [$ MYSQL_ OK-eq 0] & [$ CHECK_TIME-eq 0]
Then
/Etc/init. d/keepalived stop
Exit 1
Fi
Sleep 1
Done
#! /Bin/bash
MYSQL =/usr/local/mysql/bin/mysql
MYSQL_HOST = localhost
MYSQL_USER = root
MYSQL_PASSWORD =
CHECK_TIME = 3
# Mysql is working MYSQL_ OK is 1, mysql down MYSQL_ OK is 0
MYSQL_ OK = 1
Function check_mysql_helth (){
$ MYSQL-h $ MYSQL_HOST-u $ MYSQL_USER-e "show status;">/dev/null 2> & 1
If [$? = 0]; then
MYSQL_ OK = 1
Else
MYSQL_ OK = 0
Fi
Return $ MYSQL_ OK
}
While [$ CHECK_TIME-ne 0]
Do
Let "CHECK_TIME-= 1"
Check_mysql_helth
If [$ MYSQL_ OK = 1]; then
CHECK_TIME = 0
Exit 0
Fi

If [$ MYSQL_ OK-eq 0] & [$ CHECK_TIME-eq 0]
Then
/Etc/init. d/keepalived stop
Exit 1
Fi
Sleep 1
Done
Chmod 755/root/keepalived_check_mysql.sh

192.168.45.239 keepalived Configuration
# Vim/etc/keepalived. conf
! Configuration File for keepalived
Global_defs {
Router_id yuangang.com
}
Vrrp_script check_run {
Script "/root/keepalived_check_mysql.sh"
Interval 5
}
Vrrp_sync_group VG1 {
Group {
VI_1
}
}
Vrrp_instance VI_1 {
State BACKUP
Interface eth0
Virtual_router_id 88
Priority 80
Advert_int 1
Authentication {
Auth_type PASS
Auth_pass yuangang.com
}
Track_script {
Check_run
}
Virtual_ipaddress {
192.168.45.244
}
}
Start keepalived mysql on 238
/Etc/init. d/keepalived start
/Etc/init. d/mysqld start
Start keepalived mysql on 239
/Etc/init. d/keepalived start
/Etc/init. d/mysqld start

Test: Disable mysql on mysql 238 and connect to mysql using vip on another machine.
Disable mysql on mysql 239 and connect to mysql using vip on another machine
Author: "linux O & M"