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

We should know that compared with other databases, the most important feature of mysql is master-slave replication, but the previous version only supported one master and multiple slaves at most. For some statistical requirements, they need to cross databases, which is a troublesome thing. In the past, database middleware (mycat, etc.) could only do this kind of thing, and backup operations could only be done one by one with scripts to poll or concurrent backup. It can be said that it takes no time and effort. Then came the concept of multi-source replication.

The concept of multi-source replication was first put forward by the mariadb community, and then mysql was actively introduced to version 5.7 (the latest version of 5.6does not have this feature). Percona did not talk about it, and then launched it. This function well solves the problems mentioned above, of course, the most direct thing is to save time and money. If concurrent replication is also enabled, the delay is almost negligible in the intranet environment.

Personally, it is suggested that multi-source replication should not be the only slave database, but it is better to do the second / third slave database or hierarchical slave database, and the main purpose should be the statistical database or backup database. Because generally speaking, in order to avoid some data conflicts, multi-source replication will ignore the replication of some databases and tables, and in order to improve the high availability of the database, it is necessary to synchronize the entire database. For example, MHA and PXC environments almost require that the master and slave are consistent, so there are conflicts that lead to high availability, so doing the second / third slave database or hierarchical slave database can be avoided.

Pre-environment description

Now the testing architecture is the work1 of master library 1 and the work2 of master library 2, which are copied from multiple sources to slave library 3, and ignore the system library mysql library, so simply I do not intend to draw a picture, and it is estimated that there are many on the Internet, because there is a lot of content behind, I do not want to occupy the page, so just forget it.

Main library 1:mysql version: Aliyun rdsmysql5.6.34,ip address: 10.2.0.5

2:mysql version of main library: self-built percona-server5.7.18,ip address: 10.2.0.6

Slave 3:mysql version: self-built percona-server5.7.18,ip address: 10.2.0.7

The purpose is to:

Main library 1---work1\

From Library 3

Main library 2---work2/

I will not talk about how to build a mysql database. You will study this point by yourselves. I think the people who want to read this article will at least have no pressure in building it. At the same time, it is also assumed that there is a database for a certain business. In fact, if there is no database, it can be simulated.

Then, take a look at the following operation.

Export and import database

Even if it is not for multi-source replication, ordinary master-slave replication environments need to export and import data. After all, binlog is not always recorded (retention policy problem), and it is not realistic for him to run again if the amount of data increases.

Since the original intention of our multi-source replication is to copy only the databases with needs, it is not appropriate to use xtrabackup, because the mysql library will be backed up by default, so it is more appropriate to use mysqldump at this time, unless the previous library uses xtrabackup and the next one uses mysqldump. I think mysqlpump and mydumper can also give it a try.

Let's take a look at the operation.

# Export database mysqldump-uroot-paired databases'- h20.2.0.5-P3306-- triggers-R-- single-transaction\-- no-autocommit-- master-data=2-Q-e-databases work1 > work1.sqlmysqldump-uroot-paired databases'- h20.2.0.6-P3306-- triggers-R-single-transaction\-- no-autocommit-- master-data=2-Q-e-databases work2 > work2.sql

There may be a warnings warning when exporting, saying that the sql set to 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, can be imported. 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 one situation I think I can try. I haven't tried it yet, that is, different data tables are copied from multiple sources with the same database name. If you are interested, you can try it yourself. I will not expand it here.

# Import the database to the library mysql-uroot-paired databases'- h20.2.0.7-P3306 > create database work1 > use work1 > source work1.sql > create database work2 > use work2 > source work2.sql# can also mysql-uroot-paired databases'- h20.2.0.7-P3306-e "create database work1;use work1;source work1.sql;" mysql-uroot-paired databases'- h20.2.0.7-P3306-e "create database work2;use work2;source work2.sql" "

After the import, if you are more rigorous, see if the data tables are all imported, and the rest will be configured in the next step.

Configure a multi-source replication environment

Most of the following operations are performed in the slave library, and the master library is authorized at most. If you have done the master-slave architecture, this is generally authorized a long time ago, and you can use it directly.

If you have matched the master-slave replication environment, you should know that the old mode is to determine the location of pos, while the new mode is to set the number of GTID. As for how to be sure, just take a look at more.

# look at the backup file more work1.sql of the following sql. . .-- GTID state at the beginning of the backup-- SET @ @ GLOBAL.GTID_PURGED='3edae34c-6299-11e6-95cd-8038bc0c67be:1-6758Make4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008Lik-Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001284', MASTER_LOG_POS=3954096;. . .

I am not going to analyze it in detail here. If you are interested, you can take a look at another article about building a common master-slave architecture, which has a detailed analysis. Now you can see the gtid and pos values here, and we can just use them later.

Then, let's get down to business:

First of all, we need to change the slave library configuration file and add something.

# to add configuration items from the my.cnf configuration file of the library, you need to restart the mysql instance vim my.cnf [mysqld] master_info_repository=TABLErelay_log_info_repository=TABLEreplicate_wild_do_table=work1.%replicate_wild_do_table=work2.%read-only#5.6 above parameters, the number of parallel replication threads slave_parallel_workers = 4q5.7 new parameters, parallel replication mode selection, 5.6. of course, you can also set it online without using # slave_parallel_type = LOGICAL_CLOCK#. The purpose is to change the master-slave configuration information to the table mysql > SET GLOBAL master_info_repository = 'TABLE' Mysql > SET GLOBAL relay_log_info_repository = 'TABLE';mysql > change replication filter REPLICATE_IGNORE_DB= (mysql)

You need to pay special attention to the parameter replicate_wild_do_table, which means that only the synchronization statements of a library or table are executed, and no other libraries and tables are processed, so as to achieve the purpose of selectively copying business libraries, so that there will be no useless data and will not interfere with each other. Official document parsing can only mark one library with one parameter, and this parameter is universal, that is, under multi-source replication, this configuration is common to all source channels.

Open read-only read-only needless to say, multi-source replication is generally only the need to read, do not let them have the possibility of writing to the library.

Concurrent replication multithreading slave_parallel_workers is a new function of 5.6Gel 5.7, which can effectively speed up the efficiency of replication, especially 5.7.It supports transaction concurrent replication with considerable speed. Here there are 4 concurrent threads for each replication connection.

Slave_parallel_type chooses concurrent replication, which is compatible with the library mode of 5.6. here, it is changed to 5.7. concurrent replication is based on group commit transaction, and the concurrent effect is better. However, it is best not to change it if the version of the master-slave server database is inconsistent, because only mysql5.7 enables group commit by default.

Then, for accounts that have been authorized to copy in the main database, those who have authorized similar accounts can be ignored.

# in the authorization of the main library, mysql-uroot-paired license'- h20.2.0.5-P3306grant replication slave on *. * to 'rep'@'%' identified by' 123123 authorization can be ignored.-h20.2.0.6-P3306grant replication slave on *. * to 'rep'@'%' identified by' 123123'

All right, let's start formally configuring multi-source replication. This GTID mode is actually a little more complicated than the traditional pos mode, but there will be more GTID in the future, so I'll take the GTID mode as the main demonstration.

# Log in to the slave database mysql-uroot-paired databases'- h20.2.0.7-P330databases to clear the records reset slave all of all master-slave structures first # configure Aliyun's database, which is the self-built database of GTID mode change master tomaster_host='10.2.0.5',master_user='rep',master_password='123123',master_port=3306,MASTER_AUTO_POSITION = 1FOR CHANNEL 'al_RDS';# configuration, and this is GTID mode change master tomaster_host='10.2.0.6',master_user='rep',master_password='123123',master_port=3306,MASTER_AUTO_POSITION = 1FOR CHANNEL' me_mysql' # reset GTID value reset master # set the GTID value SET @ @ GLOBAL.GTID_PURGED='09cb91bf-2669-11e7-8b70-00163e0835ff:1-486646 report3edae34cLic6299-11e6-95cd-8038bc0c67be:1-6758play4cdc2a74-6299-11e6-95ce-008cfaf595bc:1-38813008'

Two points to note are that multi-source replication provides the identity of channel and distinguishes different source channels, so you need to add the specified channel name FOR CHANNEL 'al_RDS'; when configuring. The value of GTID is the same as the replicate_wild_do_table parameter, and the default is the global configuration to let the source channel get what it needs. Therefore, our GTID value should be a collection of GTID values of all * .sql files, separated by the', 'sign. Finally, I have to set so many GTID.

Then all the configuration is complete, you can start, start and close can specify a specific source channel, quite convenient, the following list of commands.

# enable / disable all source channels start/stop slave;# enable / disable 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';# viewing status can also be separate, not specified is all # show slave status for channel 'al_RDS'

Then, take a look at the status, show slave status to see.

# execute the command mysql-uroot-paired slave'- h20.2.0.7-P3306mysql > show slave status\ slave * 1. Row * * Slave_IO_State: Waiting for master to send event on the slave library 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: 0Master_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 Auto_Position 1db4cb1bLY 5e00-11e7-89eb-00163e046b4a:1-8 Replicate_Rewrite_DB 3edae34c 11e6-95cd-8038bc0c67be:1-6758people 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: 0Master_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 Auto_Position 1db4cb1bLY 5e00-11e7-89eb-00163e046b4a:1-8 rows in set 3edae34cly6299-11e6-95cd-8038bc0c67be:1-6758Make4c2a74-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)

For a long time, we see that there are two records, in which Replicate_Wild_Do_Table,Executed_Gtid_Set is global and has both sides, which confirms that the parameters I am talking about are global. Then their own Retrieved_Gtid_Set is different, they all choose on their own, quite intelligent. If you see Channel_Name again, they will have different channel names. Then you can see that the double yes,Master_Log_File=Relay_Master_Log_File,Read_Master_Log_Pos=Exec_Master_Log_Pos,Seconds_Behind_Master of Slave_IO_Running and Slave_SQL_Running is 0, so it is now synchronized.

If it is the traditional mode, it is simpler. You do not need to set the value of GTID. You can specify the log file name and pos as follows, and you can start without setting SET @ @ GLOBAL.GTID_PURGED.

# set channel 1 change master tomaster_host='10.2.0.5',master_user='rep',master_password='123123',master_port=3306,MASTER_LOG_FILE='mysql-bin.001284',MASTER_LOG_POS=3954096FOR CHANNEL 'al_RDS';# set channel 2 change master tomaster_host='10.2.0.6',master_user='rep',master_password='123123',master_port=3306,MASTER_LOG_FILE='mysql-bin.000014',MASTER_LOG_POS=67456FOR CHANNEL' me_mysql' # start start slave

Question summary

1. Pay attention to the progress of parallel replication when stopping a multivariate replication environment, for example, wait a while before stopping.

# Please follow Executed_Gtid_Set: item show slave status\ G. . . 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: Reading event from the relay log 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:50007036-50049107 Executed_Gtid_Set: 09cb91bf-2669-11e7-8b70-00163e0835ff:1-47551250 3edae34clue 6299-11e6-95cd-8038bc0c67be:1-6758Mei4cc2a74-6299-11e6-95ce-008cfaf595bc:1-50010063lav 50010080-5009350010099- 50010101RV 50010108RV 5001010130-5001010139Relo 50010145-50010148Relo 50010158Rover 50010179-50010184Relo 50010190-50010200VOV 50010207Rd 50010215-50010221Rd 50010227-50010236Rd 50010243Rover 5001010276-5001010293Rue 50010308-5001010293Rd 5001010308-5001010328PRE 5001010326-500101037328 Auto_Position 50010391-5001010393Rp 50010403-50010405 50010427-50010104295Wrip 50010464-50010466Wrip 50010480-50010482Plus 500101090-50010496Writer 5001010385001010540550010101010474-5001010105Ranger 5001010427-50010104296: Channel_Name: al_rds Master_TLS_Version: * * 2.row * * Slave_IO_State: Waiting for master to send event. . .

That's because as soon as you stop like this, parallel replication stops halfway, and some data may not be rolled back, or some data replication errors may occur, and then you are more likely to report an error if you want to get him up later, so you'd rather wait and stop.

Of course, if it's an online environment, when do we have to wait? So it's best to do it when the database is not busy. Or you're ready to do it again, so come on.

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.