Bash Script for Backing Up and Restoring All MySQL Databases and Users

This note is about how to quickly dump all MySQL databases into separate files from bash:
Backup:

for db in $(echo "show databases;" |mysql |grep -v "Database\|^mysql$\|information_schema\|performance_schema\|^test");
do
	 mysqldump --routines --opt $db |gzip > "$db".sql.gz;
	 echo "done with $db";
done

Restore:

for db in $(ls |cut -d "." -f 1);
do
  mysql $db < "$db".sql;
  echo "done with $db";
done

If during restore you also need to create new databases:

for db in $(ls |cut -d "." -f 1);
do
	 echo "create database \`$db\`;" |mysql;
	 mysql $db < "$db".sql;
	 echo "done with $db";
done

You can just copy and paste all of this into the terminal.
I print “done with $db” so I can tell which databases have already been processed.

And here’s how you can get all MySQL users with their passwords and database privileges:

for user in $(echo "select concat(user,'@',host) from mysql.user;" |mysql |grep -v concat);
do
  echo "show grants for $(echo $user|sed -e "s|@|'@'|g" -e "s|^|'|g" -e "s|$|'|g");" |mysql |grep -v "^Grants for\|root" |sed 's/$/;/g';
done

You can copy the output and paste it into the MySQL console on the new server, or save it to a file.
We exclude the root user so restoring on a new server doesn’t overwrite the existing one.