How to Backup and Restore MySQL Database from Command Line

Often times you need to move your site from one server to another and while moving your site you need to also move your website database. However, some huge sites may have database size in GBs (2GB or more), which can't be easily exported or imported via PHPMyAdmin. Let see how we can create MySQL dump from command line and restore it.

You can see here that many people have hard time importing huge databases, sometime due to upload limits and sometime due to import error.

Lets solve this problem and do MySQL dump restore from command line.

Command Line

We will create and restore MySQL backup using command line.

Step 1: Create MySQL Dump from Command Line

The first step is to create MySQL dump from command line. Usually people don't run into problems while creating the dump, so you can also do that via PHPMyAdmin. But in this tutorial we will create the dump using command line.

To create the dump you need to SSH into your server, you can read our other article regarding transfer files over ssh in which we've explained how to SSH into your server using PuTTY.

Once inside SSH, run the command to create the dump

sudo mysqldump -u [user] -p[password] [database_name] > [filename].sql

To create MySQL dump you need to have user and its password (this user should have access to the database).

1. Replace [user] with user who have access to database.
2. Replace [password] with actually password of the above user.
3. Replace [database_name] with the database you want to create the backup for.
4. [filename].sql will be the resulting file once the dump is complete, so give it some friendly name which would be easy to remember.

Once MySQL dump is created, you need to move this file to the server where you want to restore it.

Step 2: MySQL Restore from Dump using Command Line

Second step is to restore the dump file we've just created above. Log into your server via SSH where you have transferred this file.

Step 1: Create Database

Before doing the restore you need to create database where this dump will be restored.

You can also create database using command line, first login to your MySQL server:

mysql -u root -p<ROOT_PASSWORD>

Replace <ROOT_PASSWORD> with your MySQL root password.

Create database

create database [DATABASE_NAME]

Replace [DATABASE_NAME] with name of database and your database will be created.

Step 2: MySQL Restore Dump

Once database is created it is time to restore your dump using the command below

mysql -u [user] -p [database_name] < [filename].sql

Replace [user] with root or actual user of database, also make sure to replace [filename].sql with actual path to your dump file that you have transferred above.