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 (Percona Server)........................ [OK]
PlanetMySQL Voting: Vote UP / Vote DOWN
