Back Up MySQL Databases

A simple bash script to back up MySQL databases. This backs up every database on the server to individual gzipped files in the directory you specify so that you can easily restore them separately. If you want it to run everyday, save it with an .sh extension and add it to the crontab of an authorized user.

#backup each database separately (easier to restore)
NOW="$(date +"%Y-%m-%d")"
for i in /var/lib/mysql/*/; do
dbname=`basename $i`
/usr/bin/mysqldump -uroot -p'password' --events $dbname | gzip > /backups-directory/$NOW-$dbname.sql.gz
chmod 400 /var/backups-db/$NOW-$dbname.sql.gz

One-line restoration:

$ gunzip /var/backups/dbname.sql.gz | mysql -uroot -p dbname < dbname.sql


Leave a Reply

Your email address will not be published. Required fields are marked *