Live Backups of MySQL Using Replication

Listen Print Discuss

Live Backups of MySQL Using Replication

by Russell Dyer, author of MySQL in a Nutshell
06/16/2005

One of the difficulties with a large and active MySQL database is making clean backups without having to bring the server down. Otherwise, a backup may slow down the system and there may be inconsistency with data, since related tables may be changed while another is being backed up. Taking the server down will ensure consistency of data, but it means interruption of service to users. Sometimes this is necessary and unavoidable, but daily server outages for backing up data may be unacceptable. A simple alternative method to ensure reliable backups without having to shut down the server daily is to set up replication for MySQL.

Typically, replication is a system configuration whereby the MySQL server, known in this context as a master server, houses the data and handles client requests, while another MySQL server (a slave server) contains a complete copy of the data and duplicates all SQL statements in which data is changed on the master server right after it happens. There are several uses for replication (e.g., load balancing), but the concern of this article has to do with using replication for data backups. You can set up a separate server to be a slave and then once a day turn replication off to make a clean backup. When you're done, replication can be restarted and the slave will automatically query the master for the changes to the data that it missed while it was offline. Replication is an excellent feature and it's part of MySQL. You just need to set it up.

The Replication Process

Before explaining how to set up replication, let me quickly explain the steps that MySQL goes through to maintain a replicated server. The process is different depending on the version of MySQL. For purposes of this article, my comments will be for version 4.0 or higher, since most systems now are using the later versions.

The Replication User

There are only a few steps to setting up replication. The first step is to set up a user account to use only for replication. It's best not to use an existing account for security reasons. To do this, enter an SQL statement like the following on the master server, logged in as root or a user that has GRANT OPTION privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT
    ON *.*
    TO 'replicant'@'slave_host'
    IDENTIFIED BY 'my_pwd';

In this SQL statement, the user account replicant is granted only what's needed for replication. The user name can be almost anything. The host name (or IP address) is given in quotes. You should enter this same statement on the slave server with the same user name and password, but with the master's host name or IP address. This way, if the master fails and will be down for a while, you could redirect users to the slave with DNS or by some other method. When the master is back up, you can then use replication to get it up to date by temporarily making it a slave to the former slave server. Incidentally, if you upgraded MySQL to version 4.0 recently, but didn't upgrade your mysql database, the GRANT statement above won't work because these privileges didn't exist in the earlier versions. For information on fixing this problem, see MySQL's documentation on Upgrading the Grants Tables.

Configuring the Servers

Once the replication user is set up on both servers, we will need to add some lines to the MySQL configuration file on the master and on the slave server. Depending on the type of operating system, the file will probably be called my.cnf or my.ini. On Unix-type systems, the configuration file is usually located in the /etc directory. On Windows systems, it's usually located in c:\ or in c:\Windows. Using a text editor, add the following lines to the configuration file, under the [mysqld] group heading:

server-id = 1
log-bin = /var/log/mysql/bin.log

The server identification number is an arbitrary number to identify the master server. Almost any whole number is fine. A different one should be assigned to the slave server to keep them straight. The second line above instructs MySQL to perform binary logging to the path and file given. The actual path and file name is mostly up to you. Just be sure that the directory exists and the user mysql is the owner, or at least has permission to write to the directory. Also, for the file name use the suffix of ".log" as shown here. It will be replaced automatically with an index number (e.g., ".000001") as new log files are created when the server is restarted or the logs are flushed.

For the slave server, we will need to add a few more lines to the configuration file. We'll have to provide information on connecting to the master server, as well as more log file options. We would add lines similar to the following to the slave's configuration file:

server-id = 2

master-host = mastersite.com
master-port = 3306
master-user = replicant
master-password = my_pwd

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

relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index

This may seem like a lot, but it's pretty straightforward once you pick it apart. The first line is the identification number for the slave server. If you set up more than one slave server, give them each a different number. If you're only using replication for backing up your data, though, you probably won't need more than one slave server. The next set of lines provides information on the master server: the host name as shown here, or the IP address of the master may be given. Next, the port to use is given. Port 3306 is the default port for MySQL, but another could be used for performance or security considerations. The next two lines provide the user name and password for logging into the master server.

The last two stanzas above set up logging. The second to last stanza starts binary logging as we did on the master server, but this time on the slave. This is the log that can be used to allow the master and the slave to reverse roles, as mentioned earlier. The binary log index file (log-bin.index) is for recording the name of the current binary log file to use. As the server is restarted or the logs are flushed, the current log file changes and its name is recorded here. The log-error option establishes an error log. If you don't already have this set up, you should, since it's where any problems with replication will be recorded. The last stanza establishes the relay log and related files mentioned earlier. The relay log makes a copy of each entry in the master server's binary log for performance's sake, the relay-log-info-file option names the file where the slave's position in the master's binary log will be noted, and the relay log index file is for keeping track of the name of the current relay log file to use for replicating.

Copying Databases and Starting Replication

If you're setting up a new master server that doesn't contain data, then there's nothing left to do but restart the slave server. However, if you're setting up replication with an existing server that already has data on it, you will need to make an initial backup of the databases and copy it to the slave server. There are many methods to do this; for our examples, we'll use the utility mysqldump to make a backup while the server is running. However, there's still the problem with attaining consistency of data on an active server. Considering the fact that once you set up replication you may never have to shut down your server for backups again, it might be worth while at least to lock the users out this one last time to get a clean, consistent backup. To run the master server so that only root has access, we can reset the variable max_connections like so:

SHOW VARIABLES LIKE 'max_connections';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

SET GLOBAL max_connections = 0;

The first SQL statement isn't necessary, but we may want to know the initial value of the max_connections variable so that we can change it back when the backup is finished. Although setting the variable to a value of 0 suggests that no connections are allowed, one connection is actually reserved for the root user. Of course, this will only prevent any new connections. To see if there are any connections still running, enter SHOW PROCESSLIST;. To terminate any active processes, you can use the KILL statement.

With exclusive access to the server, using mysqldump is usually very quick. We would enter the following from the command line on the master server:

mysqldump --user=root --password=my_pwd \
      --extended-insert --all-databases \
      --master-data  > /tmp/backup.sql

This will create a text file containing SQL statements to create all of the databases and tables with data. The --extended-insert option will create multiple-row INSERT statements and thereby allow the backup to run faster, for the least amount of down time or drain on services. The --master-data option above locks all of the tables during the dump to prevent data from being changed, but allows users to continue reading the tables. With exclusive access, this feature isn't necessary. However, this option also adds a few lines like the following to the end of the dump file:

--
-- Position to start replication from
--

CHANGE MASTER TO MASTER_LOG_FILE='bin.000846' ;
CHANGE MASTER TO MASTER_LOG_POS=427 ;

When the dump file is executed on the slave server, these last lines will record the name of the master's binary log file and the position in the log at the time of the backup, while the tables were locked. When replication is started, it will go to this log file and execute any SQL statements recorded starting from the position given. This is meant to ensure that any data changed while setting up the slave server isn't missed. To execute the dump file to set up the databases and data on the slave server, copy the dump file to the slave server, make sure MySQL is running, then enter something like the following on the slave:

mysql --user=root --password=my_pwd < /tmp/backup.sql

This will execute all of the SQL statements in the dump file, which will include the CREATE and INSERT statements. Once the backed-up databases are loaded onto the slave server, execute the following SQL statement while logged in as root on the slave:

START SLAVE;

After this statement is run, the slave will connect to the master and get the changes it missed since the backup. From there, it will stay current by continuously checking the binary log as outlined before.

Backups with Replication

With replication running, it's an easy task to make a backup of the data. You just need to temporarily stop the slave server from replicating by entering the following SQL statement while logging onto the slave server as root or a user with SUPER privileges:

STOP SLAVE;

The slave server knows the position where it left off in the binary log of the master server. So we can take our time making a backup of the replicated databases on the slave server. We can use any backup utility or method we prefer. When the backup is finished, we would enter the following SQL statement from the slave server as root to restart replication:

START SLAVE;

After entering this statement, there should be a flurry of activity on the slave as it executes the SQL statements that occurred while it was down. In a very short period of time it should be current.

Automating Backups

If replication and the backup process are working properly, we can write a simple shell script to stop replication, back up the data on the slave server, and start the slave again. Such a shell script would look something like this:

#!/bin/sh

date = `date +%Y%m%d`

mysqladmin --user=root --password=my_pwd stop-slave

mysqldump --user=root --password=my_pwd --lock-all-tables \
      --all-databases > /backups/mysql/backup-${date}.sql

mysqladmin --user=root --password=my_pwd start-slave

In this example, we're using the mysqladmin utility to stop and start replication on the slave. On the first line, you may have noticed that we're capturing the date using the system function date and putting it into a good format (e.g., 20050615). This variable is used with mysqldump in the script for altering the name of the dump file each day. Of course, you can set the file path and the name of the dump file to your preferences. Notice that the date function and the formatting codes are enclosed in back-ticks (`), not single quotes (').

This is a simple script. You may want to write something more elaborate and allow for error checking. You probably would also want to compress the dump files to save space and write them to a removable media like a tape or CD. Once you have your script set up, test it. If it works, you can add it to your crontab or whatever scheduling utility you use on your server.

Conclusion

Replication is a useful administrative feature of MySQL. It's an excellent way to be assured of good regular backups of databases. There are more options and SQL statements available for replication than I was able to present here. I cover them individually in my book MySQL in a Nutshell. For active and large systems, you may want to set up more than one slave server for added protection of data. The configuration and concepts are the same for multiple slaves as it is for one slave. For extremely active and large databases, you might want to consider purchasing software like that offered by Emic. Their software costs a bit, but it does an excellent job of handing slave serves for backups and load balancing, especially.

In May 2005, O'Reilly Media, Inc., released MySQL in a Nutshell.

Sample Chapter 6, "Date and Time Functions" (PDF format), is available free online.
You can also look at the Table of Contents, the Index, and the full description of the book.
For more information, or to order the book, click here.

Russell Dyer has worked full-time for several years as a free-lance writer of computer articles, primarily on MySQL.

Return to ONLamp.com.

Questions for Russell? Ask them here
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 18 of 18.

The server is not configured as slave
2009-01-21 20:51:47 Wes_Reneau [Reply | View]

I realize this is an older thread but I used it to setup replication on Ubuntu 8.04.

I followed the directions and when I typed 'start slave', I got the following error:

The server is not configured as slave

Did some gooling to find http://blog.taragana.com/index.php/archive/solution-mysql-master-master-replication-fails-with-error-1200-hy000-on-master/ post.

You have to do this, or at least I did.

To rectify it locate and delete the master.info file and restart the mysql server. Now try to start slave and it should work. In short on my machine I did:
rm /var/lib/mysql/master.info

Lock tables
2007-12-19 17:54:06 baysky [Reply | View]

Instead of setting max_connections to 0 and kill sessions.
You could issue
FLUSH TABLES WITH READ LOCK;

to luck tables and in seperate screen do mysqldump.

Don't close out the session you issued FLUSH until dump is done. The lock will be released if session end.

The command to resume write is
UNLOCK TABLES;

intial dump of master database
2006-09-14 13:36:34 wzhao68 [Reply | View]

Hi there,

Is there an alternative to dump the master database for replication to slave server? Our database is kinda big, and there are tables over 2GB in size.
Thanks,

David

intial dump of master database
2007-12-19 17:48:16 baysky [Reply | View]

Another alternative is making a file system copy of your database bring over to slave. and of course that need to shutdown your database.

Backup script
2005-12-22 07:27:41 aquariusrick [Reply | View]

I noticed a problem on RedHat and Fedora with the following command:
date = `date +%Y%m%d`

It should read:
date=`date +%Y%m%d`

Having a space on either side of the '=' gives an error and doesn't give the desired effect.

This page has helped me a great deal. I read all the documentation re: replication on the MySQL website, and still had some questions on how to perform this very task. This is a real nice script given how simple and effective it is.

I think the coolest find in here for me was the mysqldump --master-data option when setting up the slave. I will also use this regularly when I backup the slave, that way if the master goes down and I need to reverse roles on the master and slave, having that option set should help get things back up to speed on the master a bit quicker. At least I hope!

Replication Problems
2005-11-14 08:41:13 scandog [Reply | View]

I have gone through your tutorial but have ended up with problems. When I do a mysql dump I don't get any lines for bin file or start position. Also when I upload the sql statement to the slave it remove all the slave user info I added. I would think you wouldn't want to export the mysql database. What is the point of adding a mysql replicant user that points to the master if your going to over write it with the master dump file. Also my servers have different names, and after dumping the file and adding it to the slave I can't access the database from my database software because the host names have changed.

I would also assume that you would have to restart the mysql servers after altering the my.cnf info, am I correct?

Thanks for any help you can give.

Replication Problems
2005-12-22 07:51:58 aquariusrick [Reply | View]

It works really nicely the way the article describes if you create the replicant user without a specific host name, instead create it as 'replicant'@'%'. If you change your root account to 'root'@'localhost' you should be able to replicate the mysql database to the slave without causing problems when you overwrite it with the master dump file.

(for those that MAY not know that @'%' and @'localhost' means, '%' is for ANY host and 'localhost' is for the logging in from the box itself only)

If you think you may ever have more than one slave, I'd recommend doing it this way, that way all slaves could become the master with no required changes to the user table. The only command needed to change master on the slaves would be 'CHANGE MASTER TO'.

This way if you have more than one slave you can promote any of the slaves to become a master and all remaining slaves can be pointed to replicate from the new master easily.

If you really want to have a different set of user accounts on the slave than on the master then I'd recommend using the --ignore-table=mysql.xxxx for each table you want to ignore replication.

Remember the whole point of this article is to set up replication for a working backup solution. So if you have different user accounts on the slave when you perform the backup, you'll need to have some way of restoring the user accounts associated with the master before you can call it a complete backup solution.

Replication Problems
2005-11-26 04:31:34 Russell Dyer | [Reply | View]

Sorry for the delay in responding--I've been on the road and all, and dealing with hurricanes and what not. In case you haven't resolved this yet, though, here are some suggestions.

Regarding the lines to include the binary file name and the starting position, the --master-data option with mysqldump should put these at the end of the dump file. If not, you can manually run them on the slave:

CHANGE MASTER TO MASTER_LOG_FILE = 'bin.000001';
CHANGE MASTER TO MASTER_LOG_POS = 4;

You can get the correct values for your server by entering this statement on the master:

SHOW MASTER STATUS;

Regarding the user information, if there are already users on the slave--meaning it's not going to be used just for backups--and for security purposes, you probably should add the --ignore-table=mysql.users option to the mysqldump command when backing up the master. This will exclude the users table and thereby the passwords from the dump file. This should take care of the host name problem you mentioned related to the users, as well. If you utilize the other tables in the mysql database, since they contain references to users and hostnames, you should add them to the mysqldump line. You will have to add a separate key/value pair for each: --ignore-table=mysql.xxxx for each. I should have included all of this in the article.

Let me make two more clarification points to my article: don't start the slave replicating until you have the data copied or else it will try to start replicating before you can get the dump file unloaded. The problem is that you need to have MySQL running on the slave so that you can add the dump file. To get around this, after you have the slave configured initially, restart the mysqld daemon (or mysqld_safe) with the --skip-slave-start option. Actually, put that option in the my.cnf file to be safe. Just delete it after replication is running.

There is another point I'd like to add now that I'm reading your comments and a couple others: if your master has been binary logging for quite a while before you tried setting up replication, unless you have use for the logs, you might want to start fresh by issuing a RESET MASTER statement on the master. This will delete all of the binary log files and it will commit any outstanding transactions (e.g., on InnoDB and BDB tables), so be sure that you want to do this. You might want to make an extra backup of the databases and the binary logs before you reset the master, by the way. After you reset the mater, make your backup and copy it to the slave server while replication isn't running. Then start the slave with the START SLAVE statement. Starting fresh makes it easy to be assured of a good clean start. Check the SHOW PROCESSLIST; on both servers to see the states of each. Also, run SHOW SLAVE STATUS; on the slave to see if everything looks okay. It will list the last error number and message if replication failed after starting. Check the error logs for clues if there's a problem.

How can I change the original master to be the new slave when it comes back up
2005-08-31 16:15:45 sylcheung [Reply | View]

Hi,

Thanks for the article. But can you please tell me How can I change the original master to be the new slave when it comes back up?

My understanding is when the old master comes back up, it will configure as a "master" (because that is what its configuration said). But the old slave is now the "master", wouldn't this beomes a dual master scenario? Can you please tell me how to resolve that?

Thank you.
Sam

Partial replication
2005-07-28 12:33:56 Andrew_ [Reply | View]

After reading the entire article I still have a question in mind. I’ll explain you my setup and could you could tell me if it’s something possible to do replication in these conditions?

1- One MySQL server in production environment (with 4 databases in it)

2- One MySQL in development environment which contains more databases than the production server (7 instead of 4)

I would like to replicate my production server on my development server to be able to take a backup without shutting down my prod server but I still want to use my three other development databases. So does the two servers need to have the same number of databases in it or it doesn't matter?

From my point of view, it shouldn’t work because it uses the master’s binary log but I would like to know your opinion on that guys.

Thank you

What about when disaster strikes?
2005-06-22 04:20:42 tknaps [Reply | View]

This was generally a good read, but as with many articles on backup I miss the really important part: if your database goes corrupt and you need to restore a backup from the replicating server over to the master server - what then?

For instance there will undoubtly be a log of SQL statements since the last backup that have executed and eventually lead to corruption. Most of these statements are probably valid up to the one that caused corruption (if it wasn't hardware failure). Some questions that arise:

- After restoring the backed up database on the master server, can you execute a certain amount of these statements to try to determine which one caused the corruption?

- And can you then run the logged statements up to that point to minimize data loss?

- After doing that, how do you remove the statements that caused corruption and get the replicating slave back up and running properly?

I think for an article like this to be really usefull you need to provide the reader with information on how to do recovery and optionally/preferably a method for testing that your backups are sane (a step by step test-recovery of your databases for instance).

What about when disaster strikes?
2005-07-08 10:49:23 ugob [Reply | View]

A way to manage that is use the opportunity of having a slave to perform backups on it frequently. This way, you will probably be able to have a backup of a working version of your DB.

bi-directional replication
2005-06-20 15:39:46 sakshale [Reply | View]

I have two web servers. Both run a server management system based on MySQL databases. (So, they both have databases with the same name, but different content.) I currently back them up with a nightly mysql dump of each individual database, without taking down the servers. Could they (easily) be configured to use replication to back each other up, without the database name collisions being an issue?

bi-directional replication
2005-07-08 10:50:40 ugob [Reply | View]

I don't think a master-master relationship is supported at this time. Maybe in new, future release. I read somewhere that it is possible, but unsupported by MySQL team.

Replica requirements
2005-06-17 02:38:12 leeg [Reply | View]

Thanks for this article - very timely as far as I'm concerned as I'm about to put a MySQL-based app live :-)

What sort of performance does the replica server need relative to the master? It looks like the updates to the replica DB can happen asynchronously as another thread is polling the logfile, so perhaps it doesn't need to be an identical box to the master. But where does the point come that the backup is compromised because the replicant can't keep up?

Replica requirements
2005-07-08 10:53:49 ugob [Reply | View]

If your slave is dedicated, it doesn't have to be very powerful, as it only receives Updates + Inserts.

Replica requirements
2005-07-29 06:39:58 leeg [Reply | View]

Thanks for this article; I now have a replica of my production DB :-) However, I was using OS X (I suppose you'd call my server DAMP rather than LAMP) and had to change a couple of things. There's no /etc/my.cnf by default, you have to copy one from somewhere (there's a sample file at /usr/share/mysql/my-medium.cnf). Even so, once you've edited the files as described in this article you still explicitly have to run the 'CHANGE MASTER TO' SQL statement before you can 'START SLAVE'.

Replica requirements
2005-11-26 04:55:22 Russell Dyer | [Reply | View]

I'm responding pretty late, but for future reference for others, here's my response.

You are correct. The lines I presented in the article to set the values for connecting to the master are only used one, when you first restart the slave server. They are copied to the master.info file, which is by default located in the data directory on the slave. Once the master.info file has the connection information stored in it, the server will ignore these configuration settings or options in the my.cnf file and even at the command-line when the server is restarted. The only way to change them after the master.info file is created is to edit the file--which is not a very good idea--or to use the CHANGE MASTER TO statement that you mentioned. Therefore, to adjust my article, you may as well not include them in the my.cnf file to begin with. Instead, enter them from the slave while logged into MySQL as root or the like:

CHANGE MASTER TO MASTER_HOST = 'master_host';
CHANGE MASTER TO MASTER_PORT = 3306;
CHANGE MASTER TO MASTER_USER = 'replicant';
CHANGE MASTER TO MASTER_PASSWORD = 'my_pwd';

You only need to execute these statements one time to set them since they're stored in the master.info file. They'll survive restarts. If you want to change something like the host name, though, then rerun just the statement with that particular parameter (i.e. MASTER_HOST).

Regarding MacIntosh, although my servers have Linux on them, my laptop now is an iBook G4. Yes, you're right: by default it is set up without the my.cnf file. You can copy some of the sample files like you did, or you can create a new my.cnf file with just the group headings (e.g., [mysqld]) and options that you need with a simple text editor like vi while logged in as root (i.e., su)from a terminal window.