Export your MySQL database
If you have installed your website I presume you have all the information of your database stored somewhere (database name, database user and password). If not, there is a file that should store these information and you have to find it! (Remember the way to find a file that contains a specific string with SSH commands). If you are using WordPress this file is called “wp-config.php” and it is located at the root of your website files. If you are using Siberian CMS, this file is called “app.ini” and it is stored in app/configs/app.ini. You just have to open this file to get these information.
Now you have these information, connect with SSH to your server and go to your webspace.
The command to export a MySQL database is “mysqldump” and it runs as follows:
mysqldump -u database_user database_name -p > backup_name.sql
This command will ask you for the database password. Enter it and press “enter”. Your MySQL database will be backed up in a file called backup_name.sql. Of course you can choose the name you want for this backup file. I often use the same name as the database itself.
The file “backup_name.sql” will be saved at the location from which you have run the command. My advice is, for security reasons, to perform this command one step above your website files. For example, let’s say your website files are located in a folder called “httpdocs” located here:
You should store this mysql backup here:
So, if you are located in the “httpdocs” folder when you run the command, the command would look like this:
mysqldump -u database_user database_name -p > ../backup_name.sql
This way the backup file will be stored one step above, in the “website” directory.
The other option is to create a directory called “backup_sql” for example (remember that it is better to have no space in the names of your files nor your folders’ names) in your website files, and thus in the “httpdocs” folder in our example:
Set the proper permissions for this directory so that it cannot be accessible for someone from the outside:
chmod 600 backup_sql
Then launch your backup with storing your mysql backup file in this directory:
mysqldump -u database_user database_name -p > backup_sql/backup_name.sql
Create an archive of your website files
Let’s keep our previous example of the “httpdocs” directory that stores all the files of our website. Go one step above, in the “website” directory:
If you list all the files and directories which are there with the command
you will see, at least, your httpdocs directory:
And you can also check whether the mysql backup file is at the right place:
ls -la httpdocs/backup_sql/
The above command should return:
Now let’s create a zip file of all of this. For zipping I prefer to use the tar.gz compression but it’s up to you to use another command. Note that if you want to “zip” instead of “tar.gz” a file, you have to be sure you can use “zip” on your server or you would have to install it. So the command for “tar.gz” a file is the following:
tar -zcf backup_website.tar.gz directory/
In our example:
tar -zcf backup_website.tar.gz httpdocs/
This command will create an archive of your directory “httpdocs” in a file called “backup_website.tar.gz”. Obviously, this archive will also contain your mysql backup as it is already included in the “httpdocs” folder.
Send the archive to your computer or another server
Now you have made an archive of your website, you may want to either send it to your computer or send it to another server. To send it to another server you can use the “rsync” command we have seen in the previous article about useful commands in SSH:
rsync -av backup_website.tar.gz firstname.lastname@example.org:/home/user/public_html/
To send it to your computer you can also use the command “scp”, this way:
scp -r location destination
Whereas “location” is the location on your server where the backup archive is located, “destination” is the destination on your computer. First, you have to exit SSH on your server to run this command with the command “exit”. Then, from the terminal of your computer, just run your command. For example:
rsync -av email@example.com:/var/www/vhosts/website/backup_website.tar.gz Downloads/
This command will send the backup file from the server it is located to the “Downloads” folder on your computer.
Or, with the “scp” command:
scp -p firstname.lastname@example.org:/var/www/vhosts/website/backup_website.tar.gz Downloads/
Here you are, you have made a backup of your website or web application!
Restore your website or web application from your backup archive
Now you have your backup, you may need to restore your website for any reason. In this case you have to:
- unzip or untar your backup archive in the directory that contains your website files
- restore your mysql database thanks to the mysql backup file
To process the first action, you have to send your backup file to the server. So, open a terminal window on your computer and enter (we assume the path to your website files is now /home/mywebsite/public_html/):
rsync -av Downloads/backup_website.tar.gz email@example.com:/home/mywebsite/public_html/backup_website.tar.gz
Then connect in SSH to your server, the one on which you want to restore the website, and untar the archive:
tar -zxf backup_website.tar.gz
In this last command you see that the arguments have changed, one argument though. We have replaced the argument “c”, used when we have created the archive (remember “c” for “compression”), by the argument “x” used for the extraction of the files (“x” for “extraction”).
Now your files are there. As you have compressed the “httpdocs” directory you will see it displays, if you enter “ls -la” to list the documents there. You have to move all of your website files from this “httpdocs” to the “public_html” folder:
mv httpdocs/* ./
This command will move all the items which are in the httpdocs folder, thanks to “*”, to the current folder (“./”). Thus be sure to be located in the “publich_html” folder, or the right folder of your website when you launch the command, or adjust the path to the right folders (location and destination).
Check if the user and group that own the files and directories are the right ones. If you don’t know who are the right user and group, you can enter:
ls -la ../
This command will list the files which are in the directory above, and these files are created by the server and so have the right user and group, especially the “public_html” directory.
If the user and group are not good, you can change them with:
chown -R user:group *
Be careful using this command, be sure you are located in the directory of your website otherwise you will change the permissions on all the directories and files of your server! And if these things have been changed you can fully rebuild the server…
Once the files are ok, you now have to import the database. But first you have to create the database on your server if you restore the website on another server. If you restore it on the same server you can either remove or empty the current database before importing your backup or create a new database.
Two options to create your new database:
- you have a control panel with your hosting
- you don’t have a control panel with your hosting
If you have a control panel, the option is to connect to it and create a database from the MySQL section of this panel.
If you don’t have a control panel, you have to create the user and the database in SSH:
mysql -u root -p
Be sure to have the mysql root password.
Then, once you are connected to MySQL:
Enter this command:
CREATE DATABASE database_name;
(replace “database_name” by the name you want for your database)
Your database is created.
Now let’s create a database user and give him permissions on this database.
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
(replace “new_user” by the username, and “password” by the password you want for this user)
Then give this user the right privileges:
GRANT ALL PRIVILEGES ON * . * TO 'user'@'localhost';
Then reload all the privileges:
Now you can import your database backup. Remember your database backup file is in a folder called “backup_sql” located in the “httpdocs” folder and thus, now that we have moved all the files from the “httpdocs” to the “public_html” folder, the “backup_sql” folder is in the “public_html” folder of your website. Let’s assume you are located in this public_html folder, you can enter this command to import your mysql backup:
mysql -u new_user database_name -p < backup_sql/backup_name.sql
It will ask for the user password you have just created. And that’s it, your database backup is imported.
A last step is to change the data in the files that need these information, if required. As previously stated, there is, in most of the cases, a configuration file in your website files that contains the information to connect to the database. In WordPress this configuration file is wp-config.php located at the root of your website files, or in Siberian CMS, it is the file “app.ini”, located in app/configs/.
Open this file and change the information of your previous database to the new ones (database name, user and password) if they have changed. If there is a path to your website filled in this file, and if this path has changed (like in our example here where the path of the first server was “/var/www/vhosts/website/httpdocs/” and “/home/mywebsite/public_html/” for the second server), replace it with the new path.
Then everything should be ok! You may have to clear the cache of your website, too.
Here we go, you have made a backup and restored it! 🙂