How to Configure MySQL Master-Master Replication

Step 1: Install MySQL Server

Install MySQL Server on both machines.

# apt-get update
# apt-get install mysql-server mysql-client

Step 2: Configure MySQL Servers

Edit the /etc/mysql/my.cnf file on both machines. Add or modify the following values:

Server 1:

# vi /etc/mysql/my.cnf
        server_id           = 1
        log_bin             = /var/log/mysql/mysql-bin.log
        log_bin_index       = /var/log/mysql/mysql-bin.log.index
        relay_log           = /var/log/mysql/mysql-relay-bin
        relay_log_index     = /var/log/mysql/mysql-relay-bin.index
        expire_logs_days    = 10
        max_binlog_size     = 100M
        log_slave_updates   = 1
        auto-increment-increment = 2
        auto-increment-offset = 1

Server 2:

# vi /etc/mysql/my.cnf
        server_id           = 2
        log_bin             = /var/log/mysql/mysql-bin.log
        log_bin_index       = /var/log/mysql/mysql-bin.log.index
        relay_log           = /var/log/mysql/mysql-relay-bin
        relay_log_index     = /var/log/mysql/mysql-relay-bin.index
        expire_logs_days    = 10
        max_binlog_size     = 100M
        log_slave_updates   = 1
        auto-increment-increment = 2
        auto-increment-offset = 2

Step 3: Modify my.cnf file with bind-address

Edit or add bind-address in my.cnf

# vi /etc/mysql/my.cnf    	
        bind-address    = x.x.x.x

Step 4: Restart MySQL Server

After inserting the above entries restart mysql server.

# service mysqld restart

Step 5: Create Replication Users

Connect with mysql server as root

# mysql -u root -p

Configure the replication users on each Linode. Replace x.x.x.x with the private IP address of the opposing Linode, and password with a strong password:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'x.x.x.x' IDENTIFIED BY 'password';

Run the following command to test the configuration. Use the private IP address of the opposing Linode:

 mysql -ureplication -p -h x.x.x.x -P 3306

Step 6: Configure Database Replication

Server 1:

While logged into MySQL on Server 1, query the master status:

mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      120 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

Server 2:

On Server 2 at the MySQL prompt, set up the slave functionality for that database. Replace x.x.x.x with the private IP from the first server. Also replace the value for master_log_file with the file value from the previous step, and the value for master_log_pos with the position value.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=106;
mysql> SLAVE START;

Step 7: Configure Database Replication Vice Versa

Server 2:

On Server 2, query the master status. Again note the file and position values.

mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000001 |      160 |              |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

Server 1:

Set the slave database status on Server 1, replacing the same values swapped in step 2 with those from the Server 2.

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='replication', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=160;
mysql> SLAVE START;

Step 8: Create Database

Test by creating a database and inserting a row:

Server 1:

mysql> create database test;
mysql> create table test.books (`id` varchar(10));

Server 2:

mysql> show tables in test;