Home Linux CentOS How to Take Backup and Restore the MySQL Database

How to Take Backup and Restore the MySQL Database

I want to know how can I take the backup of a MySQL database?? How can I restore the database??

Backup and Restore MySQL DatabaseBackup and Restore MySQL Database

As a system administrator, it is mostly required skill to take backup and restore the MySQL Database. We will let you know both the steps i.e. backup and restore MySQL database by command line or by the phpMyAdmin tool.

Methods:

1. Take database backup by command line.
2. Graphical method (phpMyAdmin)

Recommended: How to install MySQL 8.0 Version.

Method 1. Backup and Restore the MySQL Database by the command line

It is a safe side to take the MySQL database backup regularly. In the local environment or production environment, it is necessary. To take the database backup mysqldump utility is used.

1. Take Full Database Backup

For example, we want to take the backup of database ‘testdb’ in a sqlbackup.sql file. Run the following command.

# mysqldump -u root -p testdb > sqlbackup.sql

where,
mysqldump = Backup utilitu name
-u = indicates user
root = User name
-p = For password
testdb = Database name to which we want to take backup.
sqlbackup.sql = New file name which will store the database backup.

2. Take backup of a single table

If we need to take the table backup only. Here we have a table name ‘tbl_students’. Then mention the database name and its table name which we need to take the backup.

# mysqldump -u root -p  testdb  tbl_students > tbl_students.sql

3. Backup multiple databases

In some cases, we need to take the backup of multiple databases at the same time.We have a databases like testdb, testdb2 and testdb3. Run the folllowing command:

# mysqldump -u root -p --databases testdb testdb2 testdb3 > testdbsqlbackup.sql

4. Take backup of all the databases

Run the below mentioned command when we required to take backup of all the databases.

 # mysqldump -u root -p --all-databases > all-db-sqlbackup.sq 

Restore MySQL Database

Restoring MySQL database is a simple task. Here we are restoring the backup from sqlbackup.sql file to the testdb using the mysql command.

# mysql -u root -p testdb <  sqlbackup.sql 

Method 2. Take Backup of MySQL Database by phpMyAdmin (GUI)

phpMyAdmin is an open source tool for managing MySQL databases. It is a graphical tool to manage the databases of MySQL. Assuming that you have installed phpMyAdmin on Linux server and you can open it in the browser with http://localhost/phpMyAdmin.

phpMyAdmin homepagephpMyAdmin homepage

To take the backup, select the database. Here we have choose the mysql database.

Choose database for the backupChoose database for the backup

Next step to click on the Export on the top navigation bar. Export methods choose the Quick option. Format – select SQL from the drop down. Finally, click on Go and prompt will display on the screen to save the file.

Database backup steps by phpmyadminDatabase backup steps by phpmyadmin

This is the way to take MySQL database backup by phpMyAdmin tool.

Restore MySQL Database using phpMyAdmin

Login to the phpMyAdmin and choose the New option from the left navigation bar.

Restore database using phpMyAdminRestore database using phpMyAdmin

Create the new database. Give new database name. For example testdb and click on create.

Create new database using phpmyadminCreate new database using phpmyadmin

New created database will be shown at left navigation bar. Next click on the Import option.

New database createdNew database created

Next windows will show the option to import the MySQL backup file. Click on the Browse and upload the MySQL backup file. Once importing is done then it will show the message like import has been successfully finished.

Import the MySQL databaseImport the MySQL database



Congratulations !!! You learn how to take backup and restore MySQL database by command line as well as by GUI method (phpMyAdmin).

LEAVE A REPLY

Please enter your comment!
Please enter your name here