Dual Master Replication With MySQL 5

December 29, 2010 Database, Linux Tips & Tutorials

This document describes how to set up master-master replication with MySQL 5 on Fedora 8. Since version 5, MySQL comes with built-in support for master-master replication, solving the problem that can happen with self-generated keys. In former MySQL versions, the problem with master-master replication was that conflicts arose immediately if node A and node B both inserted an auto-incrementing key on the same table. The advantages of master-master replication over the traditional master-slave replication are that you don’t have to modify your applications to make write accesses only to the master, and that it is easier to provide high-availability because if the master fails, you still have the other master.
This HowTo is a practical guide without any warranty – it doesn’t cover the theoretical backgrounds. There are many ways to set up such a system – this is the way I chose.

1. Preparation

For this howto I set up two Fedora 8 systems (minimal installation without gui etc.) with the following configuration.

1.1 System 1

Hostname: server1.example.com
IP: 192.168.0.100

1.2 System 2

Hostname: server2.example.com
IP: 192.168.0.200

2. MySQL

2.1 RequiredPackages On Both Systems

If you haven’t installed MySQL on both systems you can install it (client & server) via:

yum -y install mysql mysql-server

2.2 MySQL Server Initial Start On Both Systems

Start the MySQL server.

/etc/init.d/mysqld start

2.3 MySQL Root Password

2.3.1 Both Systems

Set a password for the MySQL root-user on localhost.

mysqladmin -u root password %sql_root_password%

2.3.2 System 1

Set a password for the MySQL root-user on server1.example.com.

mysqladmin -u root -h server1.example.com password %mysql_root_password%

2.3.3 System 2

Set a password for the MySQL root-user on server2.example.com.

mysqladmin -u root -h server2.example.com password %mysql_root_password%

2.4 MySQL Replication User

2.4.1 System 1

Create the replication user that System 2 will use to access the MySQL database on System 1.

GRANT REPLICATION SLAVE ON *.* TO ‘slave2_user’@’%’ IDENTIFIED BY ‘%mysql_slaveuser_password%’;
FLUSH PRIVILEGES;
quit;

2.4.2 System 2

Create the replication user that System 1 will use to access the MySQL database on System 2.

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO ‘slave1_user’@’%’ IDENTIFIED BY ‘%mysql_slaveuser_password%’;
FLUSH PRIVILEGES;
quit;

2.5 Database On System 2

I proceed on the assumption that the database exampledb is already existing on System 1 – containing tables with records. So we have to create an empty database with the same name as the existing database on System 1.

mysql -u root -p
CREATE DATABASE exampledb;
quit;

3 Replication

3.1 Firewall Configuration On Both Systems

Disable firewall on both servers or open ports 3306 in firewall

3.2 Log Directory On Both Systems

In order that the MySQL server is able to create log-files we have to create a directory and pass the ownership to MySQL.

mkdir /var/log/mysql/
chown mysql:mysql /var/log/mysql/

3.3 MySQL Configuration

In the next two steps we adjust the MySQL configuration on both systems for master-master replication.

3.3.1 System 1

vi /etc/my.cnf
Add the following lines to the section [mysqld]:
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.0.200
master-user = slave1_user
master-password = %mysql_slaveuser_password%
master-connect-retry = 60
replicate-do-db = exampledb
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
Afterwards restart the MySQL server.
/etc/init.d/mysqld restart

3.3.2 System 2

vi /etc/my.cnf
Add the following lines to the section [mysqld]:
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 192.168.0.100
master-user = slave2_user
master-password = %mysql_slaveuser_password%
master-connect-retry = 60
replicate-do-db = exampledb
log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
Afterwards restart the MySQL server.
/etc/init.d/mysqld restart

3.4 Export MySQL Dump On System 1

Now we create a dump of the existing database and transfer it to system 2.

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The output should look like this. Note down the file and the position – you’ll need both later.
+——————+———-+———————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————+——————+
| mysql-bin.000004 | 98 | exampledb,exampledb | |
+——————+———-+———————+——————+
1 row in set (0.00 sec)
Open a second terminal for system 1, create the dump and transfer it to system 2. Don’t leave the MySQL-shell at this point – otherwise you’ll loose the read-lock.

cd /tmp/
mysqldump -u root -p%mysql_root_password% –opt exampledb > sqldump.sql
scp sqldump.sql [email protected]:/tmp/
Afterwards close the second terminal and switch back to the first. Remove the read-lock and leave the MySQL-shell.
UNLOCK TABLES;
quit;

3.5 Import MySQL Dump On System 2

Time to import the database dump on system 2.

mysqladmin –user=root –password=%mysql_root_password% stop-slave
cd /tmp/
mysql -u root -p%mysql_root_password% exampledb < sqldump.sql

3.6 System 2 As Master

Now we need information about the master status on system 2.

mysql -u root -p
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

UNLOCK TABLES;

At this point we’re ready to become the master for system 1. Replace %mysql_slaveuser_password% with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down at step 3.4!

CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave2_user’, MASTER_PASSWORD=’%mysql_slaveuser_password%’, MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=98;
Now start the slave …
START SLAVE;

… and take a look at the slave status. It’s very important that both, Slave_IO_Running and Slave_SQL_Running are set to Yes. If they’re not, something went wrong and you should take a look at the logs.

SHOW SLAVE STATUS;

3.7 System 1 As Master

Open a MySQL-shell on system 1 …

mysql -u root -p
… and stop the slave.
STOP SLAVE;

At this point we’re ready to become the master for system 2. Replace %mysql_slaveuser_password% with the password you choose and be sure that you replace the values for MASTER_LOG_FILE and MASTER_LOG_POS with the values that you noted down at step 3.6!

CHANGE MASTER TO MASTER_HOST=’192.168.0.200′, MASTER_USER=’slave1_user’, MASTER_PASSWORD=’%mysql_slaveuser_password%’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=958;
Now start the slave …
START SLAVE;

SHOW SLAVE STATUS;

One Response to Dual Master Replication With MySQL 5

Nikhil tripathi says:

December 29, 2010 at 12:34 pm

the content is very helpful .
thanks

MyLinuxTips