mysql master-master-replication 1

In contrast to a master-slave replication in a master-master replication changes made on one server are immediately available on both.

The setup of a master-master-replication is similar to a master-slave-replication. I show here only the differences (for the basics see mysql master-slave-replication).

In the following to old master remains as master, the old slave moves to master2.

Change on both systems /etc/my.cnf:

master:

replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-connect-retry = 60
report-host = master.mydomain.de


important is
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

auto-increment-offset and server-id must always be different.
i use report-host only due to a better finding out the server when using phpmyadmin.

master2:

replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-connect-retry = 60
report-host = master2.mydomain.de


auto-increment-offset and server-id must always be different.

By now all changes to the configs are made. To take effect, you must restart both mysql-server.

On master2 there is no user for the server master:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user00'@'%' IDENTIFIED BY '';
FLUSH PRIVILEGES;


Now the master is able to login to master2 as slave_user00 with the password some_password.

master2 is already connected to master (if you´re running master-slave at this moment). You can verify this in mysql:
show slave status\G

To see the master status, use
SHOW MASTER STATUS\G


mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000158
Position: 423588499

At last the master must setup to use the other master:

CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='slave_user00', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='db2-bin.000158', MASTER_LOG_POS=423588499;
START SLAVE;


Use show slave status\G to monitor the replication.