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
LinkedIn
Grennan.com
Twitter
