Editing Ubuntu (section)

== 8 SQL Backups ==
Daily automated date marked sql backups should be set up on a production Moodle. Dumps can be large and need to be deleted periodically.

* Create MySQL backup user and grant privileges to lock tables (better security than using root or moodleuser roles)
* Create a configuration file for root with the backup user credentials (to avoid the password being visible in the SQL backup call).
* Set the configuration file permissions to read and write for root only 
* Setup backup directory with read, write and execute permissions for root only 
* Set up two daily cron jobs, one to dump the database and the other to remove dumps more than a set number of days old.

<syntaxhighlight lang="bash">
BACKUP_USER_PASSWORD=$(openssl rand -base64 6)
mysql <<EOF
CREATE USER 'backupuser'@'localhost' IDENTIFIED BY '${BACKUP_USER_PASSWORD}';
GRANT LOCK TABLES, SELECT ON moodle.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
EOF

cat > /root/.my.cnf <<EOF
[client]
user=backupuser
password=${BACKUP_USER_PASSWORD}
EOF
chmod 600 /root/.my.cnf

mkdir -p /var/backups/moodle && chmod 700 /var/backups/moodle && chown root:root /var/backups/moodle

(crontab -l 2>/dev/null; echo "0 2 * * * mysqldump --defaults-file=/root/.my.cnf moodle > /var/backups/moodle/moodle_backup_\$(date +\%F).sql") | crontab -
(crontab -l 2>/dev/null; echo "0 3 * * * find /var/backups/moodle -name \"moodle_backup_*.sql\" -type f -mtime +7 -delete") | crontab -
</syntaxhighlight>