Tested on Ubuntu 14 / MySQL 5.5
We will configure mysql master-master replication on two nodes for all databases:
Node A: 1.1.1.1
Node B: 2.2.2.2
First install mysql in both nodes:
$ ~ apt-get install mysql-server mysql-client
In the node A configure this in my.cnf:
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 binlog_format = ROW slave_exec_mode = IDEMPOTENT expire_logs_days = 10 max_binlog_size = 100M auto-increment-increment = 2 auto-increment-offset = 1
And this in the node B:
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 binlog_format = ROW slave_exec_mode = IDEMPOTENT expire_logs_days = 10 max_binlog_size = 100M auto-increment-increment = 2 auto-increment-offset = 2
Explication:
server-id: the server identification, it must be different en each node
log_bin: path to the log binary file
log_bin_index: path to the log binary file index
relay_log: path to the log relay file
relay_log_index: path to the log relay file index
binlog_format: in this case is row based replication (ROW), all changes are replicated so is the safest form of replication. The disadvantage is that generates big update logs and more network traffic too.
slave_exec_mode: IDEMPOTENT is the best method for master-master replication, it causes suppression of duplicate-key and no-key-found errors.
expire_logs_days: log expiration in days
max_binlog_size: max size of binary logs
auto-increment-increment: the amount that the server will increment each time an auto incremented value is generated.
auto-increment-offset: the offset of increment. Example: auto-increment-increment=2 and auto-increment-offset, increment sequence will be 2, 4, 6 …
Restart mysql on both nodes:
$ ~ /etc/init.d/mysql restart
Create the replication user on both nodes:
mysql> CREATE USER 'repuser'@'%' identified by 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'%';
Now in node A get the master status, remember file and position:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 102 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Go to the node B and configure replication against the node A, using data before:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=102, MASTER_PORT=3306; mysql> START SLAVE;
In this node (B) get the master status, remember file and position:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 102 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
And back to the node A configure replication against node B, using data before:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='2.2.2.2', MASTER_USER='repuser', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=102, MASTER_PORT=3306; mysql> START SLAVE;
Check slave status on both nodes:
mysql> SHOW SLAVE STATUS\G