• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Examples of mysqldump partial backups

by admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to List Users and Privileges in MySQL
  2. Can MySQL Cluster Run Multiple Nodes On a Single Server
  3. How To Restore an NDBCluster Backup Using ndb_restore
  4. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  5. How to Rollback RPM Upgrade of the MySQL Server
  6. MySQL : Stored Procedure For Rotating the Audit Log
  7. How to Configure Multiple MySQL Servers On One System Using mysqld_multi
  8. Beginners Guide to Management of MySQL Cluster Log Files
  9. MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
  10. How to install and configure MySQL sys schema

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright