Followers

Monday, July 22, 2013

Importing & exporting MySQL dump files including/excluding data


Let us suppose we have a database created in MySQL as: testDB

EXPORT

1. DATA + STRUCTURE
[user@pc]$ mysqldump -u user -p testDB > /path-to-export/testDB.sql

2. STRUCTURE Only
[user@pc]$ mysqldump -u user -p --no-data testDB > /path-to-export/testDB.sql

3. DATA only
[user@pc]$ mysqldump -u -p --no-create-db --no-create-info testDB > /path-to-export/testDB.sql

IMPORT

1. STRUCTURE + DATA
[user@pc]$ mysql -u user -p testDB < /path-from-import/testDB.sql

I would like to mention about an issue that you might face while importing a huge dump file (specially genome databases). Default MySQL configuration will give you an error:
 "Got a packet bigger than 'max_allowed_packet' bytes"

The solution is to globally increase the import size of MySQL server+client versions

1. While importing add --max_allowed_packet=100M or your specified size.
e.g.: [user@pc]$ mysql -u user -p --max_allowed_packet=100M testDB < /path-from-import/testDB.sql

2. Open another terminal and login into MySQL server as root
Add the following lines:
mysql>  set global net_buffer_length=10000000;
mysql>  set global max_allowed_packet=10000000000;

Now proceed with the import command.

Hope it helps you!

2 comments: