A couple months ago I posted on a modified php script to convert tables MySQL tables from MyISAM to InnoDB. It worked all well and good for my test environment, but I realized that it may not be the best solution for a dedicated database server where php is not installed.
In this case, this post assumes you have both su access and mysql root access in order to batch generate the queries to convert the storage engine. I strongly recommend isolating the database from the front end at this point, it means shutting your application down until the operation has been complete.
Please TEST before running in your production environment!
Credit to this post on RackerHacker for the kickstart query generation.
This post does not cover the advantages/disadvantages of either storage engine, that is covered at MySQL's site and at the Oracle Blog.
The benefits of Moodle on InnoDB is covered here.
Some prerequisites to get out of the way.
a. Ensure that InnoDB is enabled on the server
mysql -u root -p *****
SHOW ENGINES;
Expected outputb. Disable the writeback cache (as per MySQL's recommendation)
...
InnoDB | YES | Supports transactions...
hdparm -W0 /dev/sdx (where sdx contains the /var/lib/mysql or your specific mysql location )
c. Tune the my.cnf according to MySQL's recommendation
Later on, the Day32's Tuning Primer
and the Mysql Tuner Script can be used to fine tune the other server parameters.Prereq's aside, let's push forward onto the crux of the matter.
0. Login as root on your database server.
1. Take a backup before proceeding any further.
2. Double check that you have a backup. Please.
3. Run a check on the database to avoid a GIGO issue.
mysqlcheck -c -o -r -f -h localhost -u mydbusername -p mydbname
<enter your db password>
4. Login as MySQL root and generate the batch queries, send to output file (note the single quotes plus backticks)
select concat(‘ALTER TABLE `’,table_schema,’`.`’,table_name,’` ENGINE=InnoDB;’)
from information_schema.tables
Where table_schema=’mydbname’
and ENGINE=’MyISAM’
into outfile ‘/tmp/InnoBatchConvert.sql’
5. Logout from MySQL root and back to shell
At the shell:
mysql -u root -p --verbose < /tmp/InnoBatchConvert.sql
<enter password>
6. After the script has completed, check all the tables to ensure the storage engine is InnoDB
select table_name, engine
from information_schema.tables
where table_schema = 'mydatabasename';
7. Check the database again.
mysqlcheck -c -h localhost -u mydbusername -p mydbname
<enter your db password>
That's it! All you need to do now is ensure you have a decent backup strategy in place and the InnoDB backup script is covered here. I'll follow up soon with a detailed restore procedure as well as perhaps my own version of my innodb backup scripts with rotation built in.
-noveck
