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.