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 ??? – RolandoMySQLDBA Jan 31 '14 at 17:16
  • Yes. But it's simple to do another dump, including possibly dumping no-data and creating the tables that way. – Charlie Martin Jan 31 '14 at 17:18
  • Other than information_schema, mysql, and performance_schema, how many databases are you dumping ??? – RolandoMySQLDBA Jan 31 '14 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. – Charlie Martin Jan 31 '14 at 17:24

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 !!!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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