Backing up PostgreSQL and MySQL databases |
Everyone knows how vital backing up is.
Here is my PostgreSQL and MySQL backup scheme:
backup_db.sh shell script:
#!/bin/sh
# date variable with YYY-MM-DD format
date=`date "+%Y-%m-%d"`
########### PostgreSQL backup ###########
# using pg_dumpall util to dump PostgreSQL database structure and contents to pg_backup.sql
/usr/bin/pg_dumpall -o -v >/home/postgres/pg_backup/pg_backup.sql
# compress pg_backup.sql
tar jcvf /home/postgres/pg_backup-$date.tar.bz2 /home/postgres/pg_backup
# remove uncompressed version
rm -f /home/postgres/pg_backup/pg_backup.sql
# move backup to /home/postgres/db_backup/
mv /home/postgres/pg_backup-$date.tar.bz2 /home/postgres/db_backup/pg_backup-$date.tar.bz2
########### MySQL backup ###########
# using mysqldump util to dump MySQL database structure and contents to my_backup.sql
# db_user - database user
# db_password - database password
# db_name - database name
/usr/bin/mysqldump -ac --add-drop-table --user="db_user" --password="db_password" db_name > /home/postgres/my_backup/my_backup.sql
# compress my_backup.sql
tar jcvf /home/postgres/my_backup-$date.tar.bz2 /home/postgres/my_backup
# remove uncompressed version
rm -f /home/postgres/my_backup/my_backup.sql
# move backup to /home/postgres/db_backup/
mv /home/postgres/my_backup-$date.tar.bz2 /home/postgres/db_backup/my_backup-$date.tar.bz2
Directories /home/postgres/my_backup/, /home/postgres/db_backup/ and /home/postgres/db_backup/ must exist, or change paths to your needs.
To make backups weekly I use cron:
linux ~ # su postgres
postgres@linux /root $ crontab -e
in crontab add line:
5 20 * * 6 /home/postgres/backup_db.sh
Now every satrurday at 20:05 cron will run /home/postgres/backup_db.sh script. In Directory /home/postgres/db_backup/ you will have my_backup-YYYY-MM-DD.tar.bz2 and pg_backup-YYYY-MM-DD.tar.bz2 backups, where YYYY-MM-DD is year, month and day when backup is done.
If you need to restore MySQL database do following:
tar -xjvf my_backup-YYYY-MM-DD.tar.bz2 # extract compressed backup
mysql -u db_user -p db_password -D db_name < my_backup.sql # restore/create database
If you need to restore PostgreSQL database do following:
login as postgres user
tar -xjvf pg_backup-YYYY-MM-DD.tar.bz2 # extract compressed backup
psql -f pg_backup.sql template1 # restore/create database