How To Set Up MySQL Database Replication On Fedora 10

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Tue, 2009-03-03 11:56. :: Fedora | MySQL

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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Wed, 2009-08-05 06:12.

Atención usuarios Centos 5.3 y MySQL 5.0.45:

antes de Ejecutar el comando 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; 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='192.168.0.100', 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

Submitted by Anonymous (not registered) on Tue, 2009-03-03 14:49.

This tutorial shows a number of very dangerous bad practices.

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

master-host=192.168.0.100
master-user=slave_user
master-password=slave_password
master-connect-retry=60

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:

replicate-do-db=exampledb

This tutorial is truly the blind leading the blind.

Submitted by Bruce (not registered) on Sat, 2009-03-07 04:54.
Thanks for pointing out potential security issues. Like the other comment, please teach us the new improved way of taking care of those parameters.
Submitted by Anonymous (not registered) on Tue, 2009-03-03 19:01.

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

Submitted by Rénald Casagraude (not registered) on Thu, 2009-03-19 09:32.
Simply use the pseudo-SQL statements ;-)