In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article focuses on "the method of multi-source replication of MySQL5.7". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "the method of multi-source replication of MySQL5.7".
5.7 Multisource replication 1.1 Lab Summary 1.1.1 Lab hypothesis
This lab assumes that the operating system and MySQL installation and deployment have been completed.
1.1.2 Experimental purpose
MySQL5.7 's multi-source replication technology is deployed and then simply tested.
1.1.3 Environmental Information
Operating system
MySQL version
Server address
Server role
Centos7
5.7.18
192.168.102.23
Source 1
Centos7
5.7.18
192.168.102.24
Source 2
Centos7
5.7.18
192.168.102.25
Target
1.1.4 Experimental planning
Server address
Server role
MySQL library
Account
192.168.102.23
Source 1
Emily
Repl23
192.168.102.24
Source 2
Evelyn
Repl24
192.168.102.25
Target
Source 1.emily-- > target
Source 2.evelyn-- > target
Username:
Source 1:repl23
Source 2:repl24
1.2 Lab Operation 1.2.1 Source backup Database
Back up the emily and evelyn libraries from source1 and source2, respectively, and then copy them to target. The steps are as follows:
Source 1:
# # backup
[root@dsm-db-102023 11:26:50 / root]
# mysqldump-uroot-pmysql-- single-transaction-- master-data=2-- databases emily > / root/dump/emily.sql
# # Transmission
[root@dsm-db-102023 11:34:38 / root/dump]
# scp / root/dump/emily.sql root@192.168.102.25:/root/dump/
Source 2:
# # backup
[root@test-mysql-10224 11:19:47 / root]
# mysqldump-uroot-pmysql-- single-transaction-- master-data=2-- databases evelyn > / root/dump/evelyn.sql
# # Transmission
[root@dsm-db-102023 11:34:38 / root/dump]
# scp / root/dump/emily.sql root@192.168.102.25:/root/dump/
1.2.2 create a synchronization account
Create synchronous accounts repl23 and repl24 in source1 and source2 respectively
Source1
Root@192.168.102.23:3306 [emily] > create user repl23 identified by "repl"
Root@192.168.102.23:3306 [(none)] > grant replication slave on *. * to 'repl23'@'%'
Source2
Root@192.168.102.24:3306 [evelyn] > create user repl24 identified by "repl"
Root@192.168.102.24:3306 [evelyn] > grant replication slave on *. * to 'repl24'@'%'
1.2.3 the target side recovers the database
# # restoring evelyn Library
Root@192.168.102.25:3306 [(none)] > reset master
[root@dsm-db-102025 14:01:24 / root/dump]
# mysql-uroot-pmysql
< evelyn.sql ## 查看gtid_purged root@192.168.102.25:3306 [(none)]>Show global variables like'% gtid_purged%'
+-- +
| | Variable_name | Value |
+-- +
| | gtid_purged | 921a9068-24d2-11e7-99b5-005056b59593:1-287 |
Bd783f44-258f-11e7-914b-005056b5d312:1-28071 |
+-- +
# # restoring emily Library
Root@192.168.102.25:3306 [(none)] > reset master
[root@dsm-db-102025 14:21:22 / root/dump]
# mysql-uroot-pmysql
< /root/dump/emily.sql; ##设置source1和source2的gtid_purged root@192.168.102.25:3306 [(none)]>Show variables like'% gtid_purged%'
+-- +
| | Variable_name | Value |
+-- +
| | gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4 | |
+-- +
# # set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287 author bd783f44-258f-11e7-914b-005056b5d312:1-28071 Magi 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4'
Root@192.168.102.25:3306 [(none)] > reset master
Query OK, 0 rows affected (0.01 sec)
Root@192.168.102.25:3306 [(none)] > set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287author bd783f44-258f-11e7-914b-005056b5d312:1-28071 mc7937ac78-3c39-11e7-b59e-005056b5d25f:1-4'
Query OK, 0 rows affected (0.00 sec)
Root@192.168.102.25:3306 [(none)] > show variables like'% gtid_purged%'
+- -- +
| | Variable_name | Value |
+- -- +
| | gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4 |
921a9068-24d2-11e7-99b5-005056b59593:1-287
Bd783f44-258f-11e7-914b-005056b5d312:1-28071 |
+- -- +
1 row in set (0.01 sec)
Root@192.168.102.25:3306 [(none)] >
1.2.4 modify MySQL storage mode
Modify the way MySQL stores master-info and relay-info, that is, from file storage to table storage
# # online Modification
STOP SLAVE
SET GLOBAL master_info_repository = 'TABLE'
SET GLOBAL relay_log_info_repository = 'TABLE'
# # modifying configuration File
[mysqld]
Master_info_repository=TABLE
Relay_log_info_repository=TABLE
1.2.5 synchronous operation
? Change master
Log in to slave to synchronize, and change master to two master hosts respectively. Multi-source replication needs to be marked.
FOR CHANNEL 'CHANNEL_NAME' distinction
# # source 1
Root@192.168.102.25:3306 [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.102.23',MASTER_USER='repl23', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl23'
# # source 2
Root@192.168.102.25:3306 [(none)] > CHANGE MASTER TO MASTER_HOST='192.168.102.24',MASTER_USER='repl24', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl24'
? Start slave
Start all synchronizations: start slave
Start a single synchronization: start slave for channel 'channel_name'
# # starting source 1
Root@192.168.102.25:3306 [(none)] > start slave for channel 'repl23'
# # starting source 2
Root@192.168.102.25:3306 [(none)] > start slave for channel 'repl24'
? Check slave status
Check all slave: show slave status\ G
Check a single slave: show slave status for chennel 'channel_name'\ G
# # source 1
# # source 1
Root@192.168.102.23:3306 [emily] > insert into emily (id,name) values
Query OK, 1 row affected (0.00 sec)
Root@192.168.102.23:3306 [emily] > select * from emily
+-+ +
| | id | name |
+-+ +
| | 1 | emily |
| | 2 | evelyn |
+-+ +
2 rows in set (0.00 sec)
# # target Verification
Root@192.168.102.25:3306 [emily] > select * from emily
+-+ +
| | id | name |
+-+ +
| | 1 | emily |
| | 2 | evelyn |
+-+ +
2 rows in set (0.00 sec)
# # source 2
Root@192.168.102.24:3306 [evelyn] > insert into evelyn (id,name) values (2)
Query OK, 1 row affected (1.00 sec)
Root@192.168.102.24:3306 [evelyn] > select * from evelyn
+-+ +
| | id | name |
+-+ +
| | 1 | evelyn |
| | 2 | emily |
+-+ +
2 rows in set (0.00 sec)
# # target
Root@192.168.102.25:3306 [evelyn] > select * from evelyn
+-+ +
| | id | name |
+-+ +
| | 1 | evelyn |
| | 2 | emily |
+-+ +
2 rows in set (0.00 sec)
1.2.7 Monitoring
Select * from performance_schema.replication_connection_status\ G
# # GTID
STOP SLAVE FOR CHANNEL 'CHANNEL_NAME'
SET SESSION GTID_NEXT=''
BEGIN;COMMIT
SET SESSION GTID_NEXT='AUTOMATIC'
START SLAVE FOR CHANNEL 'CHANNEL_NAME'
# # binlog+position
Stop slave sql_thread FOR CHANNEL 'CHANNEL_NAME'
Set global sql_slave_skip_counter=1
Start slave sql_thread FOR CHANNEL 'CHANNEL_NAME'
# #
Root@192.168.102.25:3306 [(none)] > set session gtid_next='bd783f44-258f-11e7-914bMur005056b5d312VO83'
Query OK, 0 rows affected (0.00 sec)
Root@192.168.102.25:3306 [(none)] > begin;commit
Query OK, 0 rows affected (0.00 sec)
Root@192.168.102.25:3306 [(none)] > set session gtid_next=automatic
Query OK, 0 rows affected (0.00 sec)
Root@192.168.102.25:3306 [(none)] > start slave for channel 'repl24'
Query OK, 0 rows affected (0.00 sec)
1.2.9 encounter an error
1 、 Last_IO_Error: Relay log write failure: could not queue event from master # # repl24
Last_IO_Error: Fatal error: Failed to run 'after_read_event' hook # # repl23
Stop slave
Start slave
Finally, it is found that a source is enabled for semi-synchronous replication, and a source does not open semi-synchronous replication.
2. Clear slave information
Reset slave
# # in the process of creating multi-source replication, a slave is found to have no channel_name. Use the following statement to clear the slave information.
Reset slave all for channel''
3. Root@192.168.102.24:3306 [evelyn] > uninstall plugin rpl_semi_sync_master
Query OK, 0 rows affected (0.01 sec)
Root@192.168.102.24:3306 [evelyn] > uninstall plugin rpl_semi_sync_slave
Query OK, 0 rows affected (0.00 sec)
At this point, I believe you have a deeper understanding of the "multi-source replication method of MySQL5.7". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.