Additional languages
« 5.9 Backup and Recovery
5.9.2 Example Backup and Recovery Strategy »
Section Navigation      [Toggle]

5.9.1. Database Backups

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 13.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 13.5.5.2, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.

To make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE. For this statement, the output file cannot already exist because allowing files to be overwritten would constitute a security risk. See Section 13.2.7, “SELECT Syntax”.

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script. See Section 8.13, “mysqldump — A Database Backup Program”, and Section 8.14, “mysqlhotcopy — A Database Backup Program”.

  1. Create a full backup of your database: 

    shell> mysqldump --tab=/path/to/some/dir --opt db_name

    Or: 

    shell> mysqlhotcopy db_name /path/to/some/dir

    You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM tables.)

  2. Stop mysqld if it is running, then start it with the --log-bin[=file_name] option. See Section 5.11.3, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 8.13, “mysqldump — A Database Backup Program”.

MySQL supports incremental backups: You need to start the server with the --log-bin option to enable binary logging; see Section 5.11.3, “The Binary Log”. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 8.13, “mysqldump — A Database Backup Program”, and Section 8.14, “mysqlhotcopy — A Database Backup Program”.

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

If you have to restore MyISAM tables, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. Note that it works only if you have enabled binary logging by starting MySQL with the --log-bin option.

  1. Restore the original mysqldump backup, or binary backup.

  2. Execute the following command to re-run the updates in the binary logs: 

    shell> mysqlbinlog binlog.[0-9]* | mysql

    In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements). See Section 8.11, “mysqlbinlog — Utility for Processing Binary Log Files”, for more information on the mysqlbinlog utility and how to use it.

You can also make selective backups of individual files:

  • To dump the table, use SELECT * INTO OUTFILE 'file_name' FROM tbl_name.

  • To reload the table, use LOAD DATA INFILE 'file_name' REPLACE .... To avoid duplicate rows, the table must have a PRIMARY KEY or a UNIQUE index. The REPLACE keyword causes old rows to be replaced with new ones when a new row duplicates an old row on a unique key value.

If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Section 6.1, “Introduction to Replication”.

If you are using a Veritas filesystem, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.


User Comments

Posted by Bryan Rentoul on September 28 2003 8:35pm[Delete] [Edit]

mysqlhotcopy, etc is great - but using it (and most other myql automation scripts) requires placing a user/password on the command line for all/some to see (ps axw)

There doesn't appear to be a way to place the user/pass into a file somewhere and specify only that (secured) filename on the command line.

I get around this in the case of mysqlhotcopy by taking a local copy of the script (perl) and hard-coding the auth info into that copy thus:

mysqlhotcopy - line 164ish:

my $dbh = DBI->connect("dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy",
'backup_user', 'backup_password'},


and again at around line 745:

my $dbh = DBI->connect("dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy",
"backup_user", "backup_password",

then, just to be sure,

chown root.nobody mysqlhotcopy
chmod 700 mysqlhotcopy

If anyone has a more elegant method for this I'd be glad to hear it.


Posted by Bryan Rentoul on June 7 2006 11:02pm[Delete] [Edit]

I have found that the minimal set of privileges to operate mysqlhotcopy is:

Reload Grants
Alter Tables

Nothing else is required. (I'm not at all sure why Reload grants is required - but it is <shrug>)

This is particularly useful information for those backing up multiple databases using a single 'backup' user for example. (Assuming we're at all security conscious that is :)

EDIT: This changed in MySQL v4.x. See comments below

Posted by Ricardo Garcia on January 13 2004 9:57am[Delete] [Edit]

If you wished there was a mysql-command-server-side version of mysqldump, don't forget this command!

SHOW CREATE TABLE `X`;

which gives you a dump of the create table statement, for use in conjunction with SELECT INTO OUTFILE. This way you can back up your entire table and definitions . It takes a little non-mysql programming work, but hey, it works! :)

Posted by Dan Mahoney on February 7 2004 10:48am[Delete] [Edit]

According to the mysqlhotcopy page, mysqlhotcopy will read from the .my.cnf options file under the [client] subheader.

Thus, if you have:

[client]
username=root
password=something

That should do it.

Posted by dave mausner on June 8 2004 6:41pm[Delete] [Edit]

in a windows batch file, you may find that the OUTFILE of a SELECT ... INTO does not seem to exist immediately after the MYSQL command terminates. this is apparently because the OUTFILE is *eventually* created by the server, not the MYSQL client. therefore, the command after MYSQL may fail to find the anticipated OUTFILE!

the solution is to create a loop like this:

mysql -u user database <backup.sql
:wait
if not exist outfile.txt goto wait

Posted by Sad Sunray on September 3 2004 6:16pm[Delete] [Edit]

This is what I did restoring my backup (I just copied the files from /usr/lib/mysql and moving them to /mysql for backup... I know my script is not perfect but it works.. *g
Note: this is linux

cd /mysql
dbs=`ls -1`
for db in $dbs
do
mysqladmin -p create $db
cd $db
tables=`ls -1 | grep ".frm"`
for table in $tables
do
n=$(($((`echo $table | wc -c`))-5))
b=`echo $table | head -c $n`
todo="RESTORE TABLE $b FROM '/mysql/$db'"
echo $todo | mysql $db
done
cd ..
done

Posted by John Doyle on January 12 2005 3:07am[Delete] [Edit]

I tried all above and none worked.

The easiest and quickest way ive found is to simply copy the database folder from the data folder and paste it into the new mysql instance.

Took 2 minutes at the most and ive not had any problems!

Windows server 2003 BTW but im pretty sure its possible with *nix

Posted by Martin Och on March 26 2005 12:45am[Delete] [Edit]

Yes, but what about with data not writen in HDD - with data in cache?
The better way is to stop mysqld first, copy files, and start mysqld.

Posted by yk on August 30 2005 3:17pm[Delete] [Edit]

Yeah, but if your data directory is a terabyte or so, it might be a pain to click-and-drag it around to make backups; dumping the databases' contents to another area might be just as fun.
I guess that a smallish script to write-lock and flush all existing tables, making sure to unlock everything only when the backup process is finished, or when the backup window is over, is the only solution for large-ish databases that need maximal uptime.
Then again keeping in sync with the backup process safely could take a little thinking ;-)

Posted by Bill Marrs on December 20 2005 3:21pm[Delete] [Edit]

I disagree with Bryan Rentoul's post about "the minimal set of privileges to operate mysqlhotcopy ... Reload Grants
Alter Tables"

I found that the minimal set is:

SELECT, RELOAD, LOCK TABLES

(ALTER is not required, but SELECT and LOCK TABLES are)

Posted by dennis guba on March 24 2006 6:01pm[Delete] [Edit]

You can also take very fast online or hot backups if you have linux volume management or LVM. You must have snapshots enabled. Basically follow the recipie given for veritas except use lvm. Of course your DB must live on a logical volume like /dev/vg01/mysql for example.

In a mysql shell (as root@unix and root@mysql):

mysql> flush tables with read lock;
mysql> flush logs;
mysql> system lvcreate --snapshot –-size=500M --name=backup /dev/vg01/mysql;
mysql> unlock tables;

Then back in shell land (as root@unix):

$ mount -o ro /dev/vg01/backup /mnt/tmp
$ cd /mnt/tmp/
$ tar czf backup-`date +%Y%m%d`.tgz mysql
$ umount /mnt/tmp
$ lvremove -f /dev/vg01/backup

Posted by Bryan Rentoul on June 7 2006 11:00pm[Delete] [Edit]

Bill Marrs on December 20 2005 4:21pm wrote:

> I disagree with Bryan Rentoul's post about "the minimal set of privileges to operate mysqlhotcopy ... (ALTER is not required, but SELECT and LOCK TABLES are)

Right you are. I had to make this change after upgrading to MySQL v4.x. I now use SELECT, RELOAD and LOCK TABLES.

Previously I did not want a would-be cracker with access to the backup user to extract data from tables using SELECT. Using ALTER but not SELECT acheived this without allowing alterations due to lack of other priviledges. With MySQL 4.x this no longer works. SELECT is wide open. Oh well. *shrug*

Posted by Dave Yost on July 27 2006 5:53am[Delete] [Edit]

The instructions on this page are way, way too complicated. There should be something simple like this:

cd mysql
support-files/mysql.server pause
rsync -ar data/ /tmp/mysqlbackup
support-files/mysql.server start

Such a pause command would flush out consistent data to all of the files, then return, and it wouldn't require a password to be stored in the script that invokes it. The start command should be expanded to either start the server from scratch as it does now or if the server is paused, to resume it. (There shouldn't be a separate resume command.)

Posted by James Marshall on September 13 2006 12:12am[Delete] [Edit]

> There doesn't appear to be a way to place the user/pass
> into a file somewhere and specify only that (secured)
> filename on the command line.

You can put the password in a file with mode e.g. 0600, and then cat the file into the command line to log in. For example:

shell> mysql -p`cat password.txt`

You can also read the password file from within a script, so that you can reveal/distribute the script without revealing the password.

Posted by Daniel Grace on December 1 2006 9:14pm[Delete] [Edit]

>You can put the password in a file with mode e.g. 0600, and >then cat the file into the command line to log in. For >example:
>
>shell> mysql -p`cat password.txt`

This won't work -- the resulting command line in 'ps' still shows the command line with the password.

Perhaps something more along the lines of cat path/to/supersecretpassword queries-to-run.sql | mysql -ubackups -p .... would be more appropriate, with the appropriate permissions on path/to/supersecretpassword.

Add your own comment.