I run a medium to large web forum that's been around for over five years. All the tables are in MyISAM and I'd like to convert them to INNODB and use Sphinx for search.

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?

I usually just get the list of tables (SHOW TABLES;), then feed it through cut/sed/awk/whatever to produce the ALTER TABLE... commands, then copy and paste those in.

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.

_________________

/* TODO: need to add signature to posts */

How many tables do you have?

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. But this requires knowledge of Unix tools and some command-line configuration, so it might be faster to just use a text editor.

Sorry, I can't comment on Sphinx.

It's IPB with the blog hosting and gallery addons plus a few other extras that demand more tables. It's a bit over 200 tables. I'm going to do a lot of testing in a local VM before doing it with the live db.

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.