How to schedule a backup of a database on Ubuntu VPS
February 28, 2021 ≈ 43 seconds
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:
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:
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
You didn't provide any data, entered invalid email or already subscribed.
Try again. And if you still can't subscribe—write us.
Address successfully subscribed!