How to Use mysqldump to Backup Specific Tables in MySQL

1. Introduction

MySQL is a widely used database management system for many websites and applications. Among its tools, the mysqldump command is essential for creating database backups and handling migrations. This command is especially useful when you need to back up only specific tables from a large database.

In this article, we’ll explain in detail how to dump specific tables using the mysqldump command. The guide is beginner-friendly, but we’ll also cover advanced options and practical use cases for intermediate users.

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 selected tables.

Basic Syntax

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

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

• -u: Specifies the database username
• -p: Prompts for the database password
• database_name: The name of the database to dump
• table_name: The name of the specific table to dump
• > output_file.sql: The output file where the dump is saved

Commonly Used Options

• --single-transaction: Ensures transaction consistency when backing up InnoDB tables
• --skip-lock-tables: Prevents table locking during the backup process

3. Dumping Specific Tables

Dumping a Single Table

To back up only a single table, specify the table name after the database name. For example, the following command dumps only the users table:

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

This command saves the structure and data of the users table from the my_database database into users_dump.sql.

Dumping Multiple Tables

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

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

This command dumps the users, orders, and products tables together.

Using a Table List

When working with a large number of tables, manually typing all table names can be cumbersome. In that case, you can use the SHOW TABLES command or a script to automatically generate a table list for dumping:

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 specific pattern.

4. Options and Advanced Usage

The mysqldump command includes various options that let you customize backups depending on your needs. Below are some useful cases.

Dumping Structure Only

If you only need the table structure without the data, 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.

Dumping Data Only

If you only want the data without the table structure, use the --no-create-info option:

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

This command dumps only the data of the users table.

Conditional Dumps

You can use the --where option to dump only rows that match specific conditions. For example, to dump only rows where id is greater than 100:

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

This is useful for backing up only the required subset of data from large tables.

5. Practical Examples

Use Case 1: Backing Up a Single Table

For example, to back up only the salary table from the employees database:

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

Use Case 2: Backing Up Data with Conditions

To dump only a subset of data, use the --where option. For instance, to back up only rows where id is greater than 100 from the users table:

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

6. Best Practices and Considerations

Avoiding Table Lock Issues

When using mysqldump, tables may become locked, preventing other operations. On production systems, it is recommended to use the --single-transaction option to avoid downtime. For InnoDB tables, combining it with --skip-lock-tables is even safer.

Handling Large Data Volumes

For very large databases, the dump process can take a long time. A common practice is to compress the output in real time using gzip:

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

This command saves disk space by compressing the dump as it is created.

7. Conclusion

In this article, we covered how to dump specific tables using the mysqldump command. From basic syntax to conditional dumps, structure-only or data-only exports, and even automation with scripts, we explored a wide range of use cases. The mysqldump command is a powerful tool that, when used properly, makes database backups and migrations much easier.

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