Create table like between two different servers

Question

I want to execute the CREATE TABLE tbl LIKE tb2 statement. But my problem is that my original table is located on a remote server and I need to create a table like it on my local system. Basically I want to execute a something like CREATE TABLE --source mysql.hostname.com:3306:db_name1:tb1 LIKE --dest 127.0.0.1:3306:db_name2:tb2". I want to execute the LIKE statement because I want to preserve the indexes.

If the above is not feasible then what are the other ways using which I can preserve the indexes created on the original table?

You can try using a DB Link. https://stackoverflow.com/questions/1565993/oracle-database-link-mysql-equivalent — ashwinsakthi, Jun 19 '17 at 14:15
Why not a structure export via phpmyadmin or some else on the other server? — A. Blub, Jun 19 '17 at 14:16

wchiquito · Answer 1 · 2017-06-20T11:00:13.560

One option is to execute the following script:

$ mysql --host=hostname -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name1\`.\`_tb1_to_local\`; CREATE TABLE \`db_name1\`.\`_tb1_to_local\` LIKE \`db_name1\`.\`tb1\`" \
&& mysqldump --host=hostname --opt ``db_name1`` ``_tb1_to_local`` -u $mysqluser -p$mysqlpass \
| mysql -u $mysqluser -p$mysqlpass ``db_name2`` \
&& mysql -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name2\`.\`tb2\`; RENAME TABLE \`db_name2\`.\`_tb1_to_local\` TO \`db_name2\`.\`tb2\`" \
&& mysql --host=hostname -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name1\`.\`_tb1_to_local\`"

Step by step script:

REMOTE (MySQL Server):

mysql> USE `db_name1`;
Database changed

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> DROP TABLE IF EXISTS `db_name1`.`tb1`,
    ->                      `db_name1`.`tb1_B`,
    ->                      `db_name1`.`tb1_A`;
Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> CREATE TABLE `tb1_A` (
    ->   `col0` INT NOT NULL,
    ->   `col1` INT NOT NULL,
    ->   `col2` DECIMAL,
    ->   PRIMARY KEY(`col0`, `col1`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `tb1_B` (
    ->     `col3` INT NOT NULL,
    ->     PRIMARY KEY (`col3`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `tb1` (
    ->   `col4` INT NOT NULL AUTO_INCREMENT,
    ->   `col5` INT NOT NULL,
    ->   `col6` INT NOT NULL,
    ->   `col7` INT NOT NULL,
    -> 
    ->   PRIMARY KEY(`col4`),
    ->   INDEX (`col5`, `col6`),
    ->   INDEX (`col7`),
    -> 
    ->   FOREIGN KEY (`col5`, `col6`)
    ->     REFERENCES tb1_A(`col0`, `col1`)
    ->     ON UPDATE CASCADE ON DELETE RESTRICT,
    -> 
    ->   FOREIGN KEY (`col7`)
    ->     REFERENCES tb1_B(`col3`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> USE `db_name1`; SHOW TABLES;
Database changed
+--------------------+
| Tables_in_db_name1 |
+--------------------+
| tb1                |
| tb1_A              |
| tb1_B              |
+--------------------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE `db_name1`.`tb1`\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `col4` int(11) NOT NULL AUTO_INCREMENT,
  `col5` int(11) NOT NULL,
  `col6` int(11) NOT NULL,
  `col7` int(11) NOT NULL,
  PRIMARY KEY (`col4`),
  KEY `col5` (`col5`,`col6`),
  KEY `col7` (`col7`),
  CONSTRAINT `tb1_ibfk_1` FOREIGN KEY (`col5`, `col6`) REFERENCES `tb1_A` (`col0`, `col1`) ON UPDATE CASCADE,
  CONSTRAINT `tb1_ibfk_2` FOREIGN KEY (`col7`) REFERENCES `tb1_B` (`col3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

LOCAL (Terminal):

$ mysql --host=hostname -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name1\`.\`_tb1_to_local\`; CREATE TABLE \`db_name1\`.\`_tb1_to_local\` LIKE \`db_name1\`.\`tb1\`"

REMOTE (MySQL Server):

mysql> USE `db_name1`; SHOW TABLES;
Database changed
+--------------------+
| Tables_in_db_name1 |
+--------------------+
| _tb1_to_local      |
| tb1                |
| tb1_A              |
| tb1_B              |
+--------------------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE `db_name1`.`_tb1_to_local`\G
*************************** 1. row ***************************
       Table: _tb1_to_local
Create Table: CREATE TABLE `_tb1_to_local` (
  `col4` int(11) NOT NULL AUTO_INCREMENT,
  `col5` int(11) NOT NULL,
  `col6` int(11) NOT NULL,
  `col7` int(11) NOT NULL,
  PRIMARY KEY (`col4`),
  KEY `col5` (`col5`,`col6`),
  KEY `col7` (`col7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

LOCAL (MySQL Server):

mysql> USE `db_name2`; SHOW TABLES;
Database changed
Empty set (0.00 sec)

LOCAL (Terminal):

$ mysqldump --host=hostname --opt ``db_name1`` ``_tb1_to_local`` -u $mysqluser -p$mysqlpass \
| mysql -u $mysqluser -p$mysqlpass ``db_name2``

LOCAL (MySQL Server):

mysql> USE `db_name2`; SHOW TABLES;
Database changed
+--------------------+
| Tables_in_db_name2 |
+--------------------+
| _tb1_to_local      |
+--------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `db_name2`.`_tb1_to_local`\G
*************************** 1. row ***************************
       Table: _tb1_to_local
Create Table: CREATE TABLE `_tb1_to_local` (
  `col4` int(11) NOT NULL AUTO_INCREMENT,
  `col5` int(11) NOT NULL,
  `col6` int(11) NOT NULL,
  `col7` int(11) NOT NULL,
  PRIMARY KEY (`col4`),
  KEY `col5` (`col5`,`col6`),
  KEY `col7` (`col7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

LOCAL (Terminal):

$ mysql -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name2\`.\`tb2\`; RENAME TABLE \`db_name2\`.\`_tb1_to_local\` TO \`db_name2\`.\`tb2\`"

LOCAL (MySQL Server):

mysql> USE `db_name2`; SHOW TABLES;
Database changed
+--------------------+
| Tables_in_db_name2 |
+--------------------+
| tb2                |
+--------------------+
1 row in set (0.00 sec)

LOCAL (Terminal):

$ mysql --host=hostname -u $mysqluser -p$mysqlpass -e "DROP TABLE IF EXISTS \`db_name1\`.\`_tb1_to_local\`"

REMOTE (MySQL Server):

mysql> USE `db_name1`; SHOW TABLES;
Database changed
+--------------------+
| Tables_in_db_name1 |
+--------------------+
| tb1                |
| tb1_A              |
| tb1_B              |
+--------------------+
3 rows in set (0.00 sec)

Hi, Can you please explain how this works. I want to create table2 `Like` table1. I will pull the data from table1 selectively later. Won't doing `mysqldump` get all the data as well? — shshnk, Jun 20 '17 at 04:44

Create table like between two different servers

1 Answers1