How to Backup and Restore MySQL Database in CentOS

Backup and Restore

This guide will show you how to backup and restore MySQL database using command line tool in VPS server on CentOS 7 and PhpMyAdmin. You need to take backup of your MySQL database on a daily basis to have minimal impact on your web site’s data loss. Similarly for some inconsistent situations you may need to restore your site’s data into MySQL database.

The PhpMyAdmin tool may be found on your cpanel, webuzo or any other control panel.

Backup MySQL Database

This section will explain with examples how to take backup of your existing MySQL database using command line tool as well as PhpMyAdmin tool.

Why do you need a backup?

It is recommended to backup your MySQL database on a daily basis even if your site is running fine. You never know when an accidental issue occurs and you completely lose your data.

The following situations may be well suited for database backup:

  • It’s important to backup your database when you make some significant changes on your database.
  • You need to keep a backup when you want to perform some clean up actions on your database for optimization purpose.
  • You want to migrate your site from one hosting to another hosting company.
  • You want to upgrade your hosting service from shared to VPS or VPS to dedicated.

There may be other situations as well when you need to backup your database.

Backup using command line tool

I am using here CentOS 7 in VPS server. mysqldump is the command line tool which is used to backup MySQL (MariaDB) database.

The format of the command that should be executed to backup your MySQL database is given below:

$ sudo mysqldump -u <database username> -p <database name> > <backup file name>.sql

sudo is required for regular user with administrative privileges. If you are logged as root user then you don’t need to use sudo. As you are are using sudo you may be asked to provide your regular user’s password to verify the intention.

Don’t forget to replace <database user name>, <database name> and <backup file name> by your own values.

The -p is used for the prompt to ask your database password.

The complete command is given using an example:

$ sudo mysqldump -u root -p roytuts_db > roytuts-backup.sql

You may need to know how to login to VPS server on CentOS 7 using SSH.

Backup using PhpMyAdmin

If you are using control panel such as cpanel, webuzo etc. then you will find the PhpMyAdmin tool in the control panel. You can easily use this tool to backup your database.

For example, we can see below how to backup using cpanel.

First login to your cpanel using your user credentials.

backup and restore mysql database using command line tool and phpmyadmin

Under the Databases section you will find the tool PhpMyAdmin as shown in the below image:

backup and restore mysql database using command line tool and phpmyadmin

Now clicking on the PhpMyAdmin tool will redirect you to the databases as shown in the below image:

backup and restore mysql database using command line tool and phpmyadmin

To backup your database click on the database on the left side, then click on Export link. The following screen appears. You can click on Custom – display all possible options.

backup and restore mysql database using command line tool and phpmyadmin

Accept the defaults or select different options as per your requirements. Finally click on the Go button at the bottom.

When Save As option appears for the backup file, save to an appropriate directory.

Restore MySQL

This section tells your how to restore your MySQL database either using command line tool or using PhpMyAdmin tool found on cpanel, webuvo etc..

Why do you need to restore?

You need to restore your database for several reasons, for example:

  • You performed clean up for optimization purpose but did not go well.
  • You want to move from shared to VPS or VPS to dedicated hosting server.
  • Accidentally you uninstall MySQL server.
  • You updated software in your web site but did not go well.

There may be other reasons also to restore your database.

Restore using Command Line Tool

I am using Linux based CentOS 7. mysql is the command line tool to restore your database file into the MySQL (MariaDB) server.

You may need to know how to login to VPS server on CentOS 7 using SSH.

Once you login to your remote server make sure you have the database file (.sql) in your user’s home directory or any other location with appropriate permission for read/execute access.

If you do not have the database file in your user’s account then you need to first upload using FileZilla or any other FTP client tool into the server.

I assume you have already created the database and database user with appropriate permissions in MySQl (MariaDB) server.

To restore database you can execute the format of the following command:

$ sudo mysql -u <database user name> -p <database name> < <database file name>.sql

sudo is required for regular user with administrative privileges. If you are logged as root user then you don’t need to use sudo. As you are are using sudo you may be asked to provide your regular user’s password to verify the intention.

Don’t forget to replace <database user name>, <database name> and <backup file name> by your own values.

The -p is used for the prompt to ask your database password.

The complete command is given using an example:

$ sudo mysql -u root -p roytuts_db < roytuts-db.sql

Restore using PhpMyAdmin

It is assumed that your database already exists in the MySQL server. If database does not exist then you can create one using MySQL Databases link as shown in the below image:

backup and restore mysql database using command line tool and phpmyadmin

You need to create database and user for your database. Finally attach the user to database.

Now you need to click on the PhpMyAdmin link as found in the above image. Clicking on this link will redirect you to the page where your databases are displayed.

Now click on Import link at the top.

backup and restore mysql database using command line tool and phpmyadmin

Now you can choose your .sql or .sql.zip file for importing tables and data into database.

Hope you got an idea how to backup and restore your database files.

Leave a Reply

Your email address will not be published. Required fields are marked *