This site is now 100% read-only, and retired.

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:

  • Dump each database seperately.
  • Dump all databases together.

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:

  • -FP
    • Output to plain text (default)
  • -Ft
    • Output to a tar file, specified with --file=filename
  • -Fc
    • Output to a custom file, specified with --file=filename

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:

  • --clean
    • Include commands to clean the database prior to inserting the data in the dump.
  • --create
    • Include commands to create the database in the dump.

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.

 

 


Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (195.172.xx.xx) on Thu 18 Aug 2005 at 11:29
there are lots of VERY useful pg_dump options. And it pays to test them out with your backup scripts.

I use,

--attribute-inserts === makes recovery very slow, but if the table schema changes this is the only one that will work.
--no-owner === not necessary but saves time if you use test machines with different accounts
--disable-triggers === essential if there are any tables with not null foreign key restraints.

disable triggers is particularly useful.

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (82.119.xx.xx) on Thu 18 Aug 2005 at 15:07
Also mysqldump has useful options:
--add-locks ... will add LOCK statements before and after INSERTs to every table, which will result to much faster restore.

-Q ... quote names - will quote db and table names in `quotes` which is good when you have table name which is also reserved word in mysql. Without this option, restore would fail (command like "INSERT INTO lock VALUES...")

[ Parent ]

It's better when it dies...
Posted by Anonymous (132.248.xx.xx) on Thu 18 Aug 2005 at 15:10
It is a good practice _not_ to use reserved words. I prefer the DB complaining I used the wrong descriptor

[ Parent ]

Still very RDBMS-specific
Posted by Anonymous (132.248.xx.xx) on Thu 18 Aug 2005 at 15:08
I mean... Well, you cannot expect anything else, as each RDBMS has its own features and -even with PostgreSQL, which strives to adhere to standards- the dumps you create with one RDBMS are rubbish for any other one - You will not be able to migrate from MySQL to PostgreSQL by doing a mysqldump mydb | pg_restore mynewdb - It's not impossible, but you have to do lots of handwork in between.

[ Parent ]

Re: Still very RDBMS-specific
Posted by Steve (82.41.xx.xx) on Thu 18 Aug 2005 at 15:15
[ View Weblogs ]

Indeed.

I guess most people will pick a database and stick to it, so migration isn't a terribly common thing.

Most of the problem probably comes from the fact that each of the available databases, mysql, postgresql, sqlite, oracle, offers a slightly different implementation of SQL and each has their own unique proprietry add-ons.

Still if you really needed to export+import to another database I'm sure it would be possible - with a bit of perl script or similar.

(I still remember the job of exporting records from the Seachains 4GL binary records (cisam) to a new MySQL database. Painful).

Steve
-- Steve.org.uk

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by K4sperl (212.33.xx.xx) on Thu 18 Aug 2005 at 18:15
You'll get troubles when you try to import the mysql data if the table or databasenames are keywords.
I use the following statement to avoid that:

mysqldump -u root -p -AQca --allow-keywords

See the manpage for more details of the options.

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (80.126.xx.xx) on Thu 18 Aug 2005 at 21:37
[Hm, I don't seem to be able to login to this site (user maurits), though there is no error message. Ah well.]

For mysqldump I recommend to add the command line argument '--opt'. This adds several handy options, the most important of which is '--lock-tables', which will lock all tables for read so you actually get a good dump even when people are trying to update the database at the same time.

Alternatively, use the mysqlhotcopy program.

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (212.202.xx.xx) on Fri 19 Aug 2005 at 07:34

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (62.4.xx.xx) on Fri 19 Aug 2005 at 10:04
i might be wrong, but isn't there a misplaced dollar in line 21 ?

      mv $i ${i/.$j/}.`expr $j + 1 `;

should be

      mv $i {$i/.$j/}.`expr $j + 1 `;
?

I get a : ./backup-mysql.sh: 21: Syntax error: Bad substitution in the first case. (bash isn't my speciality though)

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Steve (82.41.xx.xx) on Fri 19 Aug 2005 at 12:29
[ View Weblogs ]

Nope that is correct as written.

Although I'm not sure why you'd get a syntax error - I know I don't.

(It might be a bashism, not supported by a different /bin/sh ?)

Steve
-- Steve.org.uk

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (193.170.xx.xx) on Fri 19 Aug 2005 at 15:44
Hi!

Thanks for the informative article!

BTW: Don't use an external program (=a process to start) for calculations. Bash can do that itself. Change
`expr $i + 1`
to
$(( $i + 1))

Bye
Hansi

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Steve (82.41.xx.xx) on Fri 19 Aug 2005 at 15:49
[ View Weblogs ]

Good tip, thanks!

I always forget how to to addition, etc, with bash so I've gotten used to using expr for these jobs.

Steve
-- Steve.org.uk

[ Parent ]

Re: Exporting MySQL and PostgreSQL Database Contents
Posted by Anonymous (84.59.xx.xx) on Mon 5 Sep 2005 at 14:44
Just for the record:
I use

mysqldump \
--add-drop-table --add-locks --all --extended-insert \
--flush-logs --single-transaction --delete-master-logs \
--max_allowed_packet=1024k --disable-keys --quick \
--all-databases -u user -p pass

to dump some databases that use binary logs and referential integrity.

[ Parent ]

Finding mysql DB on disk?
Posted by simms (216.46.xx.xx) on Mon 23 Jan 2006 at 12:36
Slightly off-topic, but does anyone know where the Debian version of MySQL stores its actual database files? I'm trying to find a defunct server's DB files on a dying hard drive, and I don't see them anywhere....

[ Parent ]

Re: Finding mysql DB on disk?
Posted by Steve (82.41.xx.xx) on Mon 23 Jan 2006 at 12:41
[ View Weblogs ]

/var/lib/mysql/* - running "locate mysql" will help you in the future ;)

Steve

[ Parent ]

Re: Finding mysql DB on disk?
Posted by Anonymous (82.41.xx.xx) on Sat 28 Oct 2006 at 11:06
Thank you very much! Beginning to despair.

[ Parent ]

Re: Finding mysql DB on disk?
Posted by simms (216.46.xx.xx) on Mon 23 Jan 2006 at 13:04
OK, forget it, I found my answer.
In case anyone stumbles across this thread, the Debian version of MySQL stores all its database files in /var/lib/mysql. These will remain there even if you uninstall MySQL with apt-get remove, unless you also issued a dpkg --purge on your MySQL packages, which flushes all package configuration data, including the databases.

The easiest way to restore a set of old database files to a new MySQL installation is to copy them back to /var/lib/mysql prior to running apt-get install mysql-server. When I did this on my server, the newly-installed MySQL instance simply adopted the old database files as its own without any extra work. This can be verified by logging into mysql and executing the command show databases;.

[ Parent ]