...
- 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.
| No Format |
|---|
#!/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
|