Ubuntu – Automatic backup of thesql on ubuntu

backupMySQL

I have a Ubuntu server with a Mysql daatabase.
I would like to set up an automatic backup of this database during the night on another server (ovh for example).

I don't know what would be the best tool for that.
If someone has some advices…

Thanks in advance.

Best Answer

The easiest way to automate this task would be the combination of MySQL Dumps and a cronjob. You can find a lot of information on this topic on this and other internet sites, but for the sake of completion:

Create a mysqldump.sh file

Which will contain the mysql dump command we will schedule in a cron job (replace user, password and path to match your environment):

mysqldump -u root -p<mysql_root_password> --all-databases | gzip > /desired/backup/folder/mysqldb_`date +%F`.sql.gz

Edit: If you want the script to store the backup in a remote location, just mount the corresponding device or share and use the mount path in the script.

Test the script

Ensure that the script has execution permissions:

chmod +x /path/to/mysqldump.sh

Execute the script:

sh /path/to/mysqldump.sh

And test it is working correctly (The backup file will be created in the specified backup folder).

Create and schedule a new cron job

In a command prompt type

sudo crontab -e

And add the following line at the bottom of the file:

30 23 * * * /path/to/mysqldump.sh

This will execute the script every day at 23:30.

Extend this simple script

There is literally loads of things you can do in order to improve this simple backup method:

  • Backing up to another system
  • Monitor disk space
  • Email reporting
  • ...

So do not end here and experiment! :-)

Use backup-tools or cloud-based backup-services

Although the above method is one of the easiest and can be extended to suit your specific needs, it is worth mentioning that there are other alternatives:

  • backup-tools such as AutoMySQLBackup, an open-source application which might ease the process of configuring Email-notification, compression, encryption, rotation and type (e.g. incremental) of your backups. As for Mai 2018, it is available for Ubuntu Server through APT.
  • cloud-based backup-services such as BackupBird, myRepono, Bitcan, etc. which might be interesting if you manage several database-servers and want to centrally manage all their backups.

Please bare in mind that I'm not related in any way to any of the above mentioned solutions and services and I'm listing them only for reference, so use them at you own risk.

Useful Links / References:

Cron -> https://en.wikipedia.org/wiki/Cron

MySQL Dump -> https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Related Question