Set up MySQL database replication

If you use databases then you know how important it is to back them up. But backing up a database and being able to use that backup depends upon the back up being always up to date. There is a method that ensures you will always have an up to date backup. That method is database replication.Replication is a way to configure a MySQL database to update on a remote server whenever the local client is updated.

Database replication will require the use of two machines. The reason you want to use two machines is to ensure you will always have a working copy of your database (should one machine die). When using replication you think of your machines as Master and Slave. The Master is the machine that holds the original database. The Slave is the machine that holds the replicated database (the backup per say.) Both machines must have a working MySQL installation and must be networked together. You will need root access as well as access to the database administrator on both machines.

Setting up the Master

For purpose of example our database to replicate will be called "sample_database".? The first thing you need to do is open up the /etc/mysql/my.conf file for editing. You need to search for the lines:

#skip-networking
#bind-address = 127.0.0.1

Uncomment out these lines (remove the "#" character). If these lines do not exist, add them. Now we have to inform mysql which database we plan on replicating. For this you will add the following lines:
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=sample_database
server-id=1

The above lines do the following:

Line 1: Instruct mysql what log file to use.

Line 2: Instruct mysql which database to replicate

Line 3: Instruct mysql that this machine is the master.

Restart mysql on this machine with the following command:

/etc/init.d/mysql restart

The next step is to log into the mysql terminal and create a user that has replication privileges. To do this issue the command:

mysql -u root -p

You will prompted for the mysql admin password. Now we have to make some mysql magic. From the mysql prompt issue the following:

GRANT REPLICATION SLAVE ON *.* TO 'USER'@'%' IDENTIFIED BY '';

Where NEW_PASSWORD is the actual password and USER is the actual username that will have replication privileges.

Now issue the command:

FLUSH PRIVILEGES;

You're not done with mysql yet. Now issue the command:

USE sample_database;

And now the command:

FLUSH TABLES WITH READ LOCK;

And now you need to make sure mysql is seeing the Master and can show the status of the master with the command:

SHOW MASTER STATUS;

When you issue the above command you should see a listing printed out for your sample_database. Write this information down (you will see a Position number that you will need later).
Now you need to get tables and data from the sample_database. The method I will show you requires that the database on the Master be locked momentarily. To that end the database will be unavailable until the database is unlocked. Keep this in mind when setting this up.

Now you're ready to set up the Slave. Move over to the machine that will serve as the slave.

The first thing to do on the Slave is to create the database the Master will write to. On the slave open up the mysql shell with:

mysql -u root -p

You will be prompted for the password. Now enter the following command to create the database:

CREATE DATABASE sample_database;

And quit mysql with the "quit" command.

Now to configure MySQL to know it is the slave and open up the /etc/mysql/my.conf file for editing. Add the following lines:
server-id=2
master-host=IP_ADDRESS_OF_MASTER
master-user=USER
master-password=USER_PASSWORD
master-connect-retry=60
replicate-do-db=sample_database

Where IP_ADDRESS_OF_MASTER is the actual IP address of the master server, USER is the user created on the master for replication, and USER_PASSWORD is the password given to the replication user on the Master.

Save that file and restart mysql on the slave with the command:

/etc/init.d/mysql restart

Now we have to load the data from the Master into the Slave. This is when the database will be locked. Here are the commands to load the data:

mysql -u root -p

Enter the mysql admin password. Now from the mysql prompt enter the following command:

LOAD DATA FROM MASTER;

Now exit the mysql prompt with the command quit.

The next step is to stop the slave so you can finish up the Slave configuration. Issue the following:

mysql -u root -p

You will prompted for the root password.

now enter the command:

SLAVE STOP;

Now the next command is a bit lengthy:

CHANGE MASTER TO MASTER_HOST='IP_ADDRESS_OF_MASTER', MASTER_USER='USER', MASTER_PASSWORD='USER_PASSWORD', MASTER_LOG_FILE='mysql-bin.007', MASTER_LOG_POS=NUMBER;

Where:
IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
USER is the actual user you created on the Master.
USER_PASSWORD is the actual password you gave the user on the Master.
mysql-bin.007 is the File name from the output of the SHOW MASTER STATUS command from above.
MASTER_LOG_POS is the Position given in the output of the SHOW MASTER STATUS command from above.

Finally issue the command:

SLAVE START;

and then quit the mysql prompt with the command "quit".

You're done. If you have phpmyadmin installed you can check the status of the databases as they updated. You now have database replication up and running. Congratulations.

Advertisement