How to Backup and Restore MySql Database

Backup and restoration of your database is a very important process that you should know when you are running your site in production environment.

If your database is on a remote server, you need to SSH into the server for backup and restore of your database.

You can check out my post on "How to SSH into your remote server" to understand how to connect to your remote server via SSH if you don't know about it.


Backup

To Backup all MySql Databases use the following command:
mysqldump -uroot -p --all-databases > my_backup_dbs.sql

Let me give you a simple explanation for the command:

  • -u flag is to specify the mysql user that you would have created. Mostly it would be root
  • -p flag is to specify the password for the mysql user
  • --all-databases flag is used to backup all databases that are stored on your server

This command will take backup of all the databases and put the sql code in the file you specify after >. eg. my_backup_dbs.sql


To Backup single database
mysqldump -uroot -p [dbname] > my_backup_db.sql

This command will take backup of the database that you specify dbname and put the sql code in the file you specify after >. eg. my_backup_db.sql

The above mysqldump commands for backup will only backup the table structure and data


Download the backup file

Once you run the above command, my_backup_db.sql file will be generated in the root of the server.

You should download this backup file in your local machine or somewhere else otherwise if server fails, the backup file will also be lost.

To download the backup file from the server, you can use FTP Client like WinSCP or Filezilla

You can read my post on "How to use FTP client to upload and download files from a remote server"


Restoration

Upload the backup file on remote server

Before you can restore the database from your backup file, you need to upload the backup file on the remote server.

You can read my post on "How to use FTP client to upload and download files from a remote server" to know how to upload the backup file.


To Restore the Database

Before restoring the database, we need to create an empty database of the same name that we need to restore. For doing this there are two ways:

  1. Create new empty database in mysql

    mysql -u[user] -p
    Enter password: *******
    mysql> CREATE DATABASE dbname;
    mysql> USE dbname;
  2. Add these lines at the top of your backup file my_backup.sql

    CREATE DATABASE dbname;
    USE dbname;
    
    .....

Use the following command to restore the database:

mysql -u[user] -p < my_backup_db.sql

Simple explanation for the command:

  • -u flag is to specify the mysql user that you would have created. Mostly it would be root
  • -p flag is to specify the password for the mysql user

This command will restore the database from the backup file my_backup_db.sql that you specify after <.


From this post we learned:

  • How to Backup a Database from a remote server
  • How to Restore a Database in a remote server

I hope you have learned something out of this post.


Tags: