How to copy or clone a MySQL database

In MySQL the data can be backed up by making its clone or making a copy, so in case of any mishap, it can be retrieved from its clone. For the new users of MySQL, it is the popular database management tool for relational databases(SQL); it manages the data by arranging them in tables which not only help the developers to manage them easily in a well-structured way but also improved the processing of the computer by which it is being operated.

To create the backup of the database in MySQL, we will make a clone or copy in a very easy procedure which will be discussed in detail in this article.

How to copy or clone a MySQL database

To understand the making of a clone of a database in MySQL, we will consider an example of the database named; Linuxhint. To get a list of all databases used:

SHOW databases;

We will use the database; Linuxhint:

USE Linuxhint;

To display the tables of the Linuxhint database, use the command:

SHOW TABLES;

To make the backup of the database “Linuxhint” including all its tables, we use a backup command-line utility known as mysqldump. Before proceeding towards the backup of the database, let us have a little bit of a brief introduction about the “mysqldump”.

What is mysqldump command

The mysqldump command is used to create and restore the backup of the MySQL database and the general syntax of using it:

mysqldump -u [user_name] –p [database_name] > [dumpfilename.sql]

The explanation is as:

Use the clause mysqldump to create the backup of the database
Use the -u flag with the user_name to connect the MySQL server
Use the -p flag for the password of the user
Replace the database with the database name which you want to clone
Use the “>” sign to create a backup
Assign a path and also suggest a name to clone file with .sql extension

Proceeding towards the clone of “Linuxhint” we will first create another database to backup the data of the Linuxhint database in it with the name, Linuxhint_backup:

CREATE DATABASE Linuxhint_backup;

To verify the creation of the database, execute the command:

SHOW DATABASES;

The new database has been created, exit the MySQL server using the command:

exit;

We will create a sql file in the Home directory with the name of Linuxhint.sql and will export all the objects of the database Linuxhint to the file Linuxhint.sql using the mysqldump utility:

$ mysqldump -u root -p Linuxhint > Home:\Linuxhint.sql

In the above output, the mysqldump utility accesses the database “Linuxhint” and will ask for the database password, provide it. After this import the data of the file “Linuxhint.sql” to “Linuxhint_backup” using the command:

$ mysql -u root -p Linuxhint_backup < Home:\Linuxhint.sql

The data has been copied successfully, to verify this, we will open the MySQL server using the command:

$ mysql -u root -p

Display the databases, using the command:

SHOW databases;

To use Linuxhint_backup, execute the command:

USE Linuxhint_backup;

Display the tables, using the command:

SHOW tables;

It can be seen that all the data of the database “Linuxhint” has been copied in the Linuxhint_backup successfully.

Conclusion

The data can be lost either due to a crash of the server or by the user’s negligence, so it is always better to have a backup to be on the safe side. In MySQL, the backup of the databases can be created by using a simple method which is through the use of the mysqldump backup utility. A new empty database is created in MySQL, then using the mysqldump utility a sql file is created anywhere in the computer where all the data is of the database exported, then that data is copied to the newly created database again using the mysqldump utility. In this way, you can make a clone of any database in the MySQL.

How to copy or clone a MySQL database