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.
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?
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!
