How to Import or Export a MySQL Database over SSH

phpMyAdmin is fine for small databases, but large imports can time out in a browser. Over SSH you can import or export a database of any practical size with a single command.

Before you start

• Set up SSH access for your site (see our SSH guides) and connect.
• Have your database details ready from the MySQL Databases section in StackCP: the host name, database name, username and password. For a WordPress site, the same details are in wp-config.php.
• Upload your .sql file to your hosting (for example into your home folder, outside public_html).

Import a .sql file

mysql -h DB_HOST -u DB_USER -p DB_NAME < backup.sql

                        
                    

Replace DB_HOST, DB_USER and DB_NAME with your details. The -p option on its own makes MySQL ask for the password after you press Enter, so it isn't saved in your shell history. On our platform the database name and username are often the same.

Import a compressed file

.zip file: unzip it first, then import.

unzip backup.sql.zip
mysql -h DB_HOST -u DB_USER -p DB_NAME < backup.sql

                        
                    

.gz file: either decompress it first:

gunzip backup.sql.gz
mysql -h DB_HOST -u DB_USER -p DB_NAME < backup.sql

                        
                    

or stream it straight in without creating a large extra file:

gunzip < backup.sql.gz | mysql -h DB_HOST -u DB_USER -p DB_NAME

                        
                    

Export a database to a file

Use mysqldump to create a backup:

mysqldump -h DB_HOST -u DB_USER -p DB_NAME > export.sql

                        
                    

Add compression to save space and download time:

mysqldump -h DB_HOST -u DB_USER -p DB_NAME | gzip > export.sql.gz

                        
                    

Common problems

• "Access denied": check the username and password, and that the user belongs to that database.
• "Table already exists": the dump is being imported into a database that already has tables. Empty it first (after taking a backup) or use a fresh database.
• Size: each MySQL database is currently limited to 1 GB. Limits can change; ask support if you need more.

Tips

• Never leave .sql files inside public_html, where they could be downloaded by anyone. Delete them once the import is finished.
• If you are moving a WordPress site to a new domain, run a search-and-replace on URLs after importing (for example with wp search-replace).

Need help?

Our support team is happy to take a look. Open a ticket at app.fimurohost.com/submitticket.php or message us on WhatsApp at 01818160926.