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 section 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 Tutorials_tbl.

Step One:

Gets the complete structure of the data table.

Mysql>SHOW CREATE TABLE tutorials_tbl \g;*************************** 1.Row*************************** Table:Tutorials_tblCreate Table:CREATE TABLE' Tutorials_tbl ' ( ' tutorial_id ' Int(11)Not NULL auto_increment, ' Tutorial_title 'varchar(100)Not NULLDefault ‘‘, ' Tutorial_author 'varchar(40)Not NULLDefault ‘‘, ' Submission_date 'Datedefault Null, PRIMARY KEY  ( ' tutorial_id '  UNIQUE KEY  ' author_index '   ( ' Tutorial_author ' )  Type= Myisam1 row in set  (0.00 sec 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 ' ( - ' tutorial_id ' Int(11)Not NULL auto_increment, - ' Tutorial_title 'varchar(100)Not NULLDefault ‘‘, - ' Tutorial_author 'varchar(40)Not NULLDefault ‘‘, - ' Submission_date 'DateDefaultNull, -> PRIMARY KEY  ( span class= "str" > ' tutorial_id ' ),  -> ' author_index '   ( Tutorial_ Author ' ) ->  Type=myisam; query Ok, 0  rows affected  (1.80 Sec )  

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 into Clone_tbl(tutorial_id, -Tutorial_title, -Tutorial_author, -Submission_date) -SELECT tutorial_id,Tutorial_title, -> Tutorial_authorsubmission_date -> from Tutorials_tblquery Ok,3 rows affected  (0.07 Secrecords:  3 duplicates: 0 warnings: 0    

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

MySQL Replication Table