08 Oct

ProxySQL Configuration File & Startup Process Explained

When learning ProxySQL, we have seen some confusion around the configuration and especially around the usage of the configuration file. In this post, we will explain how ProxySQL treats its internal configuration and its configuration file.

The Proxysql.Cnf

For the initial startup, the configuration is loaded from the /etc/proxysql.cnf file. At this time, the configuration file will be parsed and the settings will be stored in the embedded SQLite database. From this point onwards, the settings will be accessed from the SQLite database.

However, there are four configuration settings which are always parsed from the config file, even when the SQLite database is present. This places a requirement for the configuration file to always be in the place, if missing, ProxySQL terminates.

datadir: The path of ProxySQL datadir which stores database file, logs.
restart_on_missing_heartbeats (new in 1.4.4): If MySQL threads miss restart_on_missing_heartbeats (number of) heartbeats, proxysql will raise a SIGABRT signal and restart. Default is 10.
execute_on_exit_failure (new in 1.4.4): If set, ProxySQL’s parent process will execute the defined script every time ProxySQL crashes. It is recommended to use this setting to generate an alert or log the event.
errorlog (new in 2.0.0): The file to be used as error-log. Defaults to ${datadir}/proxysql.log

09 Apr

mysql database backup shell script with status email

This post is for the backup script for MySQL database on Linux with mail. It’s a linux shell script for taking logical backup using mysqldump and sending status email.

The backup shell script works as follows:
– The script takes backup using mysqldump and compresses it.
– Upon success, it will attempt to ship the backup to specified offsite location.
– Upon detecting failure in any of the above step, it will send out failure email.
– Upon overall success, it will sendout success email with execution time and present backups list.

You might be looking for setting up physical backups for mysql using Holland backup framework.

Following is the code for MySQL backup shell script using mysqldump with status email.

07 Sep

MySQL 8 Resource Group – introduction and dynamic allocation

MySQL 8 is GA and it has a variety of new features. Recently, we happened to work with resource groups to restrict resource utilization for a MySQL thread.

That’s why I thought of writing this blog post: to give you a quick introduction to resource groups, an idea of how to dynamically allocate them and to discuss a related bug report.

Introduction To Resource Groups:

A resource group, a new feature in MySQL 8, is the entity that defines the allowed resources consumption for threads pertaining to that group. Group attributes enable control over resources and hence the database threads get restricted with those limits.

Resource groups introduce the following associated components:
1. SQL to manage the resource groups (create, alter, assign, drop).
2. Resource groups related privileges.
3. The information_schema.resource_groups table, which holds groups details.
4. A new column was added to the performance_schema.threads table to show us the assignments of threads to respective resource groups.

13 Aug

Galera cluster to AWS Aurora migration & HA_ERR_FOUND_DUPP_KEY

In this post we will see a case study of a Galera Cluster migration to AWS Aurora and quick solution to the replication issue.

A friend received an error in a Master-Master replication as follows:

Could not execute Write_rows event on table _database._table; Duplicate entry '65eJ8RmzASppBuQD2Iz73AAy8gPKIEmP-2018-08-03 08:30:03' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-changelog.000010, end_log_pos 1107814

We talked about it and I immediately started blabbering cons of writing on both masters, how to handle, roles of apps and so on. He intervened and revealed, it is Galera Cluster replicated to Aurora and he is not writing on Aurora.

He was actually migrating his Galera Cluster to Aurora and master-master was to support his rollback plans. Consider following diagram for simplicity of understanding:

galera to aws migration – sample architecture

29 Jun

Restore A Table / Database From Full Backup – Yet Another Way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows: