Some time ago I was in the situation where I was looking at 200 MyISAM tables screaming to get converted to InnoDB for performance reasons. You probably know that MyISAM is better at fulltext searches and such, but what I needed was this database stop locking entire tables when I was just doing row-level interactions. Here’s how I did in one go.
I’m not the kind of guy who’s going to spend 3 hours & 600 mouseclicks in phpmyadmin. So this needed to be automated.
Check your engines
To find out what tables currently use for their storage engine, execute:
1 2 3 4 5
To see what MySQL commands are going to be executed, you can safely type this:
1 2 3 4 5 6
As you can see Ubuntu has - thanks to Debian - the
/etc/mysq/debian.cnf file so you don’t even need a password
(that’s only after you are
root of course).
How sick is that.
Ok, on to the fun part.
To any inexperienced sysadmin reading this, I would have to make it clear to:
- Investigate if you need this & if your DB is compatible
- First test on a replica on another machine
- Make backups
- Plan for downtime
- Use at own risk
- And most important:
- Not come crying to me that I wrecked your DB : )
Now that you have taken all the necessary precautions, here’s how to feed the commands from Dryrun back to MySQL again:
1 2 3 4 5 6 7
Depending on the size of your tables this may take a while. But by the end of it, you’ll have an InnoDB-only database. Nice.
Here’s another way as suggested by Tim in the comments:
1 2 3
His use of
--skip-column-names --batch is especially noteworthy,
this way we could lose the
awk matching for ‘Tables_in_’, which
makes it more robust (what if that string changes in a future version).
This one is just in from Bob Sikkema, he mentions
For MySQL 5.5:
1 2 3 4 5
Version for MySQL prior to MySQL 5.5
1 2 3 4 5
Using the output from the query, you have a conversion script for the slave. Tables that have FULLTEXT indexes cannot be converted to MyISAM. To locate, run this query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Obviously these same methods could be used to convert all MySQL tables to MyISAM, change the encoding of all MySQL tables to UTF8, etc.
Share your thoughts & alternatives!
These were imported from my old blog. Please use disqus below for new comments