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

Sr. Linux Server Administrator. I'm managing Linux servers since 2012. I started this blog to share and discuss my ideas. Check My Profile in uPwork (oDesk) and create a job, if you need any administration help. Thanks!!

You may also like...

Leave a Reply

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