====== MySQL and PostgreSQL Backup Script ======

===== Reset Forgotten PostgreSQL Password =====

If necessary, as ''root'':

<file>
sudo su postgres && psql

ALTER USER postgres with password 'newpostgresuserpassword';

\q

exit
</file>

===== PostgreSQL Password =====

For scripts that run as ''root'':

<file>
touch ~/.pgpass && chmod 0600 ~/.pgpass && vim ~/.pgpass
</file>

and add:

<file>
#server:port:database:username:password
localhost:*:*:postgres:yourpostgresuserpass
</file>

===== db_backup.sh =====

:!: You must edit the script, at least for the MySQL 'root' password.

You can easily modify this script to only backup MySQL or only PostgreSQL.  However, the script checks to see if either PostgreSQL or MySQL database is running, then only backs up if they are.

<file>
#!/bin/sh
#
# Script to dump mysql and postgresql databases before nightly backup
#
# Use chmod 700 on this file so only root (or sudo) can view contents

# Backup destination dir
BACKUP="/root/sql_backup"

# Mysql password
MYSQLPW="password"
# Mysql username
MYSQLUSER="root"

# PostgreSQL backup username
PGSQLUSER="postgres"

# Location of the backup logfile.
logfile="$BACKUP/sql_backup.log"

################### end of configuration variables ###################

# Create the backup destination if it doesn't exist
if [ ! -d $BACKUP ]; then
    mkdir -p $BACKUP
    echo "Created backup destination directory..." >> $logfile
    echo "" >> $logfile
fi

#
# Postgresql
#

if pgrep postgres > /dev/null; then

echo "Begin Backing up PostgreSQL Databases...         `date`" >> $logfile
echo "" >> $logfile

# Dump all databases at once
/usr/bin/pg_dumpall -w --clean -h localhost -U $PGSQLUSER | gzip > "$BACKUP/postgresql-all-databases.sql.gz"
echo "Backup complete for all PostreSQL databases at once at `date +%H:%M`" >> $logfile
echo "" >> $logfile

# Individual databases
LIST_PG_DB_QUERY="select datname from pg_database where not datistemplate and datallowconn order by datname;"

databases=`psql -h localhost -U $PGSQLUSER -At -c "$LIST_PG_DB_QUERY" postgres`
for i in $databases; do
   /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
   /usr/bin/pg_dump $i -h localhost -U postgres | gzip > "$BACKUP/postgresql-$i-database.sql.gz"
   echo "Backup and Vacuum complete for database: $i at `date +%H:%M`" >> $logfile
done
else
   echo "PostgreSQL is not running.  Skipping..." >> $logfile
fi

echo "" >> $logfile

#
# Mysql
#

if pgrep mysqld > /dev/null; then

echo "Begin Backing up MySQL Databases...              `date`" >> $logfile
echo "" >> $logfile

# One big dump
/usr/bin/mysqldump -u $MYSQLUSER -p$MYSQLPW --add-locks --add-drop-table --events --ignore-table=mysql.event --skip-lock-tables --ignore-table=performance_schema.* -A -Q | gzip > "$BACKUP/mysql-all-databases.sql.gz"
echo "Backup complete for all Mysql databases at once at `date +%H:%M`" >> $logfile
echo "" >> $logfile

# Individual databases

for db in `mysql -u $MYSQLUSER -p$MYSQLPW -BNre "show databases;"`
do
    if [ "$db" != "information_schema" ] && [ "$db" != "performance_schema" ]
    then
      mysqldump -u $MYSQLUSER -p$MYSQLPW --add-drop-table --events --ignore-table=mysql.event --skip-lock-tables --ignore-table=performance_schema.* -QB "$db" | gzip > "$BACKUP/mysql-$db-database.sql.gz"
      echo "Backup complete for database: $db at `date +%H:%M`" >> $logfile
    fi
done
else
    echo "MySQL not running.  Skipping..." >> $logfile
fi

echo "" >> $logfile

exit
</file>

====== Restore A MySQL Backup ======

One-liner to restore a gzipped MySQL backup:

<file>
gunzip < mysql-database-name.sql.gz | mysql -p -h localhost mysql-database-name
</file>

:!: You will need to enter the database password of the database on 'localhost' being restored to.