Want to get Lullabot article, videocast, and podcast announcements delivered right to your in-box?
Let us know your email address (we won't share it) and we'll let you know when anything exciting happens.
Import/Export Large MYSQL Databases
May 15, 2009
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.gzGet to a mysql prompt (you will be asked for your password.)
[/path_to_mysql/]mysql -u [USERNAME] -pThen 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.gzThen 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 on this post will automatically be closed three months from the original post date.
Twitter
Mailing list
RSS Feed
Comments
MySQL Admin (GUI Tools)
MySQL Admin of the MySQL also does a great job, although it will get choked up sometime on the cache_ Drupal tables.
And the GUI tools are cross platform.
http://dev.mysql.com/downloads/gui-tools/5.0.html
Backup and Migrate
I like to use the Backup and Migrate module for exporting. With it you can easily exlude data from selected tables. Very usefull for taking backups before updating sites.
Very good post, like it!
Very good post, like it! :)
Can I make a ~translated post on my blog for the hungarian drupal community linked to your site?
Absolutely!
Anyone is welcome to translate this information :)
Thank you, the post is
Thank you, the post is already done :) Here you can find it:
http://blog.philcom.hu/2009-05-15/nagymeretu-mysql-adatbazisok-importjae...
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.
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.
NIce Information
Hey Stefan Kudwien ,
Awesome!! It’s just what I need!! Thanks!
Web Design New Jersey
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
--bestflag 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-infofrom 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.
Why Two
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.
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?
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.
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.
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.
wow, thanks for grouping up
wow, thanks for grouping up everything I was looking for
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 loadSee the drush project.
Another Drupal module for this (and, uh, much more)
Scripts for dealing with databases in a Drupal module (and for merging development and production sites and deploying them, but that's the 'more' part that you don't need to pay attention to) ... Kathleen Murtagh's DBScripts.
benjamin, Agaric Design Collective
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).
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.
Alternative for russians
To restore a backup or you can use a Sypex Dumper. This tool is easy to find with the help of the Yandex.
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!