mysql 5.7如何达成多主一从的多源复制
发布时间：2021-12-18 14:00:47 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了mysql 5.7如何实现多主一从的多源复制，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 主服务器创建用户 主1 多源同步参数 #multi replication master_info_repositor
|
这篇文章主要介绍了mysql 5.7如何实现多主一从的多源复制，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 主服务器创建用户 主1 多源同步参数 #multi replication master_info_repository=TABLE relay_log_info_repository=TABLE 开启GTID [mysqld] #GTID parameter log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true report-port=3306 report-host=192.168.56.212 master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=10 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 [mysql@ray ~]$ /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/3306/soket/mysql.sock mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) 主2 多源同步参数 #multi replication master_info_repository=TABLE relay_log_info_repository=TABLE 开启GTID [mysqld] #GTID parameter log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true report-port=3307 report-host=192.168.56.212 master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=10 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 [mysql@ray ~]$ /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/3307/soket/mysql.sock mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) 从服务器连接主服务器 多源同步参数 #multi replication master_info_repository=TABLE relay_log_info_repository=TABLE 开启GTID [mysqld] #GTID parameter log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true report-port=3308 report-host=192.168.56.212 master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=10 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 连接主1： change master to master_host='192.168.56.212', master_user='rep', master_password='123456', master_port=3306, master_auto_position = 1 for channel 'm1'; 连接主2 change master to master_host='192.168.56.212', master_user='rep', master_password='123456', master_port=3307, master_auto_position = 1 for channel 'm2'; 启动slave进程 mysql> start slave; 查看复印进程 mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.212 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: ray-bin.000005 Read_Master_Log_Pos: 194 Relay_Log_File: localhost-relay-bin-m1.000004 Relay_Log_Pos: 403 Relay_Master_Log_File: ray-bin.000005 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: 194 Relay_Log_Space: 1260 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: 1 Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224 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: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2 Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2, 97e8847a-ffdf-11e6-87ed-08002736c224:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: m1 Master_TLS_Version: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.212 Master_User: rep Master_Port: 3307 Connect_Retry: 60 Master_Log_File: ray-bin.000003 Read_Master_Log_Pos: 194 Relay_Log_File: localhost-relay-bin-m2.000005 Relay_Log_Pos: 403 Relay_Master_Log_File: ray-bin.000003 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: 194 Relay_Log_Space: 1083 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: 517e04ac-ffe3-11e6-a7ea-08002736c224 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: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2 Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2, 97e8847a-ffdf-11e6-87ed-08002736c224:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: m2 Master_TLS_Version: 2 rows in set (0.00 sec) 启动和停止单独slave进程 mysql> start slave for channel 'm1'; mysql> stop slave for channel 'm1'; mariaDB: CHANGE MASTER 'm1' TO MASTER_HOST='192.168.56.91', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='ray-bin.000007', MASTER_LOG_POS=396; mysql: CHANGE MASTER TO MASTER_HOST='192.168.56.91', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='ray-bin.000007', MASTER_LOG_POS=396 for channel="m1"; 感谢你能够认真阅读完这篇文章，希望小编分享的“mysql 5.7如何实现多主一从的多源复制”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习! （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
