How to install and configure a Linux server for MySQL?

Have you ever spent a lot of time thinking about how to install and configure a Linux server for MySQL database? I will try to highlight all the critical steps and some of the decisions you may need to make.

Linux distribution.

Unless you have a really good experience in systems administration, choose a widely supported Linux distribution. The best choices usually are RedHat or its free cousin called CentOS. Compatible alternatives you could also consider are Scientific Linux and Oracle Linux. Make sure you will be installing a 64-bit version, unless you have a very good reason not to.

Storage.

If you have multiple disks available in the server, create a single array from all disks. Choose RAID level that offers better performance rather than more disk space, so either RAID 1 or RAID 10 depending on the number of disks ("Should RAID 5 be used in a MySQL server?"). Use stripe (chunk) size of at least 128KB.

LVM.

You may skip it if database carries InnoDB tables only (other than system tables). Otherwise, or if unsure, LVM should be configured as it may help in creating consistent and lock-free MySQL backups. Make sure to leave some free space in the volume group where MySQL logical volume will be. 10% of the logical volume size could be a good default. An example of what you should see after installation:

garfield ~ # vgdisplay <your volume group name>
  --- Volume group ---
  VG Name               <your volume group name>
  System ID             
  Format                lvm2
[..]
  VG Size               1,58 TiB
  PE Size               4,00 MiB
  Total PE              413626
  Alloc PE / Size       394426 / 1,51 TiB
  Free  PE / Size       19200 / 75,00 GiB   
  VG UUID               aZcyWM-B1mc-5PEc-VeNf-W0fw-hLvV-12w29x

Partitioning.

For a dedicated database server, it is commonly enough to create four partitions:

/boot - tiny, to hold boot files - please refer to the system documentation for details
/ - 20GB or 30GB for the system files and logs
swap - nothing larger than a few gigabytes makes sense
/mnt/db - all remaining space for MySQL data and binary logs

Filesystem.

Use whatever you want for system partitions. For MySQL partition, ext3 or ext4 are fine, but if you need to squeeze that last ounce of performance out of the system, go with xfs. It can deal much better with concurrent I/O.

Time synchronization.

All your servers should have their clocks updated periodically. Install NTP client and make sure it updates time daily. How to check whether clock is out of sync or not? Once you install ntpdate package, try the following:

garfield ~ # ntpdate -q pool.ntp.org
server 77.65.7.58, stratum 2, offset -29.763656, delay 0.04489
server 91.217.142.1, stratum 2, offset -29.776960, delay 0.08609
server 195.8.52.8, stratum 2, offset -29.761839, delay 0.04979
12 Apr 12:21:25 ntpdate[8676]: step time server 77.65.7.58 offset -29.763656 sec

The clock in my computer was clearly not synchronized :-)

Swap.

Prevent needless swap activity. Add vm.swappiness = 0 into /etc/sysctl.conf. Please refer to "How to prevent swapping on a MySQL server?" for details.

I/O scheduler.

By default any Linux uses CFQ algorithm for I/O scheduling. CFQ does not really care about I/O latency and may serialize requests, which means it can work against database performance. This is why it should be replaced with deadline, or even noop when server has a good hardware RAID controller, on physical volumes where MySQL data files will be stored.

In order to change the I/O scheduler at run time, simply write into a special pseudo-file /sys/block/<block device>/queue/scheduler, for example:

echo "noop" > /sys/block/sda/queue/scheduler

The change can be made permanent by adding such line into an init script such as /etc/rc.local or other that is appropriate.

MySQL configuration.

Create MySQL data directory inside the volume mount point (e.g. /mnt/db/mysql). Do not use the mount point directly or you may be seeing the annoying lost+found database inside MySQL.
Create a directory for binary logs (e.g. /mnt/db/binlog).

Alternatively, instead of making new /mnt/db/mysql, you may simply move the data directory created by the install script from a MySQL package (e.g. mv /var/lib/mysql /mnt/db/).

And do not forget about changing these directories ownership to mysql user!

In my.cnf, set the following:

datadir to /mnt/db/mysql
log-bin to /mnt/db/binlog/mysql-bin
relay-log to /mnt/db/binlog/mysql-relay

Done.

At this point the server is ready for use and you can begin taking care of setting up privileges and tuning the MySQL configuration.

Blog