What's the best way to convert a MyISAM database to INNODB?
I know how to convert each table manually, but what's the best way to automatically convert the entire database?
Can anyone recommend a good tutorial on configuring Sphinx, btw?
3 Replies
mysql> \T /tmp/foobar
Logging to file '/tmp/foobar'
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
...
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
28 rows in set (0.00 sec)
mysql> ^Z
[1]+ Stopped mysql
rtucker@framboise:~$ grep '^|' /tmp/foobar | tail -n +2 | awk '{print "ALTER TABLE " $2 " SET ENGINE=INNODB;"}' > /tmp/foobar.sql
rtucker@framboise:~$ head /tmp/foobar.sql
ALTER TABLE CHARACTER_SETS SET ENGINE=INNODB;
ALTER TABLE COLLATIONS SET ENGINE=INNODB;
ALTER TABLE COLLATION_CHARACTER_SET_APPLICABILITY SET ENGINE=INNODB;
rtucker@framboise:~$ fg
mysql
mysql> \. /tmp/foobar.sql
(om nom nom nom)
A slightly dubious example (I can't be arsed to find a working MySQL password right now), but it'd probably work.
AFAIK, MySQL does not support converting all tables to InnoDB in one command. You have to convert each of them.
If you only have a few dozen of tables, You could run "SHOW TABLES;", copy the result to a text editor, copy and paste "ALTER TABLE" before each row and "ENGINE=INNODB;" after each row (don't forget the semicolon), and paste the text back into MySQL. Pressing Ctrl+C and Ctrl+V a few dozen times in a text editor should only take you a couple of minutes. If your text editor supports regex search/replace, you won't even need to repeat the copy and paste. I often abuse Notepad++ for exactly this purpose.
If you have hundreds of tables, you could use a shell script like this
Sorry, I can't comment on Sphinx.
The copying and pasting in a text editor is what I'm trying to avoid but I suspect I'll end up spending more time testing various scripts than it would take to actually do that.