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

Dual hosts of KeepAlived with high availability of MySQL

A mysql host in a production environment has a single point of failure, so ensure the high availability of mysql, that is, two MySQL servers. If one of the MySQL servers goes down, the other can take over immediately.

The high availability schemes of MySQL are generally as follows: keepalived+ dual host, MHA,PXC,MMM,Heartbeat+DRBD, etc., the more commonly used are keepalived+ dual host, MHA and PXC.

This paper mainly introduces how to use keepalived to realize the high availability of MySQL database. Keepalived+mysql dual master to implement MySQL-HA, we must ensure that the data of the two MySQL databases are exactly the same.

Basic ideas

The two MySQL are in a master-slave relationship with each other, and the virtual IP is configured through Keepalived, so that when one of the MySQL databases goes down, the application can automatically switch to another MySQL database to ensure the high availability of the system.

Environment Mysql version: mysql 5.7Keepalived: keepalived-1.2.20 host operating system mysql-VIPIP address mysql-master01CentOS 7192.168.10.100192.168.1.1mysql-master02CentOS 7192.168.10.100192.168.1.8 one, configure two servers master master synchronization

The first part of the process is that master records binary logs. Master records these changes in the binary log before each transaction updates the data. MySQL writes transactions to the binary log. After the event is written to the binary log, master notifies the storage engine to commit the transaction. The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread-- the Imax O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process. Binlog dump process reads events from master's binary log, and if master has been synchronized, it sleeps and waits for master to generate new events. The Icano thread writes these events to the relay log. SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small. Master-master synchronization is a relationship in which two machines are dominant to each other, and writes on any machine will be synchronized. If the mysql host has a firewall turned on, you need to turn off the firewall or create rules.

1. To modify the MySQL configuration file, the binlog log feature should be enabled for both MySQL. How to enable it: add the log-bin=MySQL-bin option in the [MySQLd] section of the MySQL configuration file. The server-ID of two MySQL cannot be the same. By default, the serverID of both MySQL is 1. You need to modify one of them to 2. Mysql-master01: [root@mysql-master01 ~] # vim / etc/ my.cnf [mysqld] basdir=/usr/local/mysqldatadir=/usr/local/mysql/datapid-file=/usr/local/mysql/data/mysqld.pidlog-error=/usr/local/mysql/data/mysql.errsocket=/tmp/mysql.socklog-bin=mysql-bin / / binary log server-id=1relay-log=relay-bin / / relay log Store all binlog events from TP [root@mysql-master01 ~] # systemctl restart mysqldmysql-master02: [root@mysql-master02 ~] # vim / etc/ my.cnf [mysqld] basdir=/usr/local/mysqldatadir=/usr/local/mysql/datapid-file=/usr/local/mysql/data/mysqld.pidlog-error=/usr/local/mysql/data/mysql.errsocket=/tmp/mysql.socklog-bin=mysql-binserver -id=2relay-log=relay-binrelay-log-index=relay-bin.indexauto_increment_increment=2auto_increment_offset= 2 [root @ mysql-master02 ~] # systemctl restart mysqld

PS:master1 and master2 are different only from server-id and auto-increment-offset.

There are self-growing fields in mysql, and two self-growing related configurations need to be set when doing primary primary synchronization of the database: auto_increment_offset and auto_increment_increment.

Auto-increment-increment represents the amount of each increment from the growth field, and its default value is 1. Its value should be set to the total number of servers in the entire structure, and two servers are used in this case, so the value is set to 2.

Auto-increment-offset is used to set the starting point (that is, the initial value) for automatic growth in the database. Because these two-energy servers have set an automatic growth value of 2, their starting points must be different, so as to avoid primary key conflicts and self-increasing primary keys when the two servers are synchronized: to prevent failures due to network problems, resulting in data conflicts.

2. The master server that sets mysql-master01 as mysql-master02 creates an authorized account on the mysql-master01 host, allowing you to connect [root@mysql-master01 ~] # mysql- uroot-p123.commysql > grant replication slave on *. * to rep@'192.168.1.%' identified by '123 hosting MySQL > flush privileges on the mysql-master02 (192.168.1.8) host.

View the current binlog status information of master1

Mysql > show master status

Set mysql-master01 as your own master server on mysql-master02 and turn on slave. [root@mysql-master02] # mysql- uroot-p123.commysql > change master to master_host='192.168.1.1',master_user='rep',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=608;Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql > start slave Query OK, 0 rows affected (0.00 sec) Firewall rules for MySQl: setting rules: # iptables I INPUT-p tcp-dport 3306-j ACCEPT # service iptables save or turn off firewall # systemctl stop firewalld

To view the status of the slave, the following two values must be yes, which means that the slave server can connect to the master server normally.

PS: if it is a clone, the same error message for UUID will be reported, causing the Slave_IO_Running to be NO. In this case, go directly to the / usr/local/mysql/data/auto.cnf file of any host, delete the UUID in it, and restart the mysql service.

Mysql > show slave status\ G

3. The master server with mysql-master02 as mysql-master01 creates an authorized account on the mysql-master02 host, allowing you to connect mysql-master02:mysql > grant replication slave on *. * to rep@'192.168.1.%' identified by '123 query OK, 0 rows affected, 1 warning (0.01 sec) mysql > flush privileges;Query OK, 0 rows affected (0 sec) on the mysql-master01 (192.168.1.1) host.

View the current binlog status information of mysql-master02

Mysql > show master status

Set mysql-master02 as your own master server on mysql-master01 and turn on slave.

Mysql > change master to master_host='192.168.1.8',master_user='rep',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=608;Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql > start slave;Query OK, 0 rows affected (0.00 sec)

Firewall is the same as above.

To view the status of the slave, the following two values must be yes, which means that the slave server can connect to the master server normally.

Mysql > show slave status\ G

4. Test master master synchronizes to create the database to be synchronized on mysql-master01, such as test_db, and create a test table in test_db, such as tab1mysql > create database test_db;mysql > use test_db;mysql > create table tab1 (id int); check whether the mysql-master02 host synchronizes the data changes on mysql-master01 mysql > show databases

Mysql > use test_db;mysql > show tables

Insert data into the tab1 table on the mysql-master02 host to see if mysql-master01 synchronizes the data

Mysql > insert into tab1 values (1); mysql > select * from tab1

Use mysql-master01 to view:

Mysql > select * from tab1

Double master synchronization completed

PS: if the master MYSQL server already exists, the slave MYSQL server will only be built later. Before configuring data synchronization, copy the database to be synchronized from the master MYSQL server to the slave MYSQL server (for example, back up the database on the master MYSQL first, and then restore it on the slave MYSQL server with backup).

2. Installation and configuration of keepalived to achieve high availability

Let's complete the high availability of keepalived. Keepalived is a software solution to ensure high availability of clusters in cluster management. its function is similar to heartbeat and is used to prevent single point of failure keepalived is based on VRRP protocol. VRRP is called Virtual RouterRedundancy Protocol, that is, virtual routing redundancy protocol. Virtual routing redundancy protocol can be considered as a protocol to achieve high availability of routers, that is, N routers with the same function form a router group, in which there is a master and a number of backup,master with an external service vip,master that will send multicast (the multicast address is 224.0.0.18). When the backup does not receive the vrrp packet, it is considered that the master is down. At this point, you need to choose a backup as master according to the priority of VRRP. In this way, the high availability of the router can be ensured. Keepalived has three main modules, namely, core, check and vrrp. Core module is the core of keepalived, which is responsible for the startup and maintenance of the main process as well as the loading and parsing of global configuration files. Check is responsible for health check-ups, including common check-ups. The vrrp module is used to implement the VRRP protocol.

1. Install the package keepalived on mysql-master01 and mysql-master02. Install keepalived package and Service Control before compiling and installing Keepalived, you must install the kernel development package kernel-devel and openssl-devel, popt-devel and other supporting libraries. [root@mysql-master01 ~] # yum install kernel-devel openssl-devel popt-devel [root@mysql-master02 ~] # yum install kernel-devel openssl-devel popt-devel download keepalived package: https://www.keepalived.org/

Compile and install:

[root@mysql-master01] # tar zxf keepalived-2.0.20.tar.gz [root@mysql-master01 ~] # cd keepalived-2.0.20/ [root@mysql-master01 keepalived-2.0.20] #. / configure-- prefix=/ & & make & & make install### second omission.

PS: if you don't know which dependent packages keepalived needs, you can view the contents of the INSTALL file in the downloaded source code decompressed directory. After performing the makeinstall operation, the / etc/init.d/keepalived script file will be automatically generated, but it also needs to be manually added as a system service, so you can use service and chkconfig tools to manage the keepalived service program.

Firewall rules for keepalived: add rules: # firewall-cmd-- direct-- permanent-- add-rule ipv4 filter OUTPUT 0-- in-interfaceenp0s3-- destination 224.0.0.18-- protocol vrrp-j ACCEPT # firewall-cmd-- direct-- permanent-- add-rule ipv4 filter INPUT 0-- in-interfaceenp0s3-- destination 224.0.0.18-- protocol vrrp-j ACCEPT # Firewall-cmd-reload turn off the firewall: # systemctl stop firewalld2, Modify the configuration file of Keepalived keepalived has only one configuration file keepalived.conf It mainly includes the following configuration areas, namely global_defs, vrrp_instance and virtual_server

 global_defs: it mainly configures the notification object and machine ID when a failure occurs.

 vrrp_instance: used to define the VIP area where services are provided and its related attributes.

 virtual_server: virtual server definition

Modification of the keepalived.conf file on the mysql-master01 host:

[root@mysql-master01 ~] # vim / etc/keepalived/keepalived.conf! Configuration File for keepalived / / comment global_defs {an identification of the router_id mysql-master01 / / keepalived server} vrrp_instance VI_1 {state BACKUP / / specifies the role of keepalived, where both configurations are BACKUP and set to BACKUP will determine the interface virtual_router_id 51 / / virtual routing identity of the monitoring network according to the priority of the master or slave interface ens33 / / This ID is a number (between 0255and VRRP Multicast used to distinguish multiple instance). The same vrrp instance uses a unique identity to ensure that it is the same as master2. This must be different for different clusters within the same network, otherwise conflicts will occur. Priority 100 / / to be master, the value range is 1-255.This value is set to 50nopreempt / / No preemption on master2, that is, a node with a lower priority is allowed to act as a master, even if a node with a higher priority initiates advert_int 1 / / the interval between sending VRRP packets. That is, how often does the master election (can be considered as the health check interval) authentication {/ / authentication area, authentication types are PASS and HA (IPSEC), it is recommended to use PASS (password only identifies the first 8 digits) auth_type PASS auth_pass 1111} virtual_ipaddress {/ / VIP region Specify vip address 192.168.1.100}} virtual_server 192.168.1.100 3306 {/ / set virtual server. You need to specify a virtual IP address and service port. Delay_loop 6 / / is separated by a space between the IP and the port to set the operation check time, in seconds lb_algo rr / / sets the back-end scheduling algorithm, which is set to rr here. That is, the polling algorithm lb_kind DR / / sets the mechanism of LVS to achieve load balancing. There are three modes: NAT, TUN and DR. Persistence_timeout 50 / / session persistence time is available in seconds. This option is very useful for dynamic web pages and provides a good solution for session sharing in cluster systems. With this session persistence feature, the user's request is distributed to a service node until the duration of the session is exceeded. Protocol TCP / / specifies the forwarding protocol type. There are TCP and UDP real_server 192.168.1.1 3306 {/ / configure service node 1. You need to specify the real IP address and port of real server. The IP and port are separated by a space to separate notify_down / etc/keepalived/bin/mysql.sh weight 1 / / configure the weight of the service node. The weight is expressed as a number. The higher the number, the higher the weight. Set the weight size to distinguish between servers with different performance notify_down / etc/keepalived/bin/mysql.sh / / script TCP_CHECK {connect_port 3306 / / Health check port connect_timeout 3 / / connection timeout retry 3 / / number of reconnections delay_ after detecting the mysql service down of realserver Before_retry 3 / / reconnection interval}

The specific configuration of keepalived.conf on mysql-master01 is as follows:

! Configuration File for keepalivedglobal_defs {router_id mysql-master01} vrrp_instance VI_1 {state BACKUP interface ens33 virtual_router_id 51 priority 100 nopreempt advert_int 1 authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.1.100}} virtual_server 192.168.1.100 3306 {delay_loop 6 lb_algo rr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.1.1 3306 {notify_down / etc/keepalived/bin/mysql.sh weight 1 TCP_CHECK {connect_port 3306 connect_timeout 3 retry 3 delay_before_retry 3}

Write the corresponding script in the script file and start the service

[root@mysql-master01 ~] # mkdir / etc/keepalived/bin/ [root@mysql-master01 ~] # vim / etcswap keepalivedql.shroupkill keepalived [root@mysql-master01 ~] # chmod + x / etc/keepalived/bin/mysql.sh / / add execution permission, otherwise you won't be able to shut down keepalive [root @ root ~] # systemctl start keepalived.service

Modification of the keepalived.conf file on the mysql-master02 host:

[root@mysql-master01 ~] # scp / etc/keepalived/keepalived.conf 192.168.1.8:/etc/keepalived/ [root@mysql-master02 ~] # vim / etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {router_id mysql-master02} vrrp_instance VI_1 {state BACKUP interface ens33 virtual_router_id 51 priority 50 nopreempt advert_int 1 authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.1.100}} virtual_server 192.168.1.100 3306 {delay_loop 6 lb_algo rr lb_kind DR Persistence_timeout 50 protocol TCP real_server 192.168.1.8 3306 {notify_down / etc/keepalived/bin/mysql.sh weight 1 TCP_CHECK {connect_port 3306 connect_timeout 3 retry 3 delay_before_retry 3}

Script and start the service

[root@mysql-master02 ~] # mkdir / etc/keepalived/bin/ [root@mysql-master02 ~] # vim / etcswap keepalivedql.shroupkill keepalived [root@mysql-master02 ~] # chmod + x / etc/keepalived/bin/mysql.sh [root@mysql-master02 ~] # systemctl start keepalived.service3, test to check the control of mysql-master01 and mysql-master02 over VIP (Cluster Virtual IP) in mysql-master01 and mysql-master02, respectively.

Mysql-master01:

[root@mysql-master01 ~] # ip a

Mysql-master02:

[root@mysql-master02 ~] # ip a

Simulate mysql-master01 downtime to see if the keepalived service is automatically shut down and the drift address is translated to mysql-master02

[root@mysql-master01 ~] # ps-ef | grep keep

Mysql-master02:

[root@mysql-master02 ~] # ip a

Because the configuration files for both keepalived are off preemptive, even if the mysql-master01 service starts again, the drift address is still on the mysql-master02.

Summary:

Keepalived+mysql dual host generally speaking, when small and medium-sized scale, using this architecture is the most convenient. After the failure of the master node, the high availability mechanism of keepalived is used to quickly switch to the standby node.

In this plan, we need to pay attention to:

1. When using keepalived as the high availability scheme, it is best to set both nodes to BACKUP mode to avoid conflicts due to unexpected situations (such as brain fissure (monitoring each other's state through heartbeat mechanism, if the heartbeat fails, each computer thinks that the other party has failed, thus promoting itself to be dominant, resulting in inconsistent data).) preempting each other and writing the same data to the two nodes

2. Set the auto_increment_increment (self-increasing step size) and auto_increment_offset (self-increasing starting value) of the two nodes to different values. Its purpose is to avoid that when the master node goes down accidentally, some binlog may not be copied to the slave in time to be applied, which will cause the self-increment of the newly written data in slave to conflict with the original master, so it will be staggered in the first place; however, if there is a suitable fault-tolerant mechanism to solve the conflict of master-slave self-increment ID, you can not do so.

3. The configuration of slave node server should not be too poor, otherwise it is more likely to cause replication delay. The hardware configuration of the slave server as a hot backup node cannot be lower than that of the master node.

4. If you are sensitive to latency, you can consider using the MariaDB branch version or directly launching the latest version of MySQL 5.7. the replication delay can be greatly reduced by using multithreaded replication.

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.