MySQL replication table details and example code

MySQL replication table detailed explanation

If we need to completely copy the MySQL data table, including the table structure, indexes, default values, etc. This cannot be achieved using only the CREATE TABLE ... SELECT command.

This section will introduce how to completely copy a MySQL data table. The steps are as follows:

1. Use the SHOW CREATE TABLE command to obtain the create table (CREATE TABLE) statement, which contains the structure and index of the original table.
2. Copy the SQL statement displayed in the following command, modify the data table name, and execute the SQL statement. The above commands will completely copy the data table structure.
3. If you want to copy the contents of a table, you can use the INSERT INTO ... SELECT statement to do so.

Examples

Try the following example to copy the table tutorials_tbl.

Step 1:

Get 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 completing the second step, you will create a new clone table clone_tbl in the database. If you want to copy the data in a table you can use the INSERT INTO... SELECT statement to do so.

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 executing the above steps, you will completely copy the table, including the table structure and table data.

Thank you for reading, I hope it can help you, thank you for your support of this site!