How to schedule a backup of a database on Ubuntu VPS

Regular backups are important. This tutorial shows how to back up the MySQL database daily and keep only the latest. It requires you to be familiar (a bit) with mysqldump and cron.

First let's create a backup folder:

    mkdir -p /home/user/backup/my-db

Change user into your user folder and my-db into your database name.

Edit crontab with sudo:

Run sudo crontab -e and add the line:

0 1 * * * /usr/bin/mysqldump my-db > /home/user/backups/my-db/`date -I`.sql && chown user:group /home/user/backups/my-db/`date -I`.sql

Change user group and database (my-db) accordingly.

Leave only 5 latest backups:

Run crontab -e, without sudo (every backup would be with your user and group rights) and add the line:

0 2 * * * cd /home/user/backups/my-db && ls -t | awk 'NR>5' | xargs --no-run-if-empty rm -f

Change user and database (my-db). ls -t — list files and sort by modification time, newest first awk 'NR>5' — get all records after 5