As file system backups might fail to backup open files, it’s good practice to close any open files before running the backup.
Closing a MySQL database file means you will have to stop the MySQL server itself, causing causing a database outage while the backup is running. But fortunately there’s a smarter way to backup you database – simple dump the entire database to a file (which is automatically closed when the dump is completed), and have your file system backup software backup the dump file instead.
Here’s a simple script to be run daily. The script creates the dump file, and removes yesterdays dump file.
# This script exports the mysql databases to file. By exporting to file, we'll have a more reliable backup of the database.
# Yesterdays backup is deleted.
yesterday=$(date --date="1 day ago" +%m-%d-%Y)
mysqldump -u user -psecret --all-databases | gzip > $backup_folder/$file_prefix`date +%m-%d-%Y`$file_suffix