Conditional or Partial MySQL Dump

Sometimes only a partial MySQL dump is necessary. You may need to export selected tables only, ignore some tables, dump only some rows from a table, etc.

Below, I list mysqldump commands I use often in my work as a web developer.

Contents

Please notice:

  1. All mysqldump commands below export data to the current Linux directory as a gzipped file db.sql.gz or a plain text file db.sql. Please ensure exporting to a non-web-accessible directory, as your database may contain sensitive data.
  2. We assume in this article that MySQL uses socket authentication. So, the commands below do not use -u and -p options. Please see section 11 if your version of MySQL uses password authentication on the command line.

1. Frequently Used Options

Normally, I use these mysqldump options often.

  • -R (--routines) — export stored routines (functions and procedures) (documentation).
    By default, without this option, your stored routines will not be in the MySQL dump file.
  • --no-create-db (-n) — there will be no CREATE DATABASE statement in the file (documentation).
  • --no-create-info (-t) — there will be no CREATE TABLE statements in the file (documentation).
  • --no-data (-d) — there will be no row data in the file (documentation).
  • --lock-all-tables (-x) — locks all tables in all databases (documentation).

Please notice: Normally, you need to add --lock-all-tables only if you are exporting tables from multiple databases. To keep the tables consistent between databases.

This is because mysqldump uses the option --opt by default (unless --skip-opt is used). And --opt includes --lock-tables which locks each dumped database. Making tables consistent inside each database, but possibly inconsistent between databases.

Please add these options to the commands in the next sections if you find it necessary.

2. Export Some Tables Only

mysqldump -R database_name table1 table2 table3 | gzip > ./db.sql.gz

This will export only the tables table1, table2, and table3 from the database database_name. It will also export the stored routines (functions and procedures) due to -R.

3. Ignore Some Tables

mysqldump -R \
--ignore-table=database_name.table1 \
--ignore-table=database_name.table2 \
database_name | gzip > ./db.sql.gz

This will export all tables in the database database_name except for the tables table1 and table2. It will also export all stored routines due to -R.

Here:

  • --ignore-table — omits the specified table from export (documentation).

Please notice: Backslash \ splits a long command into several lines on such operating systems as Linux and macOS. I am using it here to make commands more readable.

I recommend removing the backslashes and linebreaks and changing the command into 1 line, before running it.

If you are on Linux or macOS and you decide to keep the backslashes: There should be no whitespace after \ for them to work. Because the backslash \ is not a special character. It simply escapes the next character – the newline character in our case. Please see this post for more details.

4. Limit the Number of Rows

mysqldump --where="1 limit 200" database_name table1 table2 | gzip > ./db.sql.gz

This will export 200 rows from each of the listed tables, table1 and table2, of the database database_name.

Here:

  • --where — adds the same WHERE clause to every table in the export (documentation).

5. Export Rows Based on a Condition

mysqldump \
--where="date_created >= DATE_SUB(NOW(), INTERVAL 5 day) AND some_text like '%30\%%'" \
database_name table1 | gzip > ./db.sql.gz

This will export all rows of the table database_name.table1 where date_created is not older than 5 days old and some_text contains the substring '30%'.

Here:

  • --where — adds the same WHERE clause to all tables in the export (documentation).

Please see this note on using the backslash \.

6. Export Procedures and Functions Only

mysqldump -R --no-create-db --no-create-info --no-data --skip-opt database_name > ./db.sql

This will export only stored routines (procedures and functions) from the database named database_name.

Here:

  • -R (--routines) — exports stored routines (functions and procedures) (documentation).
  • --no-create-db (-n) — there will be no CREATE DATABASE statement in the file (documentation).
  • --no-create-info (-t) — there will be no CREATE TABLE statements in the file (documentation).
  • --no-data (-d) — there will be no row data in the file (documentation).
  • --skip-opt — turns off settings imposed by the default option --opt (documentation for the option --opt)
    For example, with this option, there will be no DROP TABLE and DROP FUNCTION commands in the dump file.

If you have the error:

1. Go to the MySQL configuration file.
For example, under Ubuntu 22.04 it would be /etc/mysql/mysql.conf.d/mysqld.cnf

2. Add this line under [mysqld]

log_bin_trust_function_creators = 1

3. Restart the MySQL service.

(source)

7. Export the Database Structure Only

mysqldump -R --no-data --skip-opt database_name > ./db.sql

will export the structure of the database database_name. Plus stored routines — due to -R option.

Here:

You can use this to inspect database structure changes when installing WordPress plugins:

  1. Save the database structure before and after installing the plugin.
  2. Compare the DB dump files by content, for example, in Total Commander.

It’s easy to do such checks locally when WordPress is in a Docker container.

8. Export InnoDB Tables Without Locking Them

mysqldump --opt -R --single-transaction database_name | gzip > ./db.sql.gz

This will export InnoDB tables from the database database_name without locking. Plus all stored  procedures and functions due to -R.

Here:

  • --single-transaction — starts a transaction, ensuring consistent export of transactional tables (documentation)

Please also see this discussion.

Please notice: If the database contains both InnoDB and MyISAM tables, only the InnoDB tables will be exported consistently.

If you need to export only some tables, please see the section on exporting selected tables only. This way, you could export only a few InnoDB tables without locking them.

9. Export All Tables Without Locking Them

mysqldump -R --skip-lock-tables database_name | gzip > ./db.sql.gz

This will export all tables from the database database_name without locking. Plus all stored routines due to -R.

Here:

  • --skip-lock-tables — disables locking the database tables (see the documentation for --lock-tables).
    Without this option, the tables would be locked due to the default option --opt.

Please notice: Since the tables are not locked, they may be exported in an inconsistent state. Both, InnoDB tables and MyISAM tables.

Please see the section right above for exporting InnoDB tables consistently without locking.

10. Export to Separate .sql/.txt Files

mysqldump --tab=./mydirectory/ database_name

This will export the database into separate files in the directory ./mydirectory/. File names will match the table names. There will be 2 files for each table:

  1. .sql file with DROP TABLE / CREATE TABLE statements
  2. .txt file with the table data

Here:

  • --tab=./mydirectory/ — defines the path ./mydirectory/ where the exported files will be written (documentation).

If you get the error:

, the simplest solution would be to have files written to the directory allowed by --secure-file-priv:

1. Locate this directory:

mysql
mysql> SHOW VARIABLES LIKE 'secure_file_priv';

Under Ubuntu 22.04 the result would be:

2. Now you can export to the directory you just found:

mysqldump --tab=/var/lib/mysql-files/ database_name

(source)

The other solution would be to change the value of the option --secure-file-priv or disable the option.

11. MySQL Dump with Password Authentication

In this article, we assumed that MySQL uses socket authentication for the root user on the command line. This is normal for modern versions of MySQL.

In this case, mysqldump commands do not contain -uroot -p. To export the whole database and stored routines we would use the command:

mysqldump -R database_name | gzip > ./db.sql.gz

If you have password authentication instead, which was common in older versions of MySQL, the previous command might look like:

mysqldump -R -uroot -p database_name | gzip > ./db.sql.gz

, where

  • -uroot – is for the user root. Replace root with your MySQL username if necessary.
  • -p – makes mysqldump to prompt for a password
    Sometimes also used:
  • -h host_name – a hostname. It needs to be set if not localhost.
  • -P port_number – a port number. It needs to be set if not 3306.

You can change the default MySQL authentication by setting the option default_authentication_plugin in the MySQL configuration file. For example, under Ubuntu 22.04, you can set this option in the configuration file /etc/mysql/mysql.conf.d/mysqld.cnf.

Sergei Korolev
Sergei Korolev
Web developer and the author of all articles on this site. With over 26 years of programming experience, he has specialized in web programming for more than 20 years. He is a Zend Certified Engineer in PHP 5.3 and holds several additional certifications in programming, SEO, web design, UX design, digital marketing, copywriting. You can see his resume here. He is available for remote projects at a rate of $60 USD per hour. You can contact him via this contact form. He currently lives in Belgrade, Serbia.

Leave a Reply

Your email address will not be published. Required fields are marked *