The Geek Diary

CONCEPTS | BASICS | HOWTO

Examples of mysqldump partial backups

By

Depending on whether you want a subset of databases or tables within a single database, there are various options to create the backup.

Selecting the Databases and Tables to Include In the Backup

In general the following is included in the backup:

  • The CREATE TABLE statements for each table included in the backup
  • INSERT statements for the data
  • Triggers on the included tables
  • If more than one database is backed up a CREATE DATABASE and a USE are included; but not for single database backups!

The following examples assume that ~/.my.cnf contains the options required to connect to MySQL and other options that will be used with mysqldump (see also further down).

Single database

This is the default for mysqldump. Without extra options, a CREATE DATABASE IF NOT EXISTS and a USE statement will not be included in the backup.

mysqldump ... > backup.sql

For example to backup the world sample database:

mysqldump --defaults-file=~/.my.cnf world > backup_world.sql

Single table within a database

This works the same way as for a single database, but just requires one additional argument with the name of the table:

mysqldump ... > backup.sql

For example to backup the City table from the world sample database:

mysqldump --defaults-file=~/.my.cnf world City > backup_world_City.sql

Multiple databases

This can be archived using the –databases option. CREATE DATABASE and USE statements are included in the output before each new database.

mysqldump ... --databases

For example to backup the world and sakila sample databases:

mysqldump --defaults-file=~/.my.cnf --databases world sakila > backup_world_and_sakila.sql

Multiple tables in the same database

Use the –tables option. With this option the first argument is the name of the database and the rest are the names of the tables to backup. Without extra options, a CREATE DATABASE and a USE statement will not be included in the backup.

mysqldump ... --tables

For example to backup the actor and film tables in the sakila sample database:

mysqldump --defaults-file=~/.my.cnf --tables sakila actor film > backup_sakila_actor_and_film.sql

Ignore a table

Use the –ignore-table option to ignore a table. Specify the option multiple times to exclude more than one table from the backup.

For example to exclude the actor and film tables in the sakila sample database that otherwise includes all databases and tables (the –all-databases option):

mysqldump --defaults-file=~/.my.cnf --ignore-table=sakila.actor --ignore-table=sakila.film --all-databases > backup_excluding_sakila_actor_and_film.sql

Only Include Some Rows of a Table

Use the –where=… option to specify a WHERE clause to apply to the tables included in the backup. Note that the same WHERE clause will apply to all tables included in the same backup.

For example if you are backing up the City table of the world sample database, and you only want to include those with CountryCode = ‘USA’, you can do this like:

mysqldump --defaults-file=etc/my.cnf --tables --where="CountryCode = 'USA'" world City > City_USA.sql

Locking

When you create a partial backup, you also need to consider which level of locking you need. The options are the same as for a full backup:

By default mysqldump read locks one database at a time. However there are other options:

1. –single-transaction – This is the preferred locking option if you are backing up InnoDB tables. mysqldump will start a transaction with a consistent snapshot and create the backup inside this transaction.

This means:

  • All data is consistent, i.e. it corresponds to the same point in time.
  • No locks are required (unless you also use –master-data to get the binary log file and position of the backup in which case a read lock is briefly required at the beginning while the master status is obtained).
  • However if non-InnoDB tables are included in the backup and changes are made to these, the backup will not be consistent.
  • Particularly if the backup takes a long time the read view created by the backup can cause overhead.

2. –lock-tables – This is equivalent to the default. I.e. for each dumped database, lock all tables to be dumped before dumping them.

3. –lock-all-tables – This read locks all tables in the whole instance (whether the table is included in the backup or not) for the duration of the whole backup.

Note that these three options are mutually exclusive.

Other Options

Some of the additional options useful when creating a partial backup with mysqldump are:

1. –add-drop-database, –add-drop-table, –add-drop-trigger
2. –hex-blob: Dump binary columns using hexadecimal notation (for example, ‘abc’ becomes 0x616263). If you have binary data it is important to include this option.
3. –master-data: This will include a CHANGE MASTER TO statement with the binary log file and position where the backup was created. With –master-data=2 the CHANGE MASTER TO statement will be inside a comment so not automatically executed when the backup is restored.
4. –no-create-db: Do not include CREATE DATABASE statements (in case of backing up multiple databases).
5. –no-create-info: Do not include the CREATE TABLE statements.
6. –no-data: Do not include any data. This is useful for exporting the schema.
7. –routines: Includes stored procedures and stored functions from the databases backed up.
8. –tab=…: Produce tab-separated data files. Note that there will be one file per table named after that table. This is similar to SELECT … INTO OUTFILE … and the resulting data dumps can be loaded with LOAD DATA [LOCAL] INFILE. Note that all files will be created in the same directory irrespectively of which database the table exists in, so this option is mostly useful when only tables from a single database are backed up.

There are also several options to specify the format of the data files.
–triggers: Include triggers for the tables included in the backup. This is done by default. Use –skip-triggers if you do not want to include the triggers.

You May Also Like