pg_dumpall and mysqldump – how to separate each database into individual files

Today I will show you how I do it. Using pg_dumpall or mysqldump creates one single big file containing all the databases in it. But it is really difficult to restore something from this file. So a better solution is to write each database dump into a single file. Below there is my script, not really a very beautiful one, nor even very safe to use inside a cron, but hey! It works for me and gets the job done!

#!/bin/bash

echo "PostgreSQL"
export PGPASSWORD="postgre password here"
DBLIST=`psql -U postgres -d postgres -q -t -c 'SELECT datname from pg_database'`
for d in $DBLIST
do
  echo "Dumping $d";
  pg_dump -U postgres $d > /path/to/dump/directory/$d.postgres.sql
done

echo "MySQL backup"
mysql -u root -p'mysql root password' -e "show databases" \
	| grep -Ev 'Database|information_schema' \
	| while read dbname;
do
  echo "Dumping $dbname"
  mysqldump -u root -p'mysql root password' $dbname > /path/to/dump/directory/$dbname.mysql.sql
done

In the code shown above, you need to set your `postgres` user password inside the PGPASSWORD variable and set the mysql password and the user (_user_ and _password_ ). You also need to change the path to the output file, so it will be adequate to your local situation.