The Full Monty – CentOS 5.5, drbd, PaceMaker, MySQL, Tunsten Replication and more

August 1, 2011

PART 1 – This will be a multi part post.

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:

  • CentOS (Redhat) 5.6
  • 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 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.

# 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

posted in CentOS, DRDB, HA, Heartbeat, HOW TO, Tips & Tricks by mark

 



Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org
Creative Commons License
MySQL Fan Boy by Mark Grennan is licensed under a Creative Commons Attribution-Share Alike 3.0 United States License.
HOME