How to export/import a mySQL database using SSH

Follow

Introduction

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
    If you do not have access via SSH, please refer to this help article:
    https://help.serversaustralia.com.au/hc/en-us/articles/204177700-How-do-I-get-SSH-Access

Exporting a mySQL database

  1. Access the server via SSH

  2. Execute the following command:

    mysqldump -p -u username database_name > DBbackup.sql

    Where:
    username = mySQL username
    database_name = mySQL database name
    DBbackup.sql = output file 

  3. Enter password when prompted

  4. The database is now backed up to your current directory and is named DBbackup.sql

Importing a mySQL database

  1. Upload the .sql backup file to your server and navigate to this folder using SSH.
    N.B. 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 
    https://help.serversaustralia.com.au/hc/en-us/articles/202819880-How-Do-I-Create-a-MySQL-Database-in-cPanel

  3. Execute the following command:

    mysqldump -u username -p -D database_name < DBbackup.sql

    Where:
    username = mySQL username
    database_name = newly created database on server
    DBbackup.sql = backup file 
Have more questions? Submit a request

Comments

Powered by Zendesk