You can export a MySQL database to an SQL file and import it using SSH. This is useful when browser-based tools cannot handle the database size or when you need command-line control over the process.

Before you begin

You need SSH access, compatible MySQL client tools, database credentials and permission to export or import the data. Use the full database and username, including any cPanel prefix. Store backup files outside the website’s public directory.

Export the database

From a private backup directory, use:

umask 077
mysqldump -u DATABASE_USER -p --single-transaction --no-tablespaces DATABASE_NAME > backup.sql

Replace DATABASE_USER and DATABASE_NAME. Enter the password at the prompt; do not put it in the command.

Check that the command finishes successfully and the file contains the expected data. –single-transaction provides a consistent snapshot for transactional tables such as InnoDB, but not for MyISAM. Avoid schema changes during export. Routines and scheduled events need their own export options and privileges if the application uses them.

Import the database

Create the destination through your hosting panel where applicable and grant the database user the required permissions. Then run:

mysql -u DATABASE_USER -p DESTINATION_DATABASE < backup.sql

Check for errors and test the application against the destination. Keep a verified backup until you have confirmed that the imported data is complete.