Brian Cryer

 

Cry MySQL How to...

Backup a MySQL database

These notes cover different ways to perform a backup of a MySQL database.

Backup Individual Tables (MyISAM)

These notes use "backup table" which should be considered as depreciated and may be completely removed in MySQL 5.2. For a more future proof method see "How to backup individual tables using mysqldump".

To backup an individual table the sequence is to lock the table, flush any unwritten data, copy the table and then remove the lock. This can be accomplished using the following script:

lock table table-name read;
flush table table-name;
backup table table-name to 'folder';
unlock tables;

Where "table-name" is the name of the table to be backed up and "folder" is the name of the folder to which the backup copy will be written.

Note:

  • This script only places a read lock on the file, so other processes can read from the table during the backup - but will block if they need to update rows in the table.
  • The folder must not contain a prior backup of the table, or the backup will fail. The error returned in this event will be "Failed copying .frm file (errno: 17)"
  • Remember that the folder must exist and it must exist on (or be accessible from) the database server. The error returned if the folder does not exist will be "Can't create/write to file ... (Errcode: 2)".
  • Slashes need to be "escaped", so to backup the table "accounts" to "c:\table_backups" would require the line:
    backup table accounts to 'c:\\table_backups';
  • If you use this with a database engine other than MyISAM (for example with InnoDB) it will not give an error but will fail to generate the backup.

To restore a table use:

restore table table-name from 'folder';

be aware that the restore will fail if a table already exists with that name.

How to backup individual tables using mysqldump

To backup an individual table from the command line, use mysqldump. The syntax is:

mysqldump --user=account --password=password --host=host database table > backup-file.sql

Where:

account
is the account to connect to the database, "password" is the password to us, "host" is the name of the server on which the database is located,
password
the password to use
host
the name of the server on which the database is located. This can be omitted if you want to connect to MySQL on the current host.
database
The name of the database that contains the table.
table
The name of the table to backup.
backup-file.sql
The name of the backup file to generate. This will be populated with the SQL to create the table and data.

In this instance mysqldump will generate the SQL to create the table followed by SQL to populate the data that is currently in the table.

Since it is SQL which is generated, the table can be restored by simply replying the SQL, for example:

mysql --user=root --password=secret --database=dbname < backup-file.sql

Be aware that this will drop the existing table and recreate it (complete with data). If you don't want the table dropped then you will need to edit the sql contained in backup-file.sql.

Backup an Entire Database - To a Single File

To backup an entire database (or all databases) use the "mysqldump" utility (which is run from the command line):

mysqldump --user=account --password=password --host=host --flush-logs --single-transaction database > backup-file

Where "account" is the account to connect to the database, "password" is the password to use, "host" is the name of the server on which the database is located, "database" is the name of the database to backup and "backup-file" is the name of the sql file that will be generated.

You can omit the "--host=host" bit if you want to backup the mysql database on the local computer.

If you omit the name of the database then all the databases will be backed up.

What mysqldump does is to generate SQL to recreate the database, all the tables and all the records in those tables. This is the reason why the output is piped to a file. mysqldump supports many other options and its worth consulting the manual for these - for example you can use it to dump/backup individual database tables (using --tables followed by a list of tables).

mysqldump takes a number of other options (for example it can also be used to backup a single table), and it may be worth consulting the documentation for other options (of which there are many).

To restore from this backup simply apply the SQL that mysqldump generated. For example:

mysql --user=root --password=secret --database=dbname < path\to\dbbackup.sql

Backup an Entire Database - Separate Files per Table

As an alternative to generating a single output file, mysqldump can also be used to generate separate files for each database table. The syntax in this case is:

mysqldump --user=account --password=password --host=host --flush-logs --single-transaction database --tab=backup-folder

This is similar to the previous example except that mysqldump will generate backup files in the "backup-folder" specified. In this case two files will be generated for each table, a .sql and a .txt file both named after the name of the table:

.sql
The sql file contains the sql to recreate the table, but it does not contain any data.
.txt
The txt file contains the data for the table, in tab delimited format.

An important point to be aware of is that the .sql file is generated by mysqldump whereas the .txt file is generated by mysql. So the folder location needs to be one which mysql can find and has permissions to write to. (So for example if you are backup up to "C:\Backups" and MySQL is on a different machine then you risk having the .txt files generated on the server and not locally on your pc.)

To restore from such a backup you will need to use a combination of mysql and mysqlimport - mysql to run the scripts to recreate the tables and mysqlimport to import the data. So, to recreate each table:

mysql --user=account --password=password --database=dbname < path\to\table.sql

this will need to be done for each table. Remember that the .sql files created only contain the table definition, not the data and for the data you need to use mysqlimport the syntax of which is:

mysqlimport --user=account --password=password db_name table.txt [table2.txt]

Don't be tempted to change the names of the .txt files because mysqlimport uses the name of the file to determine which table to import into. You can specify multiple txt files to import.

As you can see when performing a restore this technique is more cumbersome than using the single file backup approach, but it does have its uses. Whichever technique you use (and unless you have a good reason otherwise I would advocate the single file approach) be sure to test both your backup and restore.

These notes have been tested against MySQL version 4.1, 5.0 and 5.1.