Importing and Exporting Databases over Command Line (CLI)

As a web hosting provider, we frequently import and export databases over the command line. It is incredibly faster than downloading and uploading SQL dumps over HTTP through phpMyAdmin, and has the added benefit of bypassing common resource limitations in cPanel hosting environments.


Export a MySQL Database over Command Line:

mysqldump -u %username% -p'%password%' %databasename% > db-backup.sql

The %username%, %password% and %databasename% sections will need to be replaced with what matches an actual database you have on your server. For instance, if you had a username of testuser, a password of Pass1234 and a database name of testdb then the command would look like:

mysqldump -u testuser -p'Pass1234' testdb > db-backup.sql

Import a MySQL Database over Command Line:

mysql -u %username% -p'%password%' %databasename% < db-backup.sql

The same variables apply as before, so if you have a username of testuser, a password of Pass1234 and a database name of testdb then the command would look like:

mysql -u testuser -p'Pass1234' testdb < db-backup.sql

Importing and Exporting from External Database Severs:

Exporting from an External Server:

mysqldump -P 3303 -h 127.0.0.1 -u testuser -p'Pass1234' testdb > db-backup.sql

The -P is for the port, the -h is for the hostname. The -P is optional, but is typically going to be 3303 unless a custom port was used. The hostname could be an IP address or server hostname.

Importing from an External Server:

mysqldump -P 3303 -h 127.0.0.1 -u testuser -p'Pass1234' testdb < db-backup.sql

The -P is for the port, the -h is for the hostname. The -P is optional, but is typically going to be 3303 unless a custom port was used. The hostname could be an IP address or server hostname.

Related Tip: How to Delete Every error_log file on a cPanel Server.

How to Boost Your Website Conversions


Disclaimer: WebCitz, LLC does not warrant or make representations concerning the accuracy, likely results, or reliability of the information found on this page or any websites linked to from this page. This article was written by David W in his or her personal capacity. The opinion(s) expressed in this article are the author's own and may not reflect those of WebCitz, LLC. When you purchase through links on this site, we may earn an affiliate commission.