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

MySQL multisource replication allows replication slave to receive transactions from multiple sources at the same time. You can use multi-source replication to back up multiple servers to one server, merge table fragments, and merge data from multiple servers into one server. Multi-source replication does not implement any conflict detection or resolution when applying transactions, and these tasks are left to the application if necessary. Let's configure a multi-source replication, which requires at least two masters and slaves. Server environment: 192.168.1.2 (master1) 192.168.1.3 (new master) 192.168.1.4 (slave) you can configure masters in a multi-source replication topology to use global transaction identifier (GTID)-based replication or binary log location-based replication. For information on how to use GTID-based replication to configure master, see https://blog.51cto.com/itzhoujun/2351367. For information on how to configure master using file location-based replication, see https://blog.51cto.com/itzhoujun/2352688 and https://blog.51cto.com/itzhoujun/2352693 this time we do multi-source replication based on GTID login slaves in the slave multi-source replication topology requires a TABLE-based repository. Multi-source replication is incompatible with FILE-based repositories. The types of repositories used by mysqld can be configured at startup or dynamically. To configure the type of repository that replication slave uses at startup, use the following option to start mysqld:--master-info-repository=TABLE-- relay-log-info-repository=TABLE to modify an existing replication slave that uses the FILE repository to use the TABLE repository, convert the existing replication repository dynamically by running the following command: STOP SLAVE;SET GLOBAL master_info_repository = 'TABLE';SET GLOBAL relay_log_info_repository =' TABLE' We use dynamic modification mysql > STOP SLAVE;Query OK, 0 rows affected (0.00 sec) mysql > SET GLOBAL master_info_repository = 'TABLE';Query OK, 0 rows affected (0 sec) mysql > SET GLOBAL relay_log_info_repository =' TABLE';Query OK, 0 rows affected (0 sec) mysql > show variables like 'master_info_repository' +-+-+ | Variable_name | Value | +-+-+ | master_info_repository | TABLE | +-+-+ 1 row in set (0. 01 sec) mysql > show variables like 'relay_log_info_repository' +-- +-+ | Variable_name | Value | +-- +-+ | relay_log_info_repository | TABLE | +-- + -+ 1 row in set (0.00 sec) # indicates that login new master is set successfully (repeat this step if there is more than one new master) to enable GTID-based replication Each server must be started with GTID mode enabled, so let's modify the configuration file first, and then restart the mysql parameter. I have described mysql > show variables like'% gtid%' in detail in https://blog.51cto.com/itzhoujun/2351367 and https://blog.51cto.com/itzhoujun/2352693. +-- +-- + | Variable_name | Value | +-- +-- + | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed_compression_period | 1000 | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | gtid_purged | b45f359b-1747-11e9-bee2-00163e0ea0d9:1-15 | | session_track_gtids | OFF | + -- +-+ 8 rows in set (0.00 sec) # you can see this result This means that you have successfully added a replication user (if you already have a replication user, skip this step) mysql > CREATE USER 'xiaoming'@'%' IDENTIFIED BY' Xiaoming@123456' Query OK, 0 rows affected (0.01 sec) mysql > GRANT REPLICATION SLAVE ON. TO 'xiaoming'@'%';Query OK, 0 rows affected login slave uses the CHANGE MASTER TO statement to add a new master to the channel mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.4', MASTER_USER='xiaoming', MASTER_PASSWORD='Xiaoming@123456', MASTER_AUTO_POSITION = 1 FOR CHANNEL' newmaster-1' by using the FOR CHANNEL channel clause Query OK, 0 rows affected, 2 warnings (0.03 sec) # repeat this step if you have more than one new master and change the hostname, port, and channel name according to the actual situation. After starting a multi-source replication slaves has added all the channels to be used as a replication master, use the START SLAVE thread_types statement to start the replication. After enabling multiple channels on slave, you can choose to start all channels or select a specific channel to start to start all currently configured replication channels: START SLAVE thread_types; to start only named channels, please use the FOR CHANNEL channel clause: START SLAVE thread_types FOR CHANNEL channel; We START SLAVE directly here, start all channels and then let's check the status information mysql > select * from performance_schema.replication_applier_status +-- + | CHANNEL_NAME | SERVICE_STATE | REMAINING_DELAY | COUNT_TRANSACTIONS_RETRIES | +- -+-- + | | ON | NULL | 0 | | newmaster-1 | ON | NULL | 0 | +- +-+ 2 rows in set (0.00 sec) # indicates that there are two channels Then take a look at the information of the two channels: mysql > show slave status for channel'\ G#Slave_IO_Running and Slave_SQL_Running are both YES. That means no problem. Mysql > show slave status for channel 'newmaster-1'\ G

# Slave_IO_Running and Slave_SQL_Running are both YES, so there is no problem. The next step is to operate some test data on the database in master and new master respectively, and then check whether the slave is synchronized. If the synchronization is done, there will be no problem.

Reference: https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html

PREV: 5: use GTID to copy https://blog.51cto.com/itzhoujun/2352693

NEXT: 7: advantages and disadvantages of statement-based and row-based replication https://blog.51cto.com/itzhoujun/2354045

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.