MARIADB Multi-source replication

Recently do slow SQL optimization, compare headaches, see slow SQL is full of some select count (*) or sum of aggregate statistics such as SQL, because my database is the traditional master-slave, or a master more from, such slow SQL directly in the database statistics, there is a certain impact on the line, And because of multiple DB master-slave, statistics some related data is also relatively troublesome, so decided to set up a dedicated to the operation or aggregate statistical operation of the multi-master library, the collection of online data, easy to query, can also reduce the impact of these statistics on the online library!

Before setting up, let's talk about the previous official MySQL version only supports one master to a slave copy, while MARIADB has supported multiple master replication to a slave starting from 10. MARIADB Multi-source (master) Replication scenarios: 1 Now many companies do not want to divide the database, the sub-table in the same instance is good to say (but in this case, compared to the partition table, the table does not have much advantage of it), if the divided table split into different instances, we often need to do some summary data statistics, There is no middleware or a certain program code is difficult to do at once, or want to do two different db on the line of the Kua instance join, such operations, if there is a multi-master library, there is a complete data, it will be much easier! 2) Backup, if you want a complete on-line data backup, backup multi-master library is a good choice!

Build the Environment:

192.168.190.128 master128 (Master 1)

192.168.190.129 master129 (Master 2)

192.168.190.130 slave (from)

Step one, build the main steps relative to the traditional master-slave No special dozen differences, but the command will be different. I here because master128 and master129 are all have data, so take backup recovery, the first two master data summarized to from the library: 1.master128 data volume is large, take xtrabackup backup way, 2. master129 uses Mydump--master-data-b dbname1,dbname2 ... >all0414.sql this backup method

Step two, the use of Innobackupex to restore the maser128 data to slave, the specific recovery steps here do not say, using Mysql-u-p < All0414.sql recovery method will master129 data back to slave, 129 here access to the database, you need to add a separate!

Step Three,

Change MASTER ' master128 ' to master_host= ' 192.168.190.128 ', master_user= ' rep_user ', master_password= ' 123456 ', master_ port=3306, master_log_file= ' mysql-bin.000015 ', master_log_pos=335;

Change MASTER ' master129 ' to master_host= ' 192.168.190.129 ', master_user= ' rep_user ', master_password= ' 123456 ', master_ port=3306, master_log_file= ' mysql-bin.000008 ', master_log_pos=486;

Step four: Start slave ' master128 ';    Start slave ' master129 '; Show all slaves status\g;

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/90/6A/wKiom1jwaKeSeQcWAACPOO0DQN8012.png-wh_500x0-wm_ 3-wmp_4-s_3606378028.png "style=" Float:none; "title=" QQ picture 20170414141303.png "alt=" Wkiom1jwakeseqcwaacpoo0dqn8012.png-wh_50 "/>

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/90/69/wKioL1jwaKfiejowAACPh8ZyP_o913.png-wh_500x0-wm_ 3-wmp_4-s_3572148034.png "style=" Float:none; "title=" QQ picture 201704141413211.png "alt=" Wkiol1jwakfiejowaacph8zyp_ O913.png-wh_50 "/>

Step five, you can see that the synchronization is normal, we insert data validation synchronization in different DB names, for the same DB name, also can be normal synchronization, if you want to ignore synchronization, we need to specify a different master128.replicate_ignore_db=mysql Or Master129.replicate_ignore_db=mysql, if not established, the direct designation Replicate_ignore_db=mysql default mysql is ignored!

command is not the same place:

Stop all slaves, or stop slave ' maser128 ', stop slave ' maser129 '

Reset slave ' maser128 ' all; Reset slave ' maser129 ' all

Show slave ' master128 ' status\g show slave ' master129 ' status\g

Show all slaves status\g;

Other different places reference the official document: https://mariadb.com/kb/zh-cn/multi-source-replication/#todo

MARIADB Multi-source replication