How to Import/Export databases in MySQL from command-line

Recently i worked with MySQL import/export databases and i have found some useful commands below:

Export:

• All databases into sql file using mysqldump tool

mysqldump -u [username] -p --all-databases > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p --all-databases | gzip > /home/username/filename.sql.gz

• Single database into sql file using mysqldump tool

mysqldump -u [username] -p [dbname] > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p [dbname] | gzip > /home/username/filename.sql.gz

• Single database table into sql file using mysqldump tool

mysqldump -u [username] -p [dbname] [table_name] > /home/username/filename.sql

In case you want to compress at the same time, run below command

mysqldump -u [username] -p [dbname] [table_name] | gzip > /home/username/filename.sql.gz

Import:

• Database from sql dump

mysql -u [username] -p [dbname] > /home/username/filename.sql

or

mysql > source /home/username/all_databases_export.sql [/code]

• Single database from the dump(if it contains multiple databases):

mysql -u [username] -p --one-database [dbname] > /home/username/all_databases_export.sql

NOTE :

• While importing some large database i experience below error something as

ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes

In this case i just increase the value of –max_allowed_packet(Default=16M) as below command

mysql -u [username] -p --max_allowed_packet=256M [dbname] > /home/username/filename.sql

• Better using absolute paths.