Search form

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
done

One-line restoration:

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

Categories: