Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore? I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long as it works.

I'm abou to try it on an experimental db, but here's a puzzle: I have a mysqldump from the production database, which naturally wants to recreate the tables as myisam.

Several options present themselves:


edit the dump file -- which is an 8 GiB file so editing might be a tad difficult.
just load the dump and convert the engine afterward -- but sourcing in the file seems to take a long time.
Find some magic that will force the new DB engine to be innodb no matter what the dump says.


Is there another option? Which option should I choose?

I'll cheerfully admit DBA is not my primary skill.
I am going to answer based on option #3.
You can dump all databases except the following:

information_schema
performance_schema
mysql

You should dump it like this:
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN=&quot;-u${MYSQL_USER} -p${MYSQL_PASS}&quot;
SQL=&quot;select group_concat(schema_name) from information_schema.schemata&quot;
SQL=&quot;${SQL} where schema_name not in ('information_schema','mysql','performance_schema')&quot;
DBLIST=`mysql -ANe&quot;${SQL}&quot; | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g'  &gt; AllMyData.sql

You can just load that and you are all set.
If you want to move the MySQL Users and Passwords, do this:
MYSQL_CONN=&quot;-uroot -ppassword&quot;
SQLSTMT=&quot;SELECT CONCAT('SHOW GRANTS FOR ',&quot;
SQLSTMT=&quot;${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') &quot;
SQLSTMT=&quot;${SQLSTMT} FROM mysql.user WHERE user&lt;&gt;''&quot;
mysql ${MYSQL_CONN} -ANe&quot;${SQLSTMT}&quot; &gt; GetGrants.sql
echo &quot;SET sql_log_bin = 0;&quot; &gt; MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN &lt; GetGrants.sql | sed 's/$/;/g' &gt;&gt; MySQLUserGrants.sql
rm -f GetGrants.sql

Then, just import MySQLUserGrants.sql into the MySQL 5.6 instance.
Give it a Try !!!