The database is an important asset, which usually people forgot to backup. Before something goes wrong prepare your backup, and do the backup before going to sleep tonight.
MySQL has a built-in tool to do the backup and restore called mysqldump. mysqldump can’t automate the backup process, but we can create a script to do that. It’s recommended you understand how to use mysqldump before doing some automation because it’s make life easier and less trouble.
We’ll create a folder to store all backups on the same server ;), later we can push to S3, NextCloud, B2 whatever cloud storage you preferred.
Backup stored at: /backup/YYYY-MM-DD
YYYY-MM-DD using Year-Month-Day format
database compressed to reduce database size
data older than 5 day deleted
Create a file in
/opt/mysql-backup.sh, copy and paste following code
#!/bin/bash backup_path=/backup expiry_date=5 current_date=$(date +%Y-%m-%d) # create folder if it does not exist if [ ! -d "$backup_path" ]; then mkdir "$backup_path" fi if [ ! -d "$backup_path/$current_date" ]; then mkdir -p "$backup_path/$current_date" if [ ! -f $backup_path/$current_date/db-$(date +%H%M).sql ]; then mysqldump --all-databases | gzip -c > $backup_path/$current_date/db-$(date +%H%M).sql.gz fi else if [ ! -f $backup_path/$current_date/db-$(date +%H%M).sql ]; then mysqldump --all-databases | gzip -c > $backup_path/$current_date/db-$(date +%H%M).sql.gz fi fi # delete backup older than x day find $backup_path -type d -mtime +$expiry_date | xargs rm -Rf
For MySQL/MariaDB credential, most of the distro allow root to login without the credential, if you’re using normal user create
[client] user = USER password = PASSWORD
test the script by running
/opt/mysql-backup.sh command, it won’t gave any output if success. Check the
ls -lR /backup/mysql
create a crontab to run the script every day. Edit the crontab files
@daily /usr/bin/bash /opt/backup-mysql.sh
after few days check the
/backup directory, you’ll get a nice stucture folder