Build a three-node high-availability architecture based on MySQL 5.7 multi-source replication and Keepalived

Build a three-node high-availability architecture based on MySQL 5.7 multi-source replication and Keepalived

Basic environment preparation

Use Centos 6.X 64-bit system using MySQL MySQL-5.7.17-x86_64version, go to the official mysql-5.7.17-linux-glibc2.5-x86_64.tar.gzversion

Machine name

operating system

IP

node1

centos-6.8

192.168.11.100

node2

centos-6.8

192.168.11.101

node3

centos-6.8

192.168.11.102

Set the VIP of the three-node cluster to 192.168.11.110

Generally we recommend closing iptables

[wubx@zhishuedu.com ~]# chkconfig —del iptables
[wubx@zhishuedu.com ~]#/etc/init.d/iptables stop

And close selinux

[wubx@zhishuedu.com ~]# setenforce 0

And change the following line in the configuration file/etc/sysconfig/selinux
SELINUX=permissive

change to

SELINUX=disabled

Download MySQL

[wubx@zhishuedu.com ~]# mkdir/data/Soft
[wubx@zhishuedu.com ~]# cd/data/Soft
[wubx@zhishuedu.com ~]# wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86\_64.tar.gz

MySQL deployment conventions

The binary files are placed in the corresponding directory under/opt/mysql/.

All data files are placed in the corresponding directory under/data/mysql/.

Download the original binary file to the/data/Soft/directory.

MySQL basic installation

The following installation steps need to be performed on node1, node2, and node3 respectively.

[wubx@zhishuedu.com ~]# mkdir/opt/mysql
[wubx@zhishuedu.com ~]# cd/opt/mysql
[wubx@zhishuedu.com ~]# tar zxvf/data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
[wubx@zhishuedu.com ~]# ln -s/opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64/usr/local/mysql
[wubx@zhishuedu.com ~]# mkdir/data/mysql/mysql3309/{data,logs,tmp} -p
[wubx@zhishuedu.com ~]# groupadd mysql
[wubx@zhishuedu.com ~]# useradd -g mysql -s/sbin/nologin -d/usr/local/mysql -M mysql
[wubx@zhishuedu.com ~]# chown -R mysql:mysql/data/mysql/
[wubx@zhishuedu.com ~]# chown -R mysql:mysql/usr/local/mysql
[wubx@zhishuedu.com ~]# cd/usr/local/mysql/
[wubx@zhishuedu.com ~]# ./bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf —initialize
[wubx@zhishuedu.com ~]# cat/data/mysql/mysql3309/data/error.log |grep password
[wubx@zhishuedu.com ~]#/usr/local/mysql/bin/mysqld —defaults-file=/data/mysql/mysql3309/my3309.cnf &
[wubx@zhishuedu.com ~]# echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
[wubx@zhishuedu.com ~]# source/etc/profile

[wubx@zhishuedu.com ~]# mysql -S/tmp/mysql3309.sock -uroot -pXX

mysql> grant replication slave,replication client on. to'repl'@'%' identified by'repl4slave';
mysql> grant all privilegs on test.* to'wubx'@'%' identified by'wubx';
mysql> reset master;

Each node should proceed as described above. Please read the error log file carefully if you encounter initialization and startup failures.

Build a master-slave structure

Set up master on node1

mysql> change master to master_host='192.168.11.101',
master_port=3309, master_user='repl', 
master_password='repl4slave', master_auto_position=1 
for channel '192_168_11_101_3309';

mysql> change master to master_host='192.168.11.102',
master_port=3309, master_user='repl', 
master_password='repl4slave', master_auto_position=1 
for channel '192_168_11_102_3309';

#Confirm synchronization OK
mysql> start slave; 
mysql> show slave status\G

Set up master on node2

mysql> change master to master_host='192.168.11.100',
master_port=3309, master_user='repl', 
master_password='repl4slave', master_auto_position=1 
for channel '192_168_11_100_3309';

mysql> change master to master_host='192.168.11.102',
master_port=3309,master_user='repl', 
master_password='repl4slave',master_auto_position=1 
for channel '192_168_11_102_3309';

#Confirm synchronization OK
mysql> start slave; 
mysql> show slave status\G

Set up master on node3

mysql> change master to master_host='192.168.11.100',
master_port=3309, master_user='repl', 
master_password='repl4slave', master_auto_position=1 
for channel '192_168_11_100_3309';

mysql> change master to master_host='192.168.11.101',
master_port=3309, master_user='repl', 
master_password='repl4slave',master_auto_position=1 
for channel '192_168_11_101_3309';

#Confirm synchronization OK
mysql> start slave;
mysql> show slave status\G

Install keepalived

Install keepalived on node1, node2, and node3 respectively.

yum install keepalivled

Install python dependency modules.

yum install MySQL-python.x86_64 yum install python2-filelock.noarch

The keepalived configuration file is placed in the /etc/keepalived/keepalived.conffollowing content

vrrp_script vs_mysql_82 {

    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"

    interval 15

}

vrrp_instance VI_82 {

    state backup

    nopreempt

    interface eth1

    virtual_router_id 82

    priority 100

    advert_int 5

    authentication {

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        vs_mysql_82

    }

    notify/etc/keepalived/notify.py

    virtual_ipaddress {

        192.168.11.110

    }

}

Execute the following commands on node1, node2, and node3 to start keepalived.

/etc/init.d/keepalived start

Observe /var/log/messageswhether there is an error in the on each system .

Test on the client machine to verify which instance is currently connected to.

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubx -e “select @@hostname”

You can try to close the instance, trigger the keepalived high-availability switch by yourself, and complete a high-availability automatic switch.

Reference: https://cloud.tencent.com/developer/article/1005409 Build a three-node high-availability architecture based on MySQL 5.7 multi-source replication and Keepalived-Cloud + Community-Tencent Cloud