MySQL and MariaDB are popular relational database management systems that power a wide range of applications, from small personal projects to large enterprise solutions. Ensuring data integrity and continuity in case of disasters is critical for any application. Regular database backups are a cornerstone of a robust disaster recovery strategy, and automating the process is essential to ensure consistency and save time.
Cron is a time-based job scheduler in Unix-like operating systems that enables automation of tasks, such as running scripts or executing commands at specified intervals. By leveraging the power of cron, you can create a highly effective, automated backup solution for your MySQL or MariaDB databases.
In this guide, we will walk you through the process of setting up automatic backups for your MySQL or MariaDB databases using cron. The steps below will demonstrate how to create a backup script, configure the necessary permissions, and schedule the script to run automatically using the cron job scheduler.
sudo apt update && sudo apt-get install mysql-server
sudo mkdir -p /backups/mysql sudo chown -R mysql:mysql /backups/mysql sudo chmod 700 /backups/mysql
#!/bin/bash TIMESTAMP=$(date +"%Y%m%d%H%M") BACKUP_DIR="/backups/mysql" MYSQL_USER="user" MYSQL_PASSWORD="password" MYSQL_HOST="host" databases=`mysql --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` for db in $databases; do mysqldump --force --opt --user=$MYSQL_USER --password=$MYSQL_PASSWORD --host=$MYSQL_HOST --databases $db | gzip > "$BACKUP_DIR/$db-$TIMESTAMP.sql.gz" done
sudo chmod +x /usr/local/bin/mysql_backup.sh
/usr/local/bin/mysql_backup.sh
sudo crontab -e
0 3 * * * /usr/local/bin/mysql_backup.sh
sudo crontab -l
Comment anonymously. Login not required.