How to Setup MySQL’s my.cnf for Micro AWS EC2 Instance

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.

4 thoughts on “How to Setup MySQL’s my.cnf for Micro AWS EC2 Instance”

  1. Where you’ll find speed improvements following a switch from MyISAM to InnoDB is on things like the edit screen (much faster on InnoDB) in WordPress. If you use a good caching plugin, your visitors won’t necessarily notice much improvement, but faster DB performance does allow you to open (and close) more connections more quickly, allowing you more throughput, and, consequently, meaning you won’t have to scale up your DB hosting machine as quickly as you might otherwise.

    Reply
  2. Hi Robin,

    Could you see if you can help me?

    I am using a Ec2 in AWS and I am not allowed to edit the my.cnf file, is giving me permission denied.
    Looking into the documentation I found that I should edit the permissions by creating a parameter group in my RDS Console, setting the desired parameters and then assigning the Parameter Group to your RDS Instance.

    The problem is that I am not using a RDS Instance. I have installed the Mysql via command line and not sure how to proceed.

    Is everything working fine apart of that. I just need to change the max_allowed_packet to be able to import a large columns table to the database.

    Cheers,
    J

    Reply
    • Hi,

      Most often in terminal, if you run into permissions errors, you need to be root user.

      On ec2, you are probably the ec2-user user.

      You need to type “sudo” before your command.

      Or “sudo -i” as an initial command to run every subsequent command as root user.

      If it isn’t this, then I don’t know without looking at your setup!

      Good luck.

      Reply

Leave a comment

No links of any kind allowed in comments - if you add any links, your comment will automatically be deleted.