MARIADB Multi-master replication configuration document

• mariadb Introduction

MARIADB is a branch versionof MySQL, MARIADB's biggest feature is to replace MyISAM 's aria engine and multi-master replication capabilities, and so on. In this document, we mainly introduce multi-master replication.

• Multi-master replication (multi-master ) function Introduction

Multi-master replication is the synchronization of multiple master to a single slave via replication . As shown below:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/75/79/wKioL1Y58mCiSI3TAAFBm6IBaFc351.jpg "title=" bb.png "alt=" Wkiol1y58mcisi3taafbm6ibafc351.jpg "/>

The Main Library can be either MySQL or mariadb, since thelibrary must be mariadb, because only mariadb has the multi-master replication feature. The principle is that mariadb enables one IO thread and one SQL thread for each of the main libraries . Multiple Master is parallel, with no interference from each other.

• Purpose of multi-master replication

as the company's research and development team to do some statistical work, it is necessary to A and B libraries are joins, but the A and B libraries are on different servers, and if you want to do this, you must import the tables of the two libraries to a single server Join, but this is time-consuming and laborious, and it needs to be re-directed when the next calculation is done. So mariadb Multi-master replication can solve this problem, while synchronizing a library and B Library to a server, and real-time synchronization, so join up more convenient. MARIADB can also be used later if there are data aggregation requirements like this .

• Issues needing attention in multi-master replication

Although multi-master replication does this kind of data aggregation work is very appropriate, but you need to be aware that if more than one master is synchronized, it is possible to cause mariadb io pressure is too large, Because it's going to operate on multiple libraries. So we try to synchronize only the useful tables, and the unwanted tables are filtered out in the config file.

• Installing mariadb

• Download the https://downloads.mariadb.org/mariadb/10.0.2/ binary package from the official

• Installation

Cd/usr/local

Tar zxvf mariadb-10.0.2-linux-x86_64.tar.gz

Ln-s mariadb-10.0.2-linux-x86_64 MySQL

CD MySQL

Groupadd MySQL

Useradd-g mysq MySQL

./scripts/mysql_install_db User=mysql

Same as normal mysql installation process

• Start /etc/init.d/mysqld Start

• Synchronous

• Synchronizing libraries

In fact, synchronization is very simple and normal MySQL is the same, but multi-master replication needs to set the channel, to distinguish which master

For example, now I want to synchronize three sets of databases,user,party, and friend, then we have to start a channel name, channel name is also called user, Party ,friend. Everyone notice that the channel name is not the library name.

Grammar:

[email protected] @default_master_connection = ' Channel name ';

for our example to complete the user,the party and friend synchronization will use the following actions:

[email protected] @default_master_connection = ' user ';

Changemaster .....

Slavestart;

[email protected] @default_master_connection = ' Party ';

Changemaster .....

Slavestart;

[email protected] @default_master_connection = ' friend ';

Changemaster .....

Slavestart;

since the library name of the three groups is called Yuanfenquan, the three master will be able to synchronize the data to the LOVE21CN library of mariadb , but the table cannot have duplicate names.

to see the sync status for all master, you can use:show allslaves status

• Synchronizing partial tables

The method for table filtering and MySQL is the same, if I want to synchronize only the User_action and user_detail tables in the user table first . Then we can make the following configuration in the mariadb my.cnf:

user.replicate_wild_do_table=Yuanfenquan. user_action

user.replicate_wild_do_table=Yuanfenquan. User_detail

The format is: Channel name . replicate_wild_do_table= Library name . Table name

Remember that multiple tables also write multiple lines, do not lazy write =Yuanfenquan. User_action, Yuanfenquan. User_detail

• Library name rewrite

For example, now there is such a demand, we have just synchronized the user,Party,friend Group database, and now also need to synchronize the Mission group Mission.mission_account table, but unlike before, the requirement wants to synchronize the table to the mariadb Yuanfenquan Library, which is

The mission.mission_account of the Misson group ------->mariadb Yuanfenquan. Mission_account

The advantage of synchronizing the non-library data to the same library is: convenient operation, convenient management

you need to do the setup in Mariadb 's my.cnf:

mission.replicate-rewrite-db=mission->Yuanfenquan

mission.replicate_wild_do_table=Yuanfenquan. Mission_account

For a more complete command please refer to the official manual:

https://mariadb.com/kb/en/multi-source-replication/

This article is from the "Zhangdh Open Space" blog, so be sure to keep this source http://linuxblind.blog.51cto.com/7616603/1709761

MARIADB Multi-master replication configuration document