How to export and import a .sql file in MySQL from command line

Here are some command lines to export and import data to and from a .sql file on MySQL/MariaDB.

To export

If the entire database is needed to be exported, then run the following command:

$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

If all the DBs from the server need to be exported, then run the following command:

$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

If only some specific tables within a database is needed, then run the following command:

$ mysqldump -u [uname] -p[pass] db_name table1 table2 > tables_backup.sql

If the database is big you can add the auto-compressing output using gzip:

$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

If you have access remotely then the following would work:

$ mysqldump -P [port] -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

To import

If the entire DB is needed to be restored the following command will import .sql data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < db_backup.sql

If you have a dedicated database server, replace localhost hostname with actual server name or IP address as follows:

$ mysql -u username -p -h 10.10.99.11 databasename < db_backup.sql

Note1: If you have remote access to the database or if the database is on the localhost you might consider a GUI tool like SQLyog.

Note2: you need to create the database before importing the data.

References

• http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html