We remember when we first started auditing MySQL servers, there were very few tools available.  In one of our early big gigs, we were battling serious performance issues for a client.  At the time, tuning-primer.sh was about the only tool available that could be used to diagnose performance bottlenecks.  Fortunately, with a lot of manual interpolation of the raw data it presented, we were able to find the issue with the server and suggest how to resolve them.  For that we are very thankful.  It was a first step in analyzing MySQL status variables, minimizing the number of formulas to learn and calculate by hand.  Obviously doing it by hand takes forever!

Now fast-forward to today.  Unfortunately, not much has changed.  Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on.  Don’t get the wrong; those tools have …

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 …

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 …

Auditing your database means tracking access and changes to your data and db objects. The Audit Log Plugin has been shipped with Percona Server since 5.5.37/5.6.17, for a little over 12 months. Prior to the Audit Log Plugin, you had to work in darker ways to achieve some incarnation of an audit trail.

We have seen attempts at creating audit trails using approaches such as ‘sniffing the wire’, init files, in-schema ‘on update’ fields, triggers, proxies and trying to parse the traditional logs of MySQL (slow, general, binary, error). All of these attempts miss a piece of the pie, i.e. if you’re sniffing tcp traffic you’ll miss local connections, parsing binary logs you’re missing any reads. Your reasons for audit logging might be down to compliance requirements (HIPAA, PCI DSS) or you may need a way to examine database activity or track the connections incoming.

Over the past …

Previous episodes:

• MySQL replication monitoring 101

MySQL replication in action - Part 1: GTID & CoMySQL replication in action - Part 2 - Fan-in topologyMySQL replication in action - Part 3 - All-masters P2P topology
Introducing star topology.In all-masters P2P topologies, we have seen that we have a way of deploying a topology where all nodes are masters, and achieve better efficiency and stability than ring topologies. That …

MySQL 5.7 comes with many changes. Some of them are better explained than others.

I wanted to see how many changes I could get by comparing SHOW VARIABLES in MySQL 5.6 and 5.7.
The most notable ones are:

• binlog_format: the default is now ROW. This variable affects the format of the binary log, whether you use it as a backup complement or for replication, the change means bigger binary logs and possibly side effects.
• …

UPDATE: Almost solved! See at the end.

A clean installation of a database server is one where everything goes according to the expectations. It used to be easy: you only had to do what the manual says, and, presto! you would see your database server installed and ready to use. If something went wrong, you got one or more error messages that informed you of what needs to be fixed.

Sometimes, rarely, it happened that you got also a warning message, telling you that while the installation was successful, you could improve it by fine tuning this and that. No big deal.

Gone are those times. A clean installation nowadays is a much harder exercise, if not impossible. Let’s give it a try using MySQL 5.7.7.



Attempt #1 using mysql_install_db
The first error you could do when using a new version of MySQL is assuming that basic operations are …

A Note On Good Practice

Even though I’ve been developing for some time now it never ceases to amaze me how many people don’t comment in their code. Most of us have fallen into the trap of “the code is self explanatory.” Sure, sometimes it is: if you’re writing “Hello World.” Even that can be questionable at times.

I once interviewed a seasoned developer who, when asked about how they document, replied “I think code should be self documenting.” I wanted to slap my forehead and send them packing right away. Code is never self-documenting. Never. Especially when you go back to it 4 weeks later. And if you think it is self documenting, the poor sap who has to maintain your code doesn’t.

With that said, I would like to make a plea to all non-documenters out there with good reasons on why you want to document.

Why Document? Visual Resting Place

Great …