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
Recommended