I want to know how can I take the backup of a MySQL database?? How can I restore the 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.
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
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.
To take the backup, select the database. Here we have choose the mysql database.
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.
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.
Create the new database. Give new database name. For example testdb and click on create.
New created database will be shown at left navigation bar. Next click on the Import option.
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.
Congratulations !!! You learn how to take backup and restore MySQL database by command line as well as by GUI method (phpMyAdmin).