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.sqlReplace 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
Importing an SQL file can replace tables or data. Inspect the file for database-selection and destructive statements, and back up the destination before importing. Prefer testing with an empty destination 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.sqlCheck for errors and test the application against the destination. Keep a verified backup until you have confirmed that the imported data is complete.