Easy MySQL Backup Script

Below is an easy script for doing a quick backup of all mysql databases on the same server.


#!/bin/bash
#Dump new files
USER=root
PASSWORD=password
HOST=localhost

for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
  mysqldump                                                     \
  -u$USER -p$PASSWORD -h$HOST                                   \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > /root/mysql_backup/$i.sql;
tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;
done;