PORP LAB : ProxySQL/Orchestrator/Replication/PMM Summary PORP Lab will create 4 different nodes. Each node will have below packages/applications/db installed.

app
-- Percona Server 5.7
-- Percona Toolkit 
-- Percona XtraBackup 
-- Sysbench 
-- ProxySQL
-- Orchestrator 
-- PMM

mysql1 / mysql2 / mysql3
-- Percona Server 5.7
-- Percona Toolkit
-- pmm-client
-- Replication

PORP LAB have ProxySQL,Orchestrator and PMM properly configured, we can just create this lab and use it.

Install VirtualBox

Version 5.2.22 or Later

Install Vagrant

Version 2.2.2 or Later

Install Vagrant plugin hostmanager

vagrant plugin install vagrant-hostmanager

Update Vagrant Plugin

vagrant plugin update

Clone the repo

git clone …

ProxySQL 2.0.3, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL,  and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 2.0.3 source and binary packages available from the …

Conglomeration, Collaboration and Celebration of Database Administrators

Founders of Mydbops envisioned contributing knowledge back to the community. This vision is shaping up in its 3rd edition of the Meetup held on Saturday the 27th of April, 2019. This meetup edition had drawn a good amount of members from the Open Source Database Administrative Community, to the venue.  The core agenda was set on “High Availability concepts in ProxySQL and Maxscale”. There were also presentations in MongoDB Internals along with MySQL Orchestrator and its implementation excellence at Ola (ANI Technologies Pvt. Ltd.)

The participants from various organisations like MariaDB, TeleDNA, CTS, OLA, Infosys, …

In my previous post, some time ago, I wrote about the new cluster feature of ProxySQL. For that post, we were working with three nodes, now we’ll work with even more! If you’ve installed one ProxySQL per application instance and would like to work up to more, then this post is for you. If this is new to you, though, read my earlier post first for more context.

Check the image below to understand the structure of “one ProxySQL per application”. This means you have ProxySQL installed, and your application (Java, PHP, Apache server etc) in the same VM (virtual machine).

Having taken a look at that you probably have a few questions, such as:

• What happens if you have 20 nodes …

Distributed systems are hard – I just want to echo that. In MySQL, we have quite a number of options to run highly available systems. However, real fault tolerant systems are difficult to achieve.

Take for example a common use case of multi-DC replication where Orchestrator is responsible for managing the topology, while ProxySQL takes care of the routing/proxying to the correct server, as illustrated below. A rare case you might encounter is that the primary MySQL

node01

on DC1 might have a blip of a couple of seconds. Because Orchestrator uses an adaptive health check – not only the node itself but also consults its replicas – it can react really fast and promote the node in DC2.

Why is this problematic?

The problem occurs when

node01

resolves its temporary issue. A race condition could occur within ProxySQL that could mark it back as read-write. You can increase an …

When designing a highly-available MySQL architecture, having a proxy to route the traffic to the appropriate instances is crucial to achieve transparent (or almost) failovers/switchovers. ProxySQL, a popular open source, SQL-level proxy is a great choice for this, and even though an experimental clustering feature is available, we could follow a simpler active-passive approach too, based on Common Address Redundancy Protocol (CARP), which will also provide a highly available IP for the applications to connect.

Solution overview

As mentioned before, this setup relies on a shared IP configured through CARP on the two hosts that will be running ProxySQL. CARP configuration is outside the scope of this post but the steps are on available on the FreeBSD documentation.

We then need to make sure all ProxySQL configuration changes performed on the …

The latest enhancement in ProxySQL v2.0.0 is the native support of Galera Cluster. No more need to use an external script within the scheduler like explained also in this post of ours.

This document will cover how to take an advantage of the new feature and integrate ProxySQL with Percona XtraDB Cluster to monitor galera node status and read-write split performed by ProxySQL. To illustrate we will use a cluster of 3 nodes, below are the details:

Percona is glad to announce the release of Percona XtraDB Cluster 5.6.43-28.32 on February 28, 2019. Binaries are available from the downloads section or from our software repositories.

This release of Percona XtraDB Cluster includes the support of Ubuntu 18.10 (Cosmic Cuttlefish). Percona XtraDB Cluster 5.6.43-28.32 is now the current release, based on the following:

• Percona Server 5.6.43-84.3
• Codership WSREP API release 5.6.42
• Codership Galera library 3.25

All Percona software is …

Percona is glad to announce the release of Percona XtraDB Cluster 5.7.25-31.35 on February 28, 2018. Binaries are available from the downloads section or from our software repositories.

This release of Percona XtraDB Cluster includes the support of Ubuntu 18.10 (Cosmic Cuttlefish). Percona XtraDB Cluster 5.7.25-31.35 is now the current release, based on the following:

• Percona Server for MySQL 5.7.25-28
• Galera Replication library 3.25
• Galera/Codership WSREP API Release 5.7.24

All Percona software is open-source and free.

Bugs Fixed

…

ProxySQL in its versions up to 1.x did not natively support Percona XtraDB Cluster (PXC). Instead, it relied on the flexibility offered by the scheduler. This approach allowed users to implement their own preferred way to manage the ProxySQL behaviour in relation to the Galera events.

From version 2.0 we can use native ProxySQL support for PXC.. The mechanism to activate native support is very similar to the one already in place for group replication.

In brief it is based on the table [runtime_]mysql_galera_hostgroups and the information needed is mostly the same:

• writer_hostgroup: the hostgroup ID that refers to the WRITER
• …