How to set up MySQL multi-master replication

Update

In fact, this article is mainly from www.digitalocean.com, but I did not buy their home VPS for demo. It's just vagrant a simulation.

Introduction

Talk about using two VPS to extend MySQL. Before we talked about at 2 VPS directly through nginx to set load balancer, you can read it

The so-called Mysql replication is a real-time copy of a single data set to another server. This configuration 主从复制 is called. This is a typical operation configuration. We'll have a better plan to do it next. We 多主(主-主)复制 all have the ability to allow data to be copied to other servers, but the main difference is the ability to improve a single server 读写性能 . This setting adds data redundancy, but improves the performance of the data we read.

Suppose we have the following configuration on 2 VPS. We call it 服务器 C and 服务器 D .

• Server C 3.3.3.3
• Server D 4.4.4.4

The first step is to install and configure MySQL on the C machine

First, we want to install and on the mysql-server mysql-client server, through the following command:

sudo apt-get install mysql-server mysql-client

MySQL defaults to only accept local (127.0.0.1) connections, we need to modify some of the default configuration to implement this, we need to modify to / etc/mysql/my.cnf see the following content:

#server-id              = 1#log_bin                =/var/log/mysql/mysql-bin.log#binlog_do_db           = Include_database_ Namebind-address            = 127.0.0.1

The first line is used to indicate the current server, in our mysql 复制 configuration, we need this flag, we have to uncomment it. The second line describes the current bin log directory. The third line describes the current need to synchronize that 数据库 . Of course you can specify a lot of databases, But here, we need one. The last line shows where we allow the client to connect. We don't need a local connection. Comments.

Server-id               = 1log_bin                 =/var/log/mysql/mysql-bin.logbinlog_do_db            = example# bind-address            = 127.0.0.1

To restart the server:

sudo service MySQL restart

Next, we need mysql 命令行 to modify some of the settings,

Mysql-uroot-p

After successful landing,

Mysql>

We need to create a user to copy the data, we call him replicator , password to pick up.

Create user ' replicator ' @ '% ' identified by ' password ';

Then give him permission to copy the data.

Grant replication slave on to *.* ' replicator '% ';

This user is not able to copy all libraries, it can only replicate the database specified in the configuration file.

Next, we need to get some state of the current MySQL server, which will be used in the later server D settings. Basically, the settings for this server have been completed.

Show master status;

It will output some of the following information:

+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      107 | Example      |                  | +------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

Step Two, install Configuration server D

The first steps are the same, just follow the MySQL server and the client, and then modify the configuration file:

/etc/mysql/my.cnf

This time, it is a bit different from the C server, we will change it to the following content:

Server-id              = 2log_bin                =/var/log/mysql/mysql-bin.logbinlog_do_db           = example# bind-address            = 127.0.0.1

After restarting the server, log in to create the user.

sudo service MySQL restart
Mysql-u root-p
Create user ' replicator ' @ '% ' identified by ' password ';

Next, you need to create a database in D here example , the one you want to synchronize.

Create database example;

This user right is then used to replicate:

Grant replication slave on to *.* ' replicator '% ';

Here is the most critical step. Connect the two servers through the information we get on the C server.

Or on the D server mysqlclient, type the following command:

Slave Stop;change master to Master_host = ' 3.3.3.3 ', Master_user = ' Replicator ', Master_password = ' password ',  master_ log_file = ' mysql-bin.000001 ', master_log_pos = 107; Slave start; here about ' master_log_file ' and ' master_log_pos ', they are the information from the status output on the C server. Now D is hanging on C, we need to turn C to D, and look at D's ' m Aster status ':

Show Master Status

+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000004 |      107 | Example      |                  | +------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

step Three, complete the copy on C

In fact, the above settings are the same:

Be careful to change your MySQL output and your password.

The above command MySQL will output the following similar information:

Query OK, 0 rows affected (0.01 sec)

Here, the configuration is complete.

Test

Let's do this, create a table on C, then kill it on D and see if there is any on C.

Create database example;
CREATE TABLE Example.dummy ( id varchar); #note here was ' not '

And then, on D, see if there's a table:

Show tables in example;

You will see:

+-------------------+| Tables_in_example |+-------------------+| Dummy             |+-------------------+1 row in Set (0.00 sec)

Then on D:

drop table dummy;

C on:

Show tables;

You will see:

Empty Set (0.00 sec)

See more

Http://qiita.com/xiangzhuyuan/items/b366eb5463d7b56e1aaa

How to set up MySQL multi-master replication