MySQL Database engine replacement

Recently in doing MySQL master and slave need to make database engine changes, and then sorted out some ways to share with you if you have better ideas can be exchanged in comments.

1. View MySQL now provides engines.

On the MySQL command line, enter

Show engines;

View the current default storage engine.

Mysql> Show variables like '%storage_engine% ';

View the engine for a single table.

Mysql> Show create table table name;

2. Modify the default engine.

Join in MY.CNF and restart the service

Skip-innodbdefault-storage-engine=myisamdefault-tmp-storage-engine=myisam

3. Exchanging a single database engine

"1" ALTER TABLE

mysql > ALTER TABLE mytable engine=falcon;

This syntax is suitable for all engines, but the conversion process can be time consuming. MySQL performs a progressive copy of the old table to the new table for this purpose. During this time, the conversion operation may occupy all the server's I/O processing power, and the source table will be read locking at the time of conversion. Therefore, do this on a busy table and be aware of it.
If you do a table conversion from one engine to another, all the proprietary features that belong to the original engine will be lost. For example, converting a innodb table to a MyISAM table, and then converting it back, all foreign keys that were originally defined on the original InnoDB table will be lost.

"2" dump (dump) and import

If you want more control over the process of table conversion, you can choose to use the Mysqldump tool to dump the table into a text file and then edit the dump file to modify the CREATE TABLE statement in it. It is important to note that table names and engine types are modified, because even if the engine type is different, two tables with the same table name are not allowed in the same database. In addition, mysqldump will silently add the drop TABLE command before the CREATE TABLE statement, and if you are not careful, you are likely to lose the original data.

Mysqldump-uroot-s/tmp/mysql2.sock mysql > *.sql mysql-uroot-s/tmp/mysql2.sock db1 < *.sql

Modify the SQL file before importing

Vim *.sql

: 1, $s/innodb/myisam/G

This method makes a balance between the first two methods, which does not dump the entire table or convert all the data at once, but instead creates a new table, using the MySQL insert ... Select syntax to transfer data.
as follows:
mysql > CREATE TABLE innodb_table like myisam_table;
mysql > ALTER TABLE innodb_table engine=innodb;
mysql > INSERT into innodb_table SELECT * from myisam_table;

This approach works well if the amount of data is small, but the more efficient way is to populate the table incrementally, committing the transaction when populating each incremental block of data, so that the undo log becomes too large. Assuming that the ID is a primary key, you can run the following query repeatedly (each time you increment the values of x and y) until all the data is copied to the new table.
mysql > START TRANSACTION;
mysql > INSERT into innodb_table SELECT * from Myisam_table
WHERE ID between x and y;
mysql > COMMIT;

when the transfer operation is complete, the source table is persisted and can be deleted after the operation is completed, and the new table is populated. Note: If necessary, lock the source table at the time of conversion to avoid inconsistent data when copying.

Originated from http://www.cnblogs.com/loveLearning/archive/2013/04/17/3025502.html

This article is from "a man who doesn't want to dream" blog, please keep this source http://jzzjw.blog.51cto.com/9921462/1656587

MySQL Database engine replacement