Exporting MySQL and PostgreSQL Database Contents

Posted by Steve on Thu 18 Aug 2005 at 12:04

One of the simplest and most easily understood means of creating database backups is to dump the raw contents as SQL commands - which can later be used to reinsert all the data.

Both PostgreSQL and MySQL allow you to dump the contents of a database to plain SQL queries in much the same way, and they can be used to create simple backup scripts.

MySQL

To export a MySQL database you'll need to use the command mysqldump, this has several options which allow you to tune the dumped data.

The most obvious parameters you'll need to give are the username and password to connect to the server as.

After specifying the username and password your choice boils down to how you wish to dump things:

To dump all databases you can use:

mysqldump --user=root --pass=password --all-databases

This will output the complete structure of each database upon your system, along with the data stored in each.

If you wish to only show the table structure, without the contents, you can use the --no-data flag:

mysqldump --user=root --pass=password --all-databases --no-data

In most cases it makes more sense to just dump a particular database. In that case you can simply specify the name of the database to dump by appending its name to the command line.

For example to dump the database named "machines" use:

mysqldump --user=root --pass=password machines

(Again you can add "--no-data" to only dump the structure).

There are several more options you can use to control what kind of data is dumped in the output, to see them all consult the manpage via:

man mysqldump
Simple MySQL Backup Script

The following is a simple backup script I run upon my host to backup each individual database to its own compressed file, keeping backups for seven days.

It generates a list of all the database names taking advantage of the fact that all MySQL databases exist as directories beneath /var/lib/mysql, then it exports the current contents after rotating old backups:

#!/bin/sh

#
#  Directory we store the dumps in.
#
BACKUP_DIR=~/db-backups

#
#  Make sure output directory exists.
#
if [ ! -d $BACKUP_DIR ]; then
    mkdir -p $BACKUP_DIR
fi

#
#  Rotate backups
#
for j in  6 5 4 3 2 1 0; do
    for i in $BACKUP_DIR/*.gz.$j; do 
	if [ -e $i ]; then
	    mv $i ${i/.$j/}.`expr $j + 1 `;
	fi
    done
done


#
# Create new backups
#
for i in /var/lib/mysql/*/; do
    dbname=`basename $i`
    mysqldump --user=root $dbname |  \
	gzip > $BACKUP_DIR/$dbname.gz.0
done

To restore from a given file you would uncompress the given backup file and feed it to mysql, similar to the following command:

gunzip testdb.0.gz | mysql --user=root --pass=password
PostgreSQL

PostgreSQL has a similar tool for dumping individual, or complete, databases called "pg_dump".

Usage is similar to the mysqdump, simply specify the name of the database to be dumped, and any optional parameters:

pg_dump databaseName

You can specify several different output formats including:

If you wish to export blobs, or large objects, in your backup you must specify a non-text output format - one of the ones listed above.

There is a lot more flexability when it comes to selecting the output format in PostgreSQL than in MySQL, and you should consult the manpage for full details (via "man pg_dump").

Options you might consider using include:

To restore a given dump you can use a command similar to:

pg_restore -d newdb db.tar

This restores the contents of the dump db.tar (created with "pg_dump -Ft -b db >db.tar") to the database called newdb.

If you wish to dump all databases upon a host, rather than just a specific one, you will instead need to use the pg_dumpall command.

The flags used for pg_dumpall are identical to those used in pg_dump.

This article can be found online at the Debian Administration website at the following bookmarkable URL (along with associated comments):

This article is copyright 2005 Steve - please ask for permission to republish or translate.