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!
Good one, thanks for the post!
ReplyDeletenice one..
ReplyDelete