How to: Export and import a MySQL database using SSH

Regularly backing up your databases is essential to maintaining up to date data, recover from a data loss event, or recover specific data from an earlier time.

Importing and exporting using SSH (Secure Shell) provides the most stable and secure way for backing up large databases over 50mb.

Prerequisites

• SSH access to server

Exporting a MySQL database

1. Access the server via SSH e.g. ssh 111.111.111.111 (enter the server's IP address)
   Execute the following command:
   

   mysqldump -p -u username database_name > DBbackup.sql

   
   Required details as per above:
   username = mySQL username
   database_name = mySQL database name
   DBbackup.sql = output file
   

2. Enter password when prompted

3. The database is now backed up to your current directory and is namedDBbackup.sql

Importing a mySQL database

1. Upload the .sql backup file to yourserver and navigate to this folder using SSH. The file must be in this format, please unzip if packed up in a .zip or .tar.gz file

2. Create a new mySQL database in cPanel

3. Execute the following command:

   mysql -u username -p database_name < DBbackup.sql

   
   Required details as per above:
   username = mySQL username
   database_name = newly created database on server
   DBbackup.sql = backup file