How to Create Backup for MySQL Server Database

In this post, I will demonstrate MySQL server database backup. Creating a backup is a must. It provide safety for your data and full retrieve them back if something happens. It reduces the cost for data recovery if server breach and it is a good practice as IT security administrative to do it so.

In this guide I will provide you an easy way to create the backup yourself by integrating GNU tools and open source software to achieve it.

Three type of backup to follow:-

1. Manual Backup.
2. Semi-Automatic Backup and
3. Automated Backup.

Manual MySQL Backup

Manual backup is very easy and straight forward. In MySQL Database you must have a root access to the service. Type the following

fahad@my-server.com # mysqldump -u root --all-databases | gzip > ~/database_backup.sql.gz

The above code will dump all databases in a single SQL file and compress the file to gunzip. Then you can transfer it back to your own computer either by using WinSCP (If you have a Windows) or using Rsync in Linux. Rsync is very easy to use. In your computer type tbe following :-

mypc@fahad $ rsync fahad@my-server.com:~/database_backup.sql.gz ~/ --progress

Also, you can dump the database and change the file name to a datetime format using date program

fahad@my-server.com # mysqldump -u root  --all-databases | gzip > "$(date "+%Y-%m-%d_%H:%M:%S")-db.sql.gz"

The output will be

2019-03-02_16:18:37-db.sql.gz

In small scale application, any developer can backup manually from the MySQL server easily. But in terms of quite good volume, IT administrator must consider a new ways for backups other than manual backup.

Semi-Automatic MySQL Backup

In semi-automatic backup, we will introduce server side automatic backup to specified directory in the server (like /var/lib/mysql-backup). The backup will run chronologically using a time-based job scheduler. Then all the backup files can be retrieved from the server using your favorite tools such as WinSCP or rsync or whatever you like.

In Semi-automatic backup, we will use the following programs

1. Cron Job, and
2. Rsync

Cron job will schedule a backup every defined time to a specified location in the server using file time formatting. Then you can download all the backup files manually to your computer. But first, we must set up the server to create automatic backups. Let’s us assume that the directory located in

/var/lib/my-backups/mysql

First create the directory file

fahad@my-server.com # mkdir /var/lib/my-backups && mkdir /var/lib/my-backups/mysql

Then type the following, this will open cron task file.

fahad@my-server.com # crontab -e 

Make sure that you are running as root

Type the following at the end of the file

0 1 * * * mysqldump -u root  --all-databases | gzip > "$(date '+\%Y-\%m-\%d_\%H:\%M:\%S")-db.sql.gz"

The above code do the following:-

1. First, run mysqldump built-in program with root user, and dump all the databases using –all-databases argument.
2. Then pass all the databases dump into gzip compressor.
3. After that, gunzip will write the archive to a file in date time format.

Save and exit the file by typing (:wq) or CTRL+X CTRL+Y then press Enter for Nano file editor.

Then from your computer type the following

mypc@fahad $ mkdir ~/myserverbackups
mypc@fahad $ rsync --progress --recursive fahad@my-server.com:/var/lib/my-backups/mysql/* ~/myserverbackups/

The backup will run daily at 1 A.M. and the file will be stored in

/var/lib/my-backups/mysql

Automatic MySQL Backup

After creating a Cron job in semi-automatic backup, add another Cron job in your personal Linux computer to retrieve the backup automatically by using rsync for data synchronization

Open crontab and add the following code

10 1 * * * rsync --progress --recursive --ignore-existing fahad@my-server.com:/var/lib/my-backups/mysql/* ~/myserverbackups/

I recommend you to use the following argument--ignore-existing to only copy newer files and skip copying existing files