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.

You can back up your MySQL database using the built-in dump tool, but the second you do that backup is out of date. Since so many companies and web-based tools rely heavily on databases, it's crucial that those databases are as up-to-date as possible. One very simple solution is database replication, which keeps a real-time copy of the database on a remote server. With this in place, if something happens to the primary database, it will be much easier to get your database back up and running with current information.

This is what you'll need to set up MySQL database replication:

  • Two correctly configured MySQL servers
  • Root access and access to the database administrator on both servers
  • Ability to work from the command line

The setup will use two machines: the Master and the Slave. We'll start with the setup of the Master.

Configure the Master

The first step is to enable MySQL for networking. To do this, open the file /etc/mysql/my.conf, look for the following lines, and uncomment them if they exist:

#skip-networking
#bind-address = 127.0.0.1

If the lines do not exist, you should add them (minus the "#"). Within the same file, we have to make MySQL aware of the database that will be replicated. You can do this with these lines:

log-bin = /var/log/mysql/mysql-bin.log

binlog-do-db=DATABASE_TO_BE_REPLICATED

server-id=1

DATABASE_TO_BE_REPLICATED is the actual name of the database you want to replicate.

The above lines tell MySQL the following:

Line 1: Configures the log file to be used

Line 2: Configures the database to be replicated

Line 3: Configures the machine as the Master

After you add these lines, save the my.conf file and restart MySQL with the command /etc/inid.t/mysqld restart.

You might need to use sudo to issue the above command. Another option for the restarting of the MySQL daemon is using the service command like service mysqld start.

With this configuration complete, it's time to set up a user for replication privileges. Log on to the Master and issue the command mysql -u root -p. Enter the password for the MySQL admin password. Upon successful authentication, you will be at the MySQL prompt, where you will enter the command GRANT REPLICATION SLAVE ON *.* TO 'USER'@'%' IDENTIFIED BY 'PASSWORD';. USER is the user who will have replication privileges, and the PASSWORD is that user's MySQL password.

With that command issued, you must flush the database privileges with the command FLUSH PRIVILEGES;.

Let's make sure MySQL can see the Master with the command SHOW MASTER STATUS;. This command should list information (including the all-important position number, which will be required for the Slave setup) for the database to be replicated. You need to write down that information, which will look something like this:

File: mysql-bin.00002

Position: 230

Binlog_Do_DB: database_to_be_replicated

Binlog_Ignore_DB:
1 row in set (0.00 sec)

The next step is to retrieve the tables and data from the database to be replicated; in order to do this, the database must be temporarily locked. When the database is locked, it cannot be used, so do this at a time when the database won't be needed. To lock the database, issue the command FLUSH TABLES WITH READ LOCK;.

You must dump the database that will be copied to the Slave. There used to be a command LOAD DATA FROM MASTER, but that command has been deprecated. So, to get the data, you must do a dump with the command mysqldump -u root -p DATABASE_TO_BE_REPLICATED > DATABASE_TO_BE_REPLICATED.sql where DATABASE_TO_BE_REPLICATED is the name of the actual database being replicated. You'll be prompted for the MySQL admin password; enter that password, and the dump will process.

Now unlock the tables with the command UNLOCK TABLES; and then quit the MySQL prompt with the command quit.

Copy that database dump file to the Slave (using a USB drive or the scp command) and then restore the database (on the SLAVE) with the command mysql -u root -p DATABASE_TO_BE_REPLICATED < DATABASE_TO_BE_REPLICATED.sql where DATABASE_TO_BE_REPLICATED is the name of the database being replicated.

Learn how to configure the Slave machine.

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"