MySQL: How to export and import an .sql file from command line?

exec(“mysqldump -u root -pPASSWORD databasename | gzip > dump.sql.gz”);



If it’s an entire DB, then:
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

If it’s all DBs, then:
$ mysqldump -u [uname] -p[pass] –all-databases > all_db_backup.sql

If it’s specific tables within a DB, then:
$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

You can even go as far as auto-compressing the output using gzip (if your DB is very big):
$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):

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


To import

Type the following command to import sql data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import ‘data.sql’ file into ‘blog’ database using Sathish as username:
$ mysql -u sat -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:
$ mysql -u username -p -h databasename < data.sql

OR use hostname such as
$ mysql -u username -p -h database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h < data.sql



Developer, Technology aficionado, Beer Lover and founder of Network. Who shares a keen interest about website development, health issues, cancer awareness and other fun stuff.

Add comment

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Most popular

Most discussed