Simple script for dumping a MySQL database
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#!/bin/bash # # 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. # backup_folder="/root/mysql-backup" file_prefix="mysql_dump_" file_suffix=".sql.gz" yesterday=$(date --date="1 day ago" +%m-%d-%Y) cd $backup_folder rm $file_prefix$yesterday$file_suffix mysqldump -u user -psecret --all-databases | gzip > $backup_folder/$file_prefix`date +%m-%d-%Y`$file_suffix |