Database-agnostic dumps from mysql 1

Friday, October 21

MySql ships with a program called mysqldump for dumping table definitions and data from a database.

Usage: mysqldump [OPTIONS] database [tables]

There are a several of [OPTIONS], but when I want a nice, compact dump that I can use to create a schema definition, I use the following:

$ mysqldump -dr schema.ddl some_db --compact --compatible=mysql323,no_table_options --add-drop-table

This command results in a fairly database-agnositic schema definition in schema.ddl. There are no comments, no proprietary table or field definitions, and no data. The first option, -d, tells mysqldump to skip the data; -r tells it to output to schema.ddl; the rest of the options are verbose.

Creating new databases from this schema is painless. First, use the mysqladmin program to create a new, empty database:

$ mysqladmin create another_db;

Next, use the mysql client to execute the sql from schema.ddl:

$ mysql another_db < schema.ddl

Notes: For all three programs (mysqldump, mysqladmin, mysql), you can specify a user/pass with -u someuser -p . The commands executed will run as this user. Oh, and the -p option needn’t take an argument—you’ll be prompted for your password if you omit it.)


Leave a response

  1. PackagethiefNovember 02, 2005 @ 08:30 AM

    Just an update. I have a few apps hosted on Textdrive and as it turns out, they don’t allow table-locking by default.

    To force mysqldump to skip the table locks, add the --skip-lock-tables option.