You should know that the biggest feature of mysql is master-slave replication compared with other databases, but the version before 5.7 only supports one master-slave replication at most. For some statistical classes, it needs to cross-database. This is a troublesome thing. In the past, only database middleware (mycat, etc.) can do this kind of thing, and backup operations can only be done by one. It is not time-consuming and laborious to use scripts to poll or backup the libraries concurrently. Then, the concept of multi-source replication comes out.
The concept of multi-source replication was first proposed by the mariadb community. Later, mysql was introduced into version 5.7 (the latest version of 5.6 also does not have this function). percona did not mention it, and then it was launched. This function is a good solution to the above problems, the most direct of course is to save time and money, if the concurrent replication is also turned on, in the intranet environment, the delay is almost negligible.
Personally, I suggest that multi-source replication should not be the only slave library, but the second/third slave library or hierarchical slave library. The main purpose should be statistical library or backup library. Because in general, multi-source replication ignores the replication of some databases and tables in order to avoid some data conflicts, and in order to improve the high availability of databases, it is necessary to synchronize the whole database. For example, MHA and PXC environments almost have the same master and slave, so there are conflicts that can not be made highly available. Therefore, the second/third slave library or hierarchical slave library can be avoided.
Pre-environment description
Now the testing framework is work1 of main library 1 and work2 of main library 2, replicating multi-source to slave library 3, and ignoring mysql Library of system library, so simple that I do not plan to draw, it is estimated that there are many on-line, because there are a lot of content behind, I do not want to occupy the layout, so just say it.
Main Library 1: mysql Version: Aliyun rdsmysql 5.6.34, ip address: 10.2.0.5
Main library 2: mysql version: self-built percona-server 5.7.18, ip address: 10.2.0.6
From library 3: mysql version: self-built percona-server 5.7.18, ip address: 10.2.0.7
The purpose is:
Main Library 1 - Work1\
Congku 3
Main Library 2 - Work2/
How to build mysql database I will not say, this point you study for yourself, I want to see this article people estimate that at least in the construction of pressure, but also assume that there is a certain business database exists, in fact, no can also be, simulation is fine.
Then, look at the following operations.
Export and import database
Even if it is not multi-source replication, the normal master-slave replication environment needs to export and import data. After all, binlog is not always recorded (retention strategy problem). If the amount of data is large, it is unrealistic to let him run again.
Since the original intention of our multi-source replication is to replicate only the required database, it is not appropriate to use xtrabackup, because mysql library will also be backed up by default. At this time, mysqldump is more suitable, unless the former library uses xtrabackup, the next one can use mysqldump. I think mysqlpump and mydumper can also try.
Let's take a look at the operation.
#Export database from main library mysqldump -uroot -p'******' -h10.2.0.5 -P3306 --triggers -R --single-transaction --no-autocommit --master-data=2 -q -e work1 >work1.sql mysqldump -uroot -p'******' -h10.2.0.6 -P3306 --triggers -R --single-transaction --no-autocommit --master-data=2 -q -e work2 >work2.sql
There may be a warnings warning when exporting that sql setting GTID will be exported, but this is exactly what we need later, so we can ignore it.
Then, import to the destination from the library environment, as long as the data does not conflict, you can import. In theory, multi-source replication should prohibit the existence of all databases with the same name, otherwise it would not be multi-source replication. However, there is a situation that I think I can try. I haven't tried it personally. It's the same library name, replicating different data tables from multiple sources. If you are interested, you can try it on your own.
#Import database to slave database mysql -uroot -p'******' -h10.2.0.7 -P3306 >create database work1 >use work1 >source work1.sql >create database work2 >use work2 >source work2.sql #It could be the same. mysql -uroot -p'******' -h10.2.0.7 -P3306 -e "create database work1;use work1;source work1.sql;" mysql -uroot -p'******' -h10.2.0.7 -P3306 -e "create database work2;use work2;source work2.sql;"
After the completion of the guide, if more rigorous, to see if all the tables are imported, the rest will wait until the next step to start configuring.
Configuring a multi-source replication environment
Most of the following operations are executed in slave libraries. The main libraries are authorized at most. If the master-slave architecture has been done, it is generally authorized long ago, and can be used directly.
If you have a master-slave replication environment, you should know that the old mode is to locate pos, and the new mode is to set the number of GTID. As for how to make sure, just take a look at it.
#Look at the following sql backup file more work1.sql . . . -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758, 4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001284', MASTER_LOG_POS=3954096; . . .
I don't intend to analyze it in detail here. If you are interested, you can see another article about building a common master-slave architecture, which contains detailed analysis. Now you can see the gtid and pos values here, and we can use them later.
Then, get to the main topic:
First, we need to change the slave library configuration file and add something.
#To add configuration items from the library's my.cnf configuration file, you need to restart the mysql instance vim my.cnf [mysqld] master_info_repository=TABLE relay_log_info_repository=TABLE replicate_wild_do_table=work1.% replicate_wild_do_table=work2.% read-only slave_parallel_workers = 4 #The first two settings can be implemented online to change master-slave configuration information to record to tables SET GLOBAL master_info_repository = 'TABLE'; SET GLOBAL relay_log_info_repository = 'TABLE';
Particular attention should be paid to the parameter replicate_wild_do_table, which means that only synchronization statements of one library or table are executed, and other libraries and tables are not processed, so as to achieve the purpose of selective replication of business libraries, which will not have useless data or interfere with each other. Official document parsing can only label a library with one parameter, and this parameter is global, that is to say, under multi-source replication, all source channels are generic to this configuration. Open read-only read-only, do not need to say more, multi-source replication is generally only read requirements, do not let them have the possibility of writing libraries. Concurrent replication slave_parallel_workers is a new function of 5.6 and 5.7, which can effectively speed up the replication efficiency. Especially 5.7 supports transaction concurrent replication with considerable speed. There are four concurrent threads for each replication connection.
--------------------------------------------------------------------
Then, in the account authorized to replicate in the main library, the same kind of account authorized can be ignored.
#In the main repository authorization, what has been done can be ignored mysql -uroot -p'******' -h10.2.0.5 -P3306 grant replication slave on *.* to 'rep'@'%' identified by '123123'； mysql -uroot -p'******' -h10.2.0.6 -P3306 grant replication slave on *.* to 'rep'@'%' identified by '123123'；
---------------------------------------------------------------------
Okay, start officially configuring multi-source replication, this GTID mode is actually more complex than the traditional pos mode, but the future is more GTID, so I will take the GTID mode as the main demonstration.
#Log in to slave database mysql -uroot -p'******' -h10.2.0.7 -P3306 #Clear the records of all master-slave structures first reset slave all； #Configure Aliyun's database, which is GTID mode change master to master_host='10.2.0.5', master_user='rep', master_password='123123', master_port=3306, MASTER_AUTO_POSITION = 1 FOR CHANNEL 'al_RDS'; #Configure the self-built database, which is GTID mode change master to master_host='10.2.0.6', master_user='rep', master_password='123123', master_port=3306, MASTER_AUTO_POSITION = 1 FOR CHANNEL 'me_mysql'; #Reset GTID value reset master； #Setting GTID values SET @@GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-486646,3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758, 4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008';
Two points that need special attention are that multi-source replication provides channel identification and distinguishes different source channels, so it is necessary to add the specified channel name for CHANNEL'al_RDS'when configuring. The GTID value is the same as the replicate_wild_do_table parameter. By default, the global configuration lets the source channel take all the required values. Therefore, our gtid value should be a combination of the gtid values of all *. sql files, separated by a',', and eventually I have to set so many GTIDs.
Then all the configurations are completed, you can start, start and close can specify a specific source channel, quite convenient, the following list of commands.
#Start/close all source channels start/stop slave； #Start/close a single source channel start/stop slave for channel 'al_RDS'; #Of course, reset can also be a separate channel. #RESET SLAVE FOR CHANNEL 'al_RDS'; #View status can also be separate, not specified is all. #show slave status for channel 'al_RDS';
Then, look at the status, show slave status.
#Executing commands from libraries mysql -uroot -p'******' -h10.2.0.7 -P3306 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.2.0.5 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001297 Read_Master_Log_Pos: 5607291 Relay_Log_File: beifen1-relay-bin-al_rds.000030 Relay_Log_Pos: 5607464 Relay_Master_Log_File: mysql-bin.001297 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: work1.%,work2.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 5607291 Relay_Log_Space: 5607767 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: 2721321239 Master_UUID: 4cdc2a74-6299-11e6-95ce-008cfaf595bc 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: 4cdc2a74-6299-11e6-95ce-008cfaf595bc:38888940-39258544 Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645, 1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8, 3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758, 4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: al_rds Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.2.0.6 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000013 Read_Master_Log_Pos: 246854093 Relay_Log_File: beifen1-relay-bin-me_mysql.000004 Relay_Log_Pos: 155502415 Relay_Master_Log_File: mysql-bin.000013 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: work1.%,work2.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 246854093 Relay_Log_Space: 155502632 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: 253241 Master_UUID: 817498dc-2676-11e7-a673-00163e024674 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: 09cb91bf-2669-11e7-8b70-00163e0835ff:514003-640645 Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-640645, 1db4cb1b-5e00-11e7-89eb-00163e046b4a:1-8, 3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758, 4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-39258544 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: me_mysql Master_TLS_Version: 2 rows in set (0.00 sec)
Very long, we see two records, Replicate_Wild_Do_Table and Executed_Gtid_Set, which are universal and have both sides, confirming that the parameters I mentioned are global. Then their Retrieved_Gtid_Set s are different, they all choose by themselves, and they are quite intelligent. Channel_Name is their different channel names. Other pairs of yes and binlog, relaylog will not go into details, do know what to see, now synchronized.
--------------------------------------------------------------------------
If it's a traditional mode, it's simpler. You don't need to set the GTID value. Similarly, you can specify the log file name and pos as follows. You can start without setting SET@@GLOBAL.GTID_PURGED.
#Setting up Channel 1 change master to master_host='10.2.0.5', master_user='rep', master_password='123123', master_port=3306, MASTER_LOG_FILE='mysql-bin.001284', MASTER_LOG_POS=3954096 FOR CHANNEL 'al_RDS'; #Setting up Channel 2 change master to master_host='10.2.0.6', master_user='rep', master_password='123123', master_port=3306, MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=67456 FOR CHANNEL 'me_mysql'; #start-up start slave；
--------------------------------------------------------------------------