high availability mysql with drbd and heartbeat: mtv japan

High-Availability MySQL with DR:BD and Heartbeat: MTV Japan mobile services

©2008 MTV Networks Japan K.K.

Patrick [email protected]

System Manager

MTV Networks Japan K.K.


Let’s get started


About MTVNJ

Company

Profile

Company Name MTV Networks Japan K.K.

President Peter Robert Bullard

Address 2-8-2 Jingumae, Shibuya-ku, Tokyo 150-0001

TEL: 03-6434-3111 FAX: 03-6434-3150

Business

Description

MTV Networks Japan K.K. (MTVNJ) operates the mobile and PC

digital media services for all MTV Networks brands including MTV and

Nickelodeon in Japan. In addition to digital media services, MTVNJ

also operates a 24-hour Nickelodeon channel on CS/CATV, and the

consumer products business for Nickelodeon's character franchises in

Japan.


MTVNJ Mobile Brands


MTV Flux/MTV Mobile DB project intro


{database}

DB Selection

MTV Flux/MTV Mobile DB project


Database requirements:• flexibility

• performance

• scalability

• redundancy

• support

• cost

Project summary:Merge all the database services for the MTV Flux and

MTV Mobile services on to a single database platform.



DB Selection

reqs/DB Oracle MS SQLMySQL

Community

MySQL

Enterprise

flexibility △／× × ○ ○

performance ○ ○ ○ ○

scalability ○／△ ○ ○ ○

redundancy ○／△ ○ ○ ○

support ○ ○ × ○

cost × × ◎ △



DB Selection


We chose MySQL Enterprise


DB Selection


What we looked for:• flexibility

• performance

• scalability

• redundancy

• support

• cost

MySQL Architectures:• Single/multi Master

• Master/Slave

• Cluster

• DR:BD/Heartbeat HA Master


MySQL Architectures

www www www

Single/multi Master

• cheap(!)

• easy configuration

• load ↑ ⇒ performance ↓

• high risk (if master dies, no more DB)

flexibility performance scalability redundancy support cost

○ ○/△ × × ○ ◎

Master

×



MySQL Architectures

www www www

Master/Slave

• some redundancy (read/writes separate)

• commonly used architecture

• still some risk (operator needed for outage recovery)

• manual failover for outages

Master Slave

×




○ △ ○ △ ○ ◎

MySQL Architectures

www www www

Cluster

• redundant!

• can scale

• complicated configuration

• limited support in Japan (2007)

Cluster

△




○ ○ ◎ ◎ ○/△ △

MySQL Architectures

www www www

DR:BD/Heartbeat HA Master

• redundant (master fully redundant)

• can scale (add salves)

• reduced outage risk

• can use failover for maintenance tasks

• complicated configurationPrimary Secondary

◎




◎ ○/△ ○/△ ◎ ○ ○

MySQL Architectures


MySQL HA Master



HA Master

VIP

/etc/my.cnf

local

/var/lib/mysql

DRBD

/var/lib/mysql

DRBD

/etc/my.cnf

local

heartbeat

drbd

Application Application

Primary Secondary©2008 MTV Networks Japan K.K.


VIP

DRBD

heartbeat

Application Web site or whatever

Manages/monitors DB process and mounts:

• provides application connection point (Virtual IP)

• monitors network status• watches the mysqld process

• manages the /var/lib/mysql mount point

mysqld (the database)

Synchronous device layer data replication


HA Master


VIP

/etc/my.cnf

local

/var/lib/mysql

DRBD

/var/lib/mysql

DRBD

/etc/my.cnf

local

heartbeat

drbd


Primary Secondary

×Failover

processnetwork or mysqld

failure

1 – Application loosesconnection to mysqld

2 – heartbeat

detects the problem


HA Master

4 – Failover complete!3 – heartbeat does:

• pDB mysql dir unmount

• DRBD switch

• sDB mysql dir mount• sDB mysqld start

drbd

2 – drbd

fails over

1 – primary server fails!3 – heartbeat

detects outage


VIP

/etc/my.cnf

local

/var/lib/mysql

DRBD

/var/lib/mysql

DRBD

/etc/my.cnf

local

heartbeat

drbd



HA Master

Failover

processserver outage

4 – Failover complete!3 – heartbeat does:

• sDB mysql dir mount• sDB mysqld start


HA MySQL install



HA Master Install

local disk local disk

A Master

local disk

B Master Master

Dual Master Single Master

Old DB

architecture



VIP

/etc/my.cnf

local

/var/lib/mysql

DRBD

/var/lib/mysql

DRBD

/etc/my.cnf

local

heartbeat

drbd

New DB

architecture

HA Master Install



HA Master Install

Database migration work plan:• stop production services• perform full data backup (mysqldump)

• Replace MySQL architecture

• restore data from backup

• test database failover

• test application

• re-open production services


5 – test application

connectivity

local disk×local disk local disk


A Master B Master Master

Migration

processVIP

/etc/my.cnf

local

/var/lib/mysql

DRBD

/var/lib/mysql

DRBD

/etc/my.cnf

local

drbd

heartbeat

1 – remove old

database components

2 – install/configure

drbd and mysql

3 – install

heartbeat4 – configure VIP

HA Master Install

Primary Secondary

×©2008 MTV Networks Japan K.K.


HA Master Install

Actual migration work times:•production services stop – 00:10• full data backup (mysqldump) – 00:05

• MySQL architecture replacement – 01:30

• data restore – 00:10

• database failover test – 00:15

• application test – 03:00

• production services fully restored!



HA MySQL lessons learned



Lessons learned

Heartbeat:• no room for configuration mistakes

• pay special attention to peer connectivity

• run lots of tests before going to production!!!

Impact on our application:• essentially the same as a single master

• can use same HA for:

• single master - master/slave - multi-master

• failover fast enough that DB outages don’t affect application



Lessons learned

DR:BD:• requires separate partition (can be LVM)

• like heartbeat – test your configurations!!!

• always check your DRBD status after a failover

• peers sometime require a manual re-sync

mysqld:• can take advantage of redundancy to swap out nodes

• never try to run both primary and secondary at once!!

• leave all the redundancy to heartbeat



Lessons learned

Overall – good points:• we were able to deploy HA in a short maintenance window

• commercial support available (MySQL – LINBIT)

• MySQL consultative services were a big help

• also usable with MySQL Community

• can be part of other architectures

• multi-master - master/slave

Overall – weak points:• lots to learn, lots to test (heartbeat and DRBD)

• configuration and failover is complex• mysqld restart requires some extra steps

• all admins/vendors need to understand failover basics


HA MySQL in other MTVNJ environments


Questions?


DRBD related links

http://www.drbd.org/users-guide/s-heartbeat-r1.html

http://www.drbd.org/users-guide/s-heartbeat-crm.html

MTVNJ links

http://www.mtvn.jp/en/index.html

http://www.mtvn.jp/en/service.html

Patrick Bolduan

MTV Networks Japan KK

2-8-2 Jingu-mae, Shibuya-ku, Tokyo 150-0001

[email protected]

http://www.mtvjapan.com















http://www.mtvn.jp/en/index.html

http://www.mtvn.jp/en/service.html

mailto:[email protected]

http://www.mtvjapan.com/

high availability mysql with drbd and heartbeat: mtv japan

Documents

business mtv networks

mtv japan mobile services

manager mtv networks

mtv networks brands

mtv mobile services

mtv andnickelodeon

mtvnj mobile brands

redundancy readwrites