ProxySQL and Percona XtraDB our new HA solution

As the Data Center expand because of such many requirements we searched also to improve the High Availability of our DC.

Not a very simple task as we already run enough servers and we cannot just stop a server even for 10 minutes.

The step we made this time for HA it was one of the most sensitive part of our DC: the SQL servers.

Practically all information you put on your web site is using a database. Also on our development platforms we use very intensive DB servers.

As DB we are mostly using MySQL and MariaDB.

Why HA servers?

Maybe you think that a simple backup could solve any problem. But there are a few problems: when you make (for auto backup is the same) a backup, the tables get lock during the dump, so you cannot write new info into the tables. But this is the smallest problem. If the MySQL or MariaDB server are going off you should start the backup server asking yourself what data will be lost. Also the users will be unhappy as the will receive the ‘Cannot connect to database’ error.

For all this you need a High Availability solution.

As Percona offer HA solution which integrate also MySQL, MariaDB, even MongoDB and RocksDB it appeared very interesting for us to migrate to their solution.

Our first tests was made on 4 VM to check what’s happening with some databases.

What we discovered it was that we have to make some changes on our dumps from the old MySQL servers as we are no more running on permissive premise. Simple to tell, not so simple to make.

Some of our PHP and HACK code also has to be changed (we checked one of our SAS solution for our customers which runs under PHP and HHVM).

But at the end we just loved the solution as we was able to close any node and everything worked just fine, even it remain a single workig node

Now let’s take a look of our production solution implemented on our DC

On the right side you can see two nodes on Data Center A and two nodes on Data Center B as we want to simplify the schematics to easily understand the situation.

This are the nodes of Percona XtraDB.

When you run for the first time this nodes you should start the first node as boostrap service because it will give the cluster name. Now it is the right moment to populate the server with data.

Just as an example, we took one of the busiest wordpress based web site of a client of us and moved in realtime to the new cluster. First we prepared all using the old server, we transformed all MyISAM tables to InnoDB and transfered the data between the servers.

Once the bootstraped server is provisioned with all the data we start to join the cluster with the new node. As it is very new and empty SQL we have to wait till SST/IST is ends displaying on log that the server is SYNCED . After we pass to another node until we start the last node.

Now we have a HA solution working.

Why Data Center A and Data Center B. To have a real HA solution it is just better that your data resides on two locations. If one location become unavailable for different reasons you have the other location working

All this it is possible because of another piece of server named ProxySQL. Yes, it is just awesome, free Open Source server.

With ProxySQL you have HA and also load balancing but this is not all. You have a lot of security. The Application Servers will not connect directly to your DB Servers, it will pass through the ProxySQL which will act also as a firewall.

The ProxySQL server should run on a separate server as it should be independent from any node. You provision the server with the nodes. When a request is made the server will check for available nodes and also it will check

Some of the most important features of this solution are:

  • Query caching
  • Query Routing
  • Supports failover
  • Cross platform
  • Advanced configuration with almost 0 downtime
  • Application layer proxy A
  • dvanced topology support
  • Firewall
  • Dynamically route traffic on demand.
  • Built-in monitoring.
  • Runtime reconfigurable.
  • Load Balancing.
  • Read/Write split and sharing.

 

Stay tuned on our web page and on our FB https://www.facebook.com/CustomCloudServersDevelopment/ to know the latest development news from us !