How to Backup MySQL Database Hosted with a VPS Server?

How to Backup MySQL Database Hosted with a VPS Server?

Jul 19, 2014

There are increasingly people using VPS hosting service nowadays. Backing up database is an important task to keep the VPS secure as you can easily and quickly restore the site from a backup file if there is anything damaged and lost. It should be regularly done on the website, but users need to take responsible for the complex process by themselves. Thus, today we come out this tutorial to help readers easily and quickly backup MySQL database hosted with a VPS Server.

Backup Database via phpMyAdmin

phpMyAdmin is a free tool that is specialized for MySQL database management. With the intuitive web interface and simple operations, we can use it to backup Database easily and quickly.

Log into phpMyAdmin with your database username and password, you can see the admin panel as the below screenshot, where you can choose language in the Appearance Settings.

phpMyAdmin interface

Click on the database you want to back it up and it appears on a table panel, and then you can click on Export on the top menu bar.

Export button

In the next, you are required to select an export method as Quick export or Custom export. The Quick Export displays only the minimal options while you can configure the Tables, output, format options, object creation options, and data creation options when using Custom Export. Besides, you are available to choose the exported file format, which is generally kept as SQL. At last, click the Go button to backup and store the file on local computer.

export database image

Automated MySQL Backup with MySQLDump

The MySQLDumnp is a professional tool designed for database backup or transferring. You can take use of it to automatically backup MySQL database in the VPS server. In order to securely access your virtual private server, you can login through Secure Shell to issue command lines.

As we access the server root, we should issue commands as super user and use the su- command. And then, you need to access the right directory and create a new file to store the backup information.

code image

As the above code, we access into the corresponding directory of database and create a new file as backup-db.sh to store the backup information, and then save this file under the directory of /web/www/db-backup/. Note that the content after -u is database username and the content after -p is database password.

In addition, if you hope to back up all databases at once, you can change the databasename as ???-all-databases. If you expect to delete the backup files existed longer than 7 days, just execute the below code.

code image

Moreover, you can set the time when to execute the backup operation. For example, the below code controls the server to back the database up at 23:59 every day.

code image

Backup Database in Managed VPS with cPanel

If you purchase a managed VPS hosting that has a control panel pre-installed, you can simply backup database with a few clicks. You can just follow another tutorial for backing up database with cPanel.

If you are non-technicians and preferred to backup database with cPanel, you may switch your service to a fully managed VPS service, which is widely offered by some reputable web hosts, such as BlueHost, JustHost and InMotion Hosting. See their highlights in the below chart.