Say you have a cluster with 3 nodes using Percona XtraDB Cluster (PXC) 5.6 and one asynchronous replica connected to node1. If asynchronous replication is using GTIDs, moving the replica so that it is connected to node2 is trivial, right? Actually replication can easily break for reasons that may not be obvious at first sight.

Summary

Let’s assume we have the following setup with 3 PXC nodes and one asynchronous replica:

Regarding MySQL GTIDs, a Galera cluster behaves like a distributed master: transactions coming from any node will use the same auto-generated uuid. This auto-generated uuid is related to the Galera uuid, it’s neither ABC, nor DEF, nor GHI.

Transactions executed for …

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 …

One common job for a DBA is working with a Development Team member on a batch loading process that is taking more time than expected.

Often once you start probing into what has already been done to performance tune the load process, it seems you often learn that very little has, in fact, been done to improve efficiency and speed.

Since there are so many things that can be done to improve this process, it really is outside the scope of this article to cover them all.  Hopefully this will serve as a guide to get you started in the process.

Let’s outline 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 …

Sometimes data sets are so large, a mysqldump to load a slave is just not practical.  With some of the systems we have administrated, we have had data so large it would have taken days to load the slave when it became out of sync with the master.  When this happens, we usually rely upon Percona’s XTRABackup utility which allows us to make a hot/online backup of the master to use for loading the slave.

In the old days we had to rely upon a third-party tool called ibbackup, or InnoDB Hot Backup utility to do this task. In many ways XTRABackup is a replacement for this tool and has in fact surpassed the ibbackup utility in features and function.

The most efficient way we have found to transfer that data to the slave is the use of the netcat utility.

We also use the screen command since we expect this could take quite some time and don’t want to take the chance that a network connection issue, or a dropped VPN, …

Sometimes you encounter a server with multiple tables of a particular storage engine which you need to convert to another storage engine.  For us, this often happens when we find systems running MyISAM and we want to get these over to InnoDB.

There are a number of reasons to consider converting a table to another storage engine, such as performance, gaining additional features such as Foreign Keys, and so on.  You should, however, stop to consider that not all storage engines are created the same and do not offer the same features.

If there are hundreds of tables, the process can be very time consuming so we put together a simple bash script to automate this process.

#!/bin/sh

MY_USER="root"
MY_PASSWORD="mypassword"
MY_HOST="127.0.0.1"
MY_PORT=3306
NEW_ENGINE="InnoDB"

TABLES=`mysql -u$MY_USER -p$MY_PASSWORD -h$MY_HOST -P$MY_PORT -e"SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS 'TABLE' FROM …

Have you ever tried to start a server like MySQL and been amazed to see an error that the port is already in use? You rack your brain and try to figure out what it would be to no avail. Sometimes you do a “ps” in Linux and don’t even see anything that you think would be using the port. Well, forutantely, there are some tricks to help you find out without doing a reboot. If it is a production server, a reboot may not be an option anyway!

Below are some methods to help. We will start by looking at the “fuser” utility provided with many Linux distros:

fuser -n tcp 80
80/tcp:               1029  1030  1824  1838  1839  1840  1841 13972 14136 14137 14712

This example shows a simple check of everything using port 80. What you see above is a list of PIDs that are using that port. Now we could probably just do a simple “ps” to figure out what it is. You might also want to get more info by doing something like the …

Some time back, when a client wanted us to setup MySQL Enterprise Monitor, we were surprised to find out that disk monitoring was not available! We worked hard to come up with a solution. Eventually, we decided to setup a custom agent to monitor the disk. Below is the result of that.

While this script may not work as-is for everyone, it should at least provide a basis for such a script. This script has been modified to send an email instead of plug directly into the MySQL Enterprise Monitor. But, it hopefully will get our creative juices flowing…

#!/bin/bash
#
# This script does a very simple test for checking disk space.
#
# Itchy Ninja Software: http://www.itchyninja.com
#

CHECKDISK=`df -h | awk '{print $5}' | grep % | grep -v Use | sort -n | tail -1 | cut -d "%" -f1 -`
ALERT_VALUE="80"
MAIL_USER="root@localhost.com"
MAIL_SUBJECT="Daily Disk Check"

if [ "$CHECKDISK" -ge "$ALERT_VALUE" ]; then
echo "At least one of my disks is nearly …

Many of the DBA’s at Itchy Ninja Software, began their careers as PHP Programmers, or before… We remember times sitting in a small office together writing PHP code before there were even books on the subject! We were scouring the web looking for examples and such and relying heavy on the documentation at the php.net site!

We wrote custom shopping carts before the days of oscommerce, zencart, and such. We wrote code to interface with payment gateways and process credit cards live. We also wrote code to pull products from MySQL databases, show staff members, and such. We worked for a newspaper group and were writing news “engines” to pull the articles, archive data, etc.

Why am I reminiscing about all of that? Well, because frankly, we have been there. We know a lot of other developers are still there doing the kind of work that we began with. We know that we can help others not to make the same mistakes we have seen some of …

Being a QA Engineer, how would you feel if you had access to a framework which can generate 80+ crashes – a mix of hitting developer introduced assertions (situations that should not happen), and serious unforeseen binary crashes – for the world’s most popular open source database software – each and ever hour? What if you could do this running on a medium spec machine – even a laptop?

The seniors amongst you may object “But… generating a crash or assertion is one thing – creating a repeatable testcase for the same is quite another.”

Introducing pquery, mtr_to_sql, reducer.sh (the pquery-enabled version), and more:

80+ coredumps per hour. Fully automatic testcase creation. Near-100% testcase reproducibility. C++ core. 15 Seconds run time per trial. Up to 20-25k lines of SQL executed per trial. CLI testcases. Compatible with sporadic issues. High-end automation of many aspects.

It all …