How To Set Up MySQL Database Replication On Fedora 10

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Last edited 01/23/2009

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though. I will use Fedora 10 for the master and slave in this tutorial.

I want to say first that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

1 Preliminary Note

I'm using two Fedora 10 servers in this tutorial:

• server1.example.com (IP 192.168.0.100): master
• server2.example.com (IP 192.168.0.101): slave

In this tutorial I will show how to replicate the database exampledb from server1.example.com (master) to server2.example.com (slave).

I'm assuming that MySQL is already installed (e.g. as shown in chapter 10 on http://www.howtoforge.com/perfect-server-fedora-10-p4) and working on both servers. The database exampledb with tables and data is already existing on the master, but not on the slave.

2 Configure The Master

server1:

First we create a log directory for the MySQL bin-logs:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

Then we edit /etc/my.cnf; we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/my.cnf (in the [mysqld] section!):

vi /etc/my.cnf

[mysqld]
[...]
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1
[...]

Then we restart MySQL:

/etc/init.d/mysqld restart

Then we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p

Enter password:

Now we are on the MySQL shell.

STOP SLAVE;
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

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

The last command should show something like this (please write it down, we'll need it later on):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp):

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:

UNLOCK TABLES;
quit;

3 Configure The Slave

server2:

Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to the [mysqld] section in /etc/my.cnf:

vi /etc/my.cnf

[mysqld]
[...]
server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=slave_password
master-connect-retry=60
replicate-do-db=exampledb
[...]

Then we restart MySQL:

/etc/init.d/mysqld restart

Now we create the empty database exampledb on the slave (make sure you run STOP SLAVE; to stop all slave processes if there are any!):

mysql -u root -p

Enter password:

STOP SLAVE;
CREATE DATABASE exampledb;
quit;

We can now import the SQL dump snapshot.sql as follows:

cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Then we connect to MySQL again...

mysql -u root -p

Enter password:

... and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!):

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Finally start the slave:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS\G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/mysqld.log to find out about any errors):

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.100
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

Afterwards, you can leave the MySQL shell on server2:

quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!

4 Links

• MySQL: http://www.mysql.com/
• Fedora: http://fedoraproject.org/