If you’re using a micro AWS EC2 instance to host a website which uses a database running on MySQL (yes, this is possible using WordPress on a free usage tier!), then you’ll need to configure MySQL to play nicely on such a puny setup.
One very easy way to get this working relatively well is to use the default my.cnf file designed for small servers, called, helpfully, my-small.cnf. The default my.cnf configurations live in “/usr/share/mysql/”.
How to use my-small.cnf
MySQL comes preloaded with a selection of configurations for a variety of purposes. As we’re talking about a micro EC2 instance, here, then where better to get started with the appropriately named my-small.cnf`!
So, to install this quickly from your terminal (PuTTy or whatever terminal software you’re using to SSH to your server for command line access) simply run the following commands:
cd /etc
cp my.cnf mybackup.cnf
cp /usr/share/mysql/my-small.cnf ./my.cnf
service mysqld restart
The first command takes you to the “/etc” folder where my.cnf lives.
The second command backups up your prexisting my.cnf file (if the system fails to restart, then you’ll want to “cp mybackup.cnf my.cnf” and restart MySQL to see what went wrong!).
The third command copies in the small my.cnf file from your file system and the fourth restarts MySQL in order that the new directions take effect.
That’s it!
InnoDB Setup
Please note that you’re above actions won’t have done much if your data is stored as innodb. In order to set this up, you’ll need to add the following steps:
vi my.cnf
Edit this file to uncomment the innodb settings, then type “service mysqld restart”.
There is a good chance that MySQL will fail to start after you’ve done this, as you’ve altered the size of the innodb logs, and this isn’t something MySQL can cope with.
So, you’ll now need to stop MySQL, clear those logs, and then start MySQL, by using the following commands:
service mysqld stop
rm -f /var/lib/mysql/ib_logfile[01]
service mysql start
You should now find MySQL starts correctly.
InnoDB is better than MyISAM as a Storage Engine – for WordPress
The newer versions of WP ship with InnoDB as the storage engine on MySQL, however, if, like us, you have old and war-wearied copies of WordPress, you’ll have to convert all your tables to InnoDB. This is outside of the scope of this article, but we really recommend doing this (properly) to improve db performance and squeeze the extra speed out of your setup. Its half an hour (max) of effort to do this on the largest of WP installs, but its well worth it.
You can find out how to convert MySQL tables to use the InnoDB storage engine, here.