How to Dump Specific Tables with mysqldump: Complete Guide with Examples and Best Practices

1. Introduction

MySQL is a database management system used by many websites and applications. Among its tools, the “mysqldump” command is especially important when performing database backups or migrations. In particular, when you want to back up only specific tables from a large database, this command becomes extremely useful.

In this article, we will thoroughly explain how to dump specific tables using the mysqldump command. The guide is designed to be easy to understand for beginners, while also introducing intermediate-level options and advanced usage techniques.

2. Basic Syntax of the mysqldump Command

First, let’s review the basic usage of the mysqldump command. This command is used to dump (back up) the structure and data of an entire database or specific tables.

Basic Syntax

By specifying the username, password, database name, and table name as shown below, you can back up a specific table.

mysqldump -u username -p database_name table_name > output_file.sql

• -u: Specifies the username used to access the database
• -p: Specifies the password (you will be prompted to enter it)
• database_name: The name of the database to dump
• table_name: The specific table to dump
• > output_file.sql: Specifies the output file

Commonly Used Options

• --single-transaction: Ensures transactional consistency when backing up InnoDB tables
• --skip-lock-tables: Avoids locking tables during backup

3. How to Dump Specific Tables

Dumping a Single Table

To back up only a specific table, specify the table name after the database name. In the following example, only the users table is dumped.

mysqldump -u root -p my_database users > users_dump.sql

This command saves the structure and data of the users table in the my_database database to users_dump.sql.

Dumping Multiple Tables

If you want to back up multiple tables at once, specify the table names separated by spaces.

mysqldump -u root -p my_database users orders products > multiple_tables_dump.sql

In the above example, the three tables users, orders, and products are dumped simultaneously.

Dumping Using a Table List

When dumping a large number of tables, manually specifying every table name can be cumbersome. In such cases, you can use the SHOW TABLES command or a script to automatically generate a list of tables to dump.

mysql -u root -p my_database -N -e "SHOW TABLES LIKE 'hoge%'" > table_list.txt
mysqldump -u root -p my_database `cat table_list.txt` > partial_dump.sql

This method allows you to efficiently back up only the tables that match a specified pattern.

4. Options and Advanced Usage

mysqldump provides various options that allow you to flexibly create backups according to your needs. Below are several options for specific scenarios.

Dumping Structure Only

If you do not need the data and want to back up only the table structure, use the --no-data option.

mysqldump -u root -p my_database --no-data users > users_structure.sql

This command dumps only the structure of the users table and does not include any data.

Dumping Data Only

Conversely, if you want to dump only the table data, use the --no-create-info option.

mysqldump -u root -p my_database --no-create-info users > users_data.sql

This command excludes the table structure and dumps only the data.

Dumping Data Based on Specific Conditions

By using the --where option, you can dump only the data that matches specific conditions. For example, to dump only rows where id is greater than 100, use the following command:

mysqldump -u root -p my_database users --where="id > 100" > users_filtered_dump.sql

This allows you to extract and back up only the necessary data from a large database.

5. Practical Usage Examples

Use Case 1: Backing Up a Single Table

For example, if you want to back up only the salary table within the employees database, use the following command:

mysqldump -u root -p employees salary > salary_dump.sql

Use Case 2: Backing Up Data with Conditions

To dump only a specific range of data, use the --where option. For example, to back up only rows where the id in the users table is greater than 100:

mysqldump -u root -p my_database users --where="id > 100" > users_partial_dump.sql

6. Important Notes and Best Practices

Table Locking Issues

When using mysqldump, tables may become locked, preventing other operations from being performed. This can be particularly problematic in production systems. To avoid this issue, it is recommended to use the --single-transaction option. For InnoDB tables, combining it with the --skip-lock-tables option further improves safety.

Handling Large Data Volumes

If your database contains a very large amount of data, the dump process may take a long time. One effective approach is to use gzip to compress the dump in real time.

mysqldump -u root -p my_database | gzip > backup_$(date +%Y%m%d).sql.gz

This command compresses the data during the dump process, helping to save disk space.

7. Conclusion

In this article, we explained how to dump specific tables using the mysqldump command. We covered everything from basic command usage to conditional dumps, structure-only and data-only exports, and even efficient scripting techniques. mysqldump is a very powerful tool, and when used properly, it allows you to smoothly perform database backups and migrations.

In the next article, we will dive deeper into more advanced mysqldump options and compare it with other backup tools.