Utilizing mysqldump to Dump Specific MySQL Tables

mysqldump is often one of the primary recommendations when it comes to backing up your MySQL-related data. It’s a well-known tool in the MySQL world that helps you perform logical backups of your data. This blog post will tell you how to use the tool to dump only specific data.

What is mysqldump?

mysqldump is a well-known name in the MySQL world. The tool is a client utility that is able to perform logical backups in MySQL – it produces a set of SQL statements that can be executed in order to reproduce the data that existed in the database previously. It’s most frequently used to take a backup of MySQL data in order to transfer it to another server or to perform other tasks related to MySQL.

How to Use mysqldump?

In its simplest form, mysqldump can be invoked by using the mysqldump command:

mysqldump -u [username] -p [password] [options] [database_name] [table_name] > backup.sql

Where username is your username, password is your password, options are any specific options that you want to use together with the utility, database_name is your database name and table_name is your table name.

Another thing worth noting is that if you add these lines to your my.cnf file you can log in without typing a password when invoking mysqldump thus minimizing your risk of your password being observed by any third-parties:

[mysqldump]
user=your_user
password=your_password

Utilizing mysqldump to Dump Specific MySQL Tables

As you might have noticed from the example above, you can dump specific MySQL tables by simply specifying them after your database name, e.g:

mysqldump -u [username] -p [password] [options] demo_db demo_table1 demo_table2 demo_table3 > backup.sql

The above example would dump the tables demo_table1, demo_table2 and demo_table3 within the database demo_db into a file backup.sql.

Alternatively, if you have one (or more) tables that you want to be excluded from the dump, you can utilize the –ignore-table option:

mysqldump -u [username] -p [password] [options] demo_db --ignore-table=demo_db.demo_table1 > backup.sql

Or if you want to ignore multiple tables:

mysqldump -u [username] -p [password] [options] demo_db --ignore-table=demo_db.demo_table1 --ignore-table=demo_db.demo_table2 > backup.sql

Even though mysqldump can be a great tool to have, keep in mind that if your tables are primarily InnoDB-based or if you have a mix between InnoDB and MyISAM tables, consider using MySQL Enterprise Backup since it is able to provide you with the best performance for InnoDB backups with minimal disruption while also having the ability to back up tables using MyISAM and (or) other storage engines.

Backing up MySQL Data with Backup Ninja

Backup Ninja can also help you achieve your backup goals. With the tool you can select which type of backups you want to perform (the available options include backing up your database and files or only files respectively), then select what type of database server do you have (Backup Ninja currently supports multiple vendors including MySQL, MariaDB, Percona, PostgreSQL, TimescaleDB and MongoDB), then start the backup process:

Backup Ninja also allows you to easily manage both physical and logical backups. These types of backups can be restored locally or in the cloud – simply follow the steps outlined on your screen. Just like with mysqldump, you can also include or exclude specific MySQL tables:

Summary

mysqldump, when used properly, can become a very powerful tool in your arsenal – the tool is a well-known name in the MySQL world and it can help you dump only specific MySQL tables and perform a multitude of other things. When choosing what to use though, keep in mind that if your tables primarily use the InnoDB storage engine or if you have a mix of InnoDB and MyISAM tables, you could also have a look at Backup Ninja to back up your MySQL tables and databases in a scriptless way – if you’re interested in learning more, have a look at its documentation for details.