Author: The Pythian Group

Log Buffer #548: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers blog posts from Cloud, Oracle, and MySQL.
Cloud:
Google Stackdriver lets you track your cloud-powered applications with monitoring, logging and diagnostics. Using Stackdriver to monitor Google Cloud Platform (GCP) or Amazon Web Services (AWS) projects has many advantages—you can get detailed performance data and can set up tailored alerts.
This post is courtesy of Sam Dengler, AWS Solutions Architect. Message brokers can be used to solve a number of needs in enterprise architectures, including managing workload queues and broadcasting messages to a number of subscribers.
New Cloud Filestore service brings GCP users high-performance file storage.
One of the biggest trends in application development today is the use of APIs to power the backend technologies supporting a product.
It’s no secret that data is an essential part of running a business, no matter how large or small a business may be. Many companies host their business data using relational databases.
Oracle:
How can I print to PDF? How can I get a document/report with my data? How can I export my data from APEX to Excel?
Almost a year ago, Oracle released Oracle GoldenGate 12c (12.3.0.1.x). At that time, there were two architectures released: Microservices and Classic. Both architectures provided the same enterprise-level replication. The only difference was that one enabled a RESTful API interface with HTML5 page and the other was still command line driven.
Ubuntu 16.04: Installation of Chrome browser fails with libnss3 (>= 2:3.22) [2]

UTL_FILE_DIR and 18c
DevOps in OAC: Scripting Oracle Cloud Instance Management with PSM Cli
MySQL:
One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue.
MariaDB 10.3 is now generally available (10.3.7 was released GA on 2018-05-25). The article What’s New in MariaDB Server 10.3 by the MariaDB Corporation lists three key improvements in 10.3: temporal data processing, Oracle compatibility features, and purpose-built storage engines.
MySQL 8.0 InnoDB Cluster on ARM64 with Oracle Linux and the Raspberry Pi 3B.
Detecting Incompatible Use of Spatial Functions before Upgrading to MySQL 8.0
JFG Posted on the Percona Community Blog – A Nice Feature in MariaDB 10.3: no InnoDB Buffer Pool in Core Dumps

MySQL Swapping With Fsync

One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue documented by Jeremy Cole back in 2010. As such, it’s usually pretty easy to resolve these issues and keep MySQL out of swap space. Recently, however, we had tried all of the usual tricks but had an issue where MySQL was still swapping.
The server with the issue was a VM running with a single CPU socket (multiple cores), so we knew it wasn’t NUMA. Swappiness and MySQL were both configured correctly and when you checked the output of free -m it showed 4735M of memory available.
[sylvester@host~]$ free -m
total used free shared buff/cache available
Mem: 16046 10861 242 16 4941 4735
Swap: 15255 821 14434
The point that needs a bit more attention here is the amount of memory being used by the OS cache. As you can see, there is a total of 16046M of physical memory available to the host, with only 10861M in use and the majority of what’s left over being used by the OS cache. This typically isn’t a problem. When requests for more memory come in from threads running in the OS, it should evict pages from the OS cache in order to make memory available to the requesting process. In this case, this did not occur. Instead, we observed that it held onto that cache memory and forced MySQL to turn to swap. But why?
As it turns out, the system in question had recently been converted from MYISAM to InnoDB and hadn’t had any server configuration set to accommodate for this. As such it was still configured for innodb_flush_method at the default value, which in 5.7 is still fsync. Both Ivan Groenwold and I have both written blog posts in regards to flush methods, and it’s been generally accepted that O_DIRECT is a much better way to go in most use cases on Linux, including this one, so we wanted to get the system in question more aligned with best practices before investigating further. As it turns out, we didn’t have to look any further than this, as switching the system over to innodb_flush_method = O_DIRECT resolved the issue. It appears that fsync causes the kernel to want to hang onto its data pages, so when innodb attempted to expand its required amount of memory, it was unable to do so without accessing swap, even with swappiness set to 0 to test.
Ever since we did the change to O_DIRECT, the OS cache usage has dropped and there have been no problems with OS cache page eviction.
CONCLUSION
MySQL swapping can really ruin your day and it’s something you want to avoid if at all possible. We still run into issues with swapping every now and then and want to continue to provide the community with our findings as they become available. So if you have a server that is swapping, and the OS cache isn’t making room for MySQL, and if you’re still using fsync for InnoDB flush, consider switching to O_DIRECT.

Encrypting an existing MySQL/MariaDB RDS Instance

Often it is necessary to convert an unencrypted RDS instance into an encrypted one. And it is usually expected that this process is done with minimum or no downtime. Unfortunately, one can only enable encryption when the instance is created. However, there is still hope, as there are a couple of workarounds to encrypt your existing data.
In this article, I will discuss two different solutions to achieve this result.
Solution 1: Create a snapshot and copy the snapshot to a new encrypted snapshot:

Create a manual snapshot of the unencrypted RDS instance

Go to Snapshots from the left panel and choose the snapshot just created

From the Actions, choose Copy snapshot option and enable encryption

Select the new encrypted snapshot

Go to Actions and select Restore snapshot

Of course, we need to stop writes on the instance while the above steps are executed. However, since we are using RDS snapshots to create the new instance, this can still be a feasible option for a production instance.
To help you decide on the best solution for you, I am sharing the statistics of a test case I performed.
Case: On a t2.medium RDS instance of 100 GB allocated storage, the used data size is of 96GB, running in single AZ deployment.
Here is the time consumed at different stages of the complete process:
Creating a snapshot: 47 mins 16 secs
Copying snapshot: 14 mins 15 secs
Restoring snapshot: 2 mins 38 secs
The total time consumed, which can be considered as the downtime in this process, was approximately 64 mins in my test case.
Please note that this time will always vary and it will depend on many factors like the storage size, workload of your instance, the number of transactions going on your instance at that time, instance class, etc.
Also, note that new volumes created from existing EBS snapshots load lazily in the background. This means that after a volume is created from a snapshot, there is no need to wait for all the data to transfer from Amazon S3 to your EBS volume before your attached instance can start accessing the volume and all its data. This is the reason why restore operation takes such less amount of time.
If your instance accesses data that hasn’t yet been loaded, the volume immediately downloads the requested data from Amazon S3 and continues loading the rest of the data in the background. Storage blocks on volumes that were restored from snapshots must be initialized, i.e pulled down from Amazon S3 and written to the volume, before you can access the block. This preliminary action takes time and can cause a significant increase in the latency of an I/O operation the first time each block is accessed. Performance is restored after the data is accessed once.
If looking for a reduced downtime option, keep reading.
Solution 2:  Set up external replication from unencrypted to encrypted RDS instance:
The below-mentioned steps are performed on an unencrypted RDS MySQL 5.7.17 to convert it into an encrypted one. It’s recommended that before implementing this procedure in a production environment, you test it in your development instance and create your action plan accordingly.
Here is the overview of the steps:

Extend the Binary Log Retention Period on the unencrypted RDS instance
Create a Read Replica from the unencrypted RDS instance
Stop Replication on the Read Replica and note down the Relay_Master_Log_File & Exec_Master_Log_Pos from SHOW SLAVE STATUS
Create a manual snapshot from that Read Replica (This snapshot will be unencrypted)
Copy that snapshot and enable encryption
Restore that snapshot (this will create an encrypted RDS instance)
Start an external replication from the binary log file and position mentioned in Step-3

Below are the details of each step:
Step-1: Extend the binary log retention period on the unencrypted RDS instance
mysql> CALL mysql.rds_set_configuration(‘binlog retention hours’, 144);
RDS normally purges a binary log as soon as possible, but the binary log might still be required for the external replication. Please specify the number of hours to retain binary log files as per your requirement.
Created replication user on the unencrypted RDS instance.
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to ‘REPL_USER’@’%’ IDENTIFIED BY ‘REPL_PASSWORD’;
NOTE: REPLICATION SLAVE privilege is restricted in the RDS MySQL 5.5
Step-2: Create a Read Replica of the unencrypted RDS instance
Step-3: Once the Read Replica RDS instance becomes available, stop the replication using below command:
mysql> CALL mysql.rds_stop_replication;
After stopping the replication, note the Relay_Master_Log_File & Exec_Master_Log_Pos from the output of SHOW SLAVE STATUS\G
Step-4: Create a manual snapshot from that Read Replica
Select the Read Replica RDS instance and from the Instance Actions take a snapshot (this snapshot will be the unencrypted snapshot).
Step-5: Copy that snapshot and enable the encryption
Select the created snapshot and from the snapshot actions menu, copy the snapshot. Enable encryption and provide a name for the snapshot.
Step-6: Restore that snapshot to the RDS instance
Choose this encrypted snapshot and from snapshot actions select Restore Snapshot. This will create the new encrypted RDS instance from the snapshot.
Step-7: Set up external replication between unencrypted RDS instance and encrypted RDS instance
Once the encrypted RDS instance becomes available, set up its external replication with the unencrypted RDS instance. Start the external replication from the binary log file and position noted in Step 3.
mysql> CALL mysql.rds_set_external_master (‘RDS_ENDPOINT’, 3306, ‘repl’, ‘repl’, ‘BINARY_LOG_FILE’, ‘BINARY_LOG_POS’, 0);
Please replace the RDS_ENDPOINT with the endpoint of unencrypted RDS instance. Also replace the BINARY_LOG_FILE and BINARY_LOG_POS with the details noted down in Step 3.
Start the replication using the below command:
mysql> CALL mysql.rds_start_replication;
A few things to make sure when setting up the external replication between RDS instances:

The binary log retention period is extended on the master RDS instance, as in RDS, binary logs will be flushed as soon as the replica executes events.
On the master RDS instance, a replication user is created with the required privileges.
The access of the replica RDS instance is allowed into the security group of the master RDS instance.

Please monitor your slave status and once the slave gets in sync with the master, your instance is ready to be used. You can switch the traffic to the encrypted RDS instance. The cutover time will be the downtime in this case.
Conclusion:
While encrypting an existing RDS instance, if you are using the first solution, a read-replica creation is not required, but on the flipside, the downtime required will be considerably more. So if downtime is a considerable factor for you, then evaluate the feasibility of creating a read-replica at your end and go for the second solution, as the cutover time of moving the pointer from unencrypted to encrypted instance would be the only downtime in this solution.
I hope this helps!

Interesting happenstance when installing Ansible dependencies in a MySQL Docker container

I’ve been posting quite a bit about Docker as I’ve been working with it a lot as of late. I thought I would share something interesting I discovered a couple weeks ago while working on setting up a Docker container-based lab environment for training purposes here at Pythian, specifically when attempting to install the MySQL-python package inside the container.
I know what you’re thinking: why is he trying to install a package in a Docker container? Doesn’t that go against the “Docker run and forget about it” philosophy? Sure, but in this case, I’m looking to add orchestration via ansible, which I don’t think is completely out of the question in order to coordinate your Docker containers for something like replication. This requires using the ansible mysql_replication module, which clearly states that MySQL-python is required. I digress.
Back to the story. I run the mysql/mysql-server:5.7 docker image from docker hub, which is from the Oracle MySQL team and based on Oracle Linux. Then I attempted to install MySQL-python. Here was my output:
[vagrant@control nopkrbr]$ docker exec -e COLUMNS=”`tput cols`” -e LINES=”`tput lines`” -ti mysql1 /bin/bash
bash-4.2# yum install MySQL-python
Loaded plugins: ovl
ol7_UEKR4 | 1.2 kB 00:00
ol7_latest | 1.4 kB 00:00
(1/5): ol7_UEKR4/x86_64/updateinfo | 194 kB 00:00
(2/5): ol7_latest/x86_64/group | 659 kB 00:00
(3/5): ol7_latest/x86_64/updateinfo | 1.8 MB 00:00
(4/5): ol7_latest/x86_64/primary | 18 MB 00:03
(5/5): ol7_UEKR4/x86_64/primary | 38 MB 00:06
ol7_UEKR4 704/704
ol7_latest 26654/26654
Resolving Dependencies
–> Running transaction check
—> Package MySQL-python.x86_64 0:1.2.5-1.el7 will be installed
–> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: MySQL-python-1.2.5-1.el7.x86_64
–> Processing Dependency: libmysqlclient.so.18()(64bit) for package: MySQL-python-1.2.5-1.el7.x86_64
–> Running transaction check
—> Package mariadb-libs.x86_64 1:5.5.56-2.el7 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
MySQL-python x86_64 1.2.5-1.el7 ol7_latest 89 k
Installing for dependencies:
mariadb-libs x86_64 1:5.5.56-2.el7 ol7_latest 757 k

Transaction Summary
================================================================================
Install 1 Package (+1 Dependent package)

Total download size: 846 k
Installed size: 4.7 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): MySQL-python-1.2.5-1.el7.x86_64.rpm | 89 kB 00:00
(2/2): mariadb-libs-5.5.56-2.el7.x86_64.rpm | 757 kB 00:00
——————————————————————————–
Total 1.4 MB/s | 846 kB 00:00
Running transaction check
Running transaction test

Transaction check error:
file /etc/my.cnf from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
file /usr/share/mysql/charsets/Index.xml from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
file /usr/share/mysql/charsets/armscii8.xml from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
file /usr/share/mysql/charsets/ascii.xml from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
file /usr/share/mysql/charsets/cp1250.xml from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
file /usr/share/mysql/charsets/cp1256.xml from install of mariadb-libs-1:5.5.56-2.el7.x86_64 conflicts with file from package mysql-community-server-minimal-5.7.22-1.el7.x86_64
And so on.
When I saw this I couldn’t help but chuckle at the fact that the dependency to have lib (or libs-compat) on the system in order to install MySQL-python was resolved by using the default repo… in the MySQL community-issued Docker image… using Oracle Linux… which uses MariaDB.
This really wasn’t a big deal in this case. I was able to get around the issue by adding a few extra tasks to my playbook, as listed below.

– name: “Install mysql yum repository”
yum:
name: https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
state: present

– name: “Enable all versions in MySQL yum repository”
replace:
path: /etc/yum.repos.d/mysql-community.repo
regexp: ‘^enabled=0$’
replace: ‘enabled=1’

– name: “Install mysql compatible libraries. Required to install Python MySQL package”
yum:
disablerepo: “*”
enablerepo: “mysql{{ mysql_version_no_periods }}-community”
name: mysql-community-libs-compat
state: present

– name: “Install MySQL Python Module”
yum:
name: MySQL-python
state: present
What’s happening here is that I’m installing the mysql yum repository in the first task so I can get packages from the Oracle MySQL project instead of from the default repo which uses MariaDB. This creates the /etc/yum.repos.d/mysql-community.repo which allows you to enable and disable MySQL repositories based on major version.
The second task marks all repositories in the /etc/yum.repos.d/mysql-community.repo file as enabled, so any of them can be used.
The third task installs the latest minor version of mysql-community-libs-compat based on the major version of MySQL that I have running in my container. There is a reference here to a variable ‘mysql_version_no_periods’ which is populated in an earlier playbook with the major version of MySQL that is running in the container, but with the decimals removed. So, in this case, that value of the variable is ‘57’. This task is using this variable to disable all repositories in yum with the exception of the repository for the specific major version that I’m using, thus ensuring that when this task runs, I’ll always get the latest minor version of the mysql-community-libs-compat package for the major version of MySQL that’s running in my container.
Finally, now that my dependency is installed, the fourth and final task installs MySQL-python so I can use ansible to work with the MySQL instance running in my Docker container.
CONCLUSION
Recently, MariaDB has become a default package when attempting to use package managers for MySQL installation on various Linux distributions, so it’s easy to see how something like this could have slipped through the cracks. When you take everything into consideration about the Docker image that has been made available by the Oracle MySQL team, this is really only a very small setback for an otherwise great resource. You could even argue that this really isn’t an issue considering the Docker philosophy. However, I do believe that process orchestration via automation tools like Chef, Puppet, and Ansible aren’t going to be out of the question for Docker deployments. So I think it would be worth it to ensure that dependencies like this can be more easily resolved by making sure the repositories in use are compatible with the MySQL fork that’s installed on the image.

Getting started with Orchestrator

Orchestrator is a MySQL high availability and replication management tool. In this blog post, we will cover the first steps for getting started with it on an existing topology.
The code examples assume you are running Centos 7, but the general steps should be similar if you are running other operating system versions/flavors.
Prep work
1. Create a MySQL user on each of your database servers.
Orchestrator will connect with this user to discover the topology and to perform any changes you tell it to make.
CREATE USER ‘orchestrator’@’%’ IDENTIFIED BY ‘****’;
GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO ‘orchestrator’@’%’;
GRANT SELECT ON mysql.slave_master_info TO ‘orchestrator’@’%’;
GRANT SELECT ON meta.* TO ‘orchestrator’@’%’;

Note: Orchestrator reads replication credentials stored in mysql.slave_master_info table, which implies you need to set up your servers with master_info_repository = ‘TABLE’ option if you want Orchestrator to be able to make topology changes on your behalf.
2. (Optional) Create a table to store the cluster name on each of your database servers.
The idea is that if you set this up on each of your database servers, Orchestrator will pick up the cluster name automatically. While you could skip this step and just rename the clusters via the GUI later, it is a nice to have.
CREATE DATABASE meta;

CREATE TABLE meta.cluster (
anchor TINYINT NOT NULL,
cluster_name VARCHAR(128) CHARSET ascii NOT NULL DEFAULT ”,
cluster_domain VARCHAR(128) CHARSET ascii NOT NULL DEFAULT ”,
PRIMARY KEY (anchor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO meta.cluster (anchor, cluster_name, cluster_domain) VALUES (1, ‘testcluster’, ‘example.com’);
Installation steps
1. Get the prerequisites.
Orchestrator client requires jq package, which is available from epel repo, so let’s install that first:
sudo yum install epel-release
sudo yum install jq

2. Install Orchestrator.
Orchestrator is readily available as .deb or .rpm package so let’s use that:
sudo yum install https://github.com/github/orchestrator/releases/download/v3.0.11/orchestrator-3.0.11-1.x86_64.rpm

3. Prepare the configuration file.
Let’s start by copying the sample config file:
cp /usr/local/orchestrator/orchestrator-sample.conf.json /etc/orchestrator.conf.json

There are a lot of settings we can tweak, but to get started, we need to config the following values:

“MySQLTopologyUser”
“MySQLTopologyPassword”

These are the ones we defined above.

“DetectClusterAliasQuery”

We have to define a query that will let Orchestrator figure out the cluster name based on the table we created in the previous step.

“BackendDB”
“SQLite3DataFile”

You can use different backends for Orchestrator metadata. For simplicity, I suggest using SQLite which requires only the two lines above.
Here’s a complete configuration file:
{
“Debug”: true,
“EnableSyslog”: false,
“ListenAddress”: “:3000”,
“MySQLTopologyUser”: “orchestrator”,
“MySQLTopologyPassword”: “****”,
“MySQLTopologyCredentialsConfigFile”: “”,
“MySQLTopologySSLPrivateKeyFile”: “”,
“MySQLTopologySSLCertFile”: “”,
“MySQLTopologySSLCAFile”: “”,
“MySQLTopologySSLSkipVerify”: true,
“MySQLTopologyUseMutualTLS”: false,
“MySQLOrchestratorHost”: “127.0.0.1”,
“MySQLOrchestratorPort”: 3306,
“MySQLOrchestratorDatabase”: “orchestrator”,
“MySQLOrchestratorUser”: “orc_server_user”,
“MySQLOrchestratorPassword”: “orc_server_password”,
“MySQLOrchestratorCredentialsConfigFile”: “”,
“MySQLOrchestratorSSLPrivateKeyFile”: “”,
“MySQLOrchestratorSSLCertFile”: “”,
“MySQLOrchestratorSSLCAFile”: “”,
“MySQLOrchestratorSSLSkipVerify”: true,
“MySQLOrchestratorUseMutualTLS”: false,
“MySQLConnectTimeoutSeconds”: 1,
“DefaultInstancePort”: 3306,
“DiscoverByShowSlaveHosts”: true,
“InstancePollSeconds”: 5,
“UnseenInstanceForgetHours”: 240,
“SnapshotTopologiesIntervalHours”: 0,
“InstanceBulkOperationsWaitTimeoutSeconds”: 10,
“HostnameResolveMethod”: “default”,
“MySQLHostnameResolveMethod”: “@@hostname”,
“SkipBinlogServerUnresolveCheck”: true,
“ExpiryHostnameResolvesMinutes”: 60,
“RejectHostnameResolvePattern”: “”,
“ReasonableReplicationLagSeconds”: 10,
“ProblemIgnoreHostnameFilters”: [],
“VerifyReplicationFilters”: false,
“ReasonableMaintenanceReplicationLagSeconds”: 20,
“CandidateInstanceExpireMinutes”: 60,
“AuditLogFile”: “”,
“AuditToSyslog”: false,
“RemoveTextFromHostnameDisplay”: “.mydomain.com:3306”,
“ReadOnly”: false,
“AuthenticationMethod”: “”,
“HTTPAuthUser”: “”,
“HTTPAuthPassword”: “”,
“AuthUserHeader”: “”,
“PowerAuthUsers”: [
“*”
],
“ClusterNameToAlias”: {
“127.0.0.1”: “test suite”
},
“SlaveLagQuery”: “”,
“DetectClusterAliasQuery”: “SELECT ifnull(max(cluster_name), ””) as cluster_alias from meta.cluster where anchor=1;”,
“DetectClusterDomainQuery”: “”,
“DetectInstanceAliasQuery”: “”,
“DetectPromotionRuleQuery”: “”,
“DataCenterPattern”: “[.]([^.]+)[.][^.]+[.]mydomain[.]com”,
“PhysicalEnvironmentPattern”: “[.]([^.]+[.][^.]+)[.]mydomain[.]com”,
“PromotionIgnoreHostnameFilters”: [],
“DetectSemiSyncEnforcedQuery”: “”,
“ServeAgentsHttp”: false,
“AgentsServerPort”: “:3001”,
“AgentsUseSSL”: false,
“AgentsUseMutualTLS”: false,
“AgentSSLSkipVerify”: false,
“AgentSSLPrivateKeyFile”: “”,
“AgentSSLCertFile”: “”,
“AgentSSLCAFile”: “”,
“AgentSSLValidOUs”: [],
“UseSSL”: false,
“UseMutualTLS”: false,
“SSLSkipVerify”: false,
“SSLPrivateKeyFile”: “”,
“SSLCertFile”: “”,
“SSLCAFile”: “”,
“SSLValidOUs”: [],
“URLPrefix”: “”,
“StatusEndpoint”: “/api/status”,
“StatusSimpleHealth”: true,
“StatusOUVerify”: false,
“AgentPollMinutes”: 60,
“UnseenAgentForgetHours”: 6,
“StaleSeedFailMinutes”: 60,
“SeedAcceptableBytesDiff”: 8192,
“PseudoGTIDPattern”: “”,
“PseudoGTIDPatternIsFixedSubstring”: false,
“PseudoGTIDMonotonicHint”: “asc:”,
“DetectPseudoGTIDQuery”: “”,
“BinlogEventsChunkSize”: 10000,
“SkipBinlogEventsContaining”: [],
“ReduceReplicationAnalysisCount”: true,
“FailureDetectionPeriodBlockMinutes”: 60,
“RecoveryPollSeconds”: 10,
“RecoveryPeriodBlockSeconds”: 3600,
“RecoveryIgnoreHostnameFilters”: [],
“RecoverMasterClusterFilters”: [
“.*”
],
“RecoverIntermediateMasterClusterFilters”: [
“_intermediate_master_pattern_”
],
“OnFailureDetectionProcesses”: [
“echo ‘Detected {failureType} on {failureCluster}. Affected replicas: {countSlaves}’ >> /tmp/recovery.log”
],
“PreFailoverProcesses”: [
“echo ‘Will recover from {failureType} on {failureCluster}’ >> /tmp/recovery.log”
],
“PostFailoverProcesses”: [
“echo ‘(for all types) Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}’ >> /tmp/recovery.log”
],
“PostUnsuccessfulFailoverProcesses”: [],
“PostMasterFailoverProcesses”: [
“echo ‘Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Promoted: {successorHost}:{successorPort}’ >> /tmp/recovery.log”
],
“PostIntermediateMasterFailoverProcesses”: [
“echo ‘Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}’ >> /tmp/recovery.log”
],
“CoMasterRecoveryMustPromoteOtherCoMaster”: true,
“DetachLostSlavesAfterMasterFailover”: true,
“ApplyMySQLPromotionAfterMasterFailover”: true,
“MasterFailoverDetachSlaveMasterHost”: false,
“MasterFailoverLostInstancesDowntimeMinutes”: 0,
“PostponeSlaveRecoveryOnLagMinutes”: 0,
“OSCIgnoreHostnameFilters”: [],
“GraphiteAddr”: “”,
“GraphitePath”: “”,
“GraphiteConvertHostnameDotsToUnderscores”: true,
“BackendDB”: “sqlite”,
“SQLite3DataFile”: “/usr/local/orchestrator/orchestrator.db”
}

4. Prepare systemd script.
At the time of this writing, a systemd script to manage start/stop of the service is not included. I set that up as follows:
vi /etc/systemd/system/orchestrator.service

[Unit]
Description=”orchestrator: MySQL replication management and visualization”
After=syslog.target network.target

[Service]
Type=simple
ExecStart=/usr/local/orchestrator/orchestrator –verbose http
PIDFile=/var/run/orchestrator.pid
WorkingDirectory=/usr/local/orchestrator

[Install]
WantedBy=multi-user.target

5. Reload the service so it reads the updated configuration file /etc/orchestrator.conf.json.
service orchestrator reload
That’s it for the installation steps.
Using the GUI
The first step is pointing Orchestrator to a starting host. It will then automatically discover any other hosts that are members of the same cluster by crawling up and down the replication chain.
1. Using a browser, open the GUI (by default port 3000) on the host where you installed Orchestrator:http://192.168.56.100:3000/

2. Click ‘Discover‘ on the top bar, enter the ip address and port of any host you want, and hit the Submit button.

3. Click on Clusters -> Dashboard. After a few seconds, you should see the cluster being shown (refresh the page if needed).
4. Now click on the cluster name and you should see the details about all current members of the replication topology.
The cluster should have automatically been named according to what is specified in meta.cluster table.
You can also use the GUI to drag & drop to perform topology changes, provided you are using GTID or pseudo-GTID (which is a topic for a different post).
Closing thoughts
Orchestrator is rapidly becoming the go-to topology management tool in the MySQL world. I encourage you to start playing with it as soon as possible.
If you are interested in learning more, I suggest you also check out the following posts in the Pythian blog.
Happy Orchestrating!
MySQL High availability with HAProxy, Consul and Orchestrator

Graceful master switchover with ProxySQL and Orchestrator

 

Pythian at DataOps Barcelona

DataOps Barcelona is an event organized by Binlogic and will be taking place June 21-22, 2018 in Barcelona, Spain. Pythian is proud to be supporting this inaugural open source-focused conference in Europe!
DataOps is of course short for “Database + Operations” and contains talks about the various open source database ecosystems and how they are operationalized with regards to cloud solutions, security, automation, high availability and scaling.
Pythian has a long history of helping companies leverage open source technologies and cloud infrastructure to meet their business requirements. We excel at managing traditional open source relational database (MySQL) and relatively new NoSQL technologies (MongoDB, Cassandra).
Our world-class cloud architects are familiar with all of the major cloud providers. If you’re looking to migrate to the cloud, we can help you decide which vendor or vendors best suits your needs, then help you plan and implement your migration. And if you’re already there, we can help you optimize your cloud environment to meet your business needs.
DataOps is an opportunity for us to share our expertise with the community in Europe. Be sure to drop by the following sessions:
June 21 (Day 1)

Implementing MySQL Database-as-a-Service using open source tools – 11:50-12:30 – Matthias Crauwels (Pythian)

Insights on sysbech and custom benchmarks. – 15:30-16:20 – Martín Arrieta (Pythian), Alexey Kopytov

June 22 (Day 2)

Cassandra B side, the errors we made and what we learned – 9:40-10:20 – Carlos Rolo (Pythian), Pedrdo Vidigal (Pythian)

How to Scale MongoDB – 11:00 – 11:40 – Igor Donchovski (Pythian)

The Battle of DBAAS – RDS vs Cloud SQL vs SQL Azure – 16:15-16:55 – Sandeep Arora (Pythian)

Multi-Cloud Cassandra with Terraform and Ansible – 17:00-17:40 – Carlos Rolo (Pythian), Pedrdo Vidigal (Pythian)

We look forward to seeing you at DataOps Barcelona. Please, find us at the conference to pick our brains!

Log Buffer #547: A Carnival of the Vanities for DBAs

This Log Buffer edition covers Oracle, SQL Server and MySQL.
Cloud:
What DBAs need to know about Cloud Spanner, Part 1: Keys and indexes
Introducing sole-tenant nodes for Google Compute Engine — when sharing isn’t an option
A serverless solution for invoking AWS Lambda at a sub-minute frequency
Amazon Aurora MySQL DBA Handbook – connection management
Why you should bet on Azure for your infrastructure needs, today and in the future
Oracle:
The ability to make grants on objects in the database such as tables, views, procedures or others such as SELECT, DELETE, EXECUTE and more is the cornerstone of giving other users or schemas granular access to objects.
While clients tend to tell developers to skip wireframing and prototyping, seasoned veterans tell newbies that they can skip wireframing and proceed with prototyping.
Last week in Stream Processing & Analytics – 6.6.2018
Facebook, Google and Custom Authentication in the same Oracle APEX 18.1 app
Quick install of Prometheus, node_exporter and Grafana
MySQL:
Benchmarking the Read Backup feature in the NDB storage engine
MySQL Cluster 7.6 and the thread pool
MySQL on Docker: Running a MariaDB Galera Cluster without Container Orchestration Tools – Part 1
MySQL Streaming Xtrabackup to Slave Recovery
A friendly comparison of InnoDB and MyRocks Performance
PostgreSQL:
By Jeremy Schneider
Hello from California!
Part of my team is here in Palo Alto and I’m visiting for a few days this week. You know, for all the remote work I’ve done over the years, I still really value this in-person, face-to-face time. These little trips from Seattle to other locations where my teammates physically sit are important to me.
This is also part of the reason I enjoy user groups and conferences so much. They’re opportunities to meet with other PostgreSQL users in real life. In fact – at this very moment – one of the most important PostgreSQL conferences in the world is happening: PgCon! Having attended a few other conferences over the past year, I’m holding down the fort in the office this week in order to send a bunch of other teammates… but you can be sure I’m keeping an eye on Twitter. 🙂
In the meantime, let’s get busy with the latest updates from the postgresql virtual world. First of all, I think the biggest headline is that (just in time for pgcon) we have the first official beta version of PostgreSQL 11! The release announcement headlines with major partitioning enhancements, more parallelism, a feature to speed up SQL execution by compiling certain operations on-demand into native machine code (JIT/Just-In-Time compilation), and numerous SQL enhancements. You can also read the first draft of the release notes. This is the time to start testing and give feedback to the development community!
Closely related to this, there’s one other really big headline that I’m excited about: the new AWS RDS Preview Environment. You can now try out the new pg11 features ahead of time with a single click! In part, because the development community is so awesome, the first database available in the RDS Preview Environment is PostgreSQL. And the official PostgreSQL 11 beta release is _already_ available on RDS!! Personally, I’m hoping that this benefits the community by getting more people to test and give feedback on new features being built for PostgreSQL 11. I hope it will make a great database even better.
Moving on from headlines, let’s get to the real stuff – the meaty technical articles. 🙂
First up, who likes testing and benchmarking? One of my favorite activities, truth be told! So I can’t quite convey just how excited I am about the preview release of Kevin Closson’s pgio testing kit. For those unfamiliar, Kevin has spent years refining his approach for testing storage through database I/O paths. Much work was done in the past with Oracle databases, and he calls his method SLOB. I’m excited to start using this kit for exploring the limits of storage through PostgreSQL I/O paths too.
Right after Kevin published that post, Franck Pachot followed up with a short article using pgio to look at the impact of the ext4 “sync” filesystem option (made relevant by the recently disclosed flaws in how PostgreSQL has been interacting with Linux’s implementation of fsync).
In addition to Kevin’s release of PGIO, I also saw three other generally fun technical articles. First, Kaarel Moppel from Cybertec published an article showing much lower-than-expected impact of pl/pgsql triggers on a simple pgbench execution. Admittedly, I want to poke around at this myself, having seen a few situations where the impact seemed higher. Great article – and it certainly got some circulation on Twitter.
Next, Sebastian Insausti has published an article explaining PostgreSQL streaming replication. What I appreciate the most about this article is how Sebastian walks through the history of how streaming replication was developed. That context is so important and helpful!
Finally, the requisite Vacuum post. 🙂 This month we’ve got a nice technical article from Sourabh Ghorpade on the Gojek engineering team. Great high-level introduction to vacuuming in general, and a good story about how their team narrowly averted an “xid wraparound” crisis.

MySQL Streaming Xtrabackup to Slave Recovery

Overview
There are times when I need to restore a slave from a backup of a master or another slave, but too many times I have taken the typical approach of taking the backup on the source server and then copying it to the target server.  This takes a great deal of time, especially as your database grows in size.
These steps are going to detail how to use Netcat (nc) and Percona Xtrabackup (innobackupexec) to stream the backup from the source server to the target server, saving a great deal of time by copying the data only once to the desired location.  While the data is streaming to the target server, it’s being compressed and then uncompressed on the fly, reducing the amount of traffic going across the network by around 85% to 90% (typical backup compression ratios of MySQL Innodb I have witnessed are in this range).  I will also provide a simple script to complete these steps to give you a start at creating your own customized solution.
Requirements
In order to accomplish this task, you need to keep the following items in mind:

Netcat (nc) – Application needed on both servers, used for data streaming.

Percona Xtrabackup – Application needed on both servers, used to perform the backup.

MySQL Access – MySQL access is required in order to do the backup on the master, and the slave configuration on the slave.

Pigz (optional) – This is only needed if you want to compress and uncompress it on the fly.  If you are going to use this, it’s needed on both servers.

Debian – All code and scripts were tested using Debian.  Commands may slightly change with different OS’s.

Steps
Here are the steps that are required to accomplish this task.  The source server is the server where the backup is coming from.  The target server is where the backup is going to.
Step 1. Stop MySQL on Target Server and Clear MySQL Data
On the server that needs to be restored, we will make sure that MySQL is stopped.  Then we will clear out the old data as this will all be replaced with the backup coming from the source server.  The example assumes your MySQL data directory is /var/lib/mysql.

service mysql stop
rm -rf /var/lib/mysql/*

Step 2. Start Listener on Target Server
Now that the target server has its MySQL data directory cleared of its old data, it is now ready to receive the new backup directly from the source server. The port 2112 can be changed, but the port needs to match on both source and target commands. Nc command and options may vary by OS.

nc -l -p 2112 | unpigz -c | xbstream -x -C /var/lib/mysql

No Compression would be

nc -l -p 2112 | xbstream -x -C /var/lib/mysql

Step 3. Start Backup on Source Server
The listener is now up on the target server, ready to accept the connection. We will now start the backup on the source server to stream to the target server. Update the parallel option to match the number of cpu cores on your server. Use the lower core count between the source and target server. The port 2112 can be changed, but the port needs to match on both source and target commands. Nc command and options may vary by OS.

innobackupex –stream=xbstream –parallel=4 /tmp | pigz -c –fast | nc -w 2 TARGET_SERVER 2112

No Compression would be

innobackupex –stream=xbstream –parallel=4 /tmp | nc -w 2 TARGET_SERVER 2112

Step 4. Prepare Backup on Target Server
Percona xtrabackup requires that you prepare the backup after it has been completed to apply any outstanding logs and get the database ready to be started. Use as much memory on your target server as you can without causing it to go OOM (Out of Memory). As an example, 75% of your total memory would be a good starting point if there is nothing else running on your server. On a server with 4G of RAM you could safely set user memory to 3G.

innobackupex –use-memory=3G –apply-log /var/lib/mysql

Step 5. Update Ownership and Start MySQL
Now that the apply logs step has completed on your backup, you should be able to update the ownership of the files to mysql:mysql, and then start the MySQL service.

chown -R mysql:mysql /var/lib/mysql
service mysql start

Step 6. Configure Replication
If the source server is a slave, you should be able to just start the new slave as the positioning will be already configured. If your source server is the master, then you will have to figure out if you are using GTID or legacy replication. If you are using GTIDs, you should be able start replication with gtid_purged being set and the master auto position parameter. If you are using legacy replication, you can find the master log and position in the xtrabackup_binlog_info file in your backup directory. In this scenario, the backup directory is the MySQL data directory (/var/lib/mysql/) on the target server.
GTID Replication

mysql
CHANGE MASTER TO MASTER_HOST=’<MASTER_SERVER>’,
MASTER_USER=’<REPL_USER>’,
MASTER_PASSWORD=’<REPL_PASS>’,
MASTER_AUTO_POSITION = 1;
START SLAVE;”

Legacy Replication

cat /var/lib/mysql/xtrabackup_binlog_info
mysql
CHANGE MASTER TO MASTER_HOST=’<MASTER_SERVER>’,
MASTER_USER=’<REPL_USER>’,
MASTER_PASSWORD=’<REPL_PASS>’,
MASTER_LOG_FILE=’<LOG_FROM_xtrabackup_binlog_info’,
MASTER_LOG_POS=<POSITION_FROM_xtrabackup_binlog_info>;
START SLAVE;”

Script
Below you will find the simple script that I came up with to get you started on automating this task to quickly rebuild a slave in your own environment.  The script was created with a lot of assumptions that you may not have in your environment.  Please make sure to update accordingly.

The script is running from a separate server that has access to both MySQL servers. May work by running on one of the MySQL servers but not tested to do so.
The account running the commands on the remote servers have SUDO access to run commands.
SSH key of the remote server is set up to allow ssh access to both MySQL servers allowing for ssh with no password prompt.
.my.cnf is configured in the home directory of the account being used to SSH on the MySQL servers allowing the script to run MySQL and Xtrabackup commands with no password prompt.
The following software is installed on the MySQL servers : netcat (nc), pigz, xtrabackup.
Firewall rules are open for the port being used by NETCAT streaming.
All my testing was on Debian servers. Found with other OS’s and Netcac versions there is a -d flag for running nc in the background. In Debian you have to use -p for netcat when telling it to listen on a port.

Script

#!/bin/bash

SOURCE_SERVER=$1
TARGET_SERVER=$2

# This is if you have /var/lib/mysql on the / (Root) drive. You can change this to ‘/’ to capture the size of the drive to get an idea of how long is left on your backup
MOUNT_CONTAINING_DATADIR=’/var’

#Should match the number of CPU’s on your server
BACKUP_THREADS=2

# Seconds to wait in the loop to check that the backup has completed
TIMER=5

# Amount of memory to use to apply the logs to the newly backed up server
MEMORY_GB_USED_APPLY_LOGS=’1G’

# Change this to a 1 if you want to configure the target a slave of the source server
CONFIGURE_TARGET_AS_SLAVE_OF_SOURCE=1

GTID_STATUS=”
SSH=’ssh -q -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null’
NETCAT_PORT=2112
REPL_USER=’repl’
REPL_PASS=’repl_PASSWORD’
MYSQL_DATADIR=’/var/lib/mysql’

echo “”
echo “Starting MySQL Streaming Slave Rebuild Script”
echo “”

if [ ! $1 ];then
echo “SOURCE Server not set. Please run like :”
echo “”
echo “$0 <source_server> <target_server>”
echo “”
exit 1
fi

# VALIDATE EXECUTION

echo “Are you sure you wish to perform this rebuild. THIS IS DESTRUCTIVE!!!”
echo ” SOURCE SERVER (Performing Backup) : $SOURCE_SERVER”
echo ” TARGET SERVER (Receiving Backup) : $TARGET_SERVER”
echo ” All files in $MYSQL_DATADIR on $TARGET_SERVER will be DELETED!!!”
echo -n “START NOW ? (y/n) : ”
read CONFIRM
if [ $CONFIRM == “y” -o $CONFIRM == “Y” ]; then
echo “”
echo “STARTING REBUILD!!!”
else
echo “Y or y was not chosen. Exiting.”
exit 1
fi

# PREPARE TARGET

echo ” Stopping MySQL”
$SSH $TARGET_SERVER “service mysql stop”
echo ” Clearing $MYSQL_DATADIR”
$SSH $TARGET_SERVER “rm -rf $MYSQL_DATADIR/*”

# PERFORM STREAMING BACKUP

echo ” Start Listener on Target server $TARGET_SERVER:$NETCAT_PORT to accept the backup and place it in $MYSQL_DATADIR”
$SSH $TARGET_SERVER “nc -l -p $NETCAT_PORT | unpigz -c | xbstream -x -C $MYSQL_DATADIR” > /dev/null 2>&1 &

echo ” Starting backup on source server $SOURCE_SERVER:$NETCAT_PORT to stream backup”
$SSH $SOURCE_SERVER “innobackupex –stream=xbstream –parallel=$BACKUP_THREADS /tmp | pigz -c –fast | nc -w 2 $TARGET_SERVER $NETCAT_PORT” > /tmp/backup_log 2>&1 &
sleep 4
echo ” Watching backup every $TIMER seconds to validate when the backup is complete”
LOOP=1
while [ 1 -eq $LOOP ];do
BACKUP_PROCESSES=`$SSH $SOURCE_SERVER “ps aux | grep -v grep | grep -w innobackupex | wc -l”`
if [ $BACKUP_PROCESSES -eq 0 ]; then
echo ” Backup has COMPLETED!!”
LOOP=2
else
echo ” Backup is Running!”
sleep $TIMER
fi
done

# PREPARE AND COMPLETE BACKUP ON TARGET

echo ” Applying logs to the Xtrabackup”
$SSH $TARGET_SERVER “innobackupex –use-memory=$MEMORY_GB_USED_APPLY_LOGS –apply-log $MYSQL_DATADIR” > /tmp/backup_log 2>&1 &
sleep 3
LOOP=1
while [ 1 -eq $LOOP ];do
APPLY_PROCESSES=`$SSH $TARGET_SERVER “ps aux | grep -v grep | grep innobackupex | wc -l”`
if [ $APPLY_PROCESSES -eq 0 ]; then
echo ” Apply logs has COMPLETED!!”
LOOP=2
else
echo ” Apply Logs Running!”
sleep $TIMER
fi
done

sleep 1

echo ” Updating ownership on the backup files so that MySQL owns them”
$SSH $TARGET_SERVER “chown -R mysql:mysql $MYSQL_DATADIR”

echo ” Starting MySQL”
$SSH $TARGET_SERVER “service mysql start”

if [ $CONFIGURE_TARGET_AS_SLAVE_OF_SOURCE -eq 1 ]; then
echo ” Configuring Replication”
GTID_STATUS=`$SSH $SOURCE_SERVER “mysql -BN -e \”SHOW VARIABLES LIKE ‘gtid_mode’\”” | grep -w ON | wc -l`
if [ $GTID_STATUS -gt 0 ]; then
echo “Found GTID ON. Using Master Auto Position. SLAVE STARTED”
GTID_POS=`$SSH $TARGET_SERVER “cat $MYSQL_DATADIR/xtrabackup_binlog_info” | awk ‘{print $3}’ | head -n 1 | sed ‘s/,//’`
$SSH $TARGET_SERVER “mysql -e \”SET GLOBAL gtid_purged=’$GTID_POS’;\””
$SSH $TARGET_SERVER “mysql -e \”CHANGE MASTER TO MASTER_HOST=’$SOURCE_SERVER’, MASTER_USER=’$REPL_USER’, MASTER_PASSWORD=’$REPL_PASS’, MASTER_AUTO_POSITION = 1; START SLAVE; \””
else
echo “Found GTID not ON. Grabbing positioning from the backup file and using that to configure replication”
MASTER_LOG=`$SSH $TARGET_SERVER “cat $MYSQL_DATADIR/xtrabackup_binlog_info” | awk ‘{print $1}’`
MASTER_POS=`$SSH $TARGET_SERVER “cat $MYSQL_DATADIR/xtrabackup_binlog_info” | awk ‘{print $2}’`
echo “Setting the slave to $MASTER_LOG and $MASTER_POS. SLAVE STARTED”
$SSH $TARGET_SERVER “mysql -e \”CHANGE MASTER TO MASTER_HOST=’$SOURCE_SERVER’, MASTER_USER=’$REPL_USER’, MASTER_PASSWORD=’$REPL_PASS’, MASTER_LOG_FILE=’$MASTER_LOG’, MASTER_LOG_POS=$MASTER_POS; START SLAVE;\””
fi
fi

Script Output

[root@bastion DB]# ./mysql_file_streaming_slave_rebuild.sh 10.10.10.198 10.10.10.197

Starting MySQL Streaming Slave Rebuild Script

Are you sure you wish to perform this rebuild. THIS IS DESTRUCTIVE!!!
SOURCE SERVER (Performing Backup) : 10.10.10.198
TARGET SERVER (Receiving Backup) : 10.10.10.197
All files in /var/lib/mysql on 10.10.10.197 will be DELETED!!!
START NOW ? (y/n) : y

STARTING REBUILD!!!
Stopping MySQL
Clearing /var/lib/mysql
Start Listener on Target server 10.10.10.197:2112 to accept the backup and place it in /var/lib/mysql
Starting backup on source server 10.10.10.198:2112 to stream backup
Watching backup every 5 seconds to validate when the backup is complete
Backup is Running!
Backup is Running!
Backup is Running!
Backup has COMPLETED!!
Applying logs to the Xtrabackup
Apply Logs Running!
Apply Logs Running!
Apply logs has COMPLETED!!
Updating ownership on the backup files so that MySQL owns them
Starting MySQL
Configuring Replication
Found GTID ON. Using Master Auto Position. SLAVE STARTED

 
Conclusion
I have found that this has greatly increased the timeframe in which it took me to recover a failed slave. By transferring the data only once from the source server to the target server, and with the data being compressed during the transfer, I feel this is one of the most efficient methods of recovering a failed slave, or building a new one.

ProxySQL behind a load balancer in Google Cloud

Introduction
In this article we will explore one approach for deploying ProxySQL behind a load balancer in Google Cloud.
While considering the deployment of ProxySQL, one has basically the following options:

Install ProxySQL on existing application server(s)
Provision dedicated ProxySQL server(s) between your application servers and the database layer.

Each approach has its pros and cons, but if there’s a significant number of application servers (more than a dozen or so) having a dedicated ProxySQL “layer” can be a more attractive option, specially if there is no service discovery mechanism in place (e.g. Consul).
Let’s consider a simple scenario, with a master and a small number of slaves in a single geographic region. Assuming that you are following the best practice, your database servers should be split into different availability zones. So for ProxySQL, it also makes sense to have at least 3 instances (again, on different availability zones).
Here’s how that would look:

ProxySQL behind a load balancer in Google Cloud

Getting started
Let’s start by creating some basic infrastructure for our POC from Google Cloud shell.
Network infrastructure
You can skip this part if you already have a network configuration in place.
1. Create a custom VPC network
gcloud compute networks create my-custom-network –subnet-mode custom
2. Create a new subnet in your custom VPC network
gcloud compute networks subnets create my-custom-subnet \
–network my-custom-network \
–range 10.240.0.0/16 \
–region us-central1
3. Configure a firewall rule to allow all traffic within the subnet
gcloud compute firewall-rules create allow-all-10-240-0-0-16 \
–network my-custom-network \
–allow tcp,udp,icmp \
–source-ranges 10.240.0.0/16
4. Create a firewall rule to allow ssh, mysql, icmp traffic from anywhere to the custom network (optional)
gcloud compute firewall-rules create allow-tcp22-tcp3306-icmp \
–network my-custom-network \
–allow tcp:22,tcp:3306,icmp
ProxySQL instances
Now let’s create some instances to install ProxySQL. I will skip the actual steps of installing and configuring ProxySQL for the sake of brevity. Check the official doc for more on this.
1. Create 3 ProxySQL instances on different zones
gcloud compute instances create tst-proxysql01 \
–image-family debian-9 \
–image-project debian-cloud \
–tags proxysql-lb \
–zone us-central1-a \
–subnet my-custom-subnet

gcloud compute instances create tst-proxysql02 \
–image-family debian-9 \
–image-project debian-cloud \
–tags proxysql-lb \
–zone us-central1-b \
–subnet my-custom-subnet

gcloud compute instances create tst–proxysql03 \
–image-family debian-9 \
–image-project debian-cloud \
–tags proxysql-lb \
–zone us-central1-c \
–subnet my-custom-subnet
Now we will create instance groups. One could set the group auto-scaling properties to better manage the instances but this is out of scope of this article.
2. Create 3 instance groups for ProxySQL instances on each zone
gcloud compute instance-groups unmanaged create us-proxysql-ig1 \
–zone us-central1-a

gcloud compute instance-groups unmanaged create us-proxysql-ig2 \
–zone us-central1-b

gcloud compute instance-groups unmanaged create us-proxysql-ig3 \
–zone us-central1-c
3. Add ProxySQL instances to the appropriate instance group
gcloud compute instance-groups unmanaged add-instances us-proxysql-ig1 \
–instances tst-proxysql01 \
–zone us-central1-a

gcloud compute instance-groups unmanaged add-instances us-proxysql-ig2 \
–instances tst-proxysql02 \
–zone us-central1-b

gcloud compute instance-groups unmanaged add-instances us-proxysql-ig3 \
–instances tst-proxysql03 \
–zone us-central1-c
ProxySQL behind a load balancer
Health checks
The first thing we need to configure is the health check(s). This is what will let the load balancer know which ProxySQL instances are “healthy”.
We could use a simple TCP check here, so when the TCP ACK is received the member is marked healthy. The problem is there have been (rare) cases of ProxySQL being unresponsive while the TCP ACK is still being returned by the operating system. So it is a better idea to check for an actual response string from ProxySQL.
I’ve noticed that ProxySQL returns a letter J on the first line of the response, so I’ve decided to use that in the response string to validate ProxySQL is alive. I’ve played a bit with more complex response strings but haven’t been able to make them work. If you come up with a better alternative, please let me know in the comments section!
I am using ProxySQL admin port for the health check, but any ProxySQL port will also work.
1. Configure the load balancer health checks for ProxySQL ports
gcloud compute health-checks create tcp my-proxysql-health-check \
–port 6032 \
–response=”J”
Backend service
The next step is creating the backed and adding the instance groups to it.
I am using a session affinity setting, so all connections from a single application server are routed to the same ProxySQL instance. Feel free to take that parameter out.
2. Create the backend service
gcloud compute backend-services create my-proxysql-lb \
–load-balancing-scheme internal \
–region us-central1 \
–health-checks my-proxysql-health-check \
–protocol tcp \
–session-affinity=”CLIENT_IP”
3. Add the instance groups to backend
gcloud compute backend-services add-backend my-proxysql-lb \
–instance-group us-proxysql-ig1 \
–instance-group-zone us-central1-a \
–region us-central1

gcloud compute backend-services add-backend my-proxysql-lb \
–instance-group us-proxysql-ig2 \
–instance-group-zone us-central1-b \
–region us-central1

gcloud compute backend-services add-backend my-proxysql-lb \
–instance-group us-proxysql-ig3 \
–instance-group-zone us-central1-c \
–region us-central1
Forwarding rules
Now we need to create the forwarding rule of the load balancer. Note that if you don’t specify an IP address via the –address parameter, one will be auto-generated for you.
4. Create the forwarding rule
gcloud compute forwarding-rules create my-proxysql-lb-forwarding-rule \
–load-balancing-scheme internal \
–ports=”3306″ \
–network default \
–region us-central1 \
–backend-service my-proxysql-lb \
–subnet my-custom-subnet

Created [https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/regions/us-central1/forwardingRules/my-proxysql-lb-forwarding-rule].
IPAddress: 10.240.0.163
IPProtocol: TCP
Firewall rules
We need some firewall rules so application servers are allowed to reach the ProxySQL servers. Note that we don’t need a specific rule for the load balancer IP address, the tag used for the backends is sufficient.
We also need a rule to allow health checks to happen. This requires whitelisting some Google-owned internal IP ranges.
1. Add a firewall rule to allow traffic to the load balancer, and from the load balancer to the backends
gcloud compute firewall-rules create allow-proxysql-lb \
–network default \
–source-ranges 10.240.0.0/16 \
–target-tags proxysql-lb \
–allow tcp:3306
2. Add a firewall rule to allow the health checks to happen
gcloud compute firewall-rules create allow-proxysql-health-check \
–network default \
–source-ranges 130.211.0.0/22,35.191.0.0/16 \
–target-tags proxysql-lb \
–allow tcp:6032
Wrapping up
The next step is testing you can get to the ProxySQL instances via the load balancer.
First let’s check how do the backends look:
gcloud compute backend-services get-health my-proxysql-lb –region=us-central1


backend: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-a/instanceGroups/us-proxysql-ig1
status:
healthStatus:
– healthState: HEALTHY
instance: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-a/instances/tst-proxysql01
ipAddress: 10.240.0.29
port: 80
kind: compute#backendServiceGroupHealth

backend: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-b/instanceGroups/us-proxysql-ig2
status:
healthStatus:
– healthState: HEALTHY
instance: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-b/instances/tst-proxysql02
ipAddress: 10.240.0.30
port: 80
kind: compute#backendServiceGroupHealth

backend: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-c/instanceGroups/us-proxysql-ig3
status:
healthStatus:
– healthState: HEALTHY
instance: https://www.googleapis.com/compute/v1/projects/[PROJECT_ID]/zones/us-central1-c/instances/tst-proxysql03
ipAddress: 10.240.0.33
port: 80
kind: compute#backendServiceGroupHealth
I am not sure why port 80 is reported, but other than that, all backends seem to be healthy.
Now let’s try connecting MySQL client through the load balancer’s IP:
[root@tst-adm01 ~]# mysql -h 10.240.0.163 -uapp_rw -p 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5461327
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select @@hostname;
+————————+
| @@hostname |
+————————+
| tst-mysql-01 |
+————————+
1 row in set (0.05 sec)
You can see I was able to reach a MySQL server called tst-mysql-01, which I had previously configured in ProxySQL.
I hope you found this article useful for configuring ProxySQL behind a load balancer. If you are interested in learning more about ProxySQL, I suggest you also check out the following articles in Pythian blog:
The State Of MySQL High Availability Going In To 2018
Using ProxySQL To Validate MySQL Updates
If you have any suggestions/improvements please let me know in the comments section below.
References: https://cloud.google.com/compute/docs/load-balancing/internal/
 

Datascape Podcast Episode 24 – MariaDB and the MySQL Ecosystem

Today on the show, I am joined by my colleagues, John Schulz and Valerie Parham-Thompson. Both John and Valerie just returned from the MariaDB Conference in New York City. As MariaDB is a native, relative and viable option to MySQL, I thought that it would be a good time to delve into the platform in detail and share what we have discovered with you here today. In this episode, we find out more about the MariaDB Conference – why it was founded, what the attendance was like and who the Conference is beneficial for. We then take a closer look at MariaDB and investigate the key differences between MariaDB and MySQL – highlighting MariaDB’s newest and most user-friendly features. We also learn more about other software platforms, such as Percona and Oracle, and how they compare to MariaDB. Our insightful guests continue to offer some helpful tips and resources for anyone getting started in MariaDB. So, if you are currently using MySQL or MariaDB, have just made the switch, or perhaps have just started with MariaDB fresh off the bat, then this is an episode packed with industry knowledge to get you stepping out on the right foot!
Key points from this episode:
• Find out how the MariaDB Conference was founded.
• Learn more about the organizers of the MariaDB Conference.
• Find out what attendence was like at the 2018 MariaDB Conference.
• Valerie shares more about her session topic of schema change methods.
• Discover the key differences between MariaDB and MySQL.
• Learn more about Percona, Oracle and available open source material.
• Tips for deciding on which software to use between Percona and MariaDB.
• Defining MariaDB: What exactly is it and how does it work?
• What makes the MariaDB and MySQL functionality unique?
• The newest features in MariaDB.
• Learn more about MariaDB’s PLSQL code support.
• Why people are choosing MariaDB over other cloud products.
• Getting help and support for new MariaDB learners.
• Find out how most people become MariaDB DBA’s.
• Valerie and John share helpful resources for getting started with MariaDB.
• And much more!

PerconaPercona blogMySQLOracleMariaDB ConferenceMariaDBHigh Performance MySQL by Peter Zaitsev, Baron Schwartz & Vadim TkachenkoSeveral NinesValerie Parham-Thompson

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569