Ubuntu builds Mysql+Keepalived high availability implementation (dual-active hot standby)

Mysql5.5 dual machine hot standby

Implementation

Install two Mysql

Install MySQL 5.5

sudo apt-get update

apt-get install aptitude
aptitude install mysql-server-5.5
or sudo apt-cache search mariadb-server
apt-get install -y mariadb-server-5.5

uninstall

sudo apt-get remove mysql-*
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

Configure permissions

vim /etc/mysql/my.cnf
#bind-address = 127.0.0.1

mysql -u root -p
grant all on *.* to root@'%' identified by 'root' with grant option;
flush privileges;

Configure two MySQL servers for master-master synchronization

Configure Node 1

vim /etc/mysql/my.cnf

server-id = 1 #Node ID
log_bin = mysql-bin.log #logbinlog_format = "ROW" #log format auto_increment_increment = 2 #auto-increment ID interval (= number of nodes, to prevent ID conflicts)
auto_increment_offset = 1 #Start value of auto-increment ID (node ID)
binlog_ignore_db=mysql #Unsynchronized database binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema

Restart mysql

service mysql restart
mysql -u root -p

Record the binlog log position of node 1

show master status;
mysql-bin.000001 245 mysql,information_schema,performance_schema

Configure Node 2

vim /etc/mysql/my.cnf

server-id = 2
log_bin = mysql-bin.log                    
relay_log = mysql-relay-bin.log #Relay log log_slave_updates = ON #After the relay log is executed, the changes are recorded in the log read_only = 0
binlog_format = "ROW"
auto_increment_increment = 2
auto_increment_offset = 2
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema

Configuring Master and Slave

mysql -u root -p

CHANGE MASTER TO 
       MASTER_HOST='192.168.1.21', 
       MASTER_USER='root', 
       MASTER_PASSWORD='root', 
       MASTER_LOG_FILE='mysql-bin.000001', 
       MASTER_LOG_POS=245;

#Start synchronization start slave

#Check the synchronization status Slave_IO_Running and Slave_SQL_Running both need to be Yes       
show slave status;

Record the binlog log position of node 2

show master status;

mysql-bin.000001 1029 mysql,information_schema,performance_schema

Configure the master (node 1)

vim /etc/mysql/my.cnf

relay_log = mysql-relay-bin.log
log_slave_updates = ON
read_only = 0
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema

Turn on sync

mysql -u root -p

CHANGE MASTER TO 
       MASTER_HOST='192.168.1.20', 
       MASTER_USER='root', 
       MASTER_PASSWORD='root', 
       MASTER_LOG_FILE='mysql-bin.000001', 
       MASTER_LOG_POS=1029;

#Start synchronization start slave

#Check the synchronization status Slave_IO_Running and Slave_SQL_Running both need to be Yes       
show slave status;

Exception handling

Could not initialize master info structure, more error messages can be found in the MySQL error log
Solution: reset slave

Install and configure Keepalived

Install Keepalived

#Depends sudo apt-get install -y libssl-dev
sudo apt-get install -y openssl 
sudo apt-get install -y libpopt-dev
sudo apt-get install -y libnl-dev libnl-3-dev libnl-genl-3.dev
apt-get install daemon
apt-get install libc-dev
apt-get install libnfnetlink-dev
apt-get install libnl-genl-3.dev

#install apt-get install keepalived

#Compile and install cd /usr/local
wget https://www.keepalived.org/software/keepalived-2.2.2.tar.gz
tar -zxvf keepalived-2.2.2.tar.gz 
mv keepalived-2.2.2 keepalived
./configure --prefix=/usr/local/keepalived
sudo make && make install

#Open the log sudo vim /etc/rsyslog.d/50-default.conf 

*.=info;*.=notice;*.=warn;\
        auth,authpriv.none;\
        cron,daemon.none;\
        mail,news.none -/var/log/messages
        
sudo service rsyslog restart 
tail -f /var/log/messages

sudo mkdir /etc/sysconfig
sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
sudo cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
sudo cp /usr/local/keepalived/sbin/keepalived /sbin/
sudo mkdir /etc/keepalived
sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

Configure node information

Node 1 192.168.1.21

vim /etc/keepalived/keepalived.conf

global_defs {
   router_id MYSQL_HA #Current node name}
vrrp_instance VI_1 {    
    state BACKUP #Both configuration nodes are BACKUP
    interface eth0 #Network interface to bind virtual IP virtual_router_id 51 #VRRP group name, the settings of both nodes must be the same to indicate that each node belongs to the same VRRP group priority 101 #Priority of the node, change the priority of the other node to a lower one advert_int 1 #Multicast information sending interval, the settings of both nodes must be the same nopreempt #Do not preempt, only set it on the machine with high priority, and do not set it on the machine with low priority authentication { #Set the authentication information, both nodes must be consistent auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress { #Specify the virtual IP, both nodes must be set the same 192.168.1.111
    }
}
virtual_server 192.168.1.111 3306 { #linux virtual server (LVS) configuration delay_loop 2 #check real_server status every 2 seconds lb_algo wrr #LVS scheduling algorithm, rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind DR #LVS cluster mode, NAT|DR|TUN
    persistence_timeout 60 #Session hold time protocol TCP #Is the protocol used TCP or UDP

    real_server 192.168.1.21 3306 {
        weight 3 #Weight notify_down /usr/local/bin/mysql.sh #Script executed after detecting service down TCP_CHECK {
            connect_timeout 10 #connection timeout nb_get_retry 3 #number of reconnections delay_before_retry 3 #reconnection interval connect_port 3306 #health check port}
    }    
}

Node 2 192.168.1.20

vim /etc/keepalived/keepalived.conf

global_defs {
   router_id MYSQL_HA #Current node name}
vrrp_instance VI_1 {
    state BACKUP #Both configuration nodes are BACKUP
    interface eth0 #Network interface to bind virtual IP virtual_router_id 51 #VRRP group name, the settings of both nodes must be the same to indicate that each node belongs to the same VRRP group priority 100 #Node priority, the other priority should be lower advert_int 1 #Multicast information sending interval, the settings of both nodes must be the same nopreempt #Do not preempt, only set it on the machine with high priority, and do not set it on the machine with low priority authentication { #Set authentication information, both nodes must be consistent auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress { #Specify the virtual IP, both nodes must be set the same 192.168.1.111
    }
}
virtual_server 192.168.1.111 3306 { #linux virtual server (LVS) configuration delay_loop 2 #check real_server status every 2 seconds lb_algo wrr #LVS scheduling algorithm, rr|wrr|lc|wlc|lblc|sh|dh
    lb_kind DR #LVS cluster mode, NAT|DR|TUN
    persistence_timeout 60 #Session hold time protocol TCP #Is the protocol used TCP or UDP

    real_server 192.168.1.20 3306 {
        weight 3 #Weight notify_down /usr/local/bin/mysql.sh #Script executed after detecting service down TCP_CHECK {
            connect_timeout 10 #connection timeout nb_get_retry 3 #number of reconnections delay_before_retry 3 #reconnection interval connect_port 3306 #health check port}
    }
}

Writing exception handling scripts

vim /usr/local/bin/mysql.sh

#!/bin/sh
killall keepalived

Assign permissions

chmod +x /usr/local/bin/mysql.sh
###Test restart keepalived

service keepalived restart

View logs

tail -f /var/log/messages

View Virtual IP

ip addr # or ip a or ifconfig

#The master node will have a virtual IP
eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever

Shut down the mysql service on the master node

service mysql stop

Log information

#Master node Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: TCP connection to [192.168.1.20]:3306 failed !!!
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Removing service [192.168.1.20]:3306 from VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_vrrp[4950]: VRRP_Instance(VI_1) sending 0 priority
Aug 10 15:00:30 i-7jaope92 kernel: [100918.976041] IPVS: __ip_vs_del_service: enter

#Slave node Aug 10 15:00:31 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 10 15:00:32 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Entering MASTER STATE

The virtual IP drifts from the master node to the slave node

ip a

eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 192.168.1.111/32 scope global eth0
       valid_lft forever preferred_lft forever

Mysql connection test

mysql -h 192.168.1.111 -u root -p

This is the end of this article about building MySQL + Keepalived high availability on Ubuntu (dual-active hot standby). For more related MySQL + Keepalived high availability content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Keepalived+HAProxy to implement MySQL high availability load balancing configuration
Tutorial on using HAProxy to detect MySQL replication delay
MySQL master-slave configuration and analysis of haproxy and keepalived construction process

<<: HTML framework_Powernode Java Academy

>>: A general method for implementing infinite text carousel with native CSS