PART 1 – This will be a multi part post.

THIS DOCUMENT IS BEING UPDATED – PLEASE WATCH FOR CHANGES!

After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

• chooses a Database Management System (MySQL)
• installs the DBMS on a computer with other processes
• writes many programs to access the data (Without concern on how the queries are written.)
• moves DBMS to a computer of its own and writes more programs
• buy bigger computer to run the DBMS and writes more programs
• tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I build a highly available, production MySQL servers. Like most things it life, these problems can be avoided with a little extra work at the start.

My first goal is to create a MySQL DBMS that with 99.999% up time. MySQL can distribute read requests but not writes. This make write service a single point of failure. To fix this you can either turn a slave server into the master or provide a hot standby to become the master.

My second goal is scalability. This is done by creating more read only slave servers. More slaves complicate turning one of them into the master should the master fail. The enterprise version Continuent’s Tungsten replicator fixes this. But, my goal is to do this on the cheep for start-ups while providing flexibility for the future. With DRBD and Pacemaker I can create a hot standby for the master server.

I have chosen these programs and utilities because they are free. Enterprise support is available for each.

Here is what I’m using:

• Scientific Linux 6.1
• DRBD disk replication for a hot standby
• MySQL 5.1.xx (Percona) or MeriaDB
• Heartbeat
• Pacemaker for failure recover to the hot standby
• Tunsten data replication
• MaaKit a toolkit for users, developers, and administrators of open-source databases
• OpenArk Kit – a set of utilities for MySQL
• xtrabackup for database backups
• mydumper for quick dumps
• MySQL Sandbox for testing
• mytop and innotop
• mysqlsniffer

I’ve worked hard to make this just cut and past. I enjoyed the work. I hope you do to.

NOTE:  Type the GREEN stuff, cut and past the BLUE stuff and edit the RED stuff to fit you needs.

Getting started:

Every good system starts with good hardware. The two thinks database servers hunger for are disk space and memory. You should supply your self with as much as you can afford.  Most of my production system run on 32 gig of memory and RAID-10 systems.  Two network ports or more is recommended.  One network port will be used with a crossover cable for the heartbeat function.

To test this installation I’m building on a VMware server.  If you’d like to know more about this hardware read my “Building a Home VMware server” post.

Building the Operating System – DB1

The operating system I’m using is CentOS 5.6 Scientfic Linux 6.1 64 bit.  You might choose Redhat 6.0. I’m being conservative and I’m trying to use free (I have no budget for this project) version of commercial products with enterprise support.

To help you understand the following instructions, for this example, I’m building a virtual machines (VM) with four (4) virtual SAS hard disks. I have split the four 15G virtual disks into four partitions.  The /boot and /tmp partitions are RAID-1 and the / (root) and /data partitions are RAID-5.  The sizes of these partitions depend you your needs but your /boot needs to be about 1G and the / needs to be about 12G.  I leave /data unassigned and un-formatted.  My layout looks like this.

Md0 - /boot    Md1 - /    Md2 – /tmp    Md3 - /data
Disk 0 - 15G    256M    4G    512M    11G
Disk 1 - 15G    256M    4G    512M    11G
Disk 2 - 15G    256M    4G    512M    11G
Disk 3 - 15G    256M    Checksum    512M    Checksum
Total    1G    12G    2G    33G

Install the OS:

Start with a “minimum installation” with as few applications installed as possible.   No desktop or server applications are needed.
The first step after the install is to update the installed packages.  With a minimum install you may find you need to fix a few things like your network connections.

# yum -y update

Even with a minimum install there is a little clean up.  I remove a few un-needed services like bluetooth, printing and there are a couple of packages we will need latter that where not installed.  It is better to install them now and avoid some dependency issues.  I remove supplied MySQL.

# rpm -e bluez-utils
# rpm -e smartmontools
# yum -y install ruby
# yum -y install perl-DBD-MySQL.x86_64
# yum -y install libdbi-dbd-mysql.x86_64
# rpm -e mysql --nodeps
# chkconfig iptables off
# chkconfig ip6tables off

After the OS is install and updated the disk looks like this.

# df

Filesystem 1K-blocks  Used Available Use% Mounted on

/dev/md2  11903664    1394068 9895160  13% /
/dev/md3  29995056    176200  28270608 1%  /data
/dev/md1  505508      10547   468862   3%  /tmp
/dev/md0  256586      22969   220369   10% /boot
tmpfs     2037380     0       2037380  0%  /dev/shm

Disable Security:

Because we move the MySQL data directy you will need to disable SELinux or update it.

To disable it, edit /etc/selinux/config and change the SELINUX line to SELINUX=disabled:

# vi /etc/selinux/config

SELINUX=disabled
# echo 0 >/selinux/enforce
# service iptables stop
# chkconfig iptables off
# service ip6tables stop
# chkconfig ip6tables off

If you don’t want to disable To update SELinux for the new data directory you will need to have the the selinux tools installed.

# yum -y install policycoreutils
# semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
# restorecon -Rv /data/mysql

Network configuration:

# vi /etc/hosts
  192.168.2.22 db.grennan.com db   192.168.2.23 db1.grennan.com db1  192.168.2.24 db2.grennan.com db2   192.168.2.25 db3.grennan.com db3

Syncing Time:

Time Singularization is very important to maintaining data. You may want to edit the /etc/ntp.conf file to point to your primary NTP time server. CentOS and Redhat provide time servers for your use. I recommend using pool.ntp.org.

# yum install ntp
# chkconfig ntpd on
# ntpdate 0.pool.ntp.org
# service ntpd start

Building DB2/3

DB2 will become the hot standby server (drbd / heartbeat) and DB3 will  become the MySQL slave server (Tungsten).

If, during the OS install, you created and formatted the /data partition you will need to remove it from /etc/fstab.  On both DB1 and DB2,  edit /etc/fstab and remove the /data file system.

# vi /etc/fstab

/dev/md2                /                       ext3    defaults        1 1
/dev/md3                /data                   ext3    defaults        1 2
/dev/md1                /tmp                    ext3    defaults        1 2
/dev/md0                /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sdd3         swap                    swap    defaults        0 0
LABEL=SWAP-sdc3         swap                    swap    defaults        0 0
LABEL=SWAP-sdb3         swap                    swap    defaults        0 0
LABEL=SWAP-sda3         swap                    swap    defaults        0 0

Setup SSH:

# ssh-keygen -t dsa -f ~/.ssh/id_dsa -N ""
# cp ~/.ssh/id_dsa.pub ~/.ssh/authorized_keys
# scp -r ~/.ssh db2:

root@db2's password:
 id_dsa.pub         100%  610     0.6KB/s   00:00  id_dsa                   100%  668     0.7KB/s   00:00
 authorized_keys    100%  610     0.6KB/s   00:00  known_hosts              100%  398     0.4KB/s   00:00

Visualization

I can’t stand the color choices made for BASH so I set my own.

# vi ~/.bash_profile
export LS_COLORS='no=00:fi=00:di=00;33:ln=00;36:pi=40;33:so=00;35:bd=40;33 \ 
01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00; \ 
32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00; \ 
31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00; \ 
31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00; \ 
35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:'

Tweet