sudo bash; # become root

#create directories
mkdir -p /backup/mysql

# backup every database manually separately
mysqldump -u root DATABASE_NAME > /backup/DATABASE_NAME.sql;

# backup every database separately automatically (easier to restore)
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d).sql.gz"; done

# if you need hour minute and seconds when your backup was done go:
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d-%H.%M.%S).sql.gz"; done

so your whole backup script could look like this:

vim /scripts/backup_databases.sh

#!/bin/bash
# backup all databases (just in case)
mysqldump -u root --all-databases | gzip > "/backup/mysql/mysqldump-$(hostname)-all-$(date +%Y-%m-%d).sql.gz";

# backup user database seperately
mysqldump -u root mysql user | gzip > "/backup/mysql/mysqldump-$(hostname)-users-$(date +%Y-%m-%d).sql.gz";

# backup every database separately (easier to restore)
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backup/mysql/mysqldump-$(hostname)-$db-$(date +%Y-%m-%d).sql.gz"; done

find /backup/mysql/mysqldump-* -mtime +14 -exec rm {} \; # delete all backups older than 14 days to avoid overfilling harddisk
admin