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:

  1. edit the dump file -- which is an 8 GiB file so editing might be a tad difficult.
  2. just load the dump and convert the engine afterward -- but sourcing in the file seems to take a long time.
  3. 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.

  • When you created the mysqldump file, did you say --all-databases ??? Commented Jan 31, 2014 at 17:16
  • Yes. But it's simple to do another dump, including possibly dumping no-data and creating the tables that way. Commented Jan 31, 2014 at 17:18
  • Other than information_schema, mysql, and performance_schema, how many databases are you dumping ??? Commented Jan 31, 2014 at 17:20
  • about 5, of which only one is of significant size. Significant here being about 162 million rows of varchars and fulltext. The others are in the order of 100 rows. Commented Jan 31, 2014 at 17:24

1 Answer 1


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:

SQL="select group_concat(schema_name) from information_schema.schemata"
SQL="${SQL} where schema_name not in ('information_schema','mysql','performance_schema')"
DBLIST=`mysql -ANe"${SQL}" | sed 's/,/ /g'`
mysqldump --databases ${DBLIST} | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g'  > 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="-uroot -ppassword"
SQLSTMT="${SQLSTMT} QUOTE(user),'@',QUOTE(host),';') "
SQLSTMT="${SQLSTMT} FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql ${MYSQL_CONN} -AN < GetGrants.sql | sed 's/$/;/g' >> MySQLUserGrants.sql
rm -f GetGrants.sql

Then, just import MySQLUserGrants.sql into the MySQL 5.6 instance.

Give it a Try !!!

  • Makes good sense, and sed-ing as I dump is a good answer. Commented Jan 31, 2014 at 17:33
  • I just updated my answer to move the MySQL usernames and passwords using pure SQL. Commented Jan 31, 2014 at 17:34
  • As for option 2, see my post dba.stackexchange.com/questions/9414/… Commented Jan 31, 2014 at 17:48
  • Thanks. I'm actually running the source of the sed-ified file right now on my experimental instance. Commented Jan 31, 2014 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.