Export or Import a MySQL database via CLI

This guide will walk you through how to export or import a MySQL database using MySQL command line tools

To complete this guide you will need to have your MySQL user credentials on hand and have SSH Access to your hosting server. Serversaurus hosting relies on key based authentication to login via SSH/SFTP. To set up SSH access you can generate a SSH key pair and configure it in cPanel before proceeding with this guide.

When you are ready to begin, please SSH to your hosting server.

Export database

To export your database please adjust the below command to include your MySQL username, password and preferred filename.

mysqldump -u username -p database_name > database_backup.sql

MySQL will export the database dump to your current directory unless specified otherwise, if you would like to specify where the database is exported to, specify the absolute path.

For example:

mysqldump -u username -p database_name > /var/www/database_backup.sql

Import database

1. Login to MySQL using the below command, enter your password when prompted:

   mysql -u username -p;

2. This step has two variants, please choose from step A or B depending on your setup and then carry on to step 3.

   a) The first variant is that you need to create the database, please use the below command to create the database:

   create database database_name;

   b) The second variant is if you have an existing database which you would like to import to, you will need to follow the below steps:

   Drop the database to remove any existing tables:

   drop database database_name;

   Then recreate the database:

   create database database_name;

3. Change into your database:

   use database_name;

4. Then import your database backup using the below command, be sure to update the command with the absolute path to the .sql backup and correct .sql file name.

   source /var/www/database_backup.sql;