Shulou(Shulou.com)06/01 Report--

MySQL version 5.7 supports multi-source replication, while MySQL 5.5 and 5.6 do not.

Multi-source replication allows multiple master nodes to replicate to a slave node in parallel. A slave creates a replication channel for each master.

At least two master libraries and one slave library are required.

The master library in multi-source replication can be configured for replication based on global transaction standards (GTID) or based on binary logs.

IP planning

Main library 01192.168.174.201

Main library 02192.168.174.202

From the library 192.168.174.203

1. In multi-source replicated slave libraries, table-based repositories is required, which is incompatible with file-based repositories.

Operate on the slave library

You can add the following parameters to the parameter file

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Master_info_repository

Determines whether the slave log containing master status and connection information exists in file format (master.info) or table format (mysql.slave_master_info).

You can change the value of this parameter when no copy thread executes.

This parameter also has a direct impact on the sync_master_info system parameters.

Relay_log_info_repository

This parameter determines the location of the relay log slave node written to the file (relay-log.info) or table (mysql.slave_relay_log_info). The value of this parameter can be modified only if there is no copy thread to execute.

This parameter is used to store information about the relay log. The default is the file format, and the default name of the file is relay-log.info.

If it is in TABLE format, the log information is written to mysql.slave_relay_log_info.

To modify dynamically, use the following command

STOP SLAVE

SET GLOBAL master_info_repository = 'TABLE'

STOP SLAVE

SET GLOBAL relay_log_info_repository = 'TABLE'

Mysql > show global variables like'% repositor%'

+-+ +

| | Variable_name | Value |

+-+ +

| | master_info_repository | FILE |

| | relay_log_info_repository | FILE |

+-+ +

2 rows in set (0.03 sec)

Mysql > SET GLOBAL master_info_repository = 'TABLE'

Query OK, 0 rows affected (0.00 sec)

Mysql > SET GLOBAL relay_log_info_repository = 'TABLE'

ERROR 1766 (HY000): Unknown error 1766

Mysql > stop slave

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

Mysql > show global variables like'% repositor%'

+-+ +

| | Variable_name | Value |

+-+ +

| | master_info_repository | TABLE |

| | relay_log_info_repository | TABLE |

+-+ +

2 rows in set (0.01sec)

Edit other configuration files from the library

[root@localhost 5505] # vim / mysql_data/cnf/my.cnf

# Log

Server-id = 300

Log-bin = / mysql_log/binlog/mysql-bin

Relay-log = / mysql_log/binlog/product-relay-bin

Relay-log-index = / mysql_log/binlog/product-relay-index

Then restart the database to make the parameters effective

two。 Modify main library 1 and main library 2 parameter files, create replication users, create test data

Edit the configuration file of the main library, note that the server-id of each library cannot be the same

[root@localhost install] # vim / etc/my.cnf

# Log

Server-id = 100

Log-bin = / log/binlog/mysql-bin

Then restart the database to make the parameters effective

Create replication user

Mysql > grant replication slave on *. * to 'repl'@'192.168.174.%' identified by' repl'

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

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.17-11-log |

+-+

1 row in set (0.00 sec)

Create test data

Main Library 1

Mysql > create database sale

Query OK, 1 row affected (0.06 sec)

Mysql > use sale

Database changed

Mysql > create table sale_record (id int)

Query OK, 0 rows affected (0.16 sec)

Mysql > insert into sale_record values (10), (20)

Query OK, 2 rows affected (.95 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Main Library 2

Mysql > use market

Database changed

Mysql > create table market_record (id int)

->

Query OK, 0 rows affected (0.04 sec)

Mysql > insert into market_record values

Query OK, 2 rows affected (0.25 sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

3. Back up main Library 1 and Master Library 2

Main Library 1

[root@MySQL01 mysql_software_57] # bin/mysqldump-uroot-paired root'-h 127.0.0.1-Q-- single-transaction-- master-data=2-B sale > / tmp/20171211_sale.sql

Main Library 2

[root@localhost mysql_software_57] # bin/mysqldump-uroot-paired root'-S / mysql_data_57/mysql.sock-Q-- single-transaction-- master-data=2-B market > / tmp/20171211_market.sql

Copy the backup files on master library 1 and master library 2 to the slave library for recovery

Operate on the slave library

[root@MySQL03 mysql_software_57] # bin/mysql-uroot-paired root'-h 127.0.0.1

< /tmp/20171211_sale.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@MySQL03 mysql_software_57]# bin/mysql -uroot -p'root' -h 127.0.0.1 < /tmp/20171211_market.sql mysql: [Warning] Using a password on the command line interface can be insecure. 4. 搭建多源复制 搭建到主库1的复制，将通道起名为master-1 mysql>

Change master to

-> master_host='192.168.174.201'

-> master_port=3306

-> master_user='repl'

-> master_password='repl'

-> master_log_file='mysql-bin.000017'

-> master_log_pos=1209

-> FOR CHANNEL 'master-1'

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

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 192.168.174.201

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 1209

Relay_Log_File: mysqld-relay-bin-master@002d1.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: No

Slave_SQL_Running: No

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

Relay_Log_Space: 154

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

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

Master_UUID:

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

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)

Start the IO and SQL threads of channel master-1

Mysql > START SLAVE IO_THREAD FOR CHANNEL 'master-1'

Query OK, 0 rows affected (0.00 sec)

Mysql > START SLAVE SQL_THREAD FOR CHANNEL 'master-1'

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.174.201

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 1209

Relay_Log_File: mysqld-relay-bin-master@002d1.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000017

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

Relay_Log_Space: 541

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: 203fe772-177e-11e7-b15c-000c296b3b20

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)

Start the IO thread and SQL thread of channel master-2

Mysql > change master to

-> master_host='192.168.174.202'

-> master_port=3306

-> master_user='repl'

-> master_password='repl'

-> master_log_file='mysql-bin.000014'

-> master_log_pos=1239

-> FOR CHANNEL 'master-2'

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

Mysql > START SLAVE IO_THREAD FOR CHANNEL 'master-2'

Query OK, 0 rows affected (0.00 sec)

Mysql > START SLAVE SQL_THREAD FOR CHANNEL 'master-2'

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.174.201

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 1481

Relay_Log_File: mysqld-relay-bin-master@002d1.000002

Relay_Log_Pos: 592

Relay_Master_Log_File: mysql-bin.000017

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

Relay_Log_Space: 813

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: 203fe772-177e-11e7-b15c-000c296b3b20

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:

* 2. Row * *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.174.202

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000014

Read_Master_Log_Pos: 1239

Relay_Log_File: mysqld-relay-bin-master@002d2.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000014

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

Relay_Log_Space: 541

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: 2efd664c-177f-11e7-8323-000c29fcf2cd

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:

2 rows in set (0.01sec)

Conduct a test

Insert data on main library 1

Mysql > insert into sale_record values (30), (40), (50)

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Insert data on main library 2

Mysql > insert into market_record values (300,400),

Query OK, 3 rows affected (0.01sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.01 sec)

Query above the slave library

Mysql > select * from sale.sale_record

+-+

| | id |

+-+

| | 10 |

| | 20 |

| | 30 |

| | 40 |

| | 50 |

+-+

5 rows in set (0.00 sec)

Mysql > select * from market.market_record

+-+

| | id |

+-+

| | 100 |

| | 200 |

| | 300 |

| | 400 |

| | 500 |

+-+

5 rows in set (0.00 sec)

Common command

Start multi-source replication

Start the IO thread for all configured replication channels

Mysql > start slave io_thread

Query OK, 0 rows affected (0.01 sec)

Start the SQL thread for all configured replication channels

Mysql > start slave sql_thread

Query OK, 0 rows affected (0.01 sec)

Start the specified replication channel

Mysql > start slave io_thread for channel 'master-1'

Query OK, 0 rows affected (0.00 sec)

Stop multi-source replication

Stop the IO thread of all configured replication channels

Mysql > stop slave io_thread

Query OK, 0 rows affected (0.01 sec)

Stop the SQL thread of all configured replication channels

Mysql > stop slave sql_thread

Query OK, 0 rows affected (0.00 sec)

Stop the specified replication channel

Mysql > stop slave sql_thread for channel 'master-1'

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

Reset the slave library for multi-source replication

Reset all configured replication channels

RESET SLAVE

Reset the specified channel

RESET SLAVE FOR CHANNEL 'master-1'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.