Install and configure MySQL on EC2 with RedHat Linux

Recently I had to turn a few EC2 instances into MySQL database servers. The third time I had to do it, I grabbed the list of steps from my previous sessions and just replayed it. Later I thought maybe polishing information a little bit and publishing a step-by-step walkthrough on the blog may help a few people. So here it is.

Before you begin.

For my MySQL instances I used the following:

  • Extra Large, High-Memory, and High-CPU instances. Although the instruction should work on any type of instance.
  • RedHat Enterprise Linux 6.2 64-bit AMI
  • For MySQL data storage, multiple identical EBS devices attached to each instance

The configuration template provided in this post assumes the new MySQL instance only needs InnoDB storage engine.

Grab the packages.

Download the appropriate packages from MySQL web page. You will need client, shared-compat, and server. For example:

MySQL-client-5.5.24-1.el6.x86_64.rpm
MySQL-server-5.5.24-1.el6.x86_64.rpm
MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm

Make sure the packages you downloaded are for a 64-bit architecture. You could of course choose packages from other vendors as the change should not affect the process in any relevant way.

Install MySQL

Remove the MySQL libraries that have been shipped with the system and install the new client and library packages:

[root@ip-10-238-234-26 ~]# rpm -e --nodeps mysql-libs
[root@ip-10-238-234-26 ~]# rpm -ihv MySQL-client-5.5.24-1.el6.x86_64.rpm MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [ 50%]
   2:MySQL-client           ########################################### [100%]

Then install the server package:

[root@ip-10-238-234-26 ~]# rpm -ihv MySQL-server-5.5.24-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
Search for EBS volumes.

Examine system to find the EBS volumes you will use for MySQL storage.

[root@ip-10-32-2-145 ~]# dmesg | grep 'unknown partition'
 xvds: unknown partition table
 xvdt: unknown partition table
 xvds: unknown partition table
 xvdt: unknown partition table

Please keep in mind some of the disks reported may be EC2 ephemeral storage, which you typically do not want to use for MySQL data, so make sure to only use EBS volumes in the following steps. If you are uncertain what is what, you can call fdisk -l and check the disk sizes:

[root@ip-10-32-2-145 ~]# fdisk -l

Disk /dev/xvde1: 6442 MB, 6442450944 bytes
255 heads, 63 sectors/track, 783 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
[..]
Create partitions.

Create a new partition on the first disk.

[root@ip-10-32-2-145 ~]# fdisk /dev/xvds
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x3774dc60.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-54823, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-54823, default 54823):
Using default value 54823

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): fd
Changed system type of partition 1 to fd (Linux raid autodetect)

Command (m for help): wq
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

Assuming you created several identical EBS volumes, duplicate the partition table onto each.

[root@ip-10-32-2-145 ~]# sfdisk -d /dev/xvds |  sfdisk /dev/xvdt
Checking that no-one is using this disk right now ...
OK

Disk /dev/xvdt: 54823 cylinders, 255 heads, 63 sectors/track

sfdisk: ERROR: sector 0 does not have an msdos signature
 /dev/xvdt: unrecognized partition table type
Old situation:
No partitions found
New situation:
Units = sectors of 512 bytes, counting from 0

   Device Boot    Start       End   #sectors  Id  System
/dev/xvdt1            63 880731494  880731432  fd  Linux raid autodetect
/dev/xvdt2             0         -          0   0  Empty
/dev/xvdt3             0         -          0   0  Empty
/dev/xvdt4             0         -          0   0  Empty
Warning: no primary partition is marked bootable (active)
This does not matter for LILO, but the DOS MBR will not boot this disk.
Successfully wrote the new partition table

Re-reading the partition table ...

If you created or changed a DOS partition, /dev/foo7, say, then use dd(1)
to zero the first 512 bytes:  dd if=/dev/zero of=/dev/foo7 bs=512 count=1
(See fdisk(8).)
Verify partitions.
[root@ip-10-32-2-145 ~]# fdisk -l | grep -E '^/dev/xvd'
[..]
/dev/xvds1               1       54823   440365716   fd  Linux raid autodetect
/dev/xvdt1               1       54823   440365716   fd  Linux raid autodetect
Create RAID volume.

Create a new RAID level 0 volume over all EBS partitions. Use relatively large chunk size – 128KB or 256KB should be good choices.

[root@ip-10-32-2-145 ~]# mdadm -C /dev/md0 --chunk=256 -n 2 -l 0 /dev/xvds1 /dev/xvdt1
mdadm: Defaulting to version 1.2 metadata
mdadm: array /dev/md0 started.
Save the md array configuration.
[root@ip-10-32-2-145 ~]# echo 'DEVICE /dev/xvds1 /dev/xvdt1' >> /etc/mdadm.conf
[root@ip-10-32-2-145 ~]# mdadm --examine --scan >> /etc/mdadm.conf

Here is what the file should look like

[root@ip-10-32-2-145 ~]# cat /etc/mdadm.conf
DEVICE /dev/xvds1 /dev/xvdt1
ARRAY /dev/md/0 metadata=1.2 UUID=f7d5b2e7:495efeb3:26297d34:7be87159 name=ip-10-32-2-145:0
Rebuild the initramfs image.

If this is not done, system won’t see the new /etc/mdadm.conf contents during boot and md device may be renamed to something else.

[root@ip-10-32-2-145 ~]# mkinitrd --force /boot/initramfs-2.6.32-220.el6.x86_64.img 2.6.32-220.el6.x86_64

You have to use the initramfs file and kernel version that are appropriate for your system. You can list /boot directory contents to figure this out:

[root@ip-10-226-155-162 ~]# ls -1 /boot/
config-2.6.32-220.el6.x86_64
efi
grub
initramfs-2.6.32-220.el6.x86_64.img
symvers-2.6.32-220.el6.x86_64.gz
System.map-2.6.32-220.el6.x86_64
vmlinuz-2.6.32-220.el6.x86_64
Create filesystem.

This part may take a while depending on the volume size.

[root@ip-10-32-2-145 ~]# mkfs.ext4 /dev/md0
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=64 blocks, Stripe width=128 blocks
55050240 inodes, 220182272 blocks
11009113 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
6720 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968,
        102400000, 214990848

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 36 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
Create a mount point.

I do not like mounting to /var/lib/mysql directly. Typically I create a new directory inside /vol, or /mnt, or /data.

[root@ip-10-32-2-145 ~]# mkdir -p /vol/mysql
Store the filesystem information.

Always use noauto option during installation. It skips automatic filesystem mounting during system init, which can save you from a lot of trouble if something does not work for any reason and when the boot process hangs on this step. EC2 does not come with a system console that would allow rescuing a system from such state.

[root@ip-10-32-2-145 ~]# echo '/dev/md0   /vol/mysql   ext4   rw,nobarrier,noatime,nodiratime,noauto   0 0' >> /etc/fstab

It may actually be very reasonable to keep noauto there forever and either mount the database volume manually each time the server reboots (it implies you also have to start MySQL manually) or implement volume mounting in a way that does not block the instance in case of some errors.

Mount the new volume.

This command can now use the configuration from /etc/fstab.

[root@ip-10-32-2-145 ~]# mount /vol/mysql

The command should not return anything, but to make sure it worked, just run a quick check whether the volume was acutally mounted or not:

[root@ip-10-32-2-145 ~]# df -h /vol/mysql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              827G  201M  785G   1% /vol/mysql

Mounted on should point the new mount point and not to / for example.

Move the default data directory.

Move into the new location the default MySQL data directory created by the MySQL-server package installtion. Also create a directory for MySQL logs.

[root@ip-10-32-2-145 ~]# mv /var/lib/mysql /vol/mysql/
[root@ip-10-32-2-145 ~]# ln -s /vol/mysql/mysql /var/lib/mysql
[root@ip-10-32-2-145 ~]# mkdir /vol/mysql/log
[root@ip-10-32-2-145 ~]# chown mysql:mysql /vol/mysql/log
Set swappiness to prevent unnecessary swapping.

You can refer to this article for more details on swappiness.

[root@ip-10-32-2-145 ~]# echo 'vm.swappiness = 0' >> /etc/sysctl.conf
[root@ip-10-32-2-145 ~]# sysctl -p /etc/sysctl.conf
Configure time synchronization.

Create a new file called ntpdate in /etc/cron.daily with the following contents:

#!/bin/sh

/usr/sbin/ntpdate pool.ntp.org 1> /dev/null 2>&1

Then set the file permissions:

[root@ip-10-32-2-145 ~]# chmod +x /etc/cron.daily/ntpdate
Disable or configure SELinux.

RedHat comes with SELinux enabled, but often SELinux will be disabled during a database server installation process. To disable, perform the following:

[root@ip-10-32-2-145 ~]# cd /etc/sysconfig/
[root@ip-10-32-2-145 sysconfig]# replace 'SELINUX=enforcing' 'SELINUX=disabled' -- selinux
selinux converted
Restart the system.

It is time to restart the system. You should do this for two reasons:

  • You want to verify if the RAID volume loads correctly after a restart and that MySQL data volume can be mounted again without any problems.
  • You disabled SELinux and it is also the only way to make the change effective.
Verify md array stats and SELinux status.

Once the system boots again, verify the md array status.

[root@ip-10-32-2-145 ~]# cat /proc/mdstat
Personalities : [raid0]
md0 : active raid0 xvds1[0] xvdt1[1]
      880729088 blocks super 1.2 256k chunks

unused devices: 

It should still say md0 if you regenerated the initramfs image.

If you disabled SELinux, verify that as well.

[root@ip-10-32-2-145 ~]# selinuxenabled
[root@ip-10-32-2-145 ~]# echo $?
1

selinuxenabled indicates whether SELinux is enabled or disabled. It returns 0 if SELinux is enabled and 1 if it is not enabled.

Mount data volume.
[root@ip-10-32-2-145 ~]# mount /vol/mysql
[root@ip-10-32-2-145 ~]# df -h /vol/mysql
Filesystem            Size  Used Avail Use% Mounted on
/dev/md0              827G  201M  785G   1% /vol/mysql

If you want, now you can remove noauto from /etc/fstab.

Create MySQL configuration file.

I sometimes use this simple script that writes a good default configuration into /etc/my.cnf. It auto-tunes a few parameters that commonly need customization. You could just copy-paste it into the command line and run.

(cat <<EOF
[mysqld_safe]
log-error = /vol/mysql/log/mysql-error.log

[mysqld]
user = mysql
port = 3306
socket = /vol/mysql/mysql/mysql.sock

datadir = /vol/mysql/mysql
tmpdir = /tmp
pid-file = /vol/mysql/mysql/mysql.pid

character-set-server = utf8

slow-query-log
slow_query_log_file = /vol/mysql/log/mysql-slow.log
long_query_time = 10

log-bin = /vol/mysql/log/mysql-bin
relay-log = /vol/mysql/log/mysql-relay
server-id = $(/bin/hostname | /usr/bin/md5sum | /usr/bin/awk --non-decimal-data '{ hex=substr($1, 24, 8); printf ("0x"hex)+0 }')

back_log = 50
max_connections = 500
max_connect_errors = 100
skip-name-resolve

key_buffer_size = 8M
sort_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_heap_table_size = 64M
tmp_table_size = 64M

table_open_cache = 2048
thread_cache_size = 16

innodb_file_per_table
innodb_data_file_path = ibdata1:128M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = $(/usr/bin/awk '/MemTotal:/ { printf "%.0fM\n", ($2*0.70)/1024 }' /proc/meminfo)
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_read_io_threads = $(($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 2))
innodb_write_io_threads = $(($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 2))
innodb_io_capacity = $((($(/bin/grep -E 'md[0-9]+ : active raid0' /proc/mdstat | wc -w) - 4 ) * 130))
innodb_thread_concurrency = 0

[mysql]
no-auto-rehash
socket=/vol/mysql/mysql/mysql.sock

[client]
socket=/vol/mysql/mysql/mysql.sock

EOF
) > /etc/my.cnf

Remember this is just a template. Each server may need individual adjustments to database configuration.

It’s done.

You can now start MySQL.

[root@ip-10-32-2-145 ~]# /etc/init.d/mysql start
Starting MySQL ........................     [OK]
[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. Greg says:

    Nice write up. An easier way to determine SELinux status is with ‘sestatus’

    # sestatus
    SELinux status: disabled

    Greg

  2. Jagbir Singh says:

    Thanks Maciej for this excellent article, really helpful for anybody installing MySQL in EC2 env. I have some queries which you can answer or cover in next articles:
    1) How about using XFS instead of EXT4 for MySQL partition, do you see any benefit?
    2) Any tuning for EBS volumes as they are inherently have more latency than attached storage?
    3) Using RAID 10 instead of 5?
    4) Any script or recommended way to automate failover of Master/Slave servers?

    • Jagbir,

      1) XFS would be great, but is not available in RHEL6 by default. You’d have to purchase Red Hat Enterprise Linux Scalable File System Add-On first or do some patchwork.

      2) EBS has higher overhead for reading, but writing is quite fast as EC2 performs some caching within the local physical layer.
      The slower reading should not be a critical problem as usually a database solution that has to perform well on EC2, but also on any other cloud-based/shared environment, must assume that the working set will always fits in RAM, i.e. very few reads from disk are required to run queries. Anything that relies on good I/O performance at EC2 is not a very good design and it may fail randomly.

      3) I used RAID0, not RAID5. One can consider RAID10, but I’m not really sure that makes sense on EC2.

      4) I don’t know any real solution that would support EC2 out of the box. The environment you get is not very HA friendly, so most of the time you will be just making something work, but the reliability will never be great – at best it will be good enough.

      I have heard of people building MySQL Cluster on top of EC2 instances, but it was very much the same thing – the effort to make it work good enough for them, but nowhere near the reliability of a proper cluster running on a dedicated hardware.

  3. Sebastian says:

    Thanks for this great tutorial.

    Is is actually possible to use this “variables” directly in MySQL configuration file?
    innodb_buffer_pool_size = $(/usr/bin/awk ‘/MemTotal:/ { printf “%.0fM\n”, ($2*0.70)/1024 }’ /proc/meminfo)

    Regards
    Sebastian

    • Sebastian,

      No. You cannot use this as a config file. This is a script that can be executed on Linux and it auto-tunes some variables. It assumes 70% of physical memory will be used for InnoDB buffer pool. You can also execute the commands from inside of $() separately and replace the entire thing with the returned value. Example:

      garfield ~ # /usr/bin/awk '/MemTotal:/ { printf "%.0fM\n", ($2*0.70)/1024 }' /proc/meminfo
      5523M

      So the parameter could be set as follows: innodb_buffer_pool_size = 5523M

  4. Tarek Koudsi says:

    I was very happy to read this article, we’re going to need this soon.

Speak Your Mind

*