MySQL Master Master Replication

From ISPWiki

Minimal installation

MySQL 5.0 and later.

Install MySQL on both systems. Set up a single root password (in the future a single password will be used, as this config replicates the mysql base with the passwords). Create a special user on both servers:

grant replication slave on *.* to replication@'%' identified by 'slave';

Grant remote access permissions to the root (in case of oproxy, MySQL in your configuration will listen only the TCP-socket):

grant all on *.* to root@'%' identified by 'PASSWORD' with grant option;

In case of Debian, the same for debian-sys-maint user (see /etc/mysql/debian.cnf):

grant all on *.* to debian-sys-maint@'%' identified by 'PASSWORD';

Locate /etc/my.cnf on the first server and specify:

server-id=1

relay-log=master1-relay-bin

master-host = IP_ВТОРОЙ_МАШИНЫ
master-user = replication
master-password = slave
master-port = 3306

slave-skip-errors = all

log-bin=mysql-bin

auto_increment_increment = 2
auto_increment_offset = 1

On the second server specify:

server-id=2

relay-log=master2-relay-bin

master-host = FIRST_MACHINE_IP
master-user = replication
master-password = slave
master-port = 3306

slave-skip-errors = all

log-bin=mysql-bin

auto_increment_increment = 2
auto_increment_offset = 2

Make sure that this lines are not duplicated in the file (pay special attention to server-id and log-bin). Restart both MySQL servers. If everything goes well, you will see the following lines in the logs of both machines:

090107  8:08:39 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000013' at position 537, relay log './master2-relay-bin.000051' position: 98
090107  8:08:39 [Note] Slave I/O thread: connected to master 'replication@10.0.0.2:3306',  replication started in log 'mysql-bin.000013' at position 537

Create a base on one server. It should appear on the second one. Make sure that replication is running vice versa.