Backup and restore MySQL database table via commandline

Managing databases via Linux shell is not a big deal. And we all are familiar with MySQL databases backup and restoration via CLI (Command Line Interface) using mysqldump and mysql commands. This is explained here >> Managing MySQL database backup << It’s a very common command for a Server Administrator in his/her day-to-day life.

Are you aware about taking backup of a single database table from your database? And how restore it to an existing database? Yeah, here we discuss about those techniques.

I’ll explain it with an example. For this I created a test WordPress installation which has database “crybit_testdb” for testing purposes.

Before doing anything via MySQL interface, make sure that you have a database backup for recovery purpose, as a safety measure.

How to create backup of a single MySQL database table from command line?

I chose “wp_users” table for illustrating it. Please do the following steps to take a backup from your Linux shell.

Step 1 : Log into the server as root user.
Step 2 : Execute the following command to take the backup:

mysqldump crybit_testdb wp_users > wp_users.sql

If you are not a root user execute the following command:

mysqldump -u  -p  crybit_testdb wp_users > wp_users.sql

Where crybit_testdb is the test database and wp_users is the table.

Yeah, you’ve successfully taken the table backup to wp_users.sql file.

Now I’m going to drop that table from crybit_testdb database so that we can confirm the restoration process has completed 100% successfully.

wordpress-user-table-1

To drop a database table; execute the following command:

mysql> use crybit_testdb;
mysql> DROP TABLE wp_users;

Example

mysql> DROP TABLE wptm_users;
Query OK, 0 rows affected (0.00 sec)

That’s it! You removed the user table, so you can not log into the WP dashboard anymore. LOL you will get the error : ERROR: Invalid username. Lost your password?

wordpress-user-table

Okay, let’s start the table restoration work.

How to restore a single MySQL database table from command line?

Yeah, the table is backed up as wp_users.sql. Now we can restore it to the original database.
Execute the following command to restore the database table:

mysql crybit_testdb < wp_users.sql

As a user, execute this command:

mysql -u  -p  crybit_testdb < wp_users.sql

That’s it! Now you will be able to log into the dashboard 🙂
Let me know if you need any help on it!

Arunlal Ashok

Linux Server Administrator. I'm dealing with Linux servers since 2012. I started this blog to share and discuss my ideas with the world. Check My Profile!! in uPwork (oDesk) and let me know if you need any assistance. Thanks!!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *