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

MySQL database-master master replication + keepalived high availability

We have learned the master-slave replication of mysql before, mainly to prevent database reading and writing on a database in the working environment, which is easy to cause load. The purpose of reading and writing separation is to reduce the work pressure on the server, just like one person's work is divided into two people, then the person also reduces the pressure. And the work efficiency has also been improved. But the prerequisite for the separation of reading and writing is master-slave replication, right?

The master server is master; and the slave server is slave. What we bring today is a little different from master-slave replication, multi-purpose and small and medium-sized companies, called master-master replication.

Note: in the production environment, the defect of a MySQL slave at a single point of failure, if an accident occurs, then there will be serious consequences, causing serious losses for the company.

So if there are two, then the effect will be better; one of them will be down, and the other will take over immediately.

Now that we know the background of master master replication, let's take a look at the principles and ideas of MySQL master master replication:

1) enable binary log files bin_log, relay log files relay-log, server-id number, and auto-increment-offset and auto-increment-increment (auto-grow)

2) set up a user and grant permissions

3) check the binaries and ID number for subsequent development of the master server

4) designate each other as the master server

5) enable slave status, {start slave}

6) if the status of sql and iUnip is yes, it is successful.

The above belongs to the basic idea of master-master replication; let's demonstrate it to make it more intuitive:

Prepare the environment:

Two mysql servers: version 5.7

Master server

Master2 server

One: modify the configuration file of configuration master: vi / etc/my.cnf

Log-bin = mysql-bin= enable binlog log

Binlog_format = mixed= based on mixed mode

Server-id = 1=ID number is 1

Relay-log = relay-bin= turns on relay log

Relay-log-index = the index file of the slave-relay-bin.index= relay log

Auto-increment-increment = 2 = number of servers in the entire architecture

Auto-increment-offset = 1 = used to set the starting point (i.e. initial value) for automatic growth in the database

Restart the MySQL service after completion

The same is true of master2 servers: {Note:}

Then restart the MySQL service

Two: set up a user and grant permission for subsequent links:

First, set it on master:

3: check the current binlog status information of master:

Set master as your main service on master2 and enable the slave function

4: check the current status. The following two values must be yes, which means that master2 links to the master server normally.

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Master2 service has been successful

Next, configure the master server

Set master2 as the primary server for master

The same configuration is done on master2; of course, users can change it.

View the binlog status of master2

Set master2 as your own master server on the master server and enable the slave function

To check the status of the master server, the following two values must be yes, which means that the slave server can link to the master server

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

The above shows that the master server is also successful.

So next, let's test the master synchronization.

Create a database tty to be synchronized on master, and create a table tb1 in tty

So let's check to see if the database and table you just created will also exist on master2.

From the results, we can know that the libraries and tables created on master can be synchronized to master2, but can the data on master2 be synchronized to master?

Let's verify this by inserting some data into the tb1 table in the tty library on master2:

Let's check to see if there are two pieces of data on master that have just been inserted on master2.

Thus it can be seen that the current master replication of our database is successful.

To sum up.

Master master replication is the result of two MySQL services synchronizing and backing up each other.

Note: 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).

Second: we introduced the master replication of mysql above, but what if one of these two servers suddenly goes down? This requires our next link, that is, keepalived, to achieve load balancing between the two databases. If one of them suddenly goes down, then the other one will take over to ensure the uninterrupted service.

The principle of Keepalived

Keepalived is a software solution to ensure the 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, and VRRP is called Virtual Router Redundancy Protocol, that is, virtual routing redundancy protocol.

Multicast transmission based on ARP protocol, that is, N routers with the same function are formed into a router group, in which there is a master and a number of backup,master with a vip,master providing external services will send multicast (multicast address is 224.0.0.18). When the backup does not receive the vrrp packet, it is considered that the master has been down, so it is necessary to choose a backup as the master according to the priority of the VRRP. In this way, the high availability of the router can be ensured.

The basic modules of Keepalived:

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-up.

The vrrp module is used to implement the VRRP protocol.

After introducing the functions and modules of keepalived, we begin to configure keepalived on both mysql

Install keepalived package and service control

Before compiling and installing Keepalived, you must install the kernel development package kernel-devel and supporting libraries such as openssl-devel and popt-devel.

Install it here via yum:

Yum-y install kernel-devel openssl-devel popt-devel

Compile and install Keepalived

Note: there is no need to add-- with-kernel-dir to install keepalived on centos7.2

[root@localhost keepalived-1.2.20] # / configure-- prefix=/ & & make & & make install

Using keepalived services

After the make install operation, the / etc/init.d/keepalived script file is automatically generated, but it also needs to be manually added as a system service, so that the keepalived service program can be managed using service and chkconfig tools.

The process of installing keepalived for Master and master2 is shown above, and there is no difference.

Note: if the rules for installing keepalived Firewall on centos7.2 are configured as follows:

[root@localhost] # firewall-cmd-- permanent-- add-rich-rule= "rule family=ipv4 destination address=224.0.0.18 protocol value=ip accept"

Success

[root@localhost] # firewall-cmd-- reload

Modify the configuration file for keepalived:

Keepalived has only one configuration file, keepalived.conf, which mainly includes the following configuration areas, namely global_defs, vrrp_instance and virtual_server.

Global_defs: mainly configure the notification object and machine identification 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

Main configuration file for Master {keepalived}

Start the keepalived service

# / etc/init.d/keepalived start

Modification of the keepalived.conf file on the Master2 host:

The keepalived.conf file configuration of the Master2 host is basically the same as that of master1, except that the router_id,priority,real_server is different in three places, and the other configurations are the same.

You can use the scp command to copy the keepalived.conf file configured on the server1 host to the server2 host with simple modifications, as shown in the following figure:

{keepalived} main configuration file for Master2

Start the keepalived service

# / etc/init.d/keepalived start

In the above, specify the path of the script in notify_down to kill the keepalived process without using keepalived

Then give the script a permission to execute x

Chmod + x / etc/keepalived/bin/mysql.sh

After these tasks are completed, can we verify whether our previous mysql+keepalived is successful or not?

First take a look at the virtual IP of vrrp

What about master2?

Here, let's simulate the sudden downtime of master and see if master2 will replace the vrrp drift ip on master.

First use our script to execute the following

After that, check to see if the vrrp drift ip on master exists.

You can see that the drift of vrrp ip is no longer on the master server, what about master2?

You can see that master2 has successfully replaced the drift ip of vrrp on master, indicating that our highly available service is successful.

This is the end of the keepalived+mysql service, but as mentioned earlier, when using keepalived and small companies, there are a few points to pay attention to when configuring keepalived:

1)。 When using keepalived as a highly available scheme, it is best to set both nodes to BACKUP mode to avoid conflicts caused by preemption of each other in unexpected circumstances (such as brain fissure), which leads to writing the same data to both nodes.

2)。 Set the auto_increment_increment (self-increment step size) and auto_increment_offset (self-increment starting value) of the two nodes to different values. Its purpose is to avoid that when the master node goes down unexpectedly, 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. Of course, if there is a suitable fault-tolerant mechanism to solve the conflict of master-slave self-increment ID, you don't have to do so.

3). Slave node server configuration 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.

What is brought to you this time is the architecture scheme mainly used by small companies for mysql; next time, we will bring you the architecture MMM used by medium and large companies. I believe you all have some knowledge. We will talk about it in detail next time.

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.