I have noticed that in my server maintenance tasks it is very frequent to make backups or backups of WordPress databases, so I will try to explain
how to make a backup of the WordPress database with SSH
, it is say, from the terminal.
Many will be thinking that there are many plugins to create backup copies of our WordPress blog, and it is true, but from my point of view, creating a database backup from the terminal is one of the most solid and reliable. I must also clarify that using this method through the terminal is the most viable option when the WordPress database is of considerable size.
Before explaining anything, say that we will use
PuTTY
to access the server terminal
(in my case with the Ubuntu 14.04 x64 operating system)
.
How to make a backup of the WordPress database with SSH
First of all we access the terminal and verify that we have the
mysqldump
tool installed
(it is installed at the moment that we install the MySQL / MariaDB package on our server)
. We write in our
mysqldump
terminal and we should see something similar to:
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] –all-databases [OPTIONS]
For more options, use mysqldump –help
We will start with the simplest command we will use and then I will explain the details. The command to use is:
mysqldump -u [usuario] -p[password] [base_datos] > [nombre_backup.sql]
It is very important to pay attention to blank spaces, so I emphasize that between
-p
and
[password]
there is no blank space
.
The parts of the command that are in square brackets must be replaced by:
-
[usuario]
: the username of our database.
-
[password]
: the password of the database.
-
[base_datos]
: the name of the database that we want to backup.
-
[nombre_backup.sql]
: the name that our backup file will have, it is important that it has a
.sql
extension to avoid future problems.
If we do not specify a password after
-p
, the
mysqldump
program
mysqldump
will ask for it before starting to perform the backup. The command would be as follows:
mysqldump -u [usuario] -p [base_datos] > [nombre_backup.sql]
Compress the SQL backup of the database.
The output SQL file is a plain text file, so if the size is considerable, it is usually a good option to compress it with Gzip.
The Gzip compression application is usually installed by default, but we can do a simple check by typing in the terminal the
gzip
command, which should show us something like:
gzip: compressed data not written to a terminal. Use -f to force compression.
For help, type: gzip –h
If there is the strange situation of not having Gzip installed, we execute the following command to install it
(functional in Ubuntu distributions)
:
sudo apt-get install gzip
The command we will use to make a compressed backup with
Gzip
would be:
mysqldump -u [usuario] -p[password] [base_datos] | gzip -c > [nombre_backup.gz]
We can specify the
maximum compression level for Gzip
and thus obtain a smaller compressed file with the following command:
mysqldump -u [usuario] -p[password] [base_datos] | gzip -9 -c > [nombre_backup.gz]
We can also use
Bzip2
to compress our SQL backup:
mysqldump -u [usuario] -p[password] [base_datos] | bzip2 -c > [nombre_backup.gz]
Compress with
Bzip2 specifying the maximum compression level
:
mysqldump -u [usuario] -p[password] [base_datos] | bzip2 -9 -c > [nombre_backup.gz]
Note that the file extension is
.gz
(in the case of Gzip)
or
.bz2
(in the case of Bzip2)
, which allows us to identify that it is a compressed file.
Considerations to consider with backups or backups of compressed SQL databases.
Compression programs usually support different levels of compression and are usually indicated with a number from 1 to 9.
The larger the number, the smaller the size of the compressed file.
These compression programs use compression level 6 by default
, since the higher the compression level, the more time it will take to compress the file and more system resources will be used. This is very important, because
if the file we are going to compress is large, if we select the level of compression 9 we can use up our server resources
.
In this tutorial the Gzip and Bzip2 programs are used to compress the backup of the WordPress database, both programs are similar, the only difference is that Bzip2 manages to compress the files more and gets smaller files, but on the other hand, it consumes more resources than Gzip. My advice is to use Gzip with the default compression level.
Considerations to take into account with backups or backups of SQL databases in general.
A very frequent problem appears when the
MySQL password has one or more special characters
and the terminal does not properly recognize the command. To solve this we must place the password in single quotes. The example command would be:
mysqldump -u [usuario] -p'p@ssw#rd' [base_datos] > [nombre_backup.sql]
Another important aspect is knowing some of the options that are activated by default when creating our backup with
mysqldump
. In many tutorials you can see how they add the
--opt
to the command, for example:
mysqldump --opt -u [usuario] -p[password] [base_datos] > [nombre_backup.sql]
This
--opt
already enabled by default
(so we don't have to add it to the command)
and it is advisable not to disable it, since it activates certain options that will optimize the creation of our backup. Extracted from the official documentation, we can see what the
--opt
modifier
--opt
:
Same as –add-drop-table, –add-locks, –create-options, –quick, –extended-insert, –lock-tables, –set-charset, and –disable-keys. Enabled by default, disable with –skip-opt.
Final considerations.
I have extended more than I intended in this tutorial, but you will know more in depth that the operating system offers us when creating backups of the WordPress database using the shell. This tutorial is the result of reading different forums and webmaster pages in addition to the
official mysqldump documentation
. I hope you have been helpful and remember that you can leave any questions in the comments.