Demonstrate the process of building a multi-master from MySQL 5.7

Here's a demonstration of the process of building a multi-master from MySQL 5.7:

Lab Environment:

master_1:192.168.10.128

master_2:192.168.10.129

slave_3:192.168.10.130

First, the database that needs to be synchronized is exported on master_1 and master_2 respectively:

In master_1:

[Email protected]_1 mysql]# mysqldump-uroot-p123456--master-data=2--single-transaction--databases-- Add-drop-database Xuanzhi >xuanzhi.sql

In master_2:

[Email protected]_2 mysql]# mysqldump-uroot-p123456--master-data=2--single-transaction--databases-- Add-drop-database xuanzhi_2 >xuanzhi_2.sql

Put the backup SCP on the slave separately:

[Email protected]_1 mysql]# scp-p22 xuanzhi.sql 192.168.10.130:/data/service/mysql/

[Email protected]_2 mysql]# scp-p22 xuanzhi_2.sql 192.168.10.130:/data/service/mysql/

Second, create the copy account on master_1 and master_2, this operation is the same as the previous version of MySQL 5.7:

In 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)

In 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)

Three, respectively slave on the master_1 and master_2 data into the slave server, before importing the way to modify the MySQL storage master-info and Relay-info, that is, from file storage to table storage, add the following options in MY.CNF:

Master_info_repository=table

Relay_log_info_repository=table

can also be modified online, gray often convenient:

<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)

<Slave> [(None)]>

For more detailed analysis, refer to: http://dev.mysql.com/doc/refman/5.7/en/slave-logs.html

The following data is imported:

[Email protected] mysql]# mysql-uroot-p <./xuanzhi.sql

[Email protected] mysql]# mysql-uroot-p123456 <./xuanzhi_2.sql

Find the Binlog location and POS location for master_1 and master_2, respectively:

[email protected] mysql]# cat xuanzhi.sql |grep "Change MASTER"

--Change MASTER to master_log_file= ' master_1-bin.000001 ', master_log_pos=1539;

[email protected] mysql]# cat xuanzhi_2.sql |grep "Change MASTER"

--Change MASTER to master_log_file= ' master_2-bin.000003 ', master_log_pos=630;

[Email protected] mysql]#

Iv. login slave for synchronous operation, change master to two master servers, followed by for CHANNEL ' channel_name ' distinction

<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)

To start the slave operation, you can start all replication by starting slave, or you can start a single replication source by starting a single copy source, which is the same as stopping it:

<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)

After normal startup, you can view the status of the synchronization: Execute Show SLAVE status for CHANNEL ' Channel_name ' \g

To view the synchronization status of a replication source 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 have 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)

To view the synchronization status of a replication source 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 have 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)

You can also view the synchronization status by viewing the Performance_schema related table, executing the command: SELECT * from Performance_schema.replication_connection_status; Monitor replication status.

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

| Channel_name | Group_name | Source_uuid | thread_id | Service_state | Count_received_heartbeats | Last_heartbeat_timestamp | Received_transaction_set | Last_error_number | Last_error_message | Last_error_timestamp |

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

|            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)

<Slave> [(None)]>

V. Verifying that data is synchronized

Insert two data on master_1:

<Master_1>[xuanzhi]> INSERT into TB1 (name) VALUES (' User1 '), (' User2 ');

Query OK, 2 rows affected (0.01 sec)

Insert two data on master_2:

<Master_2> [xuanzhi_2]> INSERT into TB2 (name) VALUES (' User3 '), (' User4 ');

Query OK, 2 rows affected (0.04 sec)

Go back to slave to see if the data is in sync:

<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)

Successful implementation of multi-master one from the environment to build

Demonstrate the process of building a multi-master from MySQL 5.7