Manual database backup

One of the main advantages of having your own VPS is the ability to make things by yourself, In this short post I’m gonna show you how to make a backup of your WordPress website database by using the Mysql DUMP tool, you’ll see that it really is a piece of cake.

The only thing you need to know is the database name, so first of all login into mysql, see command below:

Login into MySQL

mysql -u root -p

This command tells MySQL that you are going to login as the user (-u) “root” and for that you’ll provide its password (-p).

Immediately after entering the root’s password you will be able to execute mysql commands.

Show Databases

show databases;

You’ll see all the databases on the list, just take note of the one you want to backup.

Export (backup)

Let’s pretend we do have one database called “people“, so the backup command will be as follow:

mysqldump -u root -p people > people-bkp.sql

The backup file that will be generated will be available in the directory where you were at the moment you logged into Mysql, feel free to download that file to have it on another place as a true backup.

Import (restore)

Later in the future you may want to restore that file into an empty database you’ve just created.

mysql -u root -p -h localhost woo < woo-bkp.sql

What else you’ll need

What you have done so far is a backup of the MySQL database, that’s great but is not the only thing you’ll need if you want to have a complete wordpress site backup. As you probably know the .sql file doesn’t contain any media file, no images nor videos, it only contain a reference to them in the form of a path.

Files and folders backup

Actual files along with your wordpress directory structure must be saved separatedly, I usually create a compressed file containing everything from the root (/) directory, see the command below:

tar -cvzf /var/www/people-bkp.tar.gz *

You won’t be able to save the .tar.gz file inside the directory you are making the backup, in the example below I execute this command being located on my wordpress website root (/) directory but the generated file will be saved one level above inside the /var/www/ directory.

Files and folders restore

Later in the future you may want to restore your website files and folders from the .tar.gz file, then you can use the following command:

tar xvzf people-bkp.tar.gz

The letter “x” used in this command stands for “extract”, just be sure you are once again in the root(/) directory of your WordPress website before executing this command, the magic will happen and you will see all the files and folders re-build having also its ownership and permissions, awesome!