How to Copy Database in MySQL

1. Copy the Database On the Same Server Instance in MySQL
2. Use the | or Pipe Operator to Copy Command in MySQL

To create a copy of the existing database is known as the MySQL Clone method. The clone includes creating a copy of table structure, constraints, functions, procedures, triggers, and all the features related to a table in one go.

The features allow users more reliability as even one node goes down, the replica or the clone can serve. Making a database copy and clone can help when an accidental loss or failure happens.

The backup process can happen daily or over a particular interval of time. The ways to copy the database over the same server instance are below.

Copy the Database On the Same Server Instance in MySQL

Copy the source database in a SQL script file. Create a new target database and then import the SQL script file into a new database.

It is a three-step process.

> 	mysql -u root -p
	****
>	mysqldump -u root -p firstDb > firstDbDump.sql
>	create database newDb;
>	mysql -u root -p newDb < firstdump.sql
> 	show databases;
> 	use newDb;
>	show tables;

The detailed description of the first approach above and the commands are below.

1. To proceed with the first approach, install MySQL server 8.0 in the local system.

2. Proceed with the installation process, and after successful installing, reach the bin path.

3. Open environment variables and add the path of the MySQL system in the system PATH variable. It allows the accessibility of MySQL from any place in the file system.

4. Open the command prompt and enter the command shown above. This command enables the use of the MySQL prompt.

   The -u attribute specifies the username that proceeds the value; the root is the username. And the -p attribute specifies the password, which comes in another line.

   The password is never added to the command. It comes in another line in an encrypted format.

5. Enter the command to copy files to a temporary intermediate file from the location.

6. mysqldump -u root -p database_name_to_be_cloned > filename_for_clone is the command that specifies the mysqldump utility with username and password as options.

   It takes the database name as a parameter and puts the dump in a file. The > argument directs the output to a particular file.

7. If the absolute file path of the file name is not provided, the SQL file will be stored in the bin directory where the MySQL server gets installed.

8. Create a new database where the SQL is supposed to get imported to make a clone of the existing DB.

9. Once a temporary or intermediate file gets formed, it can again read into the newly formed DB using the < operator. The operator redirects the SQL result in the newDb database.

10. The database can get checked using the command show databases;. This command will list out all the databases in the current SQL connection.

11. The use command allows the user to use the specified database. The use command takes a parameter as a database name.

12. The show tables allow the user to list all the tables present in a database. Hence the copied contents can get checked with the last three commands.

Use the | or Pipe Operator to Copy Command in MySQL

One liner command takes a dump of the source database and redirects to another target database.

> mysqldump -u root -p sourceDb | mysql -u root -p targetDb

1. Reach to the bin folder where MySQL gets installed.

2. Open the command prompt from the bin folder.

3. Use the above command as it is, beforehand creating source and target database respectively.

4. The command creates the SQL dump using the mysqldump utility, taking username and password as mandatory options. And the output is redirected to another target database.

5. The | or pipe symbol is mostly a LINUX/UNIX command operator that allows users to use two or more commands in one line.

   It acts as a pipeline to provide the output of the first command to another one. The second command is generally separated using the pipe symbol.

6. The command is the same described in approach one and uses the pipe symbol in one line.

Attaching a screenshot of the above command executed successfully.

As we enter the above command, it prompts the user for the password twice. As per the command here, the -p option is mentioned twice, asking the user for the password for both databases.