![Page 1: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/1.jpg)
Building a High Availability
Solution that Works
Jean-François Gagné
jeanfrancois DOT gagne AT booking.com
Presented at Percona Live Santa Clara 2015
![Page 2: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/2.jpg)
Remember High School Math ?
● Function ( f(x) = y ) ?
● A deterministic result from a known input
● f(x) = 2x, f(x) = x2, f(x) = sin(x), …
● f(a1, a2, a3, a4, …, an) = y
● Pseudo random functions
● Chaos Theory: weather prediction (butterfly effect)
2
![Page 3: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/3.jpg)
The Binary Log Function on Slaves
3
Relay Logs
Binary Logs
Configuration
• filters (empty trx w GTID)
• slave-parallel-workers
• slave-preserve-
commit-order
• binlog_format
Content of the Database
Local Transactions
Relay Logs2 Relay Logs3
Human
• skipping transactions
• modifying configuration
• restarting MySQL
• doing mistakes
Hardware
• # and speed of CPU
• amount of RAM
• speed of storage
(RAID rebuild ?)
• network (congestion ?)
Load on the system
(virtualisation ?) State of the Buffer Pool log-slave-updates
??? Others ???
![Page 4: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/4.jpg)
4
Would you build a high availability
solution on the output of such
a complex function ?
GTID + log-slave-updates
very complex high availability
Complex things break in complex ways
![Page 5: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/5.jpg)
Is there Better ?
● Yes: the Binlog Server
● A very simple concept
5
![Page 6: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/6.jpg)
The Binary Log Function on Slaves
(without log-slave-updates)
Much more simple, isn’t it ?
And the best thing about it:
because it is not running on many slaves
duplicated unreliable output does not happen.
6
Binary Logs
Local Transactions
![Page 7: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/7.jpg)
And The Binlog Server Function
7
Binary Logs from the Master
Binary Logs from the Master
Binlog Server
![Page 8: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/8.jpg)
There is Better
● The Binlog Server
● A very simple concept
A good starting point for High Availability
● http://jfg-mysql.blogspot.com/
8
![Page 9: Building a MySQL High Availability Solution that Works](https://reader036.vdocuments.us/reader036/viewer/2022071816/55ab27d61a28abc1268b45cc/html5/thumbnails/9.jpg)
Thank you
Jean-François Gagné
jeanfrancois DOT gagne AT booking.com