mysql_change_engine

Sometimes, you need to have two tables with different engines replicating to each other. For example, memory table should have a shadow table(InnoDB or MyISAM), or you can have DML operations on the master (InnoDB) and report operation on the slave(MyISAM) Other example is to load data, for example, you can create a loading table(CSV + Blackhole) to replicate the data on the salve(InnoDB or MyISAM) In this case, it is best to have tables with the same data in different engines in order to use the pros of each engines.

The procedure itself it simple, on the target table:

mysql> alter table newTable engine=MyISAM;   <- This command :)
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>  select * from newTable;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Julien |
|    2 | Vera   |
|    3 | Lukas  |
|    4 | Jonas  |
|    5 | Damien |
|    6 | MyISAM |
+------+--------+
6 rows in set (0.00 sec)

mysql> show table status\G;
*************************** 1. row ***************************
           Name: newTable
         Engine: MyISAM    <- We changed our engine from InnoDB to MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 20
    Data_length: 120
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-06-11 05:59:19
    Update_time: 2018-06-11 05:59:37
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

  • mysql_change_engine.txt
  • Last modified: 2019/10/18 20:04
  • by 127.0.0.1