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;