If i change mysql engine from Myisam to innodb, will it affect on my data

Question

I am new to Mysql . will it affect my data on server if i change mysql engine from Myisam to innodb.

Thanks

Answer 1

Changing engine from MyISAM to INNODB should not affect your data, but safe side you can easily take backup of your table before changine engine.

1. Taking backup:

   CREATE TABLE backup_table LIKE your_table;
   
   INSERT INTO backup_table SELECT * FROM your_table;
   

2. It may affect the performance of your queries. You need to configure Innodb specific System variables. e.g.

   innodb_buffer_pool_size = 16G
   innodb_additional_mem_pool_size = 2G
   innodb_log_file_size = 1G
   innodb_log_buffer_size = 8M
   

3. Changing engine to INNODB:

   ALTER TABLE table_name ENGINE=INNODB;
   

Answer 2

I found two caveats when converting MyISAM tables to InnoDB: row size and support for full-text indexes

I ran into an issue converting some tables from an off-the-shelf application from MyISAM to InnoDB because of the maximum record size. MyISAM supports longer rows than InnoDB does. The maximum in a default InnoDB installation is about 8000 bytes. You can work around this by TRUNCATE'ing a table that fails, but this will bite you later on the INSERT. You might have to break your data up into multiple tables or restructure it with variable length column types such as TEXT (which can be slower).

A stock Innodb installation doesn't support FULLTEXT indexes. This may or may not impact your application. For one application's table I was converting, we decided to look in other fields for the data we needed rather than doing full text scans. (I did an "ALTER TABLE DROP INDEX..." on it to remove the FULLTEXT index before converting to InnoDB.) I wouldn't recommend full-text indexes for a write-heavy table anyway.

If converting a big table full of data with "ALTER TABLE..." works on the first try, you're probably okay.

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

Lastly, if you want to convert from MyISAM to InnoDB on a running system that is read heavy (where UPDATEs and INSERTs are rare), you can run this without interrupting your users. (The RENAME runs atomically. It will back out all of the changes if any of them don't work.)

CREATE TABLE mytable_ind AS SELECT * FROM mytable;
ALTER TABLE mytable_ind ENGINE=InnoDB;
RENAME TABLE mytable TO mytable_myi, mytable_ind TO mytable;
DROP TABLE mytable_myi;