With Galera (Percona Cluster or MariaDB Cluster), it is sometimes advisable to not route traffic to a node during a backup due to the node already being under a heavier load.  In these situations, it may be wise to not route traffic there until the backup is complete.

Since the default /usr/bin/clustercheck script did not have the option of doing this, we created a modified version.  The below script looks for the presence of the xtrabackup tool running in the process list.  If it is found, the clustercheck script returns the appropriate exit code (502) which signals the load balancer to not route traffic its way.  The script could easily be modified to look for the presence of programs/tools in the process list.

We hope others will find it useful.

#!/bin/bash
# clustercheck.sh
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Modified by Itchy …

Ever get called out for a MySQL issue only to realize that there was no issue?  It was a false alarm from the monitor.  We sure have and it’s frustrating, especially at 3:00 or 4:00 in the morning!

Many DBAs work in an environment where there is some sort of first level support that gets assigned tickets first.  Unfortunately, many of the times these groups are, shall we say, less than skilled in MySQL.  As a result, they quickly escalate the ticket onto the primary on-call DBA, even when there is really nothing wrong.

Much of the time, there are multiple types of MySQL topology in these environments: standalone, galera cluster, replication, etc.  Writing large runbooks with detailed test cases can be a daunting process and one that will cause many first-level support engineers to give up and simply escalate the issue anyway.

In an effort to avoid undue call outs, we developed a simple bash …

Introduction

This document outlines best practices for loading data into MySQL very quickly.  While this is not a comprehensive list of loading methods and configuration, it is a good starting point.

MySQL Configuration

Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly performance tuned for loading large amounts of data.  Out of the box MySQL configuration is rarely sufficient for performance with MySQL.  It is essential that the InnoDB settings, in particular, be set properly.

First of all, consider the InnoDB Buffer Pool.  If you are doing a one-time load, it may be a good idea to configure this as large as possible.  In fact, we sometimes set this to approximately 90% of the available RAM on the system for the load.  This can then be dropped to between 70 and 80% for …

In order to simplify the configuration of MySQL for standalone nodes, clusters, and replication configurations, we decided it would be easiest to maintain a common my.cnf file.  We have to admit, the idea wasn’t ours; we picked the idea up from www.fromdual.com and thought it was such a great idea, we decided to implement it as well.

Below is our version of a standardized my.cnf implementing several of our best practices.  We hope it will be of benefit to you.

########################################################################################################
# my.cnf (Configuration file for MySQL)
#
# Provided by Itchy Ninja Software to implement general best practices for MySQL regardless of server 
# type.  We chose a single file instead of maintaining multiple versions of the configuration file.
# Based upon http://www.fromdual.com/mysql-configuration-file-sample 
#
# Sections are included for Percona XTRADB Cluster …

Recently, we experimented with active/passive clustering of MySQL via Corosync and Pacemaker.  While many choose to use DRBD, our requirement was to simply have storage fail over to the other node.  In the real world, this would likely be a SAN.  For our POC, we chose to use NFS.

This document will provide a high level overview of a redundant and highly-available clustering solution for MySQL in an active/passive clustering configuration. While there are many ways to make MySQL highly available each has its strengths and weaknesses. These will be explained herein.

Components of the Solution
This solution requires only freely available Open Source components. Each is depicted in the diagram below:

 Two server nodes (virtual servers or physical servers)
 Virtual IP (VIP)
 Shared Storage such as a SAN with filesystem which can be mounted on either node
 …

We manage hundreds of servers and have a need to add and remove DBAs, application developers, and so on regularly. Doing this manually is just beyond the scope of what we can realistically manage. Since we are already using LDAP, we wanted to find a way to integrate it with MySQL.

After reading many, many articles and getting diverse opinions as to whether or not it was even possible, we decided to give it a try. From what we read, it appears that MySQL Enterprise edition does exactly what we want; unfortunately, we don’t have an Enterprise support contract ruling that out as an option. Knowing that Percona tends to mimic many of the Enterprise features, we decided to pursue doing this with Percona Server. Further reading showed this could be possible with the PAM plugin which would then authenticate via LDAP. A little indirect, but seemed like our only solution.

Since MySQL does not provide a community version of its LDAP …

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.

Make More Efficient Use of Your Time

Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.

Cross-Platform

Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The …

On servers that are into the swap file and have multiple processes running, I often wonder if it’s MySQL that is in the swap or some other process.  With Linux this is a fairly easy thing to figure out.  Below is the format of a command to do just that:

echo 0 $(cat /proc/`pidof process`/smaps | grep TYPE | awk '{print $2}' | sed 's#^#+#') | bc

In the above command, TYPE refers to what type of Memory you want to examine.  Below are some of the options for this value:

MySQL Installation Process Checklist

All DBAs, regardless of experience level, should follow a written process when setting up a new server.  There are just too many steps to neglect doing so and many of the steps you are likely to forget have little to do with MySQL.

Naturally, every company has a different process.  The process we outline below is one we have used in the past and focuses on working through the Change Management process, setting up backups and monitoring, and focusing on good communication with team members and clients as well as ensuring documentation of your work.  Hopefully this article will give you some ideas on implementing your own process document.

Below are the steps we have documented in the past when creating a new installation of MySQL:

• Initial Change Management Processes
  • Edit the ticket and set to Waiting on Customer
  …

Introduction

This article details the proper method of load balancing either a Percona XTRADB Cluster (PXC) or MariaDB Cluster.  Both of these solutuions utilize the same Galera plugin to facilitate clustering.  This information deatails what ports must be open and how they are to be configured as well as how to route traffic to the database nodes.

There are generally a total of three (3) nodes in this cluster, along with a single load balancer. This is the minimum number of nodes required for a cluster. This does not mean that the cluster is down if one or two nodes are lost – it only means that at least three nodes are required for normal cluster operation.

Data Centers

One of the most common questions we receive is whether the cluster can span two data centers.  The short answer is “Yes, but…”   While it is possible for a cluster to span two data centers, it …