MySQL Replication Table Detailed Explanation and Instance Code

Detailed Explanation of MySQL Replication Table

If we need to completely copy the data table of MySQL, including the structure, index, default value of the table, etc. This is not possible if you only use the CREATE TABLE... SELECT command.

This section will show you how to copy the MySQL data table completely. The steps are as follows:

Use the command SHOW CREATE TABLE to obtain the Create Data Table (CREATE TABLE) statement, which contains the structure, index, and so on of the original data table. Copy the SQL statement shown in the following command, modify the data table name, and execute the SQL statement. The data table structure will be completely copied by the above command. If you want to copy the contents of the table, you can do so using the INSERT INTO... SELECT statement.

Instances

Try the following instance to copy the table tutorials_tbl.

Step 1:

Gets the complete structure of the data 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`)
) TYPE=MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

Step 2:

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 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 performing Step 2, you will create a new cloned table clone_tbl in the database. If you want to copy the data from the table you can use the INSERT INTO... SELECT statement.

mysql> INSERT INTO clone_tbl (tutorial_id,
  ->            tutorial_title,
  ->            tutorial_author,
  ->            submission_date)
  -> SELECT tutorial_id,tutorial_title,
  ->    tutorial_author,submission_date
  -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

After performing the above steps, you will copy the table completely, including the table structure and table data.

Thank you for reading, hope to help everyone, thank you for your support to this site!