Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
When you created the mysqldump file, did you say --all-databases ??? –  RolandoMySQLDBA Jan 31 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 at 17:18
    
Other than information_schema, mysql, and performance_schema, how many databases are you dumping ??? –  RolandoMySQLDBA Jan 31 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 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:

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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