Bash: MySql backup (file per db), restore+ users and privileges

Backup Mysql DB (file per db)

#!/bin/bash
USER="root"
databases=`mysql -u $USER -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

Restore to RDS

!/bin/bash
USER="root"
PASSWORD=""
HOST="db1-web-eu-west-1.rds.amazonaws.com"
FILES="/opt/dumps/*"
for f in $FILES
do
  echo "Processing $f file..."
  mysql -h $HOST -u $USER -p$PASSWORD < $f
done

Users and privileges:

• some dark magic

mysql -u root -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt

sed '1d' user_list_with_header.txt > ./user.txt

while read user; do  mysql -u root -e"$user" > user_grant.txt; sed '1d' user_grant.txt >> user_privileges.txt; echo "flush privileges" >> user_privileges.txt; done < user.txt

awk '{print $0";"}'  user_privileges.txt >user_privileges_final.sql

• delete everything, related to  root

sed -i.bak '/root/d' user_privileges_final.sql

• cleanup 

rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt user_privileges_final.sql.bak