Here is a small, but quite useful shell script which creates a backup of all databases in a MySQL Server.
If there is no change to the content of a particular MySQL database, the original backup file is not modified either and it keeps its timestamp. Thus, it will not be unnecessarily copied if you make a backup of the file system.
And here is how it works:
- Set the variable MYSQLDIR to the path name of the directory where MySQL stores its database files.
- Set the variable BACKUPDIR to the location where you want your backed up MySQL files.
- If the MySQL root user needs a password, define it with the PW variable.
- On execution the script will enumerate all directories in the ${MYSQLDBDIR} directory, which are the names of the MySQL databases. It will then run the mysqldump command with some generic options to create one backup file per MySQL database. An existing backup file is replaced only if it is outdated.
- Now save this script into /etc/cron.daily/backup-mysql to have an automated daily database backup.
#!/bin/bash
PW=
MYSQLDBDIR=/var/lib/mysql
BACKUPDIR=/home/backups/mysql
mkdir -p ${BACKUPDIR}
cd ${MYSQLDBDIR}
for db in `find * -type d -exec basename {} \;`; do
echo mysqldump ${db}
mysqldump --all --complete-insert --extended-insert=FALSE --add-drop-table --databases --flush-logs --lock-tables --quick \
--user=root --password=${PW} --host=localhost ${db} > ${BACKUPDIR}/${db}.new.sql
if [ -f ${BACKUPDIR}/${db}.sql ]; then
# echo diff -q ${BACKUPDIR}/${db}.sql ${BACKUPDIR}/${db}.new.sql
diff -q ${BACKUPDIR}/${db}.sql ${BACKUPDIR}/${db}.new.sql > /dev/null 2>&1
result=$?
if [ $result -eq 0 ]; then
# echo rm ${BACKUPDIR}/${db}.new.sql
rm ${BACKUPDIR}/${db}.new.sql
elif [ $result -eq 1 ]; then
echo mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
fi
else
echo mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
mv ${BACKUPDIR}/${db}.new.sql ${BACKUPDIR}/${db}.sql
fi
done