2

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.

4
  • When you created the mysqldump file, did you say --all-databases ??? 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. Jan 31, 2014 at 17:18
  • Other than information_schema, mysql, and performance_schema, how many databases are you dumping ??? 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. Jan 31, 2014 at 17:24

1 Answer 1

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:

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
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="SELECT CONCAT('SHOW GRANTS FOR ',"
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 !!!

4

Your Answer

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

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