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

Mariadb 10 Multi-Source replication (Multi-source replication) Service usage scenario Analysis and usage

Officially, a slave can only correspond to one master,mariadb 10. A slave can have multiple master, which replicates different DB from its own master.

This feature can be used in the OLAP environment, traditional e-commerce DB is split again, sub-database sub-table, sharding, while the OLAP environment or big data platform environment, usually requires the aggregation of all kinds of data, multiple platforms and multiple DB data compound query, and these data are scattered in each library, of course, aggregation can be achieved through the application, but there is no simpler method, such as directly to the libraries on each server. To associate the table fields. Mysql does not have the function of oracle's DBLINk, while mysql has a federated engine, but it is not very reliable, and troublesome, the performance is unpredictable.

At this time, the advantage of mariadb's multi-source replication function is realized. Directly copy the db of multiple server to the same slave, all libraries have it, and you can associate it as much as you want.

Other usage scenarios:

Two independent systems transfer interface data through interfaces, such as restful api or soap, and there are often scenarios where the communication data between the two sides are inconsistent, how to check the data. In addition to checking the API log, you can use multi-source replication to directly write the communication data of the two libraries associated with sql. It is obvious whether there is a problem with the data transmitted by the sender or received by the receiver.

DBA can be counted on the same DB SERVER, instead of going to each server separately.

Backup is convenient, focus on this multi-source replication slave for backup, do not have to copy to each server.

Big data draw, concentrate on a DB platform, do not need to be scattered to each DB server to draw, of course, the number is too many, IO can not bear, on the ssd or pcie card bar.

Let's talk about how to use it. It is recommended to see the official document of mariadb 10 multi-source replication, https://mariadb.com/kb/en/mariadb/mariadb-documentation/replication-cluster-multi-master/replication/multi-source-replication/.

Change the traditional replication mode to multi-source replication:

Stop slave, show slave status to view the stop 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 to view the status of all slave connections

Sql_slave_skip_couter-> stop slave 'connection_name', first specify the connection source name set @ @ default_master_connection='connection_name';, and then set global sql_slave_skip_counter=1; the last start slave' connection_name'

Replicate-... in multi-source replication environment Variables variable problem

You can add a conn connection string prefix before my.cnf replicate_ignore_db, such as main_connection.replicate_ignore_db=ignore_database

If you do not add a prefix, you will ignore all databases with the same name, and so on.

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

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.