Tips and Tricks

It’s always about data. What’s the use of any system without the data it relies on?

Setting up a database replication is one of many steps that should be taken in order to preserve data, preventing any loss and making disaster recovery easier.

Luckily, it’s easy with MySQL. So let’s suppose we have two servers running MySQL, one called host1 and the other host2.
Replication can be either master-master or master-slave. With a master-slave replication, the slave always replicates what the master database is executing. In master-master replication, both databases synchronize with each others.

For the purpose of this tutorial, a master-slave (here host1 and host2 respectively) scenario is examined.

First of all, open the mysql config file on host1 (usually found at /etc/my.cnf on linux, and c:\windows\my.ini on windows), and uncomment (remove the hash of) the following line:

#skip-networking

Secondly, you need to specify the file where the master (host1) should log (write) the queries it’s executing. This will enable the slave (host2) to read these queries and execute them as well. As such, add a line such as:

log-bin = /path/to/mysql-bin.log

where the value above is the path to file where MySQL should be doing the logging. You could very well create a separate directory or use the default mysql installation directory (such as c:\program files\mysql\ on windows or /var/lib/mysql on linux)

Then, you need to specify the name of the database in question. So if you’re setting up replication for one of your MySQL databases called ‘work_data’, then, this is the line you need to add to your MySQL config:

binlog-do-db = work_data

Finally, you need to specify a server id, which says that this is the master server

server-id=1

Save the config file and exit.
Now you need to give host2 the permission to replicate the data. As such, a MySQL query needs to be issued on the master.

So on host1, login to the MySQL prompt (mysql -u root -pyour_root_password) (or PHPMyAdmin, etc… whatever you use), and issue the following statement:

grant replication slave on *.* to ‘username’@’%’ identified by ‘password’;

Make sure to replace username and password with a credential of your choice. Do keep the single quotes though.
The % sign means that the slave can connect from any host. If you want it to be more secure, replace that with host2 (the slave’s hostname).

After all the above is done, restart the MySQL service (service mysqld restart (linux) or, net stop mysql, net start mysql (on windows)).

If the database had data earlier, make sure you dump it and load it on the slave before doing any of the above. Dumping data is easy and can be done by cd’ing to the MySQL bin directory and issuing:

mysqldump -Q -u root -pyour_root_password databasename > database_dump.sql

(replace the password and database name with the correct login). The whole database will now be in the file called database_dump.sql

To import it on host2, cd to the mysql bin directory and issue:

mysql -u root -pyour_root_password databasename < /path/to/the/file/database_dump.sql

The MySQL config file on host2 should have the following lines:

server-id=2
master-host = host1
master-user = username
master-password = password
master-port = 3306

where host1 is the master’s hostname/IP, and username and password are the credentials you used when granting replication access a few steps above. 3306 is the port MySQL is running on (which is the default)

Then start the slave process on host2 by issuing at the MySQL prompt:

start slave;

To make sure replication is working, issue the following SQL query on host1:

show slave status \G

(Slave_SQL_Running and Slave_IO_Running should report ‘Yes’)

Good luck

This entry was posted on Monday, September 15th, 2008 at 1:50 PM and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.