mysqldump

mysqldump is a tool for creating backups of MySQL databases in the form of a .sql file. This chart helps set up a cronjob or one-time job to backup a MySQL database with mysqldump into a Persistent Volume. You can specify an existing PVC, or helm will create one for you.

Overview

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another database server (not necessarily MariaDB or MySQL). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

If you are doing a backup on the server and your tables all are MyISAM tables, consider using mysqlhotcopy instead because it can accomplish faster backups and faster restores.

mysqldump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, --routines) and --events). Procedures and functions are however also part of the system tables (for example mysql.proc).

Performance

mysqldump doesn't usually consume much CPU resources on modern hardware as by default it uses a single thread. This method is good for a heavily loaded server.

Disk input/outputs per second (IOPS), can, however, increase for multiple reasons. When you back-up on the same device as the database, this produces unnecessary random IOPS. The dump is done sequentially, on a per table basis, causing a full-table scan and many buffer page misses on tables that are not fully cached in memory.

It's recommended that you back-up from a network location to remove disk IOPS on the database server, but it is vital to use a separate network card to keep network bandwidth available for regular traffic.

Although mysqldump will by default preserve your resources for regular spindle disks and low-core hardware, this doesn't mean that concurrent dumps cannot benefit from hardware architecture like SAN, flash storage, low write workload. The backup time would benefit from a tool such as MyDumper.

Usage

There are three general ways to invoke mysqldump:

  • shell> mysqldump [options] db_name [tbl_name ...]
  • shell> mysqldump [options] --databases db_name ...
  • shell> mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

mysqldump does not dump the INFORMATION_SCHEMA database by default. In all versions of MariaDB and in MySQL 5.1.38 and higher, mysqldump dumps INFORMATION_SCHEMA if you name it explicitly on the command line, although currently you must also use the --skip-lock-tables option. Before MySQL 5.1.38, mysqldump silently ignored INFORMATION_SCHEMA even if you named it explicitly on the command line.

 

Option groups

Some mysqldump options are shorthand for groups of other options:

Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default.

Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset options.
To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

To select the effect of --opt except for some features, use the --skip option for each feature. To disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (Actually, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)

To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.
When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

Tell us about a new Kubernetes application

Newsletter

Never miss a thing! Sign up for our newsletter to stay updated.

About

Discover and share new Kubernetes applications

Navigation