How to MySQLDump Only Specific Tables

Often database administrators need to backup their data, or migrate it from one database to another. For this purpose, they need to dump their database to a .sql file which is later imported into the destination. Typically, administrators rely on MySQLdump utility for this purpose. However, sometimes they may need to export one or more specific tables from a single database, without actually dumping the entire database. They may also need to export these tables with or without data, as required. In this article, we will learn how to MySQLdump specific tables from your database.

What is MySQLDump

MySQLDump is a command line utility that ships with almost every MySQL client installation. It allows you to easily backup your databases and tables. It does so by examining your database and generating a set of SQL statements to re-generate its structure and data later. These SQL queries are stored in a .sql file.

MySQLdump is commonly used to create backups of MySQL database, including its structure and data. It is also used to migrate databases from one server to another, by simply importing the generated .sql file in another database. MySQLDump can also be used to export one or more tables, instead of entire database. So it is good for cloning databases as well as tables.

Why MySQLDump Specific Tables

There are several reasons why you may need to export specific tables in your database. Let us look at some of them.

1. Large database – If you have a large database with many tables, and you want to work with data of a few select tables, you can just dump them instead of your entire database. It also reduces the size of your data backup.
2. Testing Purposes – If you want to pass data between your development and production databases, you can easily do this by exporting a few tables from development system and importing them in your production server.
3. Quick Replication – It is easier to migrate database tables by dumping their schema, without data, and importing them in your destination database. This saves a lot of backup time.

How to MySQLDump Only Specific Tables

Here is the general syntax of MySQLDump command.

mysqldump -u <username> -p <database_name> > /path/to/backup/file

In the above command, we mention database username, name of database and full path to resultant .sql file. On running the above command, you will be prompted for a password. Enter it to complete the command. Here is a sample command to backup database test to file test.sql.

mysqldump -u test_user -p test > /home/ubuntu/test.sql

You can view all available options for this command by running mysqldump without anything else after it. Now let us learn how to use MySQLdump to backup specific tables in a database. In all the following commands, replace database_user with your database username.

MySQLDump Single Table With Data

The most common use case is to dump a single table from a database, along with its data. You can do this by just mentioning the table name immediately after the database name in MySQLdump command as shown. Here is an example to dump only table1 from database data1.

mysqldump -u database_user data1 table1 > /home/ubuntu/table1.sql

After you run this command, you will find that the table1.sql file contains the SQL queries to regenerate the schema and data of table1.

Alternatively, you can also use -t option before mentioning the table name in MySQLdump command.

mysqldump -u database_user data1 -t table1 > /home/ubuntu/table1.sql

MySQLDump Single Table Without Data

Sometimes you may just need to dump the structure of a table without exporting any of its data. This is required if your table is very large. If you want to export a single table from a database without data, then use –no-data option as shown. Here is an example to dump only the structure of table table1 from database data1.

mysqldump -u database_user data1 table1 --no-data > /home/ubuntu/table1.sql

When you run the above command, it will dump only the SQL queries to recreate the table without any data in table1.sql file.

MySQLDump Multiple Tables With Data

Similarly, if you want to dump multiple tables from a database, mention their names one after the other, after the database name, in MySQLdump command. Here is an example to export only tables table1, table2, table3 from database data1.

mysqldump -u database_user data1 table1 table2 table3 > /home/ubuntu/tables.sql

After you run the above query, the tables.sql file will contain SQL statements to regenerate the schema and data of tables table1, table2, table3 only. If you are using -t option with MySQLdump command, you need to mention separate -t option before each table name.

mysqldump -u database_user data1 -t table1 -t table2 -t table3 > /home/ubuntu/tables.sql

MySQLDump Multiple Tables Without Data

Dumping multiple tables without data can also be done using –no-data option as shown above. Here is an example to dump tables table1, table2, table3 schema without data.

mysqldump -u database_user data1 table1 table2 table3 --no-data > /home/ubuntu/tables.sql

After running the above command, MySQLdump will create a set of SQL statements to generate the structure of tables table1, table2, table3 only.

MySQLDump Exclude Specific Tables

This is an interesting use case. If your database has many tables and you want to dump all of them except a few then it can be tedious to mention all those table names after database name. Instead, you can use –ignore-table option followed by the table to be excluded from export. Here is an example to dump all tables from data1 database, excluding tables table1, table2, and table3.

mysqldump -u database_user data1 --ignore-table=table1 --ignore-table=table2 --ignore-table=table3 > /home/ubuntu/tables.sql

OR

mysqldump -u database_user data1 --ignore-table=data1.table1 --ignore-table=data1.table2 --ignore-table=data1.table3 > /home/ubuntu/tables.sql

Limit Number of Records

Another interesting use case is to be able to export only specific rows of specific tables. Typically, we use WHERE clause in SELECT statement to get only specific rows from tables.

SELECT * from table_name WHERE condition

You can use the same WHERE condition even in MySQLdump command, using –where option. Here is an example to export only those rows from table1 in database data1, where created_at>’2024-09-11′.

mysqldump -u database_user -p data1 table1 --where="created_at='2024-09-11'" > /home/ubuntu/some_rows.sql

When you run the above statement, MySQLdump will store the SQL statements required to generate the schema of table1 and populate it with the rows where created_at>’2024-09-11′. Please remember to use double quotes to specify the WHERE condition and single quotes inside double quotes.

Here is another example to dump only 100 rows of table1 in database data1.

mysqldump -u database_user -p data1 table1 --where="1 LIMIT 100" > /home/ubuntu/some_rows.sql

Please note:

Sometimes, if your database is a high transaction one or a very large database with big tables, then it may get locked when you try to export one or more tables in it. In such cases, it is better to add –skip-lock-tables option. You can also use –single-transaction option to instruct MySQLdump to put everything into a single transaction. This way your data will not get locked while doing backups.

Conclusion

In this article, we have learnt how to export specific database tables in MySQL. You can use it to dump data or structure of one or more database tables in MySQL. It is very convenient to backup one or more tables in a database, instead of the entire database. It saves a lot of time and file size. We have also covered many common use cases faced by administrators while exporting their data. Depending on your requirement, you can use any of these commands.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read:

Difference Between InnoDB vs MyISAM
Escaping Single Quotes, Double Quotes & Backticks in MySQL
Difference Between Inner and Outer Join in SQL