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!

, , , ,

Post navigation

Arunlal Ashok

Operations Engineer at Endurance International Group. Linux lover. Like to play on Linux console. I started this blog to share and discuss Linux thoughts.

Always happy for an open discussion! Write to arun (@) crybit (dot) com. Check about me for more details. About this blog and our strong members, check The team CryBit.com
We like to travel. Our travelogues are published at Trip Mentor

Leave a Reply

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