How to use clone in MySQL?

There may be a number of occasions where you need to create an exact copy of an already defined (or created) table. enables you to perform this operation. Because we may need such duplicate tables for testing over the data without having any impact over the original table and the data stored in it.

Original_table –

IDF_nameL_nameProject_idEmailJob_TitleCityAgeSalary1.MadhavMohan [email protected],000/-2.MukundMohan [email protected],000/[email protected],50,000/[email protected],000/[email protected] MgrNoida2690,000/-

Steps to replicate (Clone) an existing table schema (structure) and it’s content –

Step 1 : To clone a table, use the query below. Using this query an empty schema (structure) of the table gets created with the same attributes of original table :

There may be a situation when you need an exact copy of a table and CREATE TABLE ... SELECT doesn't suit your purposes because the copy must include the same indexes, default values and so forth.

You can handle this situation by following the steps given below −

  • Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.

  • Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have the exact clone table.

  • Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.


Try out the following example to create a clone table for tutorials_tbl.

Step 1 − Get the complete structure about the table.

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
      Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
   `tutorial_id` int(11) NOT NULL auto_increment,
   `tutorial_title` varchar(100) NOT NULL default '',
   `tutorial_author` varchar(40) NOT NULL default '',
   `submission_date` date default NULL,
   PRIMARY KEY  (`tutorial_id`),
   UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
1 row in set (0.00 sec)

No query specified

Step 2 − Rename this table and create another table.

mysql> CREATE TABLE clone_tbl (
   -> tutorial_id int(11) NOT NULL auto_increment,
   -> tutorial_title varchar(100) NOT NULL default '',
   -> tutorial_author varchar(40) NOT NULL default '',
   -> submission_date date default NULL,
   -> PRIMARY KEY  (tutorial_id),
   -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

Step 3 − After executing step 2, you will create a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement.

How to clone a database in MySQL?

First, use the CREATE DATABASE statement to create a new database..
Second, store the data to an SQL file. We can give any name to this file, but it must end with a . ... .
Third, export all the database objects along with its data to copy using the mysqldump tool and then import this file into the new database..

What is clone in MySQL?

The clone plugin, introduced in MySQL 8.0. 17, permits cloning data locally or from a remote MySQL server instance. Cloned data is a physical snapshot of data stored in InnoDB that includes schemas, tables, tablespaces, and data dictionary metadata.

How to clone table in MySQL with data?

The fastest way to copy a table in MySQL:.
Right-click the table you want to copy in Database Explorer and select Duplicate Object..
In the dialog that opens, select the destination db..
Select to copy the table data or structure only..
Specify the name of the new table, and click OK..

How do I clone a MySQL database to another server?

To copy a MySQL database from a server to another, you use the following steps:.
Export the database on the source server to a SQL dump file..
Copy the SQL dump file to the destination server..
Import the SQL dump file to the destination server..