How To Set Up MySQL Database Replication On Fedora 10

Version 1.0
Author: Falko Timme
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:

  • (IP master
  • (IP slave

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

I'm assuming that MySQL is already installed (e.g. as shown in chapter 10 on 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


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

log-bin = /var/log/mysql/mysql-bin.log

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.

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

Next (still on the MySQL shell) do this:

USE exampledb;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |       98 | exampledb    |                  |
1 row in set (0.00 sec)


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@

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:



3 Configure The Slave


Now we have to tell MySQL on the slave that it is the slave, that the master is, 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


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:


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='', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Finally start the slave:


Then check the slave status:


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):

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                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
                 Last_Errno: 0
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)


Afterwards, you can leave the MySQL shell on server2:


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

Share this page:

5 Comment(s)

Add comment


From: Anonymous at: 2009-03-03 13:49:03

This tutorial shows a number of very dangerous bad practices.

The following configuration options have been deprecated for YEARS, with good reasons:


And this configuration option is also very dangerous, because it does not do what the author says it does, nor what people usually think it does:


This tutorial is truly the blind leading the blind.

From: Anonymous at: 2009-03-03 18:01:50

Thanks, but can you post the "new-way-of-doing-things"?

From: Bruce at: 2009-03-07 03:54:12

Thanks for pointing out potential security issues. Like the other comment, please teach us the new improved way of taking care of those parameters.

From: Rénald Casagraude at: 2009-03-19 08:32:54

Simply use the pseudo-SQL statements ;-)

From: Anonymous at: 2009-08-05 05:12:13

Atención usuarios Centos 5.3 y MySQL 5.0.45:

antes de Ejecutar el comando CHANGE MASTER TO MASTER_HOST='', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; en el esclavo, primero ejecute este otro: RESET SLAVE; de lo contrario le saca un error similar a este:

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log 


Information for Centos 5.3 and MySQL 5.0.45 users:

Before to Run the instruction CHANGE MASTER TO MASTER_HOST='', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; first run this other one: RESET SLAVE; other way probably you'll get an error like this:

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log