MySQL Reference Manual :: 5.8.1 Database Backups

Search the MySQL manual:

MySQL Reference Manual

Additional languages

Additional formats

Previous / Next / Up / Table of Contents

5.8.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.

If you want to make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE or BACKUP TABLE. For SELECT INTO ... OUTFILE, the output file cannot previously exist. For BACKUP TABLE, the same is true as of MySQL 3.23.56 and 4.0.12, because this would be a security risk. See Section 13.2.7, “SELECT Syntax” and Section 13.5.2.2, “BACKUP TABLE Syntax”.

Another way to back up a database is to use the mysqldump program or the mysqlhotcopy script. See Section 8.8, “The mysqldump Database Backup Program” and Section 8.9, “The mysqlhotcopy Database Backup Program”.

Do 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 simply copy 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 and ISAM tables.)
Stop mysqld if it's running, then start it with the --log-bin[=file_name] option. See Section 5.10.4, “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's possible to perform an online backup that takes no locks on tables; see Section 8.8, “The mysqldump 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.10.4, “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 --flushlogs. See Section 8.8, “The mysqldump Database Backup Program” and Section 8.9, “The mysqlhotcopy 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; see Section 5.10.4, “The Binary Log”.

Restore the original mysqldump backup, or binary backup.
Execute the following command to re-run the updates in the binary logs:
```
shell> mysqlbinlog hostname-bin.[0-9]* | mysql
```
In your case, 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 queries). See Section 8.5, “The mysqlbinlog Binary Log Utility” for more information on the mysqlbinlog utility and how to use it.

If you are using the update logs instead (which is a deprecated feature removed in MySQL 5.0), you can process their contents like this:
```
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
```
ls is used to sort the update log filenames into the right order.

You can also do selective backups of individual files:

To dump the table, use SELECT * INTO OUTFILE 'file_name' FROM tbl_name.
To reload the table, use and restore with LOAD DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, the table must have a PRIMARY KEY or a UNIQUE index. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record 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:

From a client program, execute FLUSH TABLES WITH READ LOCK.
From another shell, execute mount vxfs snapshot.
From the first client, execute UNLOCK TABLES.
Copy files from the snapshot.
Unmount the snapshot.

Previous / Next / Up / Table of Contents

User Comments

Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245

Add your own comment.

Top / Previous / Next / Up / Table of Contents