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.

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 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).

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:

ERROR 1418 (HY000) at line 25: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

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:

• --no-data — prevents any table rows from being exported (documentation).
• --skip-opt — disables the default --opt option (documentation for the option --opt).

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.

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.

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:

mysqldump: Got error: 1290: The MySQL server is running with the –secure-file-priv option so it cannot execute this statement when executing ‘SELECT INTO OUTFILE’

, 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:

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

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.