PlanetMysql.ru — информация о СУБД MySQL

Installing MySQL on CentOS 6.2 with PaceMaker, MHA and more

When I work with High Availability software, I’m reminded of the maze in the original computer adventure game “You are Lost in a maze of twisty-turny passages all alike…”.

If you search the web for HA programs you will find many well maintained projects all related that refer each other. The goal of this document is to give you with a step by step guide to a production worthy MySQL system. It should provide at least 99.999% access to your data and be able to scale read requests as you grow.

I have chosen these programs and utilities because they are free (as in beer) and each has enterprise support available. (When you make the money to pay for it.) If you start with this MySQL platform you will avoid many common problems. Just write your application to read and write data from different servers.

Here is what we’ll be using:

• CentOS (Redhat) 6.2
• MySQL 5.5.xx (Percona) or the MySQL of your choice
• Pacemaker - to monitor MySQL’s health
• MySQL HA (MHA) – to fail over when something happens to the master
• DRDB Cluster Management Console
• Percona Toolkit – to make live easy
• OpenArk Kit – a set of utilities for MySQL
• xtrabackup for database backups
• mytop and innotop – for real time monitoring

If you want to see all this work check out my Youtube Video at : http://tinyurl.com/7yqj5gz

I’ve worked hard to make these instructions cut and past. The GREEN stuff gets cut and pasted into the linux command line. The BLUE is copied into an application (vi, crm etc), but the RED needs to be edited to fit your environment (passwords, IP). I enjoyed the work. I hope you do to.

Getting started

Every good system starts with good hardware. Two things database servers hunger for are disk space and memory. You should supply yours with as much of each as you can afford. My rule of thumb is three times size of one copy of the data. A production system with a RAID-10 disk system is good and two network ports or more is recommended.

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

The Hardware

The operating system I’m using is CentOS 6.2 64 bit. Don’t use the 32bit versions.

How you define disk space is important part. Choose the type, size and partitions carefully. You don’t want daily activity filling the root file system (/) and taking down the database. The common places this can happen are, the MySQL data directory, the /tmp directory and log space. Keep these in separate partitions. I’ve found the OS system logs in /var/log take care of them selves. MySQL logs should be keep in the MySQL /data directory.

I’m test hardware (VMware ESXi 4) has four SAS hard disks. For each MySQL server (db1 and db2) I create four (4) 15Gig virtual hard disks, one on each physical SAS disk. I split each virtual disk into four partitions. The /boot partition is RAID-1 and the / (root), /data and /tmp 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 at least 12G.

This table show my for virtual disk and how they are raid-ed and partitioned.

Install the OS

Start with a minimum installation so you’ll have as few applications installed as possible. No desktop or server applications are needed.

After the install you should update the installed packages and include any packages you know you will be wanting or needed.

 yum -y update
 yum -y install openssh-clients rsync wget perl-DBI perl-TermReadKey

Remove any supplied MySQL.

Even with a minimum install there is a little clean up. I remove packages that are un-needed.  For better security you should remove  anything that connect to remote systems like bluetooth and printing.

 rpm -e mysql
 rpm -e mysql-libs --nodeps

Security

For the install process I turn off the firewall. In secure environments I recommend you leave it in place and deal with restrictions as they come up.

 service iptables stop
 chkconfig iptables off
 service ip6tables stop
 chkconfig ip6tables off

Because we are moving the MySQL data directory, you will need to disable SELinux or update it. To disable it, edit /etc/selinux/config and change the SELINUX line to SELINUX=disabled. Then, so you don’t have to reboot Linux for this to take effect, just write a 0 to the SELinux control file.

 vi /etc/selinux/config

SELINUX=disabled

 echo 0 >/selinux/enforce

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-python
 semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
 restorecon -Rv /data/mysql

Syncing Time

Time sync is very important to maintaining accurate 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 -y install ntp
 chkconfig ntpd on
 ntpdate 0.pool.ntp.org
 service ntpd start

Setup SSH

Now is a good time to make sure DNS works on all servers and each knows their names.

MHA need ssh access to each server. You need to create ssh keys and copy to the mysql servers.

Skip this if you already have ssh keys installed.

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

Network configuration

You can edit all the network setting with the network system configuration utility.

 system-config-network

Except you need to insure the hosts can ALWAY resolve each other.

 vi /etc/hosts

192.168.2.160 db.grennan.com db
192.168.2.161 db1.grennan.com db1
192.168.2.162 db2.grennan.com db2
192.168.2.163 db3.grennan.com db3

 scp /etc/hosts db2:/etc

Now is a good time to reboot each system and check the install.

 init 6

MySQL Setup

Install the MySQL of your choise. I’m installing Percona’s version 5.5.

 rpm -e mysql-libs-5.1.52 --nodeps
 rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
 yum -y install Percona-Server-shared-compat
 yum -y install percona-toolkit
 yum -y install Percona-Server-server-55.x86_64
 yum -y install Percona-Server-client-55

The default location for MySQL data is /var/lib/mysql. MySQL benefits from being in it’s own partition. I’ll be putting the MySQL data in /data/mysql.

 service mysql stop
 mkdir /data
 mkdir /data/mysql
 cp -prv /var/lib/mysql/* /data/mysql
 mv /var/lib/mysql /var/lib/mysql-empty
 ln -s /data/mysql /var/lib/mysql
 chown -R mysql.mysql /data/mysql

Configure MySQL

You need to change the server-id number on each server. The minimum setting you will need are:

 vi /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1 # Each system needs a unique id number
innodb_flush_log_at_trx_commit=2
sync_binlog=1
# relay_log_purge=0 # uncomment on slaves
# read_only=1 # uncomment on slaves

Percona has a service to help you configure MySQL for your hardware.   MySQL Configuration Wizard

Now we need to add a couple of user before we install the replicator. We also need to set a password for the root user. The root password is blank so just it return.

If you have skip-name-resolve set you will need to substitute the host names for IP addresses.

 service mysql start
 mysql -h localhost -u root -p

The next set of command are entered into the mysql client. You should have a ‘mysql >’ prompt.

 DROP USER ``@`localhost`;
 DROP USER ``@`db1.grennan.com` ;
 GRANT ALL on *.* TO `root`@`192.168.2.%` IDENTIFIED by `P@ssw0rd` with GRANT option;
 GRANT ALL on *.* TO `root`@`localhost` IDENTIFIED by `P@ssw0rd` with GRANT option;
 CREATE USER `repl`@`192.168.2.%` IDENTIFIED BY `RepP@ssw0rd`;
 GRANT REPLICATION SLAVE ON *.* TO `repl`@`192.168.2.%`;
 grant all on *.* to `hauser`@`db1.grennan.com` identified by `P@ssw0rd`;
 grant all on *.* to `hauser`@`db2.grennan.com` identified by `P@ssw0rd`;
 flush privileges;
 quit ;

Now, stop MySQL on all servers and copy all the MySQL data files to the slaves.

From the master (db1) at a system prompt ‘#’ :

 service mysql stop
 ssh db2 `service mysql stop`
 rsync -rog --delete /data/mysql root@db2:/data

Rather than type the password for MySQL each type we connect. We can also setup a usr .my.cnf to prevent this. This should connect you to the master (RW) server from each host.

 vi ~/.my.cnf

[Client]
user=root
password=P@ssw0rd
host=localhost
socket=/data/mysql/mysql.sock

 scp .my.cnf db2:.

Start Replication

On each system start MySQL and connect the two slaves to the master.

On the master we need to now the bin-file and the position. Note the numbers in purple.

 service mysql start
 mysql -e `reset master; reset slave;`
 mysql -e `show master status\G`

*************************** 1. row ***************************
File: mysql-bin.000001
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

On all slaves, use the master’s bin log position to setup replication. Start the slave and note that the IO and SQL are running. (Yes).
service mysql start

 mysql

 stop slave;
 CHANGE MASTER TO MASTER_HOST='192.168.2.201', MASTER_USER='repl',
 MASTER_PASSWORD='RepP@ssw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107 ;
 start slave ;
 show slave status\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Installing MHA

Every MySQL server needs to have a copy of the MHA Node and Manager installed. Download the latest version of MHA from the Google Code server.

http://code.google.com/p/mysql-master-ha/downloads/list

There are a number of required Perl modules. I chose to use the Fedora evaluation packages to resolve these.

• DBD::mysql
• Config::Tiny
• Log::Dispatch
• Parallel::ForkManager
• Time::HiRes

Here is my install with the fedora packages.

 wget http://download.fedora.redhat.com/pub/epel/6/x86_64/epel-release-6-5.noarch.rpm
 rpm -i epel-release-6-5.noarch.rpm
 yum -y install perl-DBD-MySQL
 yum -y install perl-Config-Tiny
 yum -y install perl-Log-Dispatch
 yum -y install perl-Parallel-ForkManager
 yum -y install perl-Time-HiRes
 wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.el6.noarch.rpm
 rpm -i mha4mysql-node-0.53-0.el6.noarch.rpm
 wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.53-0.el6.noarch.rpm
 rpm -i mha4mysql-manager-0.53-0.noarch.rpm

MHA only need one configuration file on each server. You can name this file anything you want. Change the setting and make sure the directories exist and are writable.

 vi /etc/MHA.cnf

Insert this configuration data into the MHA.cnf file.

[server default]
user=root
password=P@ssw0rd
manager_workdir=/var/log/masterha
manager_log=/var/log/masterha/MHA.log
remote_workdir=/var/log/masterha

[server1]
hostname=db1

[server2]
hostname=db2

You should have already installed SSH keys (see above) and the ‘hauser’ installed in MySQL.

masterha_check_ssh is used to check if you have SSH working. Look for the OK after the connect test.

 masterha_check_ssh --conf=/etc/MHA.cnf

Thu Dec 1 10:10:52 2011 – [info] Starting SSH connection tests..
Thu Dec 1 10:10:53 2011 – [debug]
Thu Dec 1 10:10:52 2011 – [debug] Connecting via SSH from root@db1(192.168.0.11) to root@db2(192.168.2.12)..
Thu Dec 1 10:10:53 2011 – [debug] ok.

masterha_check_repl is used to check MySQL replicaton. This is chatty. Your are looking for the ‘MySQL Replication Health is OK’ at the end. You should pay attention to any warnings.

 masterha_check_repl --conf=/etc/MHA.cnf

—
Thu Dec 1 10:07:22 2011 – [info] Checking slave configurations..
Thu Dec 1 10:07:22 2011 – [warning] read_only=1 is not set on slave db2(192.168.0.11:3306).
Thu Dec 1 10:07:22 2011 – [warning] relay_log_purge=0 is not set on slave db2(192.168.0.12:3306).
—
MySQL Replication Health is OK.

 

You now have MHA installed. If the configure it wrong MHA will give some errors that are not helpful and die. check the system logs (/var/log/messages) and your MHA settings and try again.

We’ll be using Pacemaker and a command like this to fail over to a slave when the master dies.

masterha_master_switch –master_state=dead –dead_master_host=db1 –conf=/etc/MHA.cnf

Putting the H in High Availability (HA)

There are may ways a system can fail and even with MySQL running. How do you know when your dead? How do you make the new is system ready to accept data? Are your servers even on the same network? I’ve chosen PaceMaker because it is the most complete and flexible.

Install Pacemaker / CoroSync

Neither RedHat or CentOS supply PaceMaker packeges. RedHat support their own propitiatory clustering suite. CentOS does supply heartbeat. Thankfully PaceMaker project provide it in EPEL repository for Redhat 5.

Note we are installing PaceMaker for RedHat 5 not 6.2. At the time of this writing a version for 6.2 was not available. This create some dependence problems we have to work around.

 wget -O /etc/yum.repos.d/pacemaker.repo http://clusterlabs.org/rpm/epel-5/clusterlabs.repo
 yum -y install libtool-ltdl
 ln -s /usr/lib64/libltdl.so.7.2.1 /usr/lib64/libltdl.so.3
 yum -y install net-snmp
 wget http://www.clusterlabs.org/rpm/epel-5/x86_64/cluster-glue-1.0.6-1.6.el5.x86_64.rpm
 wget http://www.clusterlabs.org/rpm/epel-5/x86_64/cluster-glue-libs-1.0.6-1.6.el5.x86_64.rpm
 rpm -i cluster-glue-1.0.6-1.6.el5.x86_64.rpm --nodeps
 rpm -i cluster-glue-libs-1.0.6-1.6.el5.x86_64.rpm --nodeps
 yum -y install pacemaker corosync heartbeat

Again, make sure all the name resolves are in place on both DB1 and DB2. Heartbeat and Pacemaker provide the group communications and process management respectivly. The nodes must be able to communicate with each other.

Configure CoroSync

On both DB1 and DB2.

 corosync-keygen
 chown root:root /etc/corosync/authkey
 chmod 400 /etc/corosync/authkey

 vi /etc/corosync/corosync.conf

totem {
    version: 2
    token: 5000 # How long before declaring a token lost (ms)
    token_retransmits_before_loss_const: 20 # How many token retransmits
    # before forming a new configuration
    join: 1000 # How long to wait for join messages in the membership protocol (ms)
    consensus: 7500 # How long to wait for consensus to be achieved before
    # starting a new round of membership configuration (ms)
    vsftype: none # Turn off the virtual synchrony filter
    max_messages: 20 # Number of messages that may be sent by
    #one processor on receipt of the token
    secauth: off # Disable encryption
    threads: 0 # How many threads to use for encryption/decryption
    clear_node_high_bit: yes # Limit generated nodeids to 31-bits (positive signed integers)
    # Optionally assign a fixed node id (integer)
    # nodeid: 1234
    interface {
        ringnumber: 0
        # The following three values need to be set based on your environment
        bindnetaddr: 192.168.2.201
        mcastaddr: 239.255.42.0
        mcastport: 5405
        }
}
logging {
    fileline: off
    to_syslog: yes
    to_stderr: no
    syslog_facility: daemon
    debug: on
    timestamp: on
}
amf {
    mode: disabled
}

 vi /etc/corosync/service.d/pcmk

service {
    # Load the Pacemaker Cluster Resource Manager
    name: pacemaker
    ver: 0
}

If you didn’t edit this on both servers copy it from second server.

 rsync -rop /etc/corosync db2:/etc

Now start corosync on both servers.

 chkconfig corosync on
 /etc/init.d/corosync start

Check CoroSync healty. Look for ‘no faluts’ and both members show up as members.

 corosync-cfgtool -s

Printing ring status.
Local node ID -922572608
RING ID 0
id = 192.168.2.201
status = ring 0 active with no faults

 corosync-objctl | grep members

totem.join=1000 # How long to wait for join messages in the membership protocol (ms)
runtime.totem.pg.mrp.srp.members.-922572608.ip=r(0) ip(192.168.2.201)
runtime.totem.pg.mrp.srp.members.-922572608.join_count=1
runtime.totem.pg.mrp.srp.members.-922572608.status=joined
runtime.totem.pg.mrp.srp.members.-905795392.ip=r(0) ip(192.168.2.202)
runtime.totem.pg.mrp.srp.members.-905795392.join_count=1
runtime.totem.pg.mrp.srp.members.-905795392.status=joined

Patch for Pacemaker

Yves Trudeau had created a newer version of the mysql resource agent for Pacemaker. He keeps it at:
https://github.com/y-trudeau/. Just download it and copy it into the ocf resource heartbeat directory.

 wget https://raw.github.com/y-trudeau/resource-agents/master/heartbeat/mysql
 cp mysql /usr/lib/ocf/resource.d/heartbeat/mysql

Configure Pacemaker

Fencing is normally used and is enabled by default and should be! We’ll start my turning Shoot the other Node in the Head (stonith) off. TURN THIS BACK ON BEFORE YOU GO INTO PRODUCTION! You disable it with:

 crm configure property stonith-enabled="false"

If your cluster consists of just two nodes, switch the quorum feature off with:

 crm configure property no-quorum-policy=ignore

This is a bit of magic. I’ll explain after we’re done. Create file to configure crm and paste this document into it.

 crm configure node db2.grennan.com attributes IP="192.168.2.202"

 crm configure

Now your in the CRM configuration utility. Type in the blue part of this txt and edit the red parts to fit your configuration.

crm(live)configure# primitive failover-ip ocf:heartbeat:IPaddr \
 params ip="192.168.2.200" \
 operations $id="failover-ip-operations" \
 op monitor start-delay="0" interval="2"

crm(live)configure# primitive p_mysql ocf:heartbeat:mysql \
 params pid="/var/run/mysqld/mysqld.pid" socket="/data/mysql/mysql.sock" \
 test_passwd="P@ssw0rd" enable_creation="false" replication_user="root" \
 replication_passwd="P@ssw0rd" MHASupport="true" \
 operations $id="p_mysql-operations" \
 op monitor interval="20" timeout="30" depth="0" \
 op monitor interval="10" role="Master" timeout="30" depth="0" \
 op monitor interval="30" role="Slave" timeout="30" depth="0"

crm(live)configure# ms ms_mysql p_mysql meta clone-max="2" notify="true"

crm(live)configure# colocation col_ms_mysql_failover-ip inf: failover-ip ms_mysql:Master

crm(live)configure# order ord_ms_mysql_failover-ip inf: ms_mysql:promote failover-ip:start

crm(live)configure# commit

crm(live)configure# quit

 

Monitor Pacemaker

Back at the system prompt you can monitor the health of Pacemaker from the command line.

 crm_mon -Arf

============
Last updated: Fri Feb 10 14:28:53 2012
Last change: Fri Feb 10 14:26:15 2012 via crm_attribute on db1.grennan.com
Stack: openais
Current DC: db1.grennan.com – partition with quorum
Version: 1.1.6-3.el6-a02c0f19a00c1eb2527ad38f146ebc0834814558
2 Nodes configured, 2 expected votes
3 Resources configured.
============

Online: [ db1.grennan.com db2.grennan.com ]

Full list of resources:

failover-ip (ocf::heartbeat:IPaddr): Started db1.grennan.com
Master/Slave Set: ms_mysql [p_mysql]
Masters: [ db1.grennan.com ]
Slaves: [ db2.grennan.com ]

Node Attributes:
* Node db1.grennan.com:
+ IP : 192.168.2.201
+ master-p_mysql:0 : 3601
+ readerOK : 1
+ writerOK : 1
* Node db2.grennan.com:
+ IP : 192.168.2.202
+ master-p_mysql:1 : 1
+ readerOK : 0
+ writerOK : 0

Migration summary:

* Node db1.grennan.com:
* Node db2.grennan.com:

Install DRBD Management Console

The DRBD Management Console is a Java application that eases the burden of managing your DRBD and Pacemaker/Corosync or Heartbeat based cluster systems.

Download this Jar file to your workstation.

When you run it you will need to add the server logins and cluster set. You don’t need to edit anything. Skip any installations or configurations.

http://sourceforge.net/projects/lcmc/files/LCMC-1.2.3.jar/download

[I'll post a video of configuring the Management Console here.]

 

But wait there is MORE

Percona Tool kit

You install the Perconal Tool kit when you installed MySQL. :-) Percona Toolkit is a collection of advanced command-line tools used by Percona (http://www.percona.com/) support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. As a DBA you will find them very useful. Here is a small sample:

• pt-duplicate-key-checker – Find duplicate indexes and foreign keys on MySQL tables.
• pt-heartbeat – Monitor MySQL replication delay.
• pt-index-usage – Read queries from a log and analyze how they use indexes.
• pt-query-advisor – Analyze queries and advise on possible problems.
• pt-query-digest – Analyze query execution logs and generate a query report, filter, replay, or transform queries for MySQL, PostgreSQL, memcached, and more.
• pt-query-profiler – Execute SQL statements and print statistics, or measure activity caused by other processes.
• pt-show-grants – Print MySQL grants so you can effectively replicate, compare and version-control them.
• pt-table-checksum – Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
• pt-table-sync – Synchronize MySQL table data efficiently.
• pt-visual-explain – Format EXPLAIN output as a tree.

openark kit

Shlomi Noach maintains another great set of utility.

• oak-apply-ri: apply referential integrity on two columns with parent-child relationship.
• oak-block-account: block or release MySQL users accounts, disabling them or enabling them to login.
• oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.
• oak-get-slave-lag: print slave replication lag and terminate with respective exit code.
• oak-hook-general-log: hook up and filter general log entries based on entry type or execution plan criteria.
• oak-kill-slow-queries: terminate long running queries.
• oak-modify-charset: change the character set (and collation) of a textual column.
• oak-online-alter-table: perform a non-blocking ALTER TABLE operation.
• oak-prepare-shutdown: make for a fast and safe MySQL shutdown.
• oak-purge-master-logs: purge master logs, depending on the state of replicating slaves.
• oak-repeat-query: repeat query execution until some condition holds.
• oak-security-audit: audit accounts, passwords, privileges and other security settings.
• oak-show-limits: show AUTO_INCREMENT “free space”.
• oak-show-replication-status: show how far behind are replicating slaves on a given master.

You can find them here. http://code.openark.org/forge/openark-kit

MyTOP

Mytop is a console-based (non-gui) tool for monitoring the threads and overall performance of MySQL. The original development was done by Jeremy D. Zawodny <Jeremy@Zawodny.com> and is well know after being published in the book, High Performance MySQL. I’ve updated it with as a tool for monitoring MyISAM and replication.

I would be amiss if I didn’t say something about innotop. Some consider this a predecessors to mytop. It is of you are using InnoDB. I still find mytop useful. More than half of my tables are MyISAM.

 wget http://www.mysqlfanboy.com/mytop/mytop-1.9.tar.gz
 tar zxf mytop-1.9.tar.gz
 cd mytop-1.9
 perl Makefile.PL
 make install

 wget https://innotop.googlecode.com/files/innotop-1.8.0.tar.gz
 cd innotop-1.8.0
 perl Makefile.PL
 make install

Backup

Percona XtraBackup is a hot backup utility that doesn’t lock your database during the backup!

It’s best to run this from one of your slaves. I often create a local copy and then rsync the files off the backup system to a remote (off site) server using a cron script.

 yum -y install xtrabackup

 innobackupex --user=root --password=`P@ssw0rd` /tmp/Backup

Here is an example script.

export HOST=`/bin/hostname -a`
mkdir /root/Backup
/usr/bin/innobackupex –user=root –password=`P@ssword` /root/Backup
/usr/bin/xtrabackup –prepare –target-dir=/tmp/backup
/bin/find /tmp/Backup/* -mtime +1 -exec rm {} -Rf \;

Last word

If you’ve made it this fare, now is a good time to restart everything and do some testing.

 init 6

 

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

I’m speaking at Percona Live, LinuxCon Europe, and linux.conf.au. And I just co-founded a new company.

I have a few busy weeks behind me, and even busier weeks ahead. If you’ve been wondering why recently I haven’t been updating this space too frequently, here’s why:

Yours truly and fellow ex-Linbiters Martin Loschwitz and Andreas Kurz have recently founded hastexo, an independent professional services organization focused on open-source high availability and disaster recovery. We are already offering both on-site and remote consultancy, custom training, and our Availability Checkup package, with more services lined up to be added to our offering.

We’re able to offer direct, 24/7 access to high availability experts with dial-in numbers in Europe, North America and Australia. We’re offering our services under an extremely flexible, versatile payments scheme with an attractive volume discount model. We’re experts in an array of high availability and disaster recover technologies — like Pacemaker, Corosync, Heartbeat, DRBD, highly available virtualization (a.k.a “enterprise cloud”), and cluster file systems.

And we’ve got a unique, free offering. Have you ever considered hiring a high availability consultant to review your setup or provide expert advice, but were unsure as to the expected cost involved? At hastexo, we can help. You simply go to our Help page (free-of-charge registration required), collect information as instructed, and then just create a ticket in our support system. And we’ll make a qualified estimate as to the amount of effort (and cost) required to fix your issue, or improve your uptime, or both.

And, just in case one of us has previously help you on a mailing list, on IRC, or at a conference, as we frequently do, then please leave us a message in our Shoutbox. We love to support the high availability community, and we’re thrilled to hear about it when we can help.

Speaking of conferences: next week, I’m doing back-to-back conferences in Europe.

• On Monday, I’ll present a Pacemaker/MySQL tutorial at Percona Live UK, which I’ve blogged about before. Do check it out; my discount code for registrations is still valid!
• On Friday, I’m joining Madison Kelly for a talk on high availability fencing at LinuxCon in Prague.

And, for those of you making plans for Ballarat in January: I’ll return to linux.conf.au as a tutorial speaker, together with Andrew Beekhof and Tim Serong. I have also submitted a talk for the High Availability and Distributed Storage miniconf, preceding the main conference. See you there!

PlanetMySQL Voting: Vote UP / Vote DOWN

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

PlanetMySQL Voting: Vote UP / Vote DOWN

One more DRBD tutorial, this time I will describe howto setup MySQL with DRBD (Distributed Replicated Block Device). Purpose This document describes how to to setup a failover system with MySQL and DRBD (Distributed Replicated Block Device). Introduction In this tutorial we will setup two Debian Linux nodes with a DRBD volume. MySQL will be [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

When I read on the internetz that Alex Davies was about the publish a Packt book on MySQL HA I pinged my contacts at Packt and suggested that I'd review the book .

I've ran into Alex at some UKUUG conferences before and he's got a solid background on MySQL Cluster and other HA alternatives so I was looking forward to reading the book.

Alex starts of with a couple of indepth chapters on MySQL Cluster, he does mention that it's not a fit for all problems, but I'd hoped he did it a bit more prominently ... an upfront chapter outlining the different approaches and when which approach is a match could have been better. The avid reader now might be 80 pages into MySQL cluster before he realizes it's not going to be a match for his problem.

I really loved the part where Alex correcly mentions that you should probably be using Puppet or so to manage the config files of your environment, rather than scp them around your different boxes ..

Alex then goes on to describe setting up MySQL replication and Multi Master replicataion with the different approaches one can take here, he gives some nice tips on using LVM to reduce the downtime of your MySQL when having to transfer the dataset of an already existing MySQL setup, good stuff.

He then goes on to describe MySQL with shared storage ... if you only mount your redundant sandisk once on your MySQL nodes my preference would probably be a Pacemaker stack rather than a RedHat Cluster based setup, but his setup seems to work too. Alex quickly touches on using GFS to have your data disk mounted simultaneously on both nodes (keep in mind with only 1 active MySQLd) and then goes on to describe a full DRBD based MySQL HA setup

The last chapter titled Performance tuning gives some very nice tips on both tuning your regular storae, as your
GFS setup but also the tuning parameters for MySQL Cluster

I was also really happy to see the Appendixes on the basic installation where he advocates the use of Cobbler , Kickstart and LVM ..

One of the better books I read the past couple of years .. certainly the best book from Packt so far , I hope there is more quality stuff coming from that direction !

PlanetMySQL Voting: Vote UP / Vote DOWN

This year’s Linux Plumbers Conference is taking place November 3-5, in Cambridge, MA, United States. The CfP is already closed and the program is due any day now, but the co-located miniconference on high availability clustering is still accepting proposals. This is your chance to get involved!

So if you plan to attend Plumbers or just happen to be in the area, please submit your talk! Miniconference talks are not expected to be full-blown presentations. Instead, you can float an idea in just a 5-10 minute talk and then stimulate a vibrant group discussion.

Even if you are not attending, you can still help! We are always eager to hear from our user community. What HA problems are you currently facing that the existing Linux clustering stack does not solve? How well does your application integrate with HA? Where can we improve? What’s already good, and can be made better? What sucks?

Feel free to comment below. Or send us an email on one of the mailing lists. Or grab us in #linux-ha or #linux-cluster on freenode. Make yourself heard!

PlanetMySQL Voting: Vote UP / Vote DOWN

This is one of a few MySQL High Availability strategies.  I have used this for years and found it work great.  If you don’t know about DRBD and MySQL you should read Peter’s comments.

These are step by step instructions for Redhat 5 or CentOS.

If you need more details please refer to:
http://www.drbd.org/users-guide/

Configuring MySQL for DRBD
http://dev.mysql.com/doc/refman/5.1/en/ha-drbd-install-mysql.html

Getting started:

The OS in this example is CentOS 5.5.  I added a new disk (/dev/sde) to the four disk RAID-5 and RAID-1 I was already using.   I’m only creating an 8 gig disk (vmware). You should start with a partition (LVM and or RAID) partition big enough for your data.

# uname -a
Linux db1.grennan.com 2.6.18-194.8.1.el5 #1 SMP Thu Jul 1 19:04:48 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux

# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md1              24065660   2826564  19996896  13% /
/dev/md0                101018     20988     74814  22% /boot
tmpfs                   513476         0    513476   0% /dev/shm

# fdisk -l /dev/sde

Disk /dev/sde: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1        1044     8385898+  83  Linux

DRBD:

Installation:
On machine1 and machine2 install DRBD and its kernel module.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

 # yum -y install drbd
 # yum –y install kmod-drbd82.x86_64
 # modprobe drbd  

Configuration:
On both machines edit this configuration file.  I have highlighted parts you will need to edit in red.

# vi /etc/drbd.conf

#
# please have a a look at the example configuration file in
# /usr/share/doc/drbd82/drbd.conf
#
# Our MySQL share
resource db
{
 protocol C;

 startup { wfc-timeout 0; degr-wfc-timeout 120; }
 disk { on-io-error detach; } # or panic, ...
 syncer {
 rate 6M;
 }

 on db1.grennan.com {
 device /dev/drbd1;}
 disk /dev/sde1;
 address 192.168.2.13:7789;
 meta-disk internal;
 }

 on db2.grennan.com {
 device /dev/drbd1;
 disk /dev/sde1;
 address 192.168.2.14:7789;
 meta-disk internal;
 }
}

Manage DRDB processes:

On both machines run

 # drbdadm adjust db

On machine1

 # drbdsetup /dev/drbd1 primary –o

 # service drbd start 

On machine2

 # service drbd start

On both machines(see status):

 # service drbd status

On machine1

# mkfs -j /dev/drbd1
# tune2fs -c -1 -i 0 /dev/drbd1
# mkdir /data
# mount -o rw /dev/drbd1 /data

On machine2

# mkdir /data

Test failover:
This is how you perform a manual fail over. You will use HA to do this for you in the next sections.

On primary (server1)

# umount /data
# drbdadm secondary db

On secondary (server2)

# drbdadm primary db
# service drbd status
# mount -o rw /dev/drbd1 /data
# df

Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/md1              24065660   1898696  20924764   9% /
/dev/md0                101018     14886     80916  16% /boot
tmpfs                   513472         0    513472   0% /dev/shm
/dev/drbd1             8253948    149628   7685040   2% /data

Note we never formatted (mkfs) the disk on machine2! Here it is, ready to go, DRDB has copied all the data.

MySQL:

Here are a few notes for you to think about.

• The default location for MySQL data is /var/lib/mysql.  You will be moving this to /data/mysql.

• MySQL configuration is in /etc/my.cnf.  So that changes to the configuration move with failover, you should put my.cnf in /data/mysql and create a sym-link of /etc/my.cnf to this file.

Now comes the hurdle.

• Install MySQL as you wish.
• Move your data directory to a /data/mysql

On machine1

# mkdir /data/mysql
# chown  mysql.mysql /data/mysql
# cp –prv /var/lib/mysql/* /data/mysql
Start MySQL on machine1.
Create some sample database and table. Stop MySQL. Do a manual switchover of DRBD. Start MySQL on machine2 and query for that table. It should work. But, this is of no use if you have to switchover manually every time. When you have this working you are ready to move to Heartbeat.

Here are a couple of scripts to make this easy.

drdb-secondary

# service mysql stop
# umount /data
# drbdadm secondary db
# drdb-primary:
# drbdadm primary db
# mount -o rw /dev/drbd1 /data
# service mysql start

HA:

• IMPORTANT: Heartbeat uses either Linux Services (LSB) Resource Agents or Heartbeat Resource Agents (HRA) to start and stop heartbeat resources. You will be adding MySQL (LSB), drbddisk (HRA) and IPaddr2 (HRA) are our heartbeat resources.
• Refer this page on Resource Agent
• As you are aware of it many *nix services are started using LSB Resource Agents. They are found in /etc/init.d

Installation:

On machine1 and machine2 install Heartbeat and needed utilities.  You may need to review the packages you have available using ‘yum list | grep drbd’.  These are for CentOS 5.5.  You may also need to reboot after this step.

# yum -y install gnutls*
# yum -y install ipvsadm*
# yum -y install heartbeat*
# yum -y install heartbeat.x86_64

Configuration:

Edit /etc/sysctl.conf and set net.ipv4.ip_forward = 1

# vi /etc/sysctl.conf

Controls IP packet forwarding net.ipv4.ip_forward = 1

# /sbin/chkconfig –level 2345 heartbeat on

# /sbin/chkconfig –del ldirectord

Configure HA:

You need to setup the following configuration files on both machines:

# vi /etc/ha.d/ha.cf

#/etc/ha.d/ha.cf content
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694 # If you have multiple HA setup in same network.. use different ports
bcast eth0 # Linux
auto_failback on # This will failback to machine1 after it comes back
ping 192.168.2.1 # The gateway
apiauth ipfail gid=haclient uid=hacluster
node db1.grennan.com
node db2.grennan.com  

On both machines

NOTE: Assuming 192.168.2.15 is virtual IP for your MySQL resource and mysqld is the LSB resource agent. The host name (db2) should be the secondary server’s name.
# vi /etc/ha.d haresources

# /etc/ha.d/haresources content
db2.grennan.com LVSSyncDaemonSwap::master Paddr2::192.168.2.15/24/eth0  rbddisk::db Filesystem::/dev/drbd1::/data::ext3 mysqld

# vi /etc/ha.d/authkeys

#/etc/ha.d/authkeys content
auth 2
2 sha1 BigSecretKeyks9wjwlf9gskg905snvl

Now, make your authkeys secure:

# chmod 600 /etc/ha.d/authkeys

Check your work:

On both machines, one at a time, stop MySQL and make sure MySQL does not start when the system reboots (init 6).

If it does, you may need to remove it from the init process with:

# /sbin/chkconfig –level 2345 MySQL off

Start Heartbeat.

# service heartbeat start

These commands will give you status about this LVS setup:
# /etc/ha.d/resource.d/LVSSyncDaemonSwap master status
# ip addr sh
# service heartbeat status
# df
# service mysqld status

Access your HA-MySQL server like:
# mysql –h 192.168.2.15

Shutdown machine1 to see MySQL up on machine2. ‘shutdown now’

Start machine1 to see MySQL back on machine1.

PlanetMySQL Voting: Vote UP / Vote DOWN

For those of you who haven’t yet registered, this is our reminder for today’s Clustering in Debian webinar at 1400 UTC. If you’re planning to run Pacemaker on the upcoming Debian squeeze release, don’t miss this!

PlanetMySQL Voting: Vote UP / Vote DOWN

Martin Loschwitz, the longest-serving Debian Developer in our ranks, will present a walk-through of going from Linux clustering on Debian lenny (with Heartbeat 2.1.4, shudder) to squeeze (with Heartbeat 3 and Pacemaker). In this webinar you will learn everything you need to know about a painless migration to the new Linux cluster stack.

This webinar will be held on Monday, June 7, at 1400 UTC. You must provide a valid email address to receive a meeting key. Register below!

The widget should display the start time in your local timezone. In case Dimdim gets this wrong, 1400 UTC is the correct time.

PlanetMySQL Voting: Vote UP / Vote DOWN

The full Linux cluster stack, including Pacemaker, Heartbeat, Corosync and related packages, cleared all hurdles and migrated to the Debian testing repository this morning. This means that Pacemaker and both messaging layers it supports will be in the upcoming Debian release, codenamed squeeze.

Credit goes to the debian-ha maintainers crowd for making this possible: Martin Loschwitz, Simon Horman, Frederik Schüler, Anibal Monsalve Salazar, Guido Günther, Norbert Tretkowski, and others.

In terms of high availability support per distribution, this now has Debian, Ubuntu and SLES running roughly abreast.

PlanetMySQL Voting: Vote UP / Vote DOWN