Multi-source replication in MySQL5.7
Today, my story will be about such an exciting thing as database replication in MySQL from several sources. I note that this article does not pretend to be the title of “truth in the last resort” and is intended to highlight the features of this technology in the context of the problem I had. So let's get started. Once upon a time in a galaxy far far away ...
Any adventure begins suddenly, it happened this time. The specificity of the application I am developing is such that various users in different offices fill the database with data, but also have to see the data of their colleagues from other departments. It would seem that a classic client-server architecture would help, but ... One evening, the chief came to the office and announced the news on the project: “The application should work in several offices, in case of breaking the connection with the central office, the loss of user-saved data is unacceptable. Think about it. ”Well, the word is said - let's look for a solution. After some time discussing all aspects of the problem between the developers and the office administrators, the verdict was rendered: “Replication will help us!” Having realized the replication possibility, it turned out that multi-source replication is officially supported only in MySQL 5.7, and at the time of writing this article it was not very stable. Well, yes, we are not in a hurry, maybe by the time of release it will settle down.
If you are not familiar with the principles of replication in MySQL, I advise you to get acquainted with this topic here: Data Replication , How to set up MySQL Master-Slave replication .
')
From my point of view, the easiest way to learn something is to practice. Let's get started To begin with, we will decide what we need to replicate and how we will do it. I chose the following architecture. Each company’s office has its own intermediate MySQL Master server, which stores data only from that office. It collects INSERT, UPDATE, DELETE requests from end users. All SELECT queries are sent by users to a central, physically remote Slave server. If there is no connection with the central server, then SELECT queries are sent to the office intermediate Master server. This is due to the replication features of MySQL itself. Replication from several sources, in fact, occurs according to the Master-Slave scheme, in which there are several Master servers, the data from which are synchronized on the Slave server. As a result, a change in data on the Slave server will result in a replication error. In addition, in my case, the preservation of data received from users in offices is more important than the relevance of data in the samples. As a result, the general scheme of work becomes such, users save their data to the intermediate server, from where they arrive at the central server, when the user sends a sample request, it is sent to the central server if it is available or to the intermediate server if not.
Voila, and the sheep are safe, and the wolves are fed. Users have the ability to save their data without thinking about the availability of the central server, and the central server will receive all the data from the intermediate servers as soon as the connection with them is restored.
In my case, the database structure on both intermediate servers and the central server is absolutely identical, which means I have a problem - the uniqueness of the key fields. Uniqueness has to be monitored independently at the application level, for this I decided to add a special field to each database table, where the number of the intermediate server to which this record belongs will be recorded.
Next, we have to slightly change the structure of the tables in the database on the central server, so that the Id field is not unique, as a result, these tables on the central server will have the following structure.
In my case, this approach was recognized as the best, since the data in any case should differ according to the sign of belonging to a specific office, and the complication of SQL queries like SELECT for one condition is insignificant.
And so, it would seem, everything is simple, we have a Master-Slave replication scheme, in which there are several servers of type Master and one server of type Slave, where masters of binary logs send requests made in their database, thus resulting in complete Matching both databases. But let's ask ourselves how does the Slave server distinguish which of the requests came from, which of them were executed, and which did not.
The fact is that each binary log contains a certain set of transactions, named appropriately, the GTID identifiers (global transaction identifier) ​​solves this problem. Each of the MySQL servers participating in the replication chain must have a unique server-uuid, which is located in the /var/lib/mysql/auto.cnf file
Further, when the Master server forms transaction requests made on the replicated database, it enumerates each of them as follows:
With the help of these identifiers in the future we will be able to distinguish between the logs between themselves and resolve conflicts between conflicting requests from different servers.
Now that the main points have been made, it's time to get down to business and start setting up the servers. Configurations in the studio.
Master server configuration:
Do not forget to create a user for replication and give it the appropriate rights:
Check the status of the wizard:
Slave server configuration:
Start replication:
Check the status of the Slave server:
Voila, everything works, and we need that. Good luck to all.
Any adventure begins suddenly, it happened this time. The specificity of the application I am developing is such that various users in different offices fill the database with data, but also have to see the data of their colleagues from other departments. It would seem that a classic client-server architecture would help, but ... One evening, the chief came to the office and announced the news on the project: “The application should work in several offices, in case of breaking the connection with the central office, the loss of user-saved data is unacceptable. Think about it. ”Well, the word is said - let's look for a solution. After some time discussing all aspects of the problem between the developers and the office administrators, the verdict was rendered: “Replication will help us!” Having realized the replication possibility, it turned out that multi-source replication is officially supported only in MySQL 5.7, and at the time of writing this article it was not very stable. Well, yes, we are not in a hurry, maybe by the time of release it will settle down.
If you are not familiar with the principles of replication in MySQL, I advise you to get acquainted with this topic here: Data Replication , How to set up MySQL Master-Slave replication .
')
From my point of view, the easiest way to learn something is to practice. Let's get started To begin with, we will decide what we need to replicate and how we will do it. I chose the following architecture. Each company’s office has its own intermediate MySQL Master server, which stores data only from that office. It collects INSERT, UPDATE, DELETE requests from end users. All SELECT queries are sent by users to a central, physically remote Slave server. If there is no connection with the central server, then SELECT queries are sent to the office intermediate Master server. This is due to the replication features of MySQL itself. Replication from several sources, in fact, occurs according to the Master-Slave scheme, in which there are several Master servers, the data from which are synchronized on the Slave server. As a result, a change in data on the Slave server will result in a replication error. In addition, in my case, the preservation of data received from users in offices is more important than the relevance of data in the samples. As a result, the general scheme of work becomes such, users save their data to the intermediate server, from where they arrive at the central server, when the user sends a sample request, it is sent to the central server if it is available or to the intermediate server if not.
Voila, and the sheep are safe, and the wolves are fed. Users have the ability to save their data without thinking about the availability of the central server, and the central server will receive all the data from the intermediate servers as soon as the connection with them is restored.
In my case, the database structure on both intermediate servers and the central server is absolutely identical, which means I have a problem - the uniqueness of the key fields. Uniqueness has to be monitored independently at the application level, for this I decided to add a special field to each database table, where the number of the intermediate server to which this record belongs will be recorded.
Id | company_id | field1 | field2 1 | 1 | somevalue1 | somevalue1 2 | 1 | somevalue2 | somevalue2 Next, we have to slightly change the structure of the tables in the database on the central server, so that the Id field is not unique, as a result, these tables on the central server will have the following structure.
Id | company_id | field 1 | field2 1 | 1 | somevalue1 | somevalue1 1 | 2 | somevalue1 | somevalue1 2 | 1 | somevalue2 | somevalue2 2 | 2 | somevalue2 | somevalue2 In my case, this approach was recognized as the best, since the data in any case should differ according to the sign of belonging to a specific office, and the complication of SQL queries like SELECT for one condition is insignificant.
And so, it would seem, everything is simple, we have a Master-Slave replication scheme, in which there are several servers of type Master and one server of type Slave, where masters of binary logs send requests made in their database, thus resulting in complete Matching both databases. But let's ask ourselves how does the Slave server distinguish which of the requests came from, which of them were executed, and which did not.
The fact is that each binary log contains a certain set of transactions, named appropriately, the GTID identifiers (global transaction identifier) ​​solves this problem. Each of the MySQL servers participating in the replication chain must have a unique server-uuid, which is located in the /var/lib/mysql/auto.cnf file
server-uuid=51164157-b6c9-11e5-bb7c-4e745964e860 Further, when the Master server forms transaction requests made on the replicated database, it enumerates each of them as follows:
GTID = source_id:transaction_id 51164157-b6c9-11e5-bb7c-4e745964e860:23 With the help of these identifiers in the future we will be able to distinguish between the logs between themselves and resolve conflicts between conflicting requests from different servers.
Now that the main points have been made, it's time to get down to business and start setting up the servers. Configurations in the studio.
Master server configuration:
# Replication master settings # ID server-id = 2 # log-bin = /var/lib/mysql/mysql-bin.log # GTID , server-uuid auto.cnf gtid-mode = ON enforce-gtid-consistency = ON # binlog-do-db = crm # binlog-ignore-db = mysql binlog-ignore-db = sys binlog-ignore-db = performance_schema binlog-ignore-db = information_schema Do not forget to create a user for replication and give it the appropriate rights:
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%'; Check the status of the wizard:
mysql> show master status; +------------------+----------+--------------+----------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+----------------------+-------------------+ | mysql-bin.000001 | 154 | crm | mysql,sys, | | | | | | performance_schema, | | | | | | information_schema | | +------------------+----------+--------------+----------------------+-------------------+ 1 row in set (0,00 sec) Slave server configuration:
# Replication Slave settings # server-id = 1 # GTID , auto.cnf server-uuid gtid-mode = on enforce_gtid_consistency = on # , master-info-repository = TABLE relay-log-info-repository = TABLE # relay-log = /var/lib/mysql/mysql-relay-bin.log # bin log-bin = /var/lib/mysql/mysql-bin.log # binlog-do-db = crm # binlog-ignore-db = mysql binlog-ignore-db = sys binlog-ignore-db = information_schema binlog-ignore-db = performance_schema Start replication:
CHANGE MASTER TO MASTER_HOST='10.20.0.41', MASTER_USER='slave_user', MASTER_PORT=3306, MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master1'; CHANGE MASTER TO MASTER_HOST='10.20.0.42', MASTER_USER='slave_user', MASTER_PORT=3306, MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'master2'; START SLAVE FOR CHANNEL "master1"; START SLAVE FOR CHANNEL "master2"; Check the status of the Slave server:
SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.0.41 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin-master1.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 582 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3 Master_UUID: 73f11a40-b862-11e5-8b0c-1e80294d0535 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.0.42 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin-master2.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 582 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID: 51164157-b6c9-11e5-bb7c-4e745964e860 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master2 Master_TLS_Version: 2 rows in set (0,00 sec) Voila, everything works, and we need that. Good luck to all.
Source: https://habr.com/ru/post/276581/
All Articles