MARIADB 10 Multi-source Replication (multi-source replication) Business usage scenario analysis, and how to use it

mariadb 10 Multi-source Replication (multi-source replication) Business usage scenario analysis, and how to use it


Official MySQL a slave can only correspond to one MASTER,MARIADB 10 start supporting multi-source replication, and a slave can have multiple master, replicating different db from their master.


This feature can be used in the OLAP environment, the traditional e-commerce db is dismantled and disassembled, sub-database sub-table, sharding, and OLAP environment or big data platform environment, usually need a variety of data aggregation, multiple platforms multiple DB data composite query, and this data scattered in the various libraries, how to do, Of course, the application can achieve aggregation, but there is no easier way, such as directly to the library on each server, the Table field Association. MySQL does not have Oracle's Dblink function, while MySQL has federated engine, but not very reliable, and trouble, how unpredictable performance.


At this point, the benefits of MARIADB's multi-source replication capabilities are reflected. The db of multiple servers is copied directly to the same slave, all the libraries have, and how to associate them.


Other usage scenarios:


Two independent systems, through the interface, such as RESTful API or SOAP, interface data transfer, often there are inconsistencies between the communication data on both sides of the scene, how to check the data. In addition to check the interface log, you can use multi-source replication, directly write SQL Association Two Library Communication data, in the end is the sender to pass the data or the recipient received the data there is a problem, it is obvious.
DBAs can count on the same DB server without having to check each server separately.
Backup is convenient, centralized on this multi-source replication slave backup, not to the various servers to replicate.
Large data pumping, concentrated on a single db draw on the platform, do not scatter to each DB server to draw, of course, more than a few, Io can't carry, on SSD or PCIe card bar.






The following talk about the use of methods, it is recommended to see MARIADB 10 multi-source copy official documents, https://mariadb.com/kb/en/mariadb/mariadb-documentation/ replication-cluster-multi-master/replication/multi-source-replication/




Traditional replication mode to multi-source replication:


Stop slave, show slave status View stopped location
Reset slave All;
Change master ' conn_mall ' to master_host= ' master IP ',
Master_port=3306,master_user= ', master_password= ',
Master_log_file= ' mysql-bin.000036 ', master_log_pos=189492612;
Start slave ' conn_mall ';
Show all slaves status \g or show slave ' conn_mall ' status \g
Add a new replication source:


Source-side Export mysqldump--master-data=2
Slave-side Import
Change master ' conn_erp ' to master_host= ' master IP ',
Master_port=3306,master_user= ', master_password= ',
Master_log_file= ' mysql-bin.000470 ', master_log_pos=215418341;
Start slave ' conn_erp ';
Traditional replication Mode-"command changes for multi-source Replication"


Reset slave, reset slave ' conn_erp ', multiple connection source names
Start slave-start slave ' connection_name ' or start all slaves
Show slave status, show slave ' conn_mall ' status, or show all slaves status View all slave connection states
Sql_slave_skip_couter, stop slave ' connection_name ', first specify the connection source Name set @ @default_master_connection = ' connection_name '; Then set global sql_slave_skip_counter=1; last start slave ' connection_name '
Replicate-... variables variable problem in multi-source replication environment


You can add conn connection string prefixes before my.cnf replicate_ignore_db, such as Main_connection.replicate_ignore_db=ignore_database
Without a prefix, all databases with the same name are ignored, and other variables are analogous.


Reference: http://segmentfault.com/a/1190000000607471

MARIADB 10 Multi-source Replication (multi-source replication) Business usage scenario analysis, and how to use it