{"id":262,"date":"2009-08-20T09:23:38","date_gmt":"2009-08-20T14:23:38","guid":{"rendered":"http:\/\/www.1oc.com\/blog\/?p=262"},"modified":"2009-08-20T09:23:38","modified_gmt":"2009-08-20T14:23:38","slug":"how-to-set-up-database-replication-in-mysql","status":"publish","type":"post","link":"http:\/\/blog.1oc.com\/?p=262","title":{"rendered":"How To Set Up Database Replication In MySQL"},"content":{"rendered":"

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.<\/span><\/p>\n

In this tutorial I will show how to replicate the database\u00a0exampledb<\/em><\/span> from the master with the IP address\u00a0192.168.0.100<\/em><\/span> to a slave. Both systems (master and slave) are running\u00a0Debian Sarge<\/strong>; however, the configuration should apply to almost all distributions with little or no modification.<\/span><\/p>\n

Both systems have MySQL installed, and the database\u00a0exampledb<\/em><\/span> with tables and data is already existing on the master, but not on the slave.<\/span><\/p>\n

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!<\/span><\/p>\n

1 Configure The Master<\/span><\/h4>\n

First we have to edit\u00a0\/etc\/mysql\/my.cnf<\/em><\/span>. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):<\/span><\/p>\n

<\/span><\/p>\n

#skip-networking\n#bind-address            = 127.0.0.1<\/pre>\n
Furthermore 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\u00a0exampledb<\/em><\/span>, so we put the following lines into\u00a0\/etc\/mysql\/my.cnf<\/em><\/span>:<\/p>\n
log-bin = \/var\/log\/mysql\/mysql-bin.log\nbinlog-do-db=exampledb\nserver-id=1<\/pre>\n
Then we restart MySQL:<\/p>\n
\/etc\/init.d\/mysql restart<\/span><\/em><\/span><\/p>\n
Then we log into the MySQL database as\u00a0root<\/em><\/span> and create a user with replication privileges:<\/span><\/p>\n
mysql -u root -p
\nEnter password:<\/span><\/em><\/span><\/p>\n
Now we are on the MySQL shell.<\/span><\/p>\n
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<some_password>’; <\/span><\/em>(Replace\u00a0<some_password><\/em><\/span> with a real password!)<\/span>
\nFLUSH PRIVILEGES;<\/em><\/span><\/span><\/p>\n
Next (still on the MySQL shell) do this:<\/span><\/p>\n
USE exampledb;
\nFLUSH TABLES WITH READ LOCK;
\nSHOW MASTER STATUS;<\/span><\/em><\/span><\/p>\n
The last command will show something like this:<\/span><\/p>\n
 <\/span><\/p>\n
+---------------+----------+--------------+------------------+\n| File          | Position | Binlog_do_db | Binlog_ignore_db |\n+---------------+----------+--------------+------------------+\n| mysql-bin.006 | 183      | exampledb    |                  |\n+---------------+----------+--------------+------------------+\n1 row in set (0.00 sec)<\/pre>\n
Write down this information, we will need it later on the slave!<\/span><\/p>\n
Then leave the MySQL shell:<\/span><\/p>\n
quit;<\/em><\/span><\/p>\n
 <\/em><\/span><\/p>\n
There are two possibilities to get the existing tables and data from\u00a0exampledb<\/em><\/span> from the master to the slave. The first one is to make a database dump, the second one is to use the<\/span> LOAD DATA FROM MASTER;<\/em><\/span> command on the slave. The latter has the disadvantage the the database on the master will be\u00a0locked<\/strong> during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and I recommend to follow the first method in this case. However, the latter method is very fast, so I will describe both here.<\/span><\/em><\/span><\/p>\n
If you want to follow the first method, then do this:<\/span><\/p>\n
mysqldump -u root -p<password> –opt exampledb > exampledb.sql <\/span><\/em>(Replace\u00a0<password><\/em><\/span> with the real password for the MySQL user\u00a0root<\/em><\/span>!\u00a0Important:<\/strong> There is\u00a0no<\/strong> space between\u00a0-p<\/em><\/span> and<password><\/em><\/span>!)<\/span><\/span><\/p>\n
This will create an SQL dump of\u00a0exampledb<\/em><\/span> in the file\u00a0exampledb.sql<\/em><\/span>. Transfer this file to your slave server!<\/span><\/p>\n
If you want to go the\u00a0LOAD DATA FROM MASTER;<\/em><\/span> way then there is nothing you must do right now.<\/span><\/p>\n
Finally we have to unlock the tables in\u00a0exampledb<\/em><\/span>:<\/span><\/span><\/p>\n
mysql -u root -p
\nEnter password:
\nUNLOCK TABLES;
\nquit;<\/span><\/em><\/span><\/p>\n
Now the configuration on the master is finished. On to the slave…<\/span><\/p>\n
2 Configure The Slave<\/strong><\/span><\/span><\/p>\n
On the slave we first have to create the database\u00a0exampledb<\/em><\/span>:<\/span><\/p>\n
mysql -u root -p
\nEnter password:
\nCREATE DATABASE exampledb;
\nquit;<\/span><\/em><\/span><\/p>\n
 <\/em><\/span><\/p>\n
If you have made an SQL dump of\u00a0exampledb<\/em><\/span> on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created\u00a0exampledb<\/em><\/span> on the slave:<\/span><\/span><\/em><\/span><\/p>\n
mysql -u root -p<password> exampledb < \/path\/to\/exampledb.sql <\/span><\/em>(Replace\u00a0<password><\/em><\/span> with the real password for the MySQL user\u00a0root<\/span>!Important:<\/strong> There is\u00a0no<\/strong> space between\u00a0-p<\/span><\/em> and\u00a0<password><\/em><\/span>!)<\/span><\/span><\/span><\/p>\n
If you want to go the\u00a0LOAD DATA FROM MASTER;<\/em><\/span> way then there is nothing you must do right now.<\/span><\/p>\n
Now we have to tell MySQL on the slave that it is the slave, that the master is\u00a0192.168.0.100<\/em><\/span>, and that the master database to watch is\u00a0exampledb<\/em><\/span>. Therefore we add the following lines to\/etc\/mysql\/my.cnf<\/em><\/span>:<\/span><\/p>\n
 <\/span><\/p>\n
server-id=2\nmaster-host=192.168.0.100\nmaster-user=slave_user\nmaster-password=secret\nmaster-connect-retry=60\nreplicate-do-db=exampledb<\/pre>\n
Then we restart MySQL:<\/span>\n\n\/etc\/init.d\/mysql restart<\/span><\/em><\/span>\n\n\n<\/em><\/span><\/span>\n\n\u00a0<\/em><\/span><\/span>\n\nIf you have not imported the master\u00a0exampledb<\/em><\/span>\u00a0with the help of an SQL dump, but want to go the\u00a0LOAD DATA FROM MASTER;<\/em><\/span>\u00a0way, then it is time for you now to get the data from the masterexampledb<\/em><\/span>:<\/span>\n\nmysql -u root -p\nEnter password:\nLOAD DATA FROM MASTER;\nquit;<\/span><\/em><\/span>\n\nIf you have phpMyAdmin\u00a0installed on the slave you can now check if all tables\/data from the master\u00a0exampledb<\/em><\/span>\u00a0is also available on the slave\u00a0exampledb<\/em><\/span>.<\/span>\n\n\u00a0<\/span>\n\nFinally, we must do this:<\/span>\n\nmysql -u root -p\nEnter password:\nSLAVE STOP;<\/span><\/em><\/span>\n\nIn the next command (still on the MySQL shell) you have to replace the values appropriately:<\/span>\n\nCHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<\/span><some_password><\/span><\/em><\/span>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;<\/span><\/em><\/span>\n\n\n<\/span><\/span>\n\n\n