Setting up MySQL Master-Master replication

Prepare mysql setup

After installation, depending on distribution, find my.cnf and under mysqld add (or uncomment) :

Server 1:

[mysqld]
server_id           = 1
log_bin             = /var/lib/mysql/mysql-bin.log
log_bin_index       = /var/lib/mysql/mysql-bin.log.index
relay_log           = /var/lib/mysql/mysql-relay-bin
relay_log_index     = /var/lib/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:

[mysqld]
server_id           = 2
log_bin             = /var/lib/mysql/mysql-bin.log
log_bin_index       = /var/lib/mysql/mysql-bin.log.index
relay_log           = /var/lib/mysql/mysql-relay-bin
relay_log_index     = /var/lib/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

Update bind-address with your local servers ip:

bind-address    = x.x.x.x

Create user which is going to be used on slave (adding slave IP):

 
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'x.x.x.x' IDENTIFIED BY 'password';

Configure MySQL Master-Master replication

Server 1:

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

Server 2:

 
STOP SLAVE;
CHANGE MASTER TO master_host='x.x.x.x', master_port=3306, master_user='repl', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=277;
START SLAVE;
</pre>

And then get master info from server 2:

 
SHOW MASTER STATUS;

Server 1: And use in on server 1

 
STOP SLAVE;
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=231;
START SLAVE;