Easy Ways to Backup and Restore MySQL Databases

Backup and Restore Database is very important for a Database Administrator (DB Admin). Where the Backup process allows us to get a backup of the database that we have. Meanwhile, the Restore process is used to restore/restore the database in case of an emergency or to transfer the database to another server.

This article will explain how easy it is to perform a MySQL Database Backup and Restore process. Here is the explanation:

1. Database Backup

The command used to backup the database in MySQL is mysqldump. Here is the command format:

$ mysqldump -u username -p database_name > data-dump.sql

– username : MySQL username that can be used to enter the database;
– database_name : the name of the database to be backed up;
– data-dump.sql : data dump of the backup process.

Example:

$ mysqldump -u dbadmin -p service > 20200807-service.sql

From the example above, we can see that the username used to perform this backup process is dbadmin , then the database to be backed up is service , and the backup file will be 20200807-jasa.sql

Note:
For the backup filename format, it is recommended to use the following format:

[year][month][date]-[db name].sql

By using the file format as above, it will be easier for us to identify what database is being backed up and when the backup process is done.
To make sure the backup results are as desired, verify with the head command. Here’s an example command:

$ head -n 5 20200807-service.sql

This command will produce output like below:

— MySQL dump 10.13 Distrib 8.0.21, for Linux (x86_64)

— Host: localhost Database: service
————————————————– ——
— Server version 8.0.21-0ubuntu0.20.04.4

Make sure the Database fields match the name of the database that we are backing up, in this example it is a service database .

2. Restore Database

To restore the database, the first step that must be done is to create a new database. In this tutorial we will create a new database named new_service . Here is the command:

$ mysql -u root -p

mysql> CREATE DATABASE new_service;
Query OK, 1 row affected (0.20 sec)

mysql> exit
bye

To perform the Restore database process, here is the command format:

$ mysql -u username -p new_database < data-dump.sql

– username : MySQL username that can be used to enter the database;
– newdatabase : the name of the new database that will be used to store the restoration results;
– data-dump.sql : data dump file to be imported/restored.

Example:

$ mysql -u dbadmin -p new_service < 20200807-service.sql

From the example above, we can see that the username used to carry out the restore process is dbadmin , then the new database that holds the restoration results is new_services , and the dump data to be imported/restored is 20200807-jasa.sql

If the restore command is successful then it will not issue any output. On the other hand, if this process is not successful, MySQL will display an error message in the terminal.

These are the steps that need to be taken to backup and restore a MySQL database. Thank you for visiting, I hope this article is useful.

Leave a Reply

Your email address will not be published.