Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL - The Old Way
Search
Gea-Suan Lin
July 29, 2012
Technology
4
450
MySQL - The Old Way
Gea-Suan Lin
July 29, 2012
Tweet
Share
More Decks by Gea-Suan Lin
See All by Gea-Suan Lin
High Availability Vault Service on AWS Environment
gslin
0
7k
用 AWS CodeDeploy 解決程式佈署
gslin
0
390
MySQL to NoSQL & Search Engine
gslin
0
2k
用 Vagrant 與 Docker 拯救世界
gslin
1
250
Startup IT infrastructure: Developing and Working with AWS
gslin
8
3.5k
Talk about Percona XtraDB Cluster
gslin
0
180
API Design Optimized for Mobile Platform
gslin
9
8.5k
Use Facebook::Graph to write desktop application
gslin
2
490
COSCUP 2012 - MySQL System Stability
gslin
17
12k
Other Decks in Technology
See All in Technology
BigQuery Remote FunctionでLooker Studioをインタラクティブ化
cuebic9bic
2
230
Prox Industries株式会社 会社紹介資料
proxindustries
0
210
米国国防総省のDevSecOpsライフサイクルをAWSのセキュリティサービスとOSSで実現
syoshie
2
820
本当に使える?AutoUpgrade の新機能を実践検証してみた
oracle4engineer
PRO
1
120
(非公式) AWS Summit Japan と 海浜幕張 の歩き方 2025年版
coosuke
PRO
1
340
初めてのAzure FunctionsをClaude Codeで作ってみた / My first Azure Functions using Claude Code
hideakiaoyagi
1
180
OpenHands🤲にContributeしてみた
kotauchisunsun
0
150
新卒3年目の後悔〜機械学習モデルジョブの運用を頑張った話〜
kameitomohiro
0
390
Agentic DevOps時代の生存戦略
kkamegawa
0
990
CIでのgolangci-lintの実行を約90%削減した話
kazukihayase
0
340
LinkX_GitHubを基点にした_AI時代のプロジェクトマネジメント.pdf
iotcomjpadmin
0
160
20250625 Snowflake Summit 2025活用事例 レポート / Nowcast Snowflake Summit 2025 Case Study Report
kkuv
1
210
Featured
See All Featured
Measuring & Analyzing Core Web Vitals
bluesmoon
7
490
CoffeeScript is Beautiful & I Never Want to Write Plain JavaScript Again
sstephenson
161
15k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
252
21k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
44
2.4k
Code Reviewing Like a Champion
maltzj
524
40k
Side Projects
sachag
455
42k
Fireside Chat
paigeccino
37
3.5k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
29
9.5k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Transcript
MySQL - The Old Way Gea-Suan Lin
Old != Bad
In this case, Old implies Reliable
Reliable
We are familiar with these issues,
We can workaround these issues
So...
First Solution
Built-in Replication
http://dev.mysql.com/ doc/refman/5.5/en/ replication.html
The Good Part
Simple
Dead Simple Setup
Dead Simple Concept
Master logs changes
Slave applies changes
The Bad Part
Async
Means...
Replication Lag
When you write data to master,
It’s possible unable to read from slave
Workaround
Always use master if you write data
The Worst Part
Application cannot write after master crash
This derives...
Second Solution
Master-Master
Two MySQL servers
Set each other as master server
The Good Part
Both MySQL servers can be written
The Bad Part
Data inconsistent
MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1;
MySQL Server B TRUNCATE TABLE t; INSERT t SET i=2;
MySQL Server A TRUNCATE TABLE t; INSERT t SET i=1;
TRUNCATE TABLE t; INSERT t SET i=2; MySQL Server B TRUNCATE TABLE t; INSERT t SET i=2; TRUNCATE TABLE t; INSERT t SET i=1;
Result
MySQL Server A i=2 MySQL Server B i=1
Workaround
Write one server in normal case
Write another one when primary node fails
You can write code to handle failover
But we suggest to use Heartbeat
Heartbeat can handle this failover case
When data is inconsistent,
We can use Percona’s pt-table-sync to sync data
Third Solution
DRBD + Heartbeat
DRBD is Network-based RAID-1
Block-level mirror
Heartbeat handles High Availability
http://dev.mysql.com/ doc/refman/5.5/en/ha- drbd.html
http://dev.mysql.com/ doc/refman/5.5/en/ha- heartbeat.html
The Good Part
Data consistent
You won’t need to worry about data inconsistent issue
The Bad Part
Utilization Rate
Only one server uses to serve applications
Warm up time
When I/O rate cannot catch query rate
Conclusion
DRBD has higher down-time than other twos,
And it costs higher in server hardware
But it costs lower for SA Operations
If down-time of DRBD + Heartbeat is acceptable,
You should choose it
How much down- time ?
It depends,
Usually < 30 secs to failover
With warm up time
Possible 3~5 mins for 10GB data size ?
Thanks !