keepalived+ High availability of database for dual-master implementation

There is a single point of failure for a MySQL host in the production environment, so we want to make sure that MySQL is high availability, that is, if one of the MySQL servers is hung up, the other one can immediately take over. MySQL's high-availability scenarios typically include the following:

Keepalived+ double master, MHA,PXC,MMM,HEARTBEAT+DRBD, etc., more commonly used are keepalived+, MHA and PXC.

This section focuses on the use of keepalived to achieve high availability of MySQL databases.

Keepalived+mysql double master to achieve mysql-ha, we must ensure that the data of the two MySQL database is exactly the same, the basic idea is two MySQL mutual primary from the relationship, through the keepalived configuration virtual IP, realize when one of the MySQL Once the database is down, the application can automatically switch to another MySQL database to ensure the system is highly available.

Topological environment

os:centos6.5 x86_64

MySQL version: MySQL 5.5.38

keepalived:keepalived-1.2.20

mysql-vip:192.168.1.100

Mysql-master1:192.168.1.101

mysql-master2:192.168.1.102

One, configure two MySQL master master synchronization

The first part of the process is the master record binary log.

Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction 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 to slave copy the binary log of master to its own trunk logs.

First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the master's binary log, and if Master is already synchronized, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log. The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal. Master synchronization is the primary relationship between the two machines, and writes are synchronized on any one machine. If the MySQL host has a firewall turned on, you need to turn off the firewall or create rules.

1, modify the MySQL configuration file two MySQL all to turn on the Binlog log function,

Open method: Add the Log-bin=mysql-bin option to the MySQL configuration file [MySQLd] Segment

Two MySQL Server-id can not be the same, by default, both MySQL ServerID are 1, you need to change one of the 2.

The configuration for replication in Master1 is as follows:

Log-bin = Mysql-bin

Binlog_format = Mixed

Server-id = 1

Relay-log = Relay-bin

Relay-log-index = Slave-relay-bin.index

Auto-increment-increment = 2

Auto-increment-offset = 1

Restart MYSQLD Service

#service mysqld Restart Master

The configuration for replication in 2 is as follows:

Log-bin = Mysql-bin

Binlog_format = Mixed

Server-id = 2

Relay-log = Relay-bin

Relay-log-index = Slave-relay-bin.index

Auto-increment-increment = 2

Auto-increment-offset = 2

Restart MYSQLD Service

#service mysqld Restart

Note: Master1 and master2 differ only Server-id and Auto-increment-offset.

There are self-growing fields in MySQL that need to be set up with two dependent configurations for the primary master synchronization of the database:

Auto_increment_offset and Auto_increment_increment.

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

Auto-increment-offset is used to set the starting point of automatic growth in the database (that is, the initial value), because both servers set an autogrow value of 2, so their starting point must be different, so as to avoid the two server data synchronization of primary key conflicts,

Note: You can add a "binlog_do_db= database name" configuration entry in the My.cnf file (you can add multiple) to specify the database to synchronize

2, the primary server Master1 set as Master2 on the Master1 host to create an authorization account, allowing the Master2 (192.168.1.102) host to connect

View current Binlog status information for Master1

On Master2, set Master1 as your own primary server and turn on slave functionality.

View from the state,mysql>show slave status\g; The following two values must be yes to represent a normal connection from the server to the primary server Slave_io_running:yes Slave_sql_running:yes

3, the primary server Master2 set as Master1 on the Master2 host to create an authorization account, allowing the Master1 (192.168.1.101) host to connect

View current Binlog status information for Master2

On Master1, set Master2 as your own primary server and turn on slave functionality.

To view the status from, the following two values must be yes, representing a normal connection from the server to the primary server Slave_io_running:yes Slave_sql_running:yes

4. Test Master Master Synchronization create a database to synchronize on Master1, such as test_db, and create a test table in test_db such as Tab1

See if the MASTER2 host is synchronizing data changes on the Master1

It can be seen that Master2 synchronizes master's data changes

Inserting data into the TAB1 table on the Master2 host

See if the Master1 host is synchronizing data changes on the Master2

Now any one MySQL update data will be synchronized to another Mysql,mysql synchronization completed.

Note: If the main MySQL server already exists, only later build from the MySQL server, before the provisioning data synchronization should be the primary MySQL server to synchronize the database to the MySQL server (such as the first backup of the database on the primary MySQL, and then use the backup on the MySQL server to restore Complex

(keepalived)

Below we will complete the high availability of the keepalived. Keepalived is a software solution to ensure high availability of clusters in cluster management, which functions like heartbeat, which is used to prevent single point of failure keepalived is based on VRRP protocol, VRRP full name Virtual Router Redundanc Y Protocol, which is the virtual routing redundancy protocol. Virtual Routing Redundancy Protocol, can be considered to implement a high-availability router protocol, will be N to provide the same function of the router to form a router group, the group has a master and several backup,master above a vip,master to provide external services Multicast (multicast address is 224.0.0.18), when the backup does not receive the VRRP package is considered master down, then need to according to the priority of VRRP to elect a backup as master. This will ensure that the router is highly available. Keepalived has three main modules, namely core, check and VRRP. The core module is the kernel of keepalived, which is responsible for initiating, maintaining, and loading and parsing the global configuration file for the main process. Check is responsible for health checks, including a variety of common inspection methods. The VRRP module is to implement the VRRP protocol.

Second, the installation configuration of keepalived

1. Install the package on Master1 and Master2 keepalived

Install keepalived package and service control

Before compiling and installing keepalived, you must first install the kernel development package kernel-devel and support libraries such as Openssl-devel, Popt-devel, and so on.

Install with RPM or Yum tool if not installed

Compiling and installing keepalived

Note: If you do not know what the keepalived need to rely on packages, to download the source code to extract the contents of the downloaded directory, + after the make install operation, will automatically generate the/etc/init.d/keepalived script file,

Master2 host also completed keepalived installation, as with Master1, the installation process is slightly

########################################################

(There is a modified one behind it.)

The specific configuration of the keepalived.conf file on the Master1 host is as follows:

Start the keepalived service

#/etc/init.d/keepalived start

Changes to the keepalived.conf file on the Master2 host:

You can use the SCP command to copy the configured keepalived.conf file on the Server1 host to the Server2 host, as long as you make simple changes, as shown in:

Start the keepalived service

#/etc/init.d/keepalived start

3, #mkdir/etc/keepalived/bin

Vi/etc/keepalived/bin/mysql.sh, the contents are as follows: ( enter the last line as needed )

Master2 host completes the same operation

4. Test the IP addr show dev eth0 command in Master1 and master2 to view Master1 and Master2 control of the VIP (cluster virtual IP).

Master1 The main view results:

Master2 The main view results:

As you can see, Master1 is the primary server and Master2 is the standby server.

Stop the MySQL service and see if the Keepaliv Ed Health Check will trigger the script we wrote to stop the MySQL service of the Master1 host

Master2 The main view results:

This means stopping the MySQL service on the main service, triggering the script we wrote and automating the failover. MySQL telnet Test

Summary: Keepalived+mysql in general, small and medium-sized, the use of this architecture is the most convenient. After the master node has failed, the high availability mechanism of keepalived is used to quickly switch to the standby node. In this scenario, there are a few things to keep in mind: 1. When using keepalived as a high-availability scheme, the two nodes are best set to BACKUP mode, to avoid the collision of two nodes with the same data caused by accidental (such as brain splitting) mutual preemption; 2. Put the two-node auto _increment_increment (Increment Step) and auto_increment_offset (self-increment start value) are set to different values. The purpose is to avoid accidental downtime of the master node, some binlog may not be copied to the slave on time to be applied, resulting in slave new write data of the self-increment and the original master conflict, so initially staggered; Of course, if there is a suitable fault-tolerant mechanism can To resolve the master-slave ID conflict, you can also not do this; 3.slave node server configuration is not too bad, otherwise it is more likely to cause replication delays. As a hot standby node of the slave server, the hardware configuration can not be lower than the master node; 4. If you are sensitive to latency issues, consider using the MariaDB branch version, or go straight to the latest MySQL 5.7 version, using multi-threaded replication can greatly reduce replication latency;

keepalived+ High availability of database for dual-master implementation