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:-
- Manual Backup.
- Semi-Automatic Backup and
- 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
firstname.lastname@example.org # 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 email@example.com:~/database_backup.sql.gz ~/ --progress
Also, you can dump the database and change the file name to a datetime format using date program
firstname.lastname@example.org # mysqldump -u root --all-databases | gzip > "$(date "+%Y-%m-%d_%H:%M:%S")-db.sql.gz"
The output will be
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
- Cron Job, and
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
First create the directory file
email@example.com # mkdir /var/lib/my-backups && mkdir /var/lib/my-backups/mysql
Then type the following, this will open cron task file.
firstname.lastname@example.org # 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:-
- First, run mysqldump built-in program with root user, and dump all the databases using –all-databases argument.
- Then pass all the databases dump into gzip compressor.
- 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 email@example.com:/var/lib/my-backups/mysql/* ~/myserverbackups/
The backup will run daily at 1 A.M. and the file will be stored in
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
I recommend you to use the following argument
--ignore-existing to only copy newer files and skip copying existing files