MySQL 5.1 to 5.6 Upgrade – Force InnoDB on Restore

innodbmyisamMySQLmysql-5.6mysqldump

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.

Best Answer

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

Related Solutions

MySQL Storage Engine – Which One to Choose?

Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose

InnoDB has transactions while MyISAM does not.

InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.

Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.

InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.

Mysql – why/how does the number of matched columns influences the way of excecuting a query

I can provide with a general explanation, but it may not apply specifically to your particular case:

The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.

When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.

For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1, and another index on some column col2. Given the following two queries:

SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4 returns more rows than col1 < 10, in which case we prefer to use index on col1. But then, it may return less rows than col1 BETWEEN 100 AND 110, in which case we prefer the index on col2.

Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA, then joins what should be very few rows with TableB.

But if MySQL believes the index to return many rows, it may prefer starting with TableB. Why is that? Because you are sorting on indexed columns of TableB. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.

Best Answer

Give it a Try !!!

Related Solutions

MySQL Storage Engine – Which One to Choose?

Mysql – why/how does the number of matched columns influences the way of excecuting a query

Related Question