投稿
  1. 云编程开发首页
  2. 数据库

mysql 5.7如何实现多主一从的多源复制

1971916478 • • 数据库 • 阅读 88

这篇文章主要介绍了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:
Sec开发云主机域名onds_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如何实现多主一从的多源复制”这篇文章对大家有帮助，同时也希望大家多多支持开发云，关注开发云行业资讯频道，更多相关知识等着你来学习!

相关推荐: 怎么使用mysql数据库

下文给大家带来有关怎么使用mysql数据库内容，相信大家一定看过类似的文章。我们给大家带来的有何不同呢？一起来看看正文部分吧，相信看完怎么使用mysql数据库你一定会有所收获。一、数据库的基本概念1、 常用的两种引擎：(1) InnoDB引擎： 1）支持ACI…

免责声明：本站发布的图片视频文字，以转载和分享为主，文章观点不代表本站立场，本站不承担相关法律责任；如果涉及侵权请联系邮箱：360163164@qq.com举报，并提供相关证据，经查实将立刻删除涉嫌侵权内容。

赞 (0)
打赏 微信扫一扫 微信扫一扫
19719164781971916478
0 0
上一篇 06/27 15:33
下一篇 06/27 15:33

相关推荐