mysql5.7如何实现多主一从的多源复制-创新互联

这篇文章主要介绍了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 status\G

*************************** 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如何实现多主一从的多源复制”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联-成都网站建设公司行业资讯频道，更多相关知识等着你来学习!

本文名称：mysql5.7如何实现多主一从的多源复制-创新互联
网页地址：https://www.cdcxhl.com/article28/ehicp.html

成都网站建设公司_创新互联，为您提供网站策划、响应式网站、用户体验、动态网站、品牌网站制作、搜索引擎优化

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容