How to export and import a .sql file in MySQL from command line
import
export
.sql file
mysql
mariadb
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.