Dump all MySQL and PostgreSQL databases

It's very easy to make dump of a single PostgreSQL/MySQL database. But sometimes you need a quick and simple way to dump all available databases on the server. Probably without using any real programming language too.

About a month ago i made a couple of tiny scripts that i was using along with Ansible to automate backups of staging databases. Final scripts are not open sourced and don't really deserve their own repo, so i just wanted to share it here in case if i need them in future.

PostgreSQL

Create a file pg_dump_all somewhere:

#!/bin/bash

list=$(psql -tqc "SELECT datname FROM pg_database WHERE datistemplate = false")

for d in $list; do
  echo "backing up $d"
  pg_dump --no-owner --clean $d > ./pg_$d.sql
done

No need to store any credentials in the script. Example usage:

$ PGHOST=ip PGUSER=postgres PGPASSWORD=pass PGDATABASE=postgres ./pg_dump_all

MySQL

Create a file mysql_dump_all somewhere:

#!/bin/bash

# Grab all user databases while skipping any system ones
list=$(mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD --skip-column-names -e "SHOW DATABASES;" | grep -v 'mysql\|_schema')

for d in $list; do
  echo "backing up $d"
  mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD --quote-names $d > ./mysql_$d.sql
done

Usage:

$ MYSQL_HOST=ip MYSQL_USER=root MYSQL_PASSWORD=pass ./mysql_dump_all