Data Management

Set up MySQL database replication to ensure up-to-date backups

Learn how to have a working, replicated MySQL database so your backup is always current.

Configuring the Slave

In order to configure the Slave, open the /etc/mysql/my.conf file, add the following, and save and close that file:

server-id=2

master-host=IP_ADDRESS_OF_MASTER

master-user=USER

master-password=USER_PASSWORD

master-connect-retry=60

replicate-do-db=DATABASE

Where:

  • IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
  • USER is the database administrator with replication privileges.
  • USER_PASSWORD is the password associated with the USER.
  • DATABASE is the name to be replicated.

This next step requires information returned from the SHOW MASTER STATUS; command that was run. Stop the Slave by issuing these commands:

mysql -u root -p (enter the MySQL admin password)

SLAVE STOP;

You must run the following command:

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 the following applies:

  • IP_ADDRESS_OF_MASTER is the actual IP address of the Master
  • USER is the MySQL admin user
  • USER_PASSWORD is the password for the USER
  • NUMBER is the Position Number reported from the SHOW MASTER STATUS command.

Restart the Slave by issuing the command SLAVE START; and then exit the MySQL prompt with the command quit. Now you can follow these steps to make sure everything is working:

1. Issue the command mysql -u root -p (enter the database admin password).

2. Issue the command SHOW SLAVE STATUS;.

You should see something like this:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If all is set to Yes, you now have a working, replicated MySQL database. Congratulations!

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

2 comments
dotcomindia
dotcomindia

How can i setup MySQL database replication on two different slave servers?

Bapster
Bapster

Shoud the correct command be (to restart MySQL) init.d instead of "command /etc/inid.t/mysqld restart"

Editor's Picks