Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.

It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …

Here at Pythian we get a lot of exposure to new technologies and implementation strategies via the work we do internally and for our clients. The most noteworthy technology stack that I’ve seen get a lot of traction in the MySQL community recently is the high availability stack including Orchestrator, Consul and ProxySQL. 

I won’t dive too deeply into the details of this implementation as there are several blog posts that our team has submitted on this topic, but the key thing I want you to keep in mind for this particular topic is the usage of Consul as a “source of truth” for the state of your MySQL replication clusters. If Orchestrator or its adjacent scripts are running as expected, Consul should always have the latest information pertaining to the state of your cluster. This is incredibly valuable. In fact, …

Here at Pythian we like to pay attention to what brings people to our site, and one of the main queries we’ve been seeing is “How can I allow a remote MySQL database connection?” Since our mission is to help you love your data, we asked Matthias Crauwels, Lead Database Consultant on one of Pythian’s MySQL teams to address this topic. Over to Matthias!

I’m hoping this may be the start of a series of posts where I can share some common MySQL knowledge in a (hopefully) clear and understandable manner. So let’s get started.

Technical requirements

Historically MySQL allows a client to connect in two different ways. You can either use a local socket solution (on a Linux/Unix system this is called a Unix socket, whereas on Windows systems it’s a named pipe) or via a TCP connection to a listening port (by default 3306).

Since the introduction of MySQL 8.0, a third way is also available using a newer …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on May 23, 2018.

In recent weeks I’ve been focusing on Docker in order to get a much better understanding of the containerized world that is materializing in front of us. Containers aren’t just for stateless applications anymore and we’re seeing more cases where MySQL and other databases are being launched in a containerized fashion, so it’s important to know how to configure your MySQL container!

In docker hub, you will see an option for this by doing a volume mount from the Docker host to the container on /etc/mysql/conf.d. But the problem is that the container image you’re using may …

If you have a New Relic Infrastructure Pro license, and unmonitored MySQL servers, there’s now an easy solution at your fingertips. With the New Relic MySQL integration you can monitor and graph almost any detailed metric you could possibly want. New Relic recently unified its analytics tools with New Relic One, a dashboard that provides quick access to all the New Relic tools. With an Infrastructure Pro subscription, you get access to:

• New Relic Infrastructure: Flexible, dynamic monitoring of your entire infrastructure, from services running in the cloud or on dedicated hosts, to containers running in orchestrated environments.
• New Relic Alerts: A flexible, centralized notification system that unlocks the operational potential of New Relic. Alerts is a single tool to manage alert policies and alert conditions for all of your New Relic data.
…

After spending four years working with ProxySQL in production, I’ve learned a few interesting lessons about interpreting and processing query rules. I hope to save you some time (and avoid wrong turns) with this summary of ProxySQL query rules.

ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for traffic mirroring, among other use cases.

When the rule set is short and simple, you can easily understand what the outcome for a certain query would be. However, for complex sets or combined scenarios, you need a deeper understanding of the engine logic.

Note 1: Rules are processed in rule_id order

It might seem like a trivial start, but during testing or implementation, …

PMM (Percona Monitoring and Management) is a great community tool for monitoring your OSDB (MySQL, PostgreSQL, MongoDB) fleet. It’s feature rich, and it’s built and distributed as open-source based on several de-facto industry standard tools such as Grafana and Prometheus. In the second half of last year, major version 2 was released and whereas the major components remained mostly the same, some significant architectural changes were made, but this is out-of-scope for this post.

Upgrading to PMM2 is basically the same as starting from scratch with a fresh install; it requires re-adding all your servers, which can be a painful process if you have a large fleet of instances. For servers where you have OS level access, you can just install the PMM2 agent, but for RDS there is no access to the OS so another approach is required. In this blog, I will focus on getting a large number of RDS instances added.

Adding RDS instances …

So, I admit the title for this post is a bit ambiguous. Not only is it the second post I’ve written in a short period of time, but it’s also focused on ProxySQL 2.0. As promised in the previous post, I’ve upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are.

In the comments section of the previous post, you’ll find a comment by ProxySQL Founder and CEO, René Cannaò:

“Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password.

With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 …

Recently, we were affected by an ignored configuration option introduced in MySQL 5.6. This incident caused us to perform extended planning of downtime and the rebuilding of the slave. In this post, we’ll discuss our encounter with binlog_error_action and likely bad default.

The incident started with an alert from our monitoring platform that a check failed to verify a MySQL master’s binary log coordinates. My colleague working from the other side of the globe observed the following:

mysql> show master status;
Empty set (0.00 sec)

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

Interestingly, the MySQL slave was still reporting that everything was okay!

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: …

While testing in an orchestrator lab I saw that none of my Orchestrator on-raft nodes were coming online after a reboot.

This is the status report from SystemD.

$ sudo systemctl status orchestrator
* orchestrator.service - orchestrator: MySQL replication management and visualization
   Loaded: loaded (/etc/systemd/system/orchestrator.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2020-04-03 09:30:05 UTC; 30s ago
     Docs: https://github.com/github/orchestrator
 Main PID: 957 (code=exited, status=1/FAILURE)

Apr 03 09:30:05 orchestrator-1 systemd[1]: Started orchestrator: MySQL replication management and visualization.
Apr 03 09:30:05 orchestrator-1 orchestrator[957]: 2020-04-03 09:30:05 ERROR dial tcp 127.0.0.1:3306: connect: connection refused
Apr 03 09:30:05 orchestrator-1 orchestrator[957]: 2020-04-03 09:30:05 FATAL dial tcp 127.0.0.1:3306: connect: connection refused
Apr 03 09:30:05 orchestrator-1 systemd[1]: …