Introduction
We are well aware that ProxySQL is one of the leading SQL aware proxy for MySQL. In this blog I am going to explain the backup & restore strategies of the ProxySQL . I think, still there is not well structured blog about this topic .
If you are looking for other articles on our ProxySQL Series :
[Read more]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 …
[Read more]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, …
[Read more]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:
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 …
[Read more]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 …
[Read more]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:
All Percona software is …
[Read more]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:
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:
Recently I received several questions about rotating log files for ProxySQL, so I decided to draft it up as a blog post. Let me go by this using an example.
In my testing lab, I have set up a fairly default ProxySQL (version 1.4.13) service. The default location for the proxysql.log is in /var/lib/proxysql.
[root@proxysql ~]# ls -hal /var/lib/proxysql/proxysql.log* -rw-------. 1 root root 4.9K Jan 30 18:47 /var/lib/proxysql/proxysql.log
I created a pretty basic default logrotate configuration to ensure my logfile rotates on a daily basis and five rotations are kept before expiring.
[root@proxysql ~]# cat /etc/logrotate.d/proxysql
/var/lib/proxysql/proxysql.log {
missingok
daily
notifempty
compress
create 0600 root root
rotate 5
}
First attempt
Let’s check whether this is actually the correct file that is used that we will be rotating. As it turned out it is!
[root@proxysql ~]# lsof | …[Read more]