by Karen Stevenson on May 15, 2009 // Short URL

Import/Export Large MYSQL Databases

When working with MYSQL I often use phpMyAdmin, which is a nice GUI way to manipulate my database. But some operations won't work in phpMyAdmin when the database is too large. In particular, you can't import or export really large databases using phpMyAdmin. So sometimes you need to do things on the command line.

So I thought I'd document some of the command line snippets we use frequently. In the following, replace [USERNAME] with your mysql username, [DBNAME] with your database name, [/path_to_file/DBNAME] with the path and name of the file used for the database dump, and [/path_to_mysql/] with the path to mysql bin (like /Applications/MAMP/Library/bin/).

Copy/Export a Large Database

MYSQL has no 'Copy' function. You create a copy by dumping the database with mysqldump.

To dump the database and gzip it at the same time, use the following. This will prompt you for your password.

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Import a Large Database

If you want to replace the database with a fresh dump created by the above process, do the following.

First, unzip the file.

gzip -d [/path_to_file/DBNAME].sql.gz

Get to a mysql prompt (you will be asked for your password.)

[/path_to_mysql/]mysql -u [USERNAME] -p

Then do the following to wipe out the old database and replace it with the new dump:

SHOW DATABASES;
DROP DATABASE [DBNAME];
CREATE DATABASE [DBNAME];
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Conditional Dumps

Sometimes the search index is huge and you want to omit it from the dump. Do so with:

mysqldump -u [USERNAME] -p [DBNAME] --ignore-table=[DBNAME].search_index | gzip > [/path_to_file/DBNAME].sql.gz

There are actually a number of tables you could exclude, like the sessions table, the watchdog table and all the cache* tables.

But if you use the above technique to destroy and recreate the database after doing this, you will be missing all those excluded tables. So you will want to do a two step process instead:

First, create a backup with ONLY the table information, no data.

mysqldump -u [USERNAME] -p [DBNAME] --no-data | gzip > [/path_to_file/DBNAME].info.sql.gz

Then create a backup, including only data from the tables you need.

[path_to_mysql/]mysqldump -u [USERNAME] -p [DBNAME]  --no-create-info --ignore-table=[DBNAME].search_index --ignore-table=[DBNAME].cache --ignore-table=[DBNAME].cache_block --ignore-table=[DBNAME].cache_content --ignore-table=[DBNAME].cache_filter --ignore-table=[DBNAME].cache_form --ignore-table=[DBNAME].cache_menu --ignore-table=[DBNAME].cache_mollom --ignore-table=[DBNAME].cache_page --ignore-table=[DBNAME].cache_pathdst --ignore-table=[DBNAME].cache_pathsrc --ignore-table=[DBNAME].cache_views | gzip > [/path_to_file/DBNAME].data.sql.gz;

Well that's a lot of typing. Wouldn't it be nice if there was a wildcard we could use instead of typing out all those cache_ tables? Well there is!! You can do:

[path_to_mysql/]mysqldump -u [USERNAME] -p [DBNAME]  --no-create-info --ignore-table=[DBNAME].search_index --ignore-table=[DBNAME].cache% | gzip > [/path_to_file/DBNAME].data.sql.gz;

After doing this, just import the two files as above, first the one with only the table info, and then the data. Result, a (relatively) small database with all the optional tables emptied out.

Note that the wildcard trick above is not documented anywhere that I can see, so you'll want to test that it works in your setup.

Comments

eigentor

mysqldumper

Being no commandline hero, I use mysqldumper for importing/exporting Databases. http://www.mysqldumper.de/
It can handle large databases and has a lot more sensible options (like doing backups via cron and a perlscript, which can circumvent script runtime).

Phpmyadmin actually starts to choke with fairly small databases, so when I go past say 20MB in the database, it is of no use anymore than for managing and doing SQL operations.

Reply

Stefan Kudwien

my.cnf import settings

To speed up the import of huge (> 1 GB) SQL dumps, you can add the following two lines to the [mysqld] section of your my.cnf and restart MySQL:

[mysqld]
# Performance settings used for import.
delay_key_write=ALL
bulk_insert_buffer_size=256M

This will dramatically speed up the import by delaying index generation and assigning extra large buffers. Remove again after importing, since these settings are not meant for production sites.

Reply

Garrett Albright

The code for importing a

The code for importing a database dump is several more steps than necessary. The below has never failed me:

gunzip dump.sql.gz | mysql -u[USER] -p[PASS] [DBNAME]

Also, assuming all machines concerned have bzip2 installed, consider using that instead of gzip to shave a few bytes off the compressed dump file. And to shave a few more, use the --best flag to tell the compressor to use its best compression scheme (it works for gzip too):

mysqldump -u[USER] -p[PASS] [DBNAME] | bzip2 --best > [DBNAME].sql.bz2

bunzip2 [DBNAME].sql.bz2 | mysql -u[USER] -p[PASS] [DBNAME]

As for the conditional dumps section, some of that stuff is new to me, but I don't understand why two dumps is necessary in this case (one with structure and one with data). Why not just omit the --no-create-info from the second command and omit the first one entirely? Maybe I'm missing something…

As for GUI tools, I like Sequel Pro, the successor to the classic CocoaMySQL, though the interface still has some issues. I like the fact that they're aiming for SQLite and PostgreSQL support in future releases, as well, so once D7 and its new database system come into widespread use, it should help in (what I hope will be) the migrations of many Drupal installations away from the mediocrity of MySQL and on to something better.

Reply

KarenS

Why Two

I don't understand why two dumps is necessary in this case (one with structure and one with data)

You'll need the table data if you're creating a new database or those tables won't exist. The data-only dump will not create any tables.

You'll want to re-create the database any time the tables change to pick up the additional tables. If there is any uncertainty about whether the tables have changed (i.e. a new module may add new tables), the destroy and re-create method may be best.

This is actually describing a method for keeping a local copy of the live database. You may want to do things differently for other purposes. There are lots of different ways of doing this.

Reply

Garrett Albright

I follow you, but still

I follow you, but still don't quite understand the reasoning, so let me rephrase the question: What's the benefit of creating two separate dumps, one with table structure and one with data, over creating a single dump that has both the table structure and the data in the same file?

Reply

pjsz

Because you want different tables in each dump

For the data dump, you want to exclude large unnecessary tables such as caches and search indexes where the data will be worthless to you.

For the schema dump, you need every table obviously.

Thus she does one data dump with a list of ignore tables and another schema dump with a different list of ignore tables. It makes good sense.

I like the source command in mysql. I had not seen that before. Thanks Karen.

Reply

Sztefan

I'd recommend the GNU-script

I'd recommend the GNU-script MySQL Dumper, I'm using it since many years as my standard backup-solution. With this script you can even export large databases, if you have 30 sec script limitation (often on hosted sites), it splits the database in proper slices to not exceed the time limitation. Great!
MySQL-Dumper also keeps an eye of your charset so a language with special-chars will still be readable after import and not full of question-marks for unknown special-chars.

Reply

KarenS

Other Tools

It's great to get a list of other tools, and I want to encourage people to list the ones they use, but I also want to note that part of the reason for knowing the command line code is that we sometimes work on systems we don't control. If they don't have anything other than phpMyAdmin installed, you have to rely on the command line.

Reply

Moshe Weitzman

drush

Helpful tips. Yte I have nearly forgotten these incantations in favor of drush sql dump. You can configure a list of excluded tables in .drushrc file.

Want to move a DB from one server to another - drush sql load

See the drush project.

Reply

Hans van den Berk

Bigdump

Great idea to address this issue for a change.
I have been saved various times by the bigdump.php script.
(Googleing should do the trick).

Reply

Vesa Palmu

Backups via Subversion

We have a few environments where external developers do not have access to actual production environment and should not have access to user profile information. Most developers can only access Subversion, so we have created a small Perl backup script that makes a database dump on demand and sanitizes it to remove cached data + replace user information with static nonsense. This works surprisingly well together with svn even with large databases.

Reply

Rob Loach

Import

Instead of extracting the database and then running it through MySQL. You can do it all in one command with a pipe:
gunzip < [/path_to_file/DBNAME].sql.gz | mysql -u [USERNAME] -p [DBNAME]

This will feed the data to MySQL while it extracts.

Great article, Karen. Keep it up!

Reply