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.