Debian Multi-Master MySQL Replication Guide

[Ryan Moat]

I spent a couple of weeks trying to figure out Multi-Master Replication in MySQL, but the results didn't turn out. It kept going to One-Way Replication, or the replication would not work at all.
I finally figured it out and have compiled four different guides into one for Debian. I thought i'd share.

The first step is to make sure you have the same version of MySQL on both machines you want to replicate.
To update/upgrade or install if it isn't already installed, type this in the console:
apt-get install mysql-client
apt-get install mysql-server
apt-get install mysql-common

After you have the same MySQL version installed on both machines (or however many machines you are using), we need to make sure MySQL listens on all IP addresses.

Make sure you are the su user, or logged into root while doing these steps.

Type:
nano /etc/mysql/my.cnf

Comment the following lines out:
#skip-networking (This line is not in the latest version of the Debian MySQL).
#bind-address = 127.0.0.1

Now we need to setup where the MYSQL should write the logs. Make sure both my.cnf files are opened up on the MySQL servers by typing:
nano /etc/mysql/my.cnf

In this example, we'll use the database name called testdb. Replace this with your own database.

Server I (add the following to the my.cnf file):

server-id=1
binlog-do-db=testdb
master-host=<IP Address of Server 2> i.e. (master-host=192.168.0.254)
master-user=<Username> (Username you want to use for replication)
master-password=<password> (Password you want to use for replication)
master-port=3306
log-bin = /var/lib/mysql/master-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Server II (add the following to the my.cnf file):

server-id=2
binlog-do-db=testdb
master-host=<IP Address of Server 2> i.e. (master-host=192.168.0.253)
master-user=<Username> (Username - Same username as used for Server I)
master-password=<password> (Password - Same password as used for Server I)
master-port=3306
log-bin = /var/lib/mysql/master-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

After you have done this, restart MYSQL on both machines by browsing to the: /etc/init.d directory and typing: ./mysql restart

After you have restarted MySQL on both machines, we need to setup the replication user on Server I.

First let's setup a password for MySQL so you need a password to use MySQL.

Type: mysqladmin password yournewpassword

After this is done, to use MySQL, you'll need to type mysql -uroot -p to get into MySQL to do the rest of the following steps.

Type: mysql -uroot -p
It will ask for the password that you just set with the mysqladmin.
After that, you'll come to the mysql> Prompt.

Once you're at the mysql> Prompt type this on Server I:
(NOTE: Replace username and password with the one's you set in the my.cnf. The master-user and master-password. Also keep the quotes. i.e. (TO 'databaseuser'@'%' IDENTIFIED BY 'mypassword';))

GRANT REPLICATION SLAVE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT ON *.* TO 'username'@'%';
GRANT SUPER ON *.* TO 'username'@'%';
GRANT RELOAD ON *.* TO 'username'@'%';
GRANT SELECT ON *.* TO 'username'@'%';
GRANT DROP ON *.* TO 'username'@'%'; (You may not need this, I did it anyways)
GRANT ALTER ON *.* TO 'username'@'%'; (You may not need this either)
FLUSH PRIVILEGES;

If you have a domain and want to setup the replication user you'd type the exact same thing only you'd replace this 'username'@'%' with 'username'@'%.domainname.com';

----------------------------------------------------------------------------------

Now we need to get a clean snapshot of Server I's data. There are three different methods you can do here. I used the METHOD 3, but it is not recommended for huge databases. I'll give steps to all three. We need to get the snapshot over onto Server II.

METHOD 1 - (You may need SSH on Server 1 and Server 2 installed to do this):
Still on Server 1 in MySQL type this on the prompt mysql>

mysql>USE testdb; (Replace testdb with your database).
mysql>FLUSH TABLES WITH READ LOCK;

Exit MySQL by typing quit. Type this in the Shell:

sudo tar cvf /tmp/mysql.bak /var/lib/mysql
Now go back into MySQL (mysql -uroot -p).

Type this on the prompt mysql>
SHOW MASTER STATUS;
(Take note of the LOG FILE NAME and the POSITION. We'll need it later.)
Now type this on the prompt mysql>: UNLOCK TABLES;

Now copy the snapshot to the slave:

Exit MySQL again by typing quit; Type this in the Shell:

scp /tmp/mysql.bak user@server2:/tmp (I think you'd replace user with a username that has been setup on that machine, and server2 would be the IP address. i.e. (scp /tmp/mysql.bak administrator@192.168.13.254:/tmp)).

Now go to Server 2 and type:
cd /etc/init.d
./mysql stop
cd /
sudo tar xvf /tmp/mysql.bak
cd /etc/init.d
./mysql start

Now start MySQL on Server 2 by typing:
mysql -uroot -p
When the mysql> prompt comes up, type:
mysql> CHANGE MASTER TO MASTER_HOST='IP-of-Server1',
-> MASTER_USER='username', (Username that was set in the my.cnf file)
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='log_file_name' (Recorded log file name from SHOW MASTER STATUS command we did earlier on Server 1).
-> MASTER_LOG_POS=recorded_log_position; (Recorded log position from SHOW MASTER command we did on Server 1. There are no ''s around the recorded log position.

Then type:
mysql> START SLAVE;


Now we need to start replication from slave to master:
On Server 2 now open up mysql (mysql -uroot -p).

Type this on the mysql> prompt:
mysql>SHOW MASTER STATUS;

Write down the Log Filename and Log Position like we did with Server I. Now go to Server 1 and start mysql again if it is no longer opened by typing:
mysql -uroot -p
At the prompt type this:
mysql> CHANGE MASTER TO MASTER_HOST='IP-of-Server2',
-> MASTER_USER='username',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='recorded_log_filename',
-> MASTER_LOG_POS=recorded_logposition';

Start replication on the master:
mysql>START SLAVE;

On both Server 1 and Server 2, to see the status of the two type this under MYSQL:
mysql> SHOW SLAVE STATUS \G;

It will say whether it is connected to the master, etc.
Look for SLAVE_IO_Running and SLAVE_SQL_Running. They both should say YES.




METHOD 2 - If you do not like Option 1.

mysql>use testdb; (Replace testdb with your database).
mysql> SHOW MASTER STATUS; (Write down the Log file and Position)
mysql> quit;

On the shell:
mysqldump -u root -p<password> --opt testdb > testdb.sql (Replace the password with the one you set in the my.cnf file. There is not space between -p and <password>.
This will create an SQL dump of testdb in the file testdb.sql. Transfer this file to your Server 2.

Open MYSQL again and unlock the tables:
mysql>UNLOCK TABLES;



METHOD 3 - I used this because it was fast and easy, and worked well for my database.

use testdb; (Replace testdb with your database)
mysql> SHOW MASTER STATUS; (Write down the Log file and Position)
mysql> LOAD DATA FROM MASTER;

ON Server 1 Unlock the tables by going into MYSQL and typing:
mysql> UNLOCK TABLES;
mysql>quit;

Again check the SLAVE status on both machines by typing:
mysql>SHOW SLAVE STATUS \G;

It should say "Waiting for master to send event",
SLAVE_IO_Running: Yes
Slave_SQL_Running: Yes
It should also show all the settings you put into the my.cnf file.

I welcome any comments, questions, and/or updates to this guide.

Thanks,
Ryan