MySQL High Availability - Percona

As for the PHP script, the full copy can be found , as it is a bit lengthy I will just explain the logic on what it does. 1. To start the test, it bootstraps the test table via ...
2MB Sizes 10 Downloads 237 Views
MySQL High Availability Vol. 1

By Jervin Real

In this Percona eBook, the first in a series on MySQL High Availability, senior MySQL consultant Jervin Real introduces mysqlnd_ms with a simple HA solution using asynchronous MySQL replication. He then demonstrates how to leverage an all-primary cluster with writes to all nodes using Percona XtraDB Cluster. Percona XtraDB Cluster is an active/active high availability and high scalability open source solution for MySQL clustering. It integrates Percona Server and Percona XtraBackup with the Galera library of MySQL high availability solutions in a single package which enables you to create a cost-effective MySQL high availability cluster.

Copyright © 2006-2014 Percona LLC

MySQL High Availability

Vol. 1

Table of Contents Chapter 1: Simple MySQL Master HA with mysqlnd_ms Chapter 2: High Availability with mysqlnd_ms on Percona XtraDB

3 6

About Percona Percona was founded in August 2006 and now employs a global network of experts with a staff of more than 120 people. Our customer list is large and diverse, including Fortune 50 corporations, popular websites, and small startups. We have over 2,000 customers and, although we do not reveal all of their names, chances are we're working with nearly every large MySQL user you've heard about. To put Percona's MySQL expertise to work for you, please contact us.

Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here Skype: oncall.percona GTalk: [email protected] AIM (AOL Instant Messenger): oncallpercona Telephone direct-to-engineer: +1-877-862-4316 or UK Toll Free: +44-800-088-5561 Telephone to live operator: +1-888-488-8556 Customer portal: https://customers.percona.com/

Copyright © 2006-2014 Percona LLC

MySQL High Availability Chapter 1: Simple MySQL Master HA with mysqlnd_ms

Simple MySQL Master HA with mysqlnd_ms By Jervin Real The mysqlnd plugin, MySQL Master Slave, is a transparent layer on top of mysqlnd extension. This allows you to do read-write splitting and slave reads load balancing without needing to change anything from your application. But do you know you can also achieve a form of high availability with this plugin? I shared 2 forms during a recent presentation, using async MySQL replication either in master-slave configuration or master-master configuration, while the second form is having an all primary cluster where you can write to all nodes. This first part is to demonstrate how you can achieve a simple HA solution using the first form. First, all the sample code here can be found on my GitHub repository. So, to use the mysqlnd_ms plugin, it uses an additional external configuration file in JSON format. This configuration file, will define your master and slave nodes, failover properties and any filters (connection selection method) you want to dictate how the algorithm will provide you the connection. Let’s start with the mysqlnd_ms configuration I used,

3

MySQL High Availability Chapter 1: Simple MySQL Master HA with mysqlnd_ms

Here, I have two applications defined, one called “primary” and another called “standby.” I have not defined any slaves for simplicity. The two MySQL instances running on port 33001 and 33002 are in master-master configuration.

This is the custom INI file I used for the tests, master-slave.ini . The first line simply enables the plugin for use. The second line, mysqlnd_ms.disable_rw_split instructs the plugin that I should only send all queries to the master because I only have masters for this test. As for the PHP script, the full copy can be found , as it is a bit lengthy I will just explain the logic on what it does. 1. To start the test, it bootstraps the test table via DROP and then CREATE queries. 2. It then enters a for loop where it will execute an INSERT followed by a SELECT to validate the newly inserted row and additional information like the curr