Mysql – InnoDB to MyISAM Conversion Problem

database-engineinnodbmyisamMySQLstorage-engine

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin

I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value

skip-innodb

Afterwards, I found some errors in some php scripts in the server

Unknown table engine 'InnoDB'

I checked some databases in phpmyadmin i found in each database some tables IN USE

How can I make a successful conversion of all tables from InnoDB to MyISAM ?

Best Answer

Setting skip-innodb does disable the InnoDB Storage Engine, but that does not convert the tables.

You need to script the conversion of all the tables.

STEP #1

Comment out the skip-innodb

[mysqld]
#skip-innodb

STEP #2

service mysql restart

STEP #3

Execute the Following Script snf View Results

CONVERT_SCRIPT=/tmp/InnoDBConversionToMyISAM.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='InnoDB' AND table_schema <> 'mysql'"
mysql -uroot -p -ANe"${SQL}" > ${CONVERT_SCRIPT}

STEP #4

View the script

vi /tmp/InnoDBConversionToMyISAM.sql

less /tmp/InnoDBConversionToMyISAM.sql

If you like the contents, then ...

STEP #5

mysql> source /tmp/InnoDBConversionToMyISAM.sql

STEP #6

Uncomment out the skip-innodb

[mysqld]
skip-innodb

STEP #7

service mysql restart

STEP #8

There is no STEP 8. That's it !!!

Give it a Try !!!

Related Solutions

Mysql – Speeding up conversion of MyISAM to InnoDB

Let me start by saying, I hate ALTER. It's evil, IMHO.

Say, this is your current table schema -

CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;

Here's the path I recommend -

Create a new table object that will replace the old one:

CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8

Insert all the rows from the old table by name into the new table:

INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;

Smoke test your migration:

SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;

Swap table names so that you can maintain a back up in case you need to rollback.

RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;

Proceed to regression testing.

This approach becomes more and more preferable with tables with multiple indexes and millions of rows.

Thoughts?

Mysql – Is it common practice to mix InnoDB and MyISAM tables on same server

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types.

For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be regenerated easily and it's a small table, say for queuing, you might choose Memory. If you have a table that's 90% reads, and the data has got to be there when you look for it, then you'd probably choose a storage engine that supports transactions and configurable atomicity, such as InnoDB. If you want accessibility through the file system w/o damaging data, you might choose CSV.

Nonetheless, you can safely use multiple storage engines within the same schema as well as the physical host.

Let me note though, that your buffers play a role in this whole mess. If you use both MyISAM and InnoDB, you will need to be careful that your key_buffer and innodb_buffer_pool do not contend. This will take careful planning on your part, but that's what we do.