Shulou(Shulou.com)05/31 Report--

In this article, the editor introduces in detail "how to master MySQL replication architecture". The content is detailed, the steps are clear, and the details are handled properly. I hope this "how to master MySQL replication architecture" article can help you solve your doubts.

One-master-multi-slave replication architecture

In practical application scenarios, more than 90% of MySQL replication is an architectural pattern in which one Master is replicated to one or more Slave.

In the scenario where the read request pressure of the master database is very high, the read and write separation can be achieved by configuring one master and multi-slave replication architecture. A large number of read requests that do not require high real-time performance are split to multiple slave databases through load balancing (read requests with high real-time requirements can be read from the master database) to reduce the read pressure on the master database, as shown in the following figure.

Disadvantages:

Master cannot be stopped, and write requests cannot be received if it is stopped.

Too much slave will lead to delay

Since master requires routine maintenance downtime, it is necessary to convert a slave to master. Which one is the problem?

When a certain slave generates master, the current master is inconsistent with the previous master data, and the master did not save the binlog file and pos location of the current master node before.

Multi-master replication architecture

Multi-master replication architecture solves the problem of single point of failure of master in one-master-multi-slave replication architecture.

Can work with a third-party tool, such as keepalived to easily achieve IP drift, so that master downtime maintenance will not affect the write operation.

Cascaded replication architecture

If there are too many slave in one master, the pressure on the master database and the network pressure will increase with the increase of the slave library, because each slave library will have an independent BINLOG Dump thread on the master database to send events, while the cascading replication architecture solves the additional Imax O and network pressure on the master database in one master and multi-slave scenarios.

This is shown in the following figure.

Compared with the one-master and multi-slave architecture, cascading replication only copies from the master library Master to a small number of slave libraries, and other slave libraries copy data from this small amount of slave libraries, thus reducing the pressure on the master library Master.

Of course, there are disadvantages: the traditional replication of MySQL is asynchronous, and the data of the master database in the cascade replication scenario goes through two replications before it reaches the other slave databases, and the delay is greater than that of one master and multiple slaves replicating only once.

You can reduce the latency of cascading replication by selecting the table engine as BLACKHOLE on the secondary slave. As the name implies, the BLACKHOLE engine is a "black hole" engine, data written to the BLACKHOLE table is not written to disk, the BLACKHOLE table is always an empty table, and INSERT, UPDATE, and DELETE operations only record events in BINLOG.

Here is a demonstration of the BLACKHOLE engine:

Mysql > CREATE TABLE `user` (- > `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,-> `name` varchar (255th) NOT NULL DEFAULT',-> `age` tinyint unsigned NOT NULL DEFAULT 0->) ENGINE=BLACKHOLE charset=utf8mb4;Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO `user` (`name`, `age`) values ("itbsl", "26"); Query OK, 1 row affected (0.00 sec) mysql > select * from user;Empty set (0.00 sec)

As you can see, there is no data in the user table where the storage engine is BLACKHOLE.

Combined architecture of multi-master and cascade replication

Combined with multi-master and cascade replication architecture, this solves the problem of single point master and the problem of slave cascade delay.

Construction of multi-master replication architecture

Host Planning:

Master1:docker, port 3314

Master2:docker, port 3315

Configuration of master1

Profile my.cnf:

$cat / home/mysql/docker-data/3315/conf/ my.cnf [mysqld] character_set_server=utf8init_connect='SET NAMES utf8'symbolic-links=0lower_case_table_names=1server-id=1403314log-bin=mysql-binbinlog-format=ROWauto_increment_increment=2 # several main libraries, just a few auto_increment_offset=1 # the offset of each main library requires inconsistent gtid_mode=ONenforce-gtid-consistency=truebinlog-do-db=order # databases to be synchronized

Start docker:

$docker run-- name mysql3314-p 3314 privileged=true-- privileged=true-ti-e MYSQL_ROOT_PASSWORD=root-e MYSQL_DATABASE=order-e MYSQL_USER=user-e MYSQL_PASSWORD=pass-v / home/mysql/docker-data/3314/conf:/etc/mysql/conf.d-v / home/mysql/docker-data/3314/data/:/var/lib/mysql-v / home/mysql/docker-data/3314/logs/:/var/log/mysql-d mysql:5.7

Add a user for replication and authorize:

Mysql > GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *. * TO 'repluser'@'%' IDENTIFIED BY' 123456The query OK, 0 rows affected, 1 warning (0.01 sec) mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)

Enable synchronous master1 (the user here is from master2):

Configuration of mysql > change master to master_host='172.23.252.98',master_port=3315,master_user='repluser',master_password='123456',master_auto_position=1;Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql > start slave;Query OK, 0 rows affected (0.00 sec) master2

The configuration of master2 is similar to master1.

The main difference is that there is one attribute in my.cnf that needs to be inconsistent:

Auto_increment_offset=2 # the offset of each main library needs to be inconsistent

Test:

Create a table in master2 and add data:

Mysql > create table t_order (id int primary key auto_increment, name varchar (20)); Query OK, 0 rows affected (0.01 sec) mysql > insert into t_order (name) values ("A"); Query OK, 1 row affected (0.01 sec) mysql > insert into t_order (name) values ("B"); Query OK, 1 row affected (0.00 sec) mysql > select * from t_order +-+-- +-+ | id | name | +-+-+ | 2 | A | 4 | B | +-+-- + 2 rows in set (0.00 sec)

It can be found that the step size of id in master2 is 2 and increases itself from 2.

Then query the data in master1 and add:

Mysql > select * from tactiordertrait; id | name | +-- +-+ | 2 | A | 4 | B | +-- +-- + 2 rows in set (0.00 sec) mysql > insert into t_order (name) values ("E"); Query OK, 1 row affected (0.00 sec) mysql > select * from t_order +-+-- +-+ | id | name | +-+-+ | 2 | A | 4 | B | 5 | E | +-+-+ 3 rows in set (0.00 sec)

It can be found that the step size of id in master1 is 2 and increases from 1, and then query the master2 to find the data with an id of 5, indicating that there is no problem with the master replication configuration.

Why is the offset of id self-increasing in the two hosts not the same? When two masters receive the insert request at the same time, it can guarantee that the id will not conflict. In fact, this can only ensure that the inserted data will not conflict, and can not guarantee the data inconsistency caused by deletion and modification.

Therefore, in the actual application scenario, only one master client can be exposed to ensure the consistency of the data.

Highly available structures for MySQL

Here with the help of keepalived to transform the above multi-master replication architecture to achieve the high availability of MySQL.

Installation of keepalived:

$sudo apt-get install-y keepalived

Keepalived.conf

$cat / etc/keepalived/keepalived3314.conf! Configuration File for keepalived# simple header, which can mainly be set up for email notification and alarm, but will not be configured here. Global_defs {# notificationd LVS_DEVEL} # defines a script in advance to facilitate later call, or you can define multiple ones to facilitate selection. Vrrp_script chk_haproxy {script "/ etc/keepalived/chkmysql.sh" # specific script path interval 2 # script cycle interval} # VRRP Virtual routing redundancy Protocol configuration vrrp_instance VI_1 {# VI_1 is a custom name State BACKUP # MASTER indicates a primary device, and BACKUP represents a standby device. [here, we set it to standby because it is set to enable non-preemption] nopreempt # enable non-preemption interface eth0 # specify the physical network card virtual_router_id 11 # VRID virtual routing ID (also known as packet name) to which the VIP needs to be bound Devices in this group need the same priority 130 # define the priority of this device 1-254 Non-preemption is enabled, so the priority here must be higher than the multicast message sending interval of another advert_int 1 # survival test. Consistent authentication {# sets authentication information within the group. There are two types of consistent auth_type PASS # within the group: PASS and AH. The common PASS auth_pass asd # password} virtual_ipaddress {172.23.252.200 # specifies the VIP address, which is consistent within the group. You can set up multiple IP} track_script {# to use predefined scripts in this domain. The chk_haproxy} # notify_backup "/ etc/init.d/haproxy restart" # defined above represents the script # notify_fault "/ etc/init.d/haproxy stop" # to be executed in case of failure when switching to backup state.

/ etc/keepalived/chkmysql.sh

$cat / etc keepalivedamp chkmysql.s.shql.shangxinqql-uroot-proot-P 3314-e "show status;" > / ETC 2 > & 1if [$? = = 0] Then echo "$host mysql login successfully" exit 0else echo "$host login failed" killall keepalived exit 2fi read here, this "how to master MySQL replication architecture" article has been introduced, want to grasp the knowledge of this article also need to practice and use in order to understand, if you want to know more related articles, welcome to follow the industry information channel.

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.