MySQL multi-source replication configuration based on Gtid

The meaning of multi-source replication

1. You can summarize a database of multiple servers from a library, or a database's library sub-table.

2. Intensive use of hardware resources from the library server, after all, weak one database business volume is not the cost of consuming the entire server resources is not economical.

3. More convenient data backup for a business library, optimize the database backup script writing logic

Map of extension and complement

Implementation steps

1. Back up the data on the main library, taking into account that the Gtid problem is recommended to only use the Mysqldump program for backup

CentOS: #mysqldump--login-path=3306 \ #mysql官方工具都支持login-path fast logon--single-transaction \ #事务一致性备份 to prevent backup integrity from being corrupted by transactional consistency --master-data=2 \ #在备份文件的头部加一句change Master to POS comment statement-A >ip06p3306.sql #备份所有数据库

1. 
   

2. Transfer exported files to a server from the library

CENTOS_IP06: #scp ip06p3306.sql [email protected]:/root/centos_ip31: #scp ip31p3306.sql [Email protected]:/root/

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

CentOS: #mysql--login-path=3306-f <ip06p3306.sql #-f parameter, ignoring gtid conflicts that occur when you apply a backup

4. From the head of two copies of the backup file to view the Gtid that were performed when the two primary libraries were backed up

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/98/4A/wKioL1k5h_LwIdGGAAB2R1HTbRU977.png "title=" Qq20170609002414.png "width=" "height=" 144 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:600PX;HEIGHT:144PX; "alt = "Wkiol1k5h_lwidggaab2r1htbru977.png"/>

CentOS: #less ip06p3306.sql|grep purged #得到GTID_ip06 centos: #less ip31p3306.sql|grep purged #得到GTID_ip31

5. Reset the Gtid from the library (executed from the library)

Mysql>reset master; Mysql>set global gtid_purged= ' gtid_ip06,gtid_ip31 ';

6. Point to the respective main library from the library

Mysql>change master to \ master_host= ' 192.168.1.6 ', master_port=3306,        Master_user= ' Repl ', master_password= ' 123456 ', \ master_auto_postion=1 \ auto-align Gtid, delete not executed relaylog after reboot from library outage, re-pull For channel ' master_3306 '; set a channel name for this master, which can be set to an easily recognizable name

7. Set up libraries that are not easy to sync: (such as MySQL library that causes duplicate users to be duplicated)

But the sys,performance,information is the same as the pattern or view, the data is generated instantly when read, does not really store the data, can not filter

Mysql>stop slave sql_thread; #停止从库的重放进程, much more convenient than stopping the whole library from the Mysql>change Replication filter replicate_ignore_db= (mysq L,DB1); \ #把复制的库放在括号里, use, separate mysql>start slave sql_thread;

8. View the 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   &nbSp 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-140,                           efa1d12e-3c56-11e7-a99e-000c29ba498d:1-481    auto_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   &nbsP 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 n      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-2b4b-11e7-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-140,                             efa1d12e-3c56-11e7-a99e-000c29ba498d:1-481     Auto_Position: 1    Replicate_Rewrite_DB:      channel_name: master_zabbix    master_tls_version:            2 rows in set  (0.01 SEC)

This article is from "Long SQL Road ..." Blog, be sure to keep this source http://l0vesql.blog.51cto.com/4159433/1933651

MySQL multi-source replication configuration based on Gtid