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

This article mainly introduces the implementation steps of MySQL multi-source replication configuration. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. The following is to understand the implementation steps of MySQL multi-source replication configuration with the author.

The significance of multi-source replication

1. You can summarize the databases of multiple CVMs on a slave database, or summarize the sub-databases and tables of a database.

two。 Intensive use of the hardware resources of slave CVM, after all, it is uneconomical to take up the entire CVM resources when the volume of database business is small.

3. It is more convenient to back up the data of each business database and optimize the script writing logic of database backup.

Topological graph

Implementation steps

1. Backup the data on the main database. Considering the problems of gtid, it is recommended that only mysqldump program be used for backup.

Centos:#mysqldump-login-path=3306\ # mysql official tools all support login-path login-single-transaction\ # transaction consistency backup to prevent backup integrity from being compromised by transaction consistency-master-data=2\ # add a change master to pos comment statement to the backup file header-A > ip06p3306.sql # backup all databases

two。 Transfer the exported file to the CVM of the slave library

Centos_ip06:#scp ip06p3306.sql root@192.168.1.11:/root/ centos_ip31:#scp ip31p3306.sql root@192.168.1.11:/root/

3. Import the backup file of the master library to the slave library

Centos:#mysql-- login-path=3306\-- f reset master; mysql > set global gtid_purged='GTID_ip06,GTID_ip31'

6. Point the slave libraries to their respective master libraries

Mysql > change master to\ master_host='192.168.1.6',master_port=3306,\ master_user='repl',master_password='123456',\ master_auto_postion=1\ automatically align GTID. After restarting from the downtime of the library, delete the unexecuted relaylog and re-pull for channel 'master_3306'; to set a channel name for this master, which can be set to an easily recognizable name.

7. Set libraries that are not convenient for synchronization: (for example, mysql libraries that cause duplicate users to be copied)

However, sys,performance,information exists as a schema or view, and the data is generated immediately when it is read. It does not really store the data, and can not be filtered.

Mysql > stop slave sql_thread;# stops the replay process of the slave library, which is more convenient than stopping the entire slave library mysql > change replication filter\ replicate_ignore_db= (mysql,db1);\ # put the copied library in parentheses and separate mysql > start slave sql_thread with

8. View information:

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.6 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin. 000016 Read_Master_Log_Pos: 194 Relay_Log_File: localhost-relay-bin-master_3306.000033 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000016 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: 803 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: 13306 Master_UUID: 449fdd93-1b80-11e7-b054-000c29aa1d65 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: 066c3194-2b4b-11e7-b67b-000c290b6ef0:1-2951823, 449fdd93-1b80-11e7-b054-000c29aa1d65:1-2951823 Efa1d12e-3c56-11e7-a99e-000c29ba498d:1-481Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master_3306 Master_TLS_Version: * * 2.row * * Slave_IO _ State: Waiting for master to send event Master_Host: 192.168.1.31 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000026 Read_Master_Log_Pos: 375018221 Relay_Log_File: localhost-relay-bin-master_zabbix.000032 Relay_Log_Pos: 375018394 Relay_Master_Log_File: mysql-bin.000026 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: 375018221 Relay_Log_Space: 375018619 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: 313306 Master_UUID: 066c3194-2b4bmur11e7- B67b-000c290b6ef0 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: 066c3194-2b4b-11e7-b67b-000c290b6ef0:2310554-2951823 Executed_Gtid_Set: 066c3194-2b4b-11e7-b67b-000c290b6ef0:1-2951823, 449fdd93-1b80-11e7-b054-000c29aa1d65:1-2951823 Efa1d12e-3c56-11e7-a99e-000c29ba498d:1-481Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: master_zabbix Master_TLS_Version: 2 rows in set (0.01sec)

After reading the above MySQL multi-source replication configuration implementation steps, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.