Inductive Automation
News Room

News / April 24, 2014

How To Set Up Database Replication In MySQL

This article 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.

Here we will show how to replicate the database exampledb from the master with the IP address 192.168.1.100 to a slave. Both systems (master and slave) are running Microsoft Windows; however, the configuration should apply to all distributions with little or no modification.

Both systems have MySQL 5.0 installed, and the database exampledb with tables and data is already existing on the master, but not on the slave.

Step 1: Configure the Master


First we have to edit the file C:\Program Files\MySQL\MySQL Server 5.0\my.ini. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking
#bind-address = 127.0.0.1


Now we have to tell MySQL to 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. Put the following lines into the file C:\Program Files\MySQL\MySQL Server 5.0\my.ini below the [mysqld] line:

log-bin = "C:\Program Files\MySQL\MySQL Server 5.0\data\mysql-bin.log"
binlog-do-db=exampledb
server-id=1


Then we restart the MySQL service by running the following commands in the Command Prompt (located at Start -> All Programs -> Accessories -> Command Prompt):

net stop mysql
net start mysql


Then we need to change directory to our MySQL bin directory by running the following command in Command Prompt:

cd "C:\Program Files\MySQL\MySQL Server 5.0\bin"

Now we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p
Enter password:


Now we are in the MySQL shell. Issue the following commands.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';
(Replace <some_password> with a real password!)
FLUSH PRIVILEGES;


Next (still in the MySQL shell) do this:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;


The last command will show something like this:


Write down this information, we will need it later on the slave!

Then leave the MySQL shell:

exit;

Now we need to make a database dump of the master. In the Command Prompt run the following command:

mysqldump -u root -p<password> --opt exampledb > exampledb.sql
(Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)


This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server.

Finally we have to unlock the tables in exampledb:

mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;


Now the configuration on the master is finished.

Step 2: Configure the Slave

On the slave we first have to create the database exampledb in the Command Prompt:

cd "C:\Program Files\MySQL\MySQL Server 5.0\bin"
mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
exit;


Now we need to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < "C:\path\to\exampledb.sql"

Next we have to tell MySQL on the salve that it is the slave, that the master is 192.168.1.100. Therefore we add the following lines to C:\Program Files\MySQL\MySQL Server 5.0\my.ini below the [mysqld] line:

server-id=2
master-host=192.168.1.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb


Then we restart MySQL:

net stop mysql
net start mysql


Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;


In the next command (still on the MySQL shell) you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
  • MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.1.100).
  • MASTER_USER is the user we granted replication privileges on the master.
  • MASTER_PASSWORD is the password of MASTER_USER on the master.
  • MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
Now all that is left to do is start the slave. Still on the MySQL shell we run:

START SLAVE;
exit;


That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.