Mysql Master to Master Replication

This will setup two Amazon EC2 CentOS 5 machines to cluster replicate all their MySQL databases.

Server 1: /etc/my.sql


#replication
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=test

#information for becoming slave.
master-host =
master-user = replication
master-password = slave
master-port = 3306



Server 2: /etc/my.sql

#replication
server-id=2
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=test

#information for becoming slave.
master-host =
master-user = replication
master-password = slave
master-port = 3306


Restart /etc/init.d/mysql restart on both servers.

Then run the following SQL on...
server 1:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='10.253.15.15',MASTER_USER='replication',MASTER_PASSWORD='slave';
START SLAVE;
GRANT ALL PRIVILEGES ON *.* TO 'replication'@'' identified by 'slave';

server 2:

MASTER_HOST='10.253.65.221',MASTER_USER='replication',MASTER_PASSWORD='slave';
STOP SLAVE;
show slave status\G;
GRANT ALL PRIVILEGES ON *.* TO 'replication'@'' identified by 'slave';


You should then be able to create and remove databases on either servers and it will replicate to the other.

I tried to use "load data from master" but it only worked on very simple data. On large sets, it was better to use mysqlhotcopy (perl script that came with mysql on centos)

On CentOS, you first need to export the mysql directory in the /etc/exports file like this:
var/lib/mysql 10.253.15.15(rw,no_root_squash)
and then run:
exportfs -r

then on the machine with the tables, you run:
mkdir /tmp/mounted_mysql_directory
mount -o vers-3 10.253.65.221:/var/lib/mysql /tmp/mounted_mysql_directory

Now that you have the directory exported and mounted, you can hot copy the files from server 1 to server 2:

mysqlhotcopy --addtodest --resetmaster --resetslave -u root -p yourdatabasename_development /tmp/mounted_mysql_directory

The resetmaster and resetslave are important here. MySQL will keep an index in the replication log where it thinks its master is and where it is as a slave. After I copy the database over, I want them to start fresh. So, from here you can look at the master status on server 1 and master status on server 2 to get these indexes. then you can run CHANGE MASTER TO with an extra parameter:
MASTER_LOG_POS=98;
if the index was 98. This will then get them both in sync and ready to accept further transactions to apply.

You can also skip a given number of replication transactions using:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N;
just be careful what you skip.

Coming from a Notes/Domino background, this idea of replicating transactions was a little new. Notes, will keep a sequence number for each record and keep the data in sync. It does not matter how the data got into its current state, it will just do its best at keeping the data replicated (using sequence numbers and time stamps as needed).

MySQL is more transactional based. The documents talk about the my.cnf parameters like "binlog-ignore-db" will act as a filter for how transactions get written to the replication log. To a Notes person, this seems a little inefficient. It means to keep the data in sync, you have to run the exact same transactions on each server (even if the operations are repetitive and act on the same records). In Notes, it will be able to skip ahead to the final state rather than going through each transaction to get to the final state. MySQL has no such ability.

As a result, MySQL cannot just sync itself if you run into problems. This is also true when you add a member to the cluster or replace a failed one. To get things in sync again, you really have to think it through and make sure you lock tables as needed, get the data the same with copy tools like mysqlhotcopy and let the transactions start again on the cluster when you know it is ready.

I would also expect this would have a different scalability path than Notes - if your bottleneck is the amount of transactions you are getting, then adding additional replicas may not help in MySQL where it would in Notes.