演示一下在MySQL 5.7下搭建多主一从的过程

首页 > 代码库 > 演示一下在MySQL 5.7下搭建多主一从的过程

演示一下在MySQL 5.7下搭建多主一从的过程

2024-08-15 03:12:42 210人阅读

下面演示一下在MySQL 5.7下搭建多主一从的过程：

实验环境：

Master_1: 192.168.10.128

Master_2: 192.168.10.129

Slave_3: 192.168.10.130

一、分别在Master_1和Master_2上导出需要同步的数据库：

在Master_1：

[root@Master_1 mysql]# mysqldump -uroot -p123456 --master-data=http://www.mamicode.com/2 --single-transaction --databases --add-drop-database xuanzhi >xuanzhi.sql

在Master_2：

[root@Master_2 mysql]# mysqldump -uroot -p123456 --master-data=http://www.mamicode.com/2 --single-transaction --databases --add-drop-database xuanzhi_2 >xuanzhi_2.sql

把分别把备份scp到Slave上：

[root@Master_1 mysql]# scp -P22 xuanzhi.sql 192.168.10.130:/data/service/mysql/

[root@Master_2 mysql]# scp -P22 xuanzhi_2.sql 192.168.10.130:/data/service/mysql/

二、在Master_1和Master_2上创建复制账号，这个操作跟MySQL 5.7之前版本一样：

在Master_1：

<Master_1>[(none)]> grant replication slave on *.* to ‘repl‘@‘192.168.10.130‘ identified by ‘123456‘;

Query OK, 0 rows affected, 1 warning (0.00 sec)

在Master_2:

<Master_2> [(none)]> grant replication slave on *.* to ‘repl‘@‘192.168.10.130‘ identified by ‘123456‘;

Query OK, 0 rows affected, 1 warning (0.02 sec)

三、分别Slave上把Master_1和Master_2的数据导入Slave服务器，在导入前先修改MySQL存储master-info和relay-info的方式，即从文件存储改为表存储，在my.cnf里添加以下选择：

master_info_repository=TABLE

relay_log_info_repository=TABLE

也可以在线修改，灰常方便：

<Slave> [(none)]> stop slave;

Query OK, 0 rows affected (0.02 sec)

<Slave> [(none)]> SET GLOBAL master_info_repository = ‘TABLE‘;

Query OK, 0 rows affected (0.00 sec)

<Slave> [(none)]> SET GLOBAL relay_log_info_repository = ‘TABLE‘;

Query OK, 0 rows affected (0.00 sec)

更多的详细解析可以参考：http://dev.mysql.com/doc/refman/5.7/en/slave-logs.html

下面进行数据导入：

[root@Slave mysql]# mysql -uroot -p <./xuanzhi.sql

[root@Slave mysql]# mysql -uroot -p123456 <./xuanzhi_2.sql

分别找出Master_1和Master_2的binlog位置和Pos位置：

[root@Slave mysql]# cat xuanzhi.sql |grep " CHANGE MASTER"

-- CHANGE MASTER TO MASTER_LOG_FILE=‘Master_1-bin.000001‘, MASTER_LOG_POS=1539;

[root@Slave mysql]# cat xuanzhi_2.sql |grep " CHANGE MASTER"

-- CHANGE MASTER TO MASTER_LOG_FILE=‘Master_2-bin.000003‘, MASTER_LOG_POS=630;

[root@Slave mysql]#

四、登录Slave进行同步操作，分别change master到两台Master服务器，后面以FOR CHANNEL ‘CHANNEL_NAME‘区分

<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.10.128‘,MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘Master_1-bin.000001‘,MASTER_LOG_POS=1539 FOR CHANNEL ‘Master_1‘;

Query OK, 0 rows affected, 2 warnings (0.05 sec)

<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.10.129‘,MASTER_USER=‘repl‘, MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘Master_2-bin.000003‘,MASTER_LOG_POS=630 FOR CHANNEL ‘Master_2‘;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

进行启动slave操作，可以通过start slave的方式去启动所有的复制，也可以通过启动单个复制源的方式，下面进行单个复制源的启动进行演示(停止也是一样)：

<Slave> [(none)]> start slave for CHANNEL ‘Master_1‘;

Query OK, 0 rows affected (0.01 sec)

<Slave> [(none)]> start slave for CHANNEL ‘Master_2‘;

Query OK, 0 rows affected (0.02 sec)

正常启动后，可以查看同步的状态：执行SHOW SLAVE STATUS FOR CHANNEL ‘channel_name‘\G

查看复制源Master_1的同步状态：

<Slave> [(none)]> SHOW SLAVE STATUS FOR CHANNEL ‘Master_1‘\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Master_1-bin.000001

Read_Master_Log_Pos: 1987

Relay_Log_File: localhost-relay-bin-master_1.000002

Relay_Log_Pos: 771

Relay_Master_Log_File: Master_1-bin.000001

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: 1987

Relay_Log_Space: 991

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: 100128

Master_UUID: 44b653d4-8843-11e5-b97e-000c29dfaaf7

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:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: master_1

Master_TLS_Version:

1 row in set (0.00 sec)

查看复制源Master_2的同步状态：

<Slave> [(none)]> SHOW SLAVE STATUS FOR CHANNEL ‘Master_2‘\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.129

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: Master_2-bin.000003

Read_Master_Log_Pos: 1078

Relay_Log_File: localhost-relay-bin-master_2.000002

Relay_Log_Pos: 771

Relay_Master_Log_File: Master_2-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: 1078

Relay_Log_Space: 991

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: 100129

Master_UUID: 583f5433-43ef-11e5-8958-000c29d5bdfa

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:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name: master_2

Master_TLS_Version:

1 row in set (0.00 sec)

也可以通过查看performance_schema相关的表查看同步状态，执行命令：SELECT * FROM performance_schema.replication_connection_status; 监控复制状态。

+--------------+------------+--------------------------------------+-----------+---------------+---------------------------+--------------------------+--------------------------+-------------------+--------------------+----------------------+

| master_1 | | 44b653d4-8843-11e5-b97e-000c29dfaaf7 | 34 | ON | 184 | 2015-08-14 08:06:10 | | 0 | | 0000-00-00 00:00:00 |

| master_2 | | 583f5433-43ef-11e5-8958-000c29d5bdfa | 36 | ON | 183 | 2015-08-14 08:06:24 | | 0 | | 0000-00-00 00:00:00 |

2 rows in set (0.00 sec)

五、验证数据是否同步

在Master_1上插入两条数据：

<Master_1>[xuanzhi]> insert into tb1(name) values (‘user1‘),(‘user2‘);

Query OK, 2 rows affected (0.01 sec)

在Master_2上插入两条数据：

<Master_2> [xuanzhi_2]> insert into tb2(name) values (‘user3‘),(‘user4‘);

Query OK, 2 rows affected (0.04 sec)

回到Slave上查看数据是否正常把数据同步过来了：

<Slave> [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| xuanzhi |

| xuanzhi_2 |

+--------------------+

6 rows in set (0.03 sec)

<Slave> [(none)]> select * from xuanzhi.tb1;

+----+-------+

| id | name |

+----+-------+

| 1 | user1 |

| 2 | user2 |

+----+-------+

2 rows in set (0.00 sec)

<Slave> [(none)]> select * from xuanzhi_2.tb2;

+----+-------+

| id | name |

+----+-------+

| 1 | user3 |

| 2 | user4 |

+----+-------+

2 rows in set (0.00 sec)

成功的实现了多主一从的环境搭建

演示一下在MySQL 5.7下搭建多主一从的过程

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > 演示一下在MySQL 5.7下搭建多主一从的过程

演示一下在MySQL 5.7下搭建多主一从的过程

看完仍有疑问？有类似问题直接问程序猿