MySQL Replication Table

MySQL Replication Table

If we need to copy the MySQL data table completely, including the table structure, index, default value and so on. If you only use CREATE TABLE ... The SELECT command is not possible.

This article will show you how to copy the MySQL data sheet in full, with the following steps:

• Use the SHOW CREATE TABLE command to get the Createtable statement, which contains the structure, index, and so on of the original data table.
• Copy the SQL statement displayed by the following command, modify the data table name, and execute the SQL statement, which will completely replicate the data table structure with the above command.
• If you want to copy the contents of the table, you can use INSERT into ... SELECT statement to implement.

Instance

Try the following instance to copy the table Cnblogs_tbl.

Step One:

Gets the complete structure of the data table.

Mysql>SHOWCREATE TABLEcnblogs_tbl \g;*************************** 1. Row***************************       Table: Cnblogs_tblCreate Table:CREATE TABLE' cnblogs_tbl ' (' cnblogs_id ' )int( One) not NULLauto_increment, ' Cnblogs_title 'varchar( -) not NULL default "', ' Cnblogs_author 'varchar( +) not NULL default "', ' submission_date ' datedefault NULL,  PRIMARY KEY(' cnblogs_id '),UNIQUE KEY' Author_index ' (' Cnblogs_author ')) ENGINE=InnoDB1Rowinch Set(0.00sec) Error:no query specified

Step Two:

Modify the data table name of the SQL statement and execute the SQL statement.

Mysql> CREATE TABLE' Clone_tbl ' ( -' cnblogs_id 'int( One) not NULLAuto_increment, -' Cnblogs_title 'varchar( -) not NULL default "',   -' Cnblogs_author 'varchar( +) not NULL default "',   -' Submission_date ' datedefault NULL,   - PRIMARY KEY(' cnblogs_id '), - UNIQUE KEY' Author_index ' (' Cnblogs_author ') -) ENGINE=InnoDB; Query OK,0Rows Affected (1.80Sec

Step Three:

After performing the second step, you will create a new clone table Clone_tbl in the database. If you want to copy data from a datasheet you can use INSERT into ... SELECT statement to implement.

Mysql> INSERT  intoclone_tbl (cnblogs_id, -Cnblogs_title, -Cnblogs_author, -submission_date) - SELECTCnblogs_id,cnblogs_title, -cnblogs_author,submission_date -  fromcnblogs_tbl; Query OK,3Rows Affected (0.07sec) Records:3Duplicates:0Warnings:0

After performing the above steps, you will complete the replication table, including the table structure and table data.

MySQL Replication Table