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 # 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 ~/ --progress

Also, you can dump the database and change the file name to a datetime format using date program # mysqldump -u root  --all-databases | gzip > "$(date "+%Y-%m-%d_%H:%M:%S")-db.sql.gz"

The output will be

How the file will appear in the server
Example of the output of the non date formatted and formatted date

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


First create the directory file # mkdir /var/lib/my-backups && mkdir /var/lib/my-backups/mysql

Then type the following, this will open cron task file. # crontab -e 

Make sure that you are running as root

Open a crontab file in server to create automatic mysql database backup
Crontab file for root user

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* ~/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 --ignore-existing* ~/myserverbackups/

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

Install Visual Studio in Ubuntu 18.04

The Easy Way

You can install visual studio code in Ubuntu from snap environment

alex@alexa-pc:~$ sudo snap install vscode

The problem with the snap application that they are installed in sand boxed environment. Hard to recommend it.

Another way around

Go to and click for the .deb package. After download. Press CTRL+ALT+T, the terminal will appear and write:-

alex@alexa-pc:~$ cd ~/Downloads
alex@alexa-pc:~/Downloads$ sudo dpkg -i code_1.27.2-1536736588_amd64.deb

And enjoy using it. The version 1.27.2-1536736588 from previous code is changing.