In this tutorial, we’ll learn how to create a backup of any database using MySQLDumps and then we’ll recover the database.
Introduction
The backing up of a database is a crucial part of a Database Management System. As a database administrator, it is important to learn how to create a backup of databases. But the question arises, is why? What is the need for backups? The answer to this is, We need to create a backup of databases for reasons like when we are testing the database or at the time of migrating a database.
For this tutorial, you must have MySQL installed on your laptop/desktop, you can download and install it from the mentioned link: https://dev.mysql.com/downloads/
We are all set now and let’s get started!
Database Backup using MySQLDumps
It is believed for this tutorial that you already have a database and tables for which you need to create the backup. You can also refer to our database, for understanding how backup with MySQLDump works. In this tutorial, we’ll be using a database named work which consists of two tables(employee and sample).
To create a backup of a database using MySQLDump, we must know the path of MySQLDump. Now, When you installed MySQL, it was installed inside the program files. You can go to C drive and you’ll find the program files folder, inside the program file folder, search for MySQL and enter into the MySQL folder.
When you’ll enter into the MySQL folder you’ll get three different folders, you need to select MySQL Server 8.0. Remember, that 8.0 is the version and it may vary according to the version you’ve installed. After getting into the MySQL Server folder, you’ll find a Bin folder, and within the bin folder exists the MySQLDump file.
The path is mentioned: C:\Program Files\MySQL\MySQL Server 8.0\bin
Copy the path for the bin folder, or you can directly copy the above path because this is the default path for most of us.
Now, we’ll open a command prompt, when you search for CMD on search are, do select to open cmd as administrator. Make sure to use cmd as an administrator only, as the process needs administrator privileges.
After opening the command prompt, write:
cd/Code language: Bash (bash)We need to start from the root directory, now we’ll enter into the bin folder of MySQL:
cd C:\Program Files\MySQL\MySQL Server 8.0\binCode language: Bash (bash)Now, that we are inside the bin folder, it’s time to log into MySQLDump, to do that follow the below-mentioned command:
mysqldump -u root -p work > workdump.sqlCode language: SQL (Structured Query Language) (sql)- -u is for MySQL username, in this case, our username is root, you write the name of your MySQL username.
- -p is for the password, after writing -p we will write the name of the database we want the backup for, in our case we have the database name as work. After that, we’ll write the location path where we want to save the backup and the name with which it will be saved. We’ve written the name of the archive only because we want to store it within the MySQL Server folder.
If you didn’t receive any error message then it means that the backup was successful. You can check for the backup file in the directory or folder which you mentioned in the command.
This is how you create a backup for any database in MySQL using MySQLDump.
Recovering a database
Now that we have backed up our database, this is the time to drop that database and see if we could recover it back, which we in turn will.
To drop a database, use the below-mentioned statement:
DROP DATABASE work;Code language: SQL (Structured Query Language) (sql)We are dropping the work database here as we created the backup of this database only.
Now when you need to restore a database, you must first create a new database, and then only you’ll be able to restore all the tables within the database. So we’ll create a new database and it will again be called work.
CREATE DATABASE work;Code language: SQL (Structured Query Language) (sql)Now we’ll open the command prompt as Administrator and get into the bin directory of MySQL Server, the path is mentioned below:
C:\Program Files\MySQL\MySQL Server 8.0\bin
Now we will insert the backed up data into the new database which we have created, it’s exactly how we backup the data just one thing will change, the sign between the database name and the dump file will change to less than sign.
mysqldump -u root -p work < workdump.sqlCode language: SQL (Structured Query Language) (sql)Conclusion
Now that you know how to backup your MySQL database, you can rest assured that your data is safe and can be recovered if necessary. Remember to always back up your database regularly so that you have a recent copy in case of disaster. That’s how we create a backup and restore it using MySQLDump.
Useful Resource:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html