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;
Nice post. I learn something new and challenging on sites I stumbleupon everyday. Its always interesting to read content from other writers and practice a little something from their sites.
Everything is very open with a clear description of the issues. It was definitely informative. Your site is extremely helpful. Thanks for sharing!
Everything is very open with a really clear description of the issues. It was definitely informative. Your site is useful. Thank you for sharing!