Mysqldump tricks help you get the most out of this amazing and essential tool. It is typically used for backing up and migrating MySQL databases and data. But sometimes you may want to get a little fancier then just backing up or restoring a full database. Here are a few Mysqldump tricks to maximize your effectiveness.
Mysqldump Tricks – Dump a single MySQL table
If you want to dump a single table from a MySQL database you can. The following command will help you accomplish this amazing feat:
mysqldump [options] db_name table_name > filename.sql
And just like that you have a file with just that table exported into it. But what if you want to dump more than one table, but not the whole DB. The following command will help you get there:
mysqldump [options] db_name table1_name [table2_name table3_name ...] > filename.sql
Simply adding the table names with spaces between each name will add them to the export file.
Skip specific tables in a DB with Mysqldump
Now that we know how to export only specific tables from a DB. How can we use Mysqldump to export a full DB but exclude one or more tables? Use the following command for a single table:
mysqldump [options] db_name --ignore-table=db_name.table1_name > filename.sql
Or if you want to exclude multiple tables:
mysqldump [options] db_name --ignore-table=db_name.table1_name --ignore-table=db_name.table2_name --ignore-table=db_name.table3_name > filename.sql
Now your Mysqldump powers are increasing. Now let’s move on to something a bit different.
Restore a single table from a Mysqldump File
So you already have a Mysqldump file with multiple tables in it, or a full DB dump. But you only want to restore a single table from the file. Ok, so this isn’t exactly a Mysqldump trick, it’s a Sed trick, but just using a Mysqldump file.
Let’s say you have a Mysqldump file called “filename.sql” and you want to restore only the table named “myFavoriteTable”. Using the command below Sed will copy the correct table contents into the file “myFavoriteTable.sql” so you can restore that file/table individually.
sed -n -e '/CREATE TABLE.*`myFavoriteTable`/,/Table structure for table/p' filename.sql > myFavoriteTable.sql
Now you have several new Mysqldump tricks up your sleeve. Hopefully they save you time and effort in the future.
If you would like to learn how to use compression with mysqldump check out this post.