MySQL – Multi Master Replication

There are two common types of replication in MySQL, that is Multi Master and Master-Slave replications.

For Master-Slave replication, the Slave will act as the backup MySQL server which only keeps on updated by referring to the data passed from the Master. This slave server can also act as the READONLY server which is usefull for those user who just wish to generate reports from the data instead of pumping in data.

For Multi-Master replication, both MySQL server will replicate data to each other. Both server are available online for data entry. Both servers allow application to write data into its database and will replicate the data to across other master server vice versa.

On this post, I will refer to Multi Master instead of Master and Slave configuration. Configuring the Multi Master replication on MySQL is kinda simple if you understand on how it works and what will be the problem which is commonly faced. There are main problem found on the Multi-Master settings that is:

1. Duplicate records due to indentical primary key which uses auto increment.

This common problem can be addressed if you configure the server properly. Configuring Multi Master on Windows or Linux are the same. Below are some of the hint you may refer on the Multi Master MySQL server replication:

Install MySQL Server

You may install the MySQL server either fromt the repository or source provided by the mysql provider at http://www.mysql.com.

If you are using CENTOS/REDHAT, you may use the “yum” command to install the MySQL from the repository. To ease your work or make sure there is none of the required component left out during the installation, use the wildcards. For example:

yum install “mysql*”

If you are a Window users, I believe you cannot use yum. All you need to do is just download the installation source from http://www.mysql.com.

Setting Up Master Server

Once the MySQL server is being installed. It can runs with default settings but will be an independent MySQL server which do not have master enabled. In order to enable the master on the installed MySQL server do the following steps:

• Create a mysql user with replication privileges only.
• Stop all MySQL server
• Update the MySQL server configuration file with some additional settings.
• Repeat the above steps for the rest of the master server
• Start all MySQL server

Create a mysql user with replication privileges:

Execute the following SQL command to create a new mysql user which will be used by other mysql server to connect to the server as slave. For example, if the username is “repluser” and password is “slavepass”:

GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@'%’ IDENTIFIED BY ’slavepass’;

Stop all MySQL server:

For Linux, to stop the MySQL server, you may execute the following command:

service mysqld stop

For Windows, execute the services management console and stop the mysql instances.

Update the MySQL server configuration:

Add the following line into the MySQL server configuration. If the settings already exists, update it with this setting:

server-id=1

log-bin

log-slave-updates

replicate-same-server-id=0


auto_increment_increment=10

auto_increment_offset=1

master-host=another master node IP

master-user=repluser

master-password=slavepass

master-port=3306

report-host=server-name

skip-slave-start

Important:

1. Make sure there will be no clashes on the server id. Server id must be unique for each MySQL master in the same network.
2. Both auto_increment_increment and auto_increment_offset settings are used to make sure there is no clash on the auto increment on the msater server data.
3. replicate-same-server-id should be turned off by set it to 0 value. This is to avoid the master server replicates its own data which passes by other MySQL master which will cause infinite loop or data consistencies issue.
4. Each master need to be slave to another master server in order to have the updated data on other mysql server. This is set under master-host setting. If there is only 2 MySQL server ( A and B), then the settings will be Server A >> Server B >> Server A.
5. log-bin is set enables the master server to log the query that executed on itself by the web application or administrator.
6. log-slave-updates enables the master to log all the query passed from other MySQL master server. This is important if the network is having more than 2 Master server.
7. All slave should not be started automatically. This is due to of all the master is not started, the slave will throw error as it is not able to connect to the master.

Start all MySQL server

For Linux, to start the MySQL server, you may execute the following command:

service mysqld start

For Windows, execute the services management console and start the mysql instances.

Once all the settings being done and the Master server is started. The next step will be start all the slave service. To start the slave, login into Master Server and run the following command:

> mysql

mysql> slave start;