the perfect mysql backup

on
this is the perfect mysql backup tool for me.


how to use it:
  • create LocalUserWithMinimalReadOnlyRight on mysql with your favourite tool. i only give it SELECT, RELOAD, LOCK TABLES privileges.
  • download backup-mysql.logrotate, edit the file, change the username and password
  • put the files in /etc/logrotate.d/
  • run this for the very first time: 
for i in `mysql --batch -e 'show databases' | tail -n +2`; do
if [ ! -e /bak/mysql/$i ]; then mkdir -m 700 /bak/mysql/$i; fi
mysqldump --opt -u LocalUserWithMinimalReadOnlyRight $i | gzip -c > /bak/mysql/$i/$i.sql.gz
done

that's it. i will creates 5 daily backup, 4 weekly backup and 3 monthly backup.
for best result, make sure you put your backup on separate drive or even on a separate network/country/planet...

--
tips & tricks on computers and gadgets
denny on IT ; let's doIT
documenting the brain

Comments

Post a Comment