When MySQL 5.7.8 was released it had a new trick up its sleeve: a new utility that performs logical backups – mysqlpump – was introduced. The utility enables one or more MySQL databases to be backed up or transferred to another database server.
mysqlpump Prerequisites and Features
In order to use mysqlpump, make sure that the following privileges are acquired on the user that you use the tool with:
SELECTfor dumped tables – the option is also required to dump user definitions on the MySQL database;
SHOW VIEWfor dumped views;
TRIGGERfor dumped triggers;
LOCK TABLESif the –single-transaction option is not used
The feature list of mysqlpump includes:
- Faster dump process due to the fact that the databases and objects within them can be processed in parallel;
- The ability to choose dump contents (i.e databases, stored programs, and user accounts);
- User accounts can be dumped as MySQL CREATE USER and GRANT statements;
- InnoDB secondary indexes are created quicker due to the fact that they are added after the data has been loaded into the database;
- Compressed output can be created;
- A dump process indicator is included.
Mysqlpump can be invoked by typing mysqlpump into the console. By default, the tool dumps all databases and its behaviour is the same as typing mysqlpump –all-databases into the console. Here’s a basic example (the following query will dump a database called dbname into a backup file called backup.sql):
$ mysqlpump --user=root --password --databases dbname > backup.sql
Output can be forwarded to a file in two ways – either by creating a file using the “>” parameter (for example > backup.sql) or by using the –result-file parameter. The usage of the “>” parameter creates a file that has UTF-16 encoding, –result-file creates the output in ASCII format.
The tool can be used in a variety of ways, and here’s a few of them:
- To dump a specific database, use mysqlpump like so:
$ mysqlpump db_name
where db_name is the name of the database;
- To dump multiple databases, use the –databases parameter like so:
$ mysqlpump --databases db_name1, db_name2...
- To dump specific tables within specific databases, use:
$ mysqlpump db_name table_1, table_2…
where db_name is the database name and table_1, table_2 are the table names;
- To exclude specific databases from being dumped, provide the –exclude-databases parameter:
$ mysqlpump --exclude-databases=db_name
The % sign can also be used to specify that all databases should be excluded (see example below);
- To dump only the account definitions (CREATE USER and GRANT statements) excluding databases, use the –users option:
$ mysqlpump --users --exclude-databases=%
- To only export metadata about certain databases (skip all data associated with the tables within certain databases), specify the –skip-dump-rows option:
$ mysqlpump --databases db_name1 db_name2 --skip-dump-rows --user=root --password > backup.sql
- To dump all tables except for a table with a certain name:
$ mysqlpump --user=root --password --exclude-tables=tablename --result-file=backup.sql
- To dump all tables except for a table with a certain name pattern (the following query would dump all tables from all databases except for tables matching the “demo_” pattern: demoa, demo1 etc. – one underscore represents one character):
$ mysqlpump --user=root --password --exclude-tables=demo_ --result-file=backup.sql
- To dump all databases with a certain pattern, use the wildcard parameter % (databases or tables can also be excluded by specifying the –exclude-databases or –exclude-tables parameters):
$ mysqlpump --user=root --password --include-databases=dbname% --result-file=backup.sql
- To dump all users except specific users (for example, excluding the root user):
mysqlpump --user=root --password --users --exclude-users=root
The data can be restored by using the command-line client or another tool like phpMyAdmin.
mysqlpump is a client utility that was introduced to MySQL starting from version 5.7.8. The tool performs logical backups and has native support for parallelization. The utility enables one or more MySQL databases to be backed up or transferred to another database server: in many aspects, mysqlpump is similar to mysqldump, but it also includes a set of new features.