Top 5 open source tools for MySQL administrators

For database administrators (DBAs), keeping databases running at peak performance can be a little like spinning plates: It takes agility, concentration, quick reactions, a cool head, and an occasional call out from a helpful onlooker. Databases are central to the successful operation of almost every application. As DBAs are responsible for an organization’s data, finding dependable tools that help them to streamline the database management process and ease day-to-day maintenance tasks is essential. DBAs need good tools to keep their systems spinning smoothly.

So what are the tried and trusted tools for MySQL administrators? Here I share my top five open source tools for MySQL administrators and discuss their value in the support of day-to-day MySQL administration tasks. For each of them, I’ve provided a link to the GitHub repository and listed the number of GitHub stars at the time of writing.

Mycli

The Mycli project provides MySQL command line auto-completion and syntax highlighting. It is one of the most popular MySQL tools for administrators.

Security restrictions such as jump hosts and two-factor authentication leave many MySQL DBAs with command-line only access to their systems. In such circumstances, beloved GUI tools such as MySQL Workbench, Monyog, and others are not an option.

At the command line, much of the time is spent in a light-on-black terminal world. So one of the best things about Mycli is the richness of its syntax highlighting. This allows you, for example, to visually separate functions and operators from query strings in WHERE clauses. For a short, single-line query this may not be such a big deal, but it becomes a game changer when you work with queries that perform JOIN operations over more than a couple of tables. Am I doing the JOIN using indexed columns? Am I filtering using leading wildcards in my WHERE clauses? Mycli supports multi-line queries and syntax highlighting, which means you can home in on the sections that matter most when reviewing or optimizing queries. You can choose from a number of syntax highlighting color schemes or create your own.

The other killer feature of Mycli is smart completion. This allows to you pick out table and column names from a context sensitive list by entering just their first few characters. No more abandoning your current input to run SHOW CREATE TABLE because you forgot the name of the column you want in your WHERE clause!

 

With Mmycli, you can alias favorite queries using \fs, e.g. \fs myAlias myQuery. This is really handy, as you can then execute the query using \f myAlias whenever it’s needed.

The Mycli project uses the BSD 3 license. There are 44 contributors, 1.2k commits, and 5k stars.

Gh-ost

If like 99 percent of MySQL DBAs you have faced implementing a change to a MySQL table while fearing the impact on production, then you should consider Gh-ost (GitHub Online Schema Migration). Gh-ost provides MySQL schema changes without blocking writes, without using triggers, and with the ability to pause and resume the migration!

Why is this so important? Since MySQL 5.6 shipped with new ALTER TABLE ... ALGORITHM=INPLACE DDL (Data Definition Language) functionality, it became possible to modify a table without blocking writes for common operations such as adding an index (B-tree). However, there remain a few conditions where writes (DML statements) are blocked, most notably the addition of a FULLTEXT index, the encryption of the tablespace, and the conversion of a column type.

Other popular online schema change tools, such as Percona’s pt-online-schema-change, work by implementing a set of three triggers (INSERT, UPDATE, and DELETE) on the master to keep a shadow copy table in sync with changes. This introduces a small performance penalty due to write amplification, but more significantly requires seven instances of metadata locks. These effectively stall DML (Data Manipulation Language) events.

Since Gh-ost operates using the binary log, it is not susceptible to the trigger-based drawbacks. Finally Gh-ost is able to effectively throttle activity to zero events, allowing you to pause the schema migration for a while if your server begins to struggle, and resume when the activity bubble moves on.

So how does Gh-ost work?  By default, Gh-ost connects to a replica (slave), identifies the master, and applies the migration on the master. It receives changes on a replica to the source table in binlog_format=ROW, parses the log, and converts these statements to be re-executed on the master’s shadow table. It keeps track of the row counts on the replica and identifies when it is time to perform an atomic cutover (switch tables).

 

Gh-ost provides an alternative mode where you execute the migration directly on the master (whether it has slaves or not), read back the master’s binlog_format=ROW events, and then re-apply them to the shadow table.

A final option is available to run the migration only on the replica without impacting the master, so you can test or otherwise validate the migration.

 

Note that if your schema has foreign keys then Gh-ost may not operate cleanly, as this configuration is not supported.

Note that oak-online-alter-table was the predecessor to Gh-ost. You can read a comparison between Gh-ost and pt-online-schema-change performance by Peter Zaitsev, Percona’s CEO, along with a response from Shlomi Noach, the author and maintainer of the OAK toolkit and Gh-ost.

The Gh-ost project uses the MIT license. It has 29 contributors, nearly 1k commits, and 3k stars.

PhpMyAdmin

One of the longest running and most mature projects among MySQL tools is the venerable PhpMyAdmin tool used to administer MySQL over the web. phpMyAdmin allows the DBA to browse and modify MySQL database objects: databases, tables, views, fields, and indexes. There are options to perform data export using more than a dozen formats, modify MySQL users and privileges, and – my favorite—execute ad-hoc queries.

You’ll also find a Status tab that dynamically plots questions, connections/processes, and network traffic for your given database instance, along with an Advisor tab that shows you a list of possible performance issues along with recommendations on how to remediate.

PhpMyAdmin uses the GPLv2 license. This is a huge project with more than 800 contributors, an amazing 112k commits, and 2.7k stars. An online demo is available at https://demo.phpmyadmin.net/master-config/

Sqlcheck

SQL anti-patterns can slow down queries, but often it takes experienced DBAs and developers poring over code to identify and resolve them. Sqlcheck reflects the efforts of Joy Arulraj to codify the book “SQL Anti-patterns: Avoiding the Pitfalls of Database Programming” by Bill Karwin. Karwin identifies four categories of anti-pattern:

1. Logical database design
2. Physical database design
3. Query
4. Application development

 

Sqlcheck can be targeted at varying risk levels, categorized as low, medium, or high risk. This is helpful if your list of anti-patterns is large, since you can prioritize the queries with the greatest performance impact. All you need to do to get started is gather a list of your distinct queries into a file and then pass them as an argument to the tool.

I used a sample collected from the PMM Demo environment to generate the following output:

[michael@fedora ~]$ sqlcheck —file_name PMMDemoQueries.txt
+————————————————————————-+
|                   SQLCHECK                      |
+————————————————————————-+
> RISK LEVEL    :: ALL ANTI-PATTERNS
> SQL FILE NAME :: output
> COLOR MODE    :: ENABLED
> VERBOSE MODE  :: DISABLED
> DELIMITER     :: ;
————————————————————————-
==================== Results ===================

————————————————————————-
SQL Statement: select table_schema, table_name, table_type, ifnull(engine, ‘none’) as engine,
ifnull(version, ‘0’) as version, ifnull(row_format, ‘none’) as row_format,
ifnull(table_rows, ‘0’) as table_rows, ifnull(data_length, ‘0’) as data_length,
ifnull(index_length, ‘0’) as index_length, ifnull(data_free, ‘0’) as data_free,
ifnull(create_options, ‘none’) as create_options from information_schema.tables
where table_schema = ‘innodb_small’;
[output]: (HINTS) NULL Usage
[Matching Expression: null]
...
==================== Summary ===================
All Anti-Patterns and Hints  :: 7
>  High Risk   :: 0
>  Medium Risk :: 0
>  Low Risk    :: 2
>  Hints       :: 5

Sqlcheck is covered by the Apache License 2.0. The project has five contributors, 187 commits, and 1.4k stars.

Orchestrator

Orchestrator is a high availability and replication management tool. It provides the ability to discover the replication topology of a MySQL environment by crawling up and down the chain to identify masters and slaves. It can also be used to refactor your replication topology via the GUI, providing a drag-and-drop interface to promote a slave to a master. This is a very safe operation. In fact Orchestrator rejects any illegal operations so as not to break your system.

Finally, Orchestrator can support recovery when nodes suffer failures, as it uses the concept of state to intelligently choose the correct recovery method and to decide the appropriate master promotion process to use.

Orchestrator is another tool provided by Shlomi Noach at GitHub. It is covered by the Apache License 2.0. Orchestrator has 34 contributors, 2,780 commits, and 900 stars at the time of this writing.

 

Keeping the plates spinning

At the start of this piece, I spoke about the MySQL administrator’s role being like that of a plate spinner. Occasionally, the administrator might benefit from a shout out from a helpful onlooker when things start to wobble and need attention. Percona Monitoring and Management (PMM) takes on takes on the job of shouting out, highlighting areas that need attention and helping database administrators to identify and resolve database issues.

PMM incorporates a number of best-of-breed open source tools, including Orchestrator, to provide a comprehensive database monitoring and management facility. Its graphical presentation provides easily assimilated visual clues to the state of your database servers over time, and supports MySQL, MariaDB, and MongoDB servers. Check out our public demo!

Just like my top five tools, and like all of Percona’s software, PMM is entirely free and open source software that can be downloaded from the Percona website or from GitHub.

Each of the tools I’ve described addresses a different aspect of the MySQL administrator’s role. They contribute to your arsenal of database management tools, and allow you to tap into the experience and skills of the contributors to these popular projects. They are free and open source and can be adapted to the needs of your own environment if necessary, or you can use them without modification. If you haven’t yet explored these gems, I recommend you take a closer look to see if they offer you advantages over your current methods and tools.

—

Michael Coburn serves as a product manager at Percona where he is responsible for Percona Monitoring and Management. With a foundation in systems administration, Coburn enjoys working with SAN technologies and high availability solutions.