ajira.eu

A tutorial on how to set up two MySQL servers with Master - Master replication.

1. What is database replication?

Database replication basically means that you keep a continuously synchronized mirror of a database on two or more servers. If one of the servers changes something in the database, it is immediately synchronized to the other server(s). This has a couple of benefits. The most common use is load balancing; spreading the database over multiple servers greatly enhances the performance of large databases and databases with high traffic (e.g. real busy websites). This also brings the benefit of redundancy. If one of the database servers suddenly kicks the bucket, you still have the data on the other server(s).

All in all, it has a couple of benefits that, in certain situations, could outweigh the costs of additional servers.

For this example I will assume you have at least some basic knowledge of databases in general and know your way around a FreeBSD, UNIX or Linux system. Of course, the database replication will work just fine on a Windows based server as well, but obviously the paths and daemon control commands will be a bit different.

I will show you how to set up replication for a single database in a “master-master” situation. This means both servers are master for- and slave to- each other.

For this example I will assume the following information:

• The database name is exampledb.
• Server_1 has IP address 192.168.1.1.
• Server_2 has IP address 192.168.1.2.

First, we need to add a user that has replication rights. Log on to MySQL with a user that has GRANT privileges (or just use root), and issue the following commands on the MySQL prompt:

GRANT REPLICATION SLAVE ON *.exampledb TO
	replication@192.168.1.2 IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Obviously you could replace the *.exampledb by *.*, so that the user can replicate all databases. You could also replace the replication@192.168.1.2 by replication@%, so that the user can log on from any IP address (as long as the firewall permits ). Personally, I prefer to keep things tight and secure, so I hardcode the databases and IP addresses.

Next up, we need to collect some information from the database. On the MySQL prompt, issue the following commands:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The SHOW MASTER STATUS; command is the important one. It should give us an output that is some what like this:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.001 | 112      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need to write down the values for File and Position. We need these values later on when we configure Server_2.

After that, we make some changes to the MySQL configuration file, which is usually found in the database directory. Default installations of MySQL usually put this file at either /var/db/mysql/my.cnf, and some Linux distributions put it at /etc/mysql/my.cnf. Add the following information at the [mysqld] section:

 
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.1.2
master-user = replication
master-password = password
master-connect-retry = 60
replicate-do-db = exampledb
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

When that is done, we restart MySQL. On FreeBSD the proper command would be /usr/local/etc/rc.d/mysql-server restart, and on most Linux distributions it would be /etc/init.d/mysql restart.

Now, we need to make sure that Server_2 has the initial database, so replication can take place. For this we simply make a dump of the database on Server_1, and copy the file over to Server_2, where we import it again. The simplest way to make a dump of the database is the following commands:

mysqldump -u root -p exampledb ] exampledb.sql
tar zfvc exampledb.tgz exampledb.sql

Lastly, we need to unlock the tables, so they can be replicated. Log back on to MySQL and enter the following commands on the MySQL prompt:

UNLOCK TABLES;
quit;

Right.. onward to Server_2!

First of all, we need to create the initial database, by importing the database dump file that we created on Server_1. This is easily done using the following commands:

tar zfvx exampledb.tgz
mysql -u root –p [ exampledb.sql
 

Configuration-wise, Server_2 is practically identical to Server_1, with exception of the IP address. So, we log on to MySQL and create a new user, and show the information we need:

GRANT REPLICATION SLAVE ON *.exampledb TO
	replication@192.168.1.1 IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

As before, we write down the values for File and Position, and move on the MySQL configuration file. Add the following information at the [mysqld] section:

 
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.1.1
master-user = replication
master-password = password
master-connect-retry = 60
replicate-do-db = exampledb
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

When that is done, we restart MySQL.

After MySQL is restarted, we initiate the actual replication. In this case, Server_1 is the master, and Server_2 is the slave. On the MySQL prompt, enter the following commands:

UNLOCK TABLES;
LOAD DATA FROM MASTER;
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication’, 
	MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.001',
	MASTER_LOG_POS=112; 
START SLAVE;
quit;

If we get no error messages, the replication should work. We can check this with the SHOW SLAVE STATUS\G command. Please note how I use \G to end the command, as opposed to the regular ; (semilicon). The \G command forces the results to appear vertically instead of horizontally. Since the output gives quite a lot of information, having the results appear vertically makes it a lot easier to read. The command should give an output somewhat like this:

*************************** 1. row ***************************
          Master_Host: 192.168.1.1
          Master_User: replication
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: mysql-bin.001
  Read_Master_Log_Pos: 112
       Relay_Log_File: mysql-bin.006
        Relay_Log_Pos: 175
Relay_Master_Log_File: mysql-bin.001
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db: exampledb
  Replicate_ignore_db: 
           Last_errno: 0
           Last_error: 
         Skip_counter: 0
  Exec_master_log_pos: 112
      Relay_log_space: 175

Yes, that is a lot of information, but the most important ones to us are Slave_IO_Running and Slave_SQL_Running. If both of these are running then everything is fine, and we can move on to the last step.

So… Server_2 is now replicating properly with Server_1, but since it’s a master-master situation, we want the replication to occur in both directions. For this to happen, we need to initiate the replication on Server_1, so that Server_1 is the slave, and Server_2 is the master. On the MySQL prompt at Server_1, enter the following commands:

LOAD DATA FROM MASTER;
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='replication', 
	MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.006', 
	MASTER_LOG_POS=175; 
START SLAVE;
quit;

Again… we can check the status of the replication with the SHOW SLAVE STATUS\G command. If both Slave_IO_Running and Slave_SQL_Running are running then we’re done!

The databases should now synchronize automatically. Add some records on both servers and see if they show up on the other as well.

On older versions of MySQL (lower than 4.1.1), there are some limitations:

• The USER(), UUID(), LOAD_FILE(), and CONNECTION_ID() functions do not work reliably on a slave (they are replicated without changes).
• The FLUSH, ANALYZE, OPTIMIZE, and REPAIR statements are not replicated. This means that if you change permissions on the one of the servers by editing the tables directly, you will need to FLUSH PRIVILEGES manually on the other server(s).