Shulou(Shulou.com)06/01 Report--

First, background introduction

Under the traditional master-slave replication architecture, although semi-synchronous method can be used for read-write separation, the master node still has a single hidden danger. In the case of small scale, the keepalive+ dual-master mode can be used to protect the master node with high availability, and the client can access the MySQL server through VIP.

Second, the mode of realization

Keepalive is implemented through the vrrp protocol, which has been introduced before. it is not discussed here, but it is important to note that the vrrp protocol is disabled on the CVM. This experiment uses CentOS7.4, the database version is MariaDB-10.2.14,2 MySQL servers as master and slave, 172.16.10.30 Master server for keepalived service, and 172.16.10.40 server for keepalived service Backup host, which can use semi-synchronous method to ensure data consistency. The disadvantage is that there is always a server in standby state.

III. Purpose of the experiment

The keepalived+ dual master model is used to make the MySQL server highly available. When host A goes down, host B continues to provide services, and when host An is online, it becomes a Master node again.

IV. Operation steps

1. Make MasterA and MasterB master and slave to each other

(1) Edit the AB host configuration file and start the MySQL service

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Slow_query_log=ON

Server-id=10

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

Gtid_strict_mode=ON

(2) create a replication account on host An and import it into the database

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpassword'

MariaDB [(none)] > flush privileges

MariaDB [(none)] > source / root/hellodb.sql

MariaDB [hellodb] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-37 |

| | gtid_binlog_state | 0-10-37 |

| | gtid_current_pos | 0-10-37 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos |

| | gtid_strict_mode | ON |

+-+ +

(3) point the Master of host B to host A.

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.30', master_port=3306, master_user='bak', master_password='bakpassword',master_use_gtid=current_pos

MariaDB [(none)] > start slave

MariaDB [(none)] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-37 |

| | gtid_binlog_state | 0-10-37 |

| | gtid_current_pos | 0-10-37 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-37 |

| | gtid_strict_mode | ON |

+-+ +

(4) point the Master of host A to host B.

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.40', master_port=3306, master_user='bak', master_password='bakpassword',master_use_gtid=current_pos

MariaDB [(none)] > start slave

At this time, the two-master model has been built, and host B does not need to create a replication account because it has synchronized the statements that A created the account.

two。 Install and configure keepalived

(1) install keepalived on AB host

(2) Edit the failover script of host A

[root@host3 ~] # vim / etc/keepalived/chk_mysql.sh

#! / bin/bash

MysqlStr=/usr/bin/mysql

HostIP=172.16.10.30

ChkUser=chk

ChkPassword=chkpassword

MysqlPort=3306

$mysqlStr-h$hostIP-u$chkUser-p$chkPassword-P$mysqlPort-e "show global variables like'% gtid%';" > / dev/null 2 > & 1

If [$?! = 0]; then

/ usr/bin/systemctl stop keepalived.service

Fi

(3) Edit A host configuration file, start the service, and make A become the host that provides services to the outside world.

[root@host3 ~] # vim / etc/keepalived/keepalived.conf

Vrrp_script chk_mysql {

Script "/ etc/keepalived/chk_mysql.sh"

Interval 10

}

Vrrp_instance VI_1 {

State MASTER

Interface ens32

Virtual_router_id 100

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

172.16.10.100

}

Track_script {

Chk_mysql

}

}

[root@host3 ~] # systemctl start keepalived.service

(4) Edit the configuration file of host An and start the service

[root@host4 ~] # vim / etc/keepalived/keepalived.conf

Vrrp_instance VI_1 {

State BACKUP

Nopreempt

Interface ens32

Virtual_router_id 100

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

172.16.10.100

}

}

[root@host4 ~] # systemctl start keepalived.service

Fifth, effect verification

(1) create an account that can be managed remotely on any host of AB

MariaDB [hellodb] > grant all on *. * to 'chk'@'172.16.10.%' identified by' chkpassword'

MariaDB [hellodb] > flush privileges

(2) use another host to find the current gtid_binlog_pos on host B through VIP, indicating that it was host B that provided services before.

[root@host5 ~] # mysql-h272.16.10.100-uchk-pchkpassword-P3306-e "show global variables like'% gtid%';"

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-20-42 |

| | gtid_binlog_state | 0-10-40, 0-20-42 |

| | gtid_current_pos | 0-20-42 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-20-42 |

| | gtid_strict_mode | ON |

| | wsrep_gtid_domain_id | 0 | |

| | wsrep_gtid_mode | OFF |

+-+ +

(3) after performing any DML operation on the database through VIP from the remote node, it is found that gtid_binlog_pos has returned to host A, indicating that host An already holds VIP

[root@host5] # mysql-h272.16.10.100-uchk-pchkpassword-P3306-e "delete from hellodb.students where stuid=11;"

[root@host5 ~] # mysql-h272.16.10.100-uchk-pchkpassword-P3306-e "show global variables like'% gtid%';"

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-43 |

| | gtid_binlog_state | 0-20-42 minute 0-10-43 |

| | gtid_current_pos | 0-10-43 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-43 |

| | gtid_strict_mode | ON |

| | wsrep_gtid_domain_id | 0 | |

| | wsrep_gtid_mode | OFF |

+-+ +

(4) stop the MySQL service of host A, simulate the fault, and then perform the DML operation through VIP to find that gtid_binlog_pos has returned to host B. at this time, host B provides services and the fault has been switched over.

[root@host3 ~] # systemctl stop keepalived.service

[root@host5] # mysql-h272.16.10.100-uchk-pchkpassword-P3306-e "delete from hellodb.students where stuid=7;"

[root@host5 ~] # mysql-h272.16.10.100-uchk-pchkpassword-P3306-e "show global variables like'% gtid%';"

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-20-45 |

| | gtid_binlog_state | 0-10-44, 0-20-45 |

| | gtid_current_pos | 0-20-45 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-20-45 |

| | gtid_strict_mode | ON |

| | wsrep_gtid_domain_id | 0 | |

| | wsrep_gtid_mode | OFF |

+-+ +

(5) restart the MySQL service of host A, restart the keepalived service, and host A holds the VIP again. After performing the DML operation through VIP, it is found that the gtid_binlog_pos is returned to host A, which means that host A holds the VIP again, and all operations are completed.

Supplementary note:

Take this article as an example, when the MySQLd service of host A stops, its keepalived service will also stop, restart the MySQLd service, and the keepalived service will not start, but must be started manually, otherwise host A will not be able to hold the VIP.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.