Look to these excellent tools to improve CLI
and web admin, SQL queries, schema migration, and replication and
recovery in your MySQL environment
Thinkstock
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! Amjith Ramanujam
Smart completion in action in Mycli. 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). GitHub
Gh-ost operation modes. 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. GitHub
Gh-ost general flow. 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. IDG
PhpMyAdmin status page showing questions, connections/processes, and traffic graphs. 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. IDG
PhpMyAdmin start screen. 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:
Logical database design
Physical database design
Query
Application development
Joy Arulraj
Sqlcheck at work. 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. GitHub
Orchestrator provides a window into MySQL replication and recovery.
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.
New
Tech Forum provides a venue to explore and discuss emerging enterprise
technology in unprecedented depth and breadth. The selection is
subjective, based on our pick of the technologies we believe to be
important and of greatest interest to InfoWorld readers. InfoWorld does
not accept marketing collateral for publication and reserves the right
to edit all contributed content. Send all inquiries to newtechforum@infoworld.com.
Amjith Ramanujam
GitHub
GitHub
IDG
IDG
Joy Arulraj
GitHub
No comments:
Post a Comment