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:
We will use the database; Linuxhint:
To display the tables of the Linuxhint database, use the command:
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:
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:
To verify the creation of the database, execute the command:
The new database has been created, exit the MySQL server using the command:
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:
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:
The data has been copied successfully, to verify this, we will open the MySQL server using the command:
Display the databases, using the command:
To use Linuxhint_backup, execute the command:
Display the tables, using the command:
It can be seen that all the data of the database “Linuxhint” has been copied in the Linuxhint_backup successfully.
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.