Code: Select all
#!/bin/sh
#backup all mysql databases
# list MySQL databases and dump each
DIR=/thebackupdirectory/
DATESTAMP=$(date +%Y%m%d-%H:%M:%S)
DB_USER=
DB_PASS=
# remove old backups
find ${DIR} -type f -mtime +5 -exec rm -rf {} \;
DB_LIST=`mysql -u $DB_USER -p"$DB_PASS" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
FILENAME=${DIR}${DB}-${DATESTAMP}.sql.gz
mysqldump -u $DB_USER -p"$DB_PASS" --opt --flush-logs $DB | gzip > $FILENAME
done
mysqlcheck -u $DB_USER -p"$DB_PASS" --all-databases > /root/mysql_backups/check_errors-${DATESTAMP}.log
This is what i use, nice thing about this is that it will back up each database independently, nice if you only need to restore 1 database instead of all of them, so it is similar to dazza's script
you can take the time out of the datestamp if your only running once a day, i run this a few times a day so its nice to have at what time it ran, also it will produce a logfile of backups. and also del old files more than 5 days
(found this somewhere on that internet thingy so thanks to who wrote it)
Also since i have a few systems im running mysql replication and this script runs on a slave so it wont mess with the master database, its a good idea if you have an old laptop, desktop or raspi to set as a slave