gh-ost now powers our production schema migrations. We hit some serious limitations using pt-online-schema-change on our large volume, high traffic tables, to the effect of driving our database to a near grinding halt or even to the extent of causing outages. With gh-ost, we are now able to migrate our busiest tables at any time, peak hours and heavy workloads included, without causing impact to our service.

gh-ost supports testing in production. It goes a long way to build trust, both in integrity and in control. Are your databases just too busy and you cannot run existing online-schema-change tools? Have you suffered outages due to migrations? Are you tired of babysitting migrations that run up to 3:00am? Tired of being the only one tailing logs? Please, take a look at gh-ost. I believe it changes online migration paradigm.

For a more thorough overview, please read the announcement on the GitHub Engineering Blog, and proceed to the documentation.

gh-ost is open sourced under the MIT license.

This is a list of technologies (and resources) in and around MySQL, and means to serve as a place to find reliable software and info. I recently happened to notice there are some tools I’m familiar with that are unknown to others; tools unknown to me that are in good use.

The list is naturally and intentionally incomplete. I wish this to be a community based creation; so I put some categories and some tools. I left many out, deliberatey. Please assist by filling in the missing projects, tools, libraries! Additions gladly accepted via pull-requests. Do note the contribution guidelines (somewhat lengthy, I apologize).

I will moderate FUDs, promotional, commercials etc., and otherwise it may take some days for me to merge requests.

The work is licensed under the Creative Commons Attribution-ShareAlike 4.0 International License.

• Orchestrator reads your replication topologies (give it one server – be it master or slave – in each topology, and it will reveal the rest).
• It keeps a state of this topology.
• It can continuously poll your servers to get an up to date topology map.
• It visualizes the topology in a clear and slick D3 tree.
• It allows you to modify your topology; move slaves around. You can use the command line variation, the JSON API, or you can use the web interface.

Quick links: Orchestrator Manual, FAQ, Downloads

Nothing like nice screenshots

To move slaves around the topology (repoint a slave to a different master) through orchestrator‘s web interface, we use Drag and Drop,

just

like

that.

Safety

Orchestrator keeps you safe. It does so by:

• Correctly calculating the binary log files & positions (aka coordinates) of the slave you’re moving, its current master, its new master; it properly stops, starts and stalls your replication till everything is in sync.
• Helping you to avoid shooting yourself in the leg. It will not allow moving a slave that uses STATEMENT based replication under a ROW based replication server. Or a 5.5 under a 5.6. Or anything under a server that doesn’t have binary logs. Or log_slave_updates. Or if one of the servers involed lags too much. Or more…

Visualization

It also points out a few problems, visually. While it is not – and will not be – a monitoring tool, it requires some replication status info for its own purposes. Too much lag? Replication not working? Server cannot be accessed? Server under maintenance? This all shows up in your topology. We use it a lot to get a holistic view over our current replication topologies status.

State

Orchestrator keeps the state of your topologies. Unlike other tools that will drill down from the master and just pick up on whatever’s connected right now, orchestrator remembers what used to be connected, too. If a slave is not replicating at this very moment, that does not mean it’s not part of the topology. Same for a MySQL service that has been temporarily stopped. And this includes all their slaves, if any. Until told otherwise (or until too much time passes and a server is assumed dead), orchestrator keeps the map intact.

Maintenance

Orchestrator supports a maintenance-mode state; it’s a flag saying “this server is in maintenance mode right now”. But this flag includes an owner and a reason for audit purposes. And while a server is under maintenance, orchestrator will disallow replication topology changes that include this server.

Audit

Operations performed via orchestrator are audited (well, almost all). You have a complete history on what slave has been moved from where to where; what server has been taken under maintenance and when, etc.

Automation

The most important thing is of course automating error-prone human sequences of actions. Repointing slaves is a mess (when you don’t have GTIDs). Automation saves time and greatly reduces the possibility that something goes wrong (of course never eliminates). We happen to use orchestrator at Outbrain on production, and twice in the past month had major events where orchestrator saved us many hours and worry.

Support

Orchestrator supports “standard” replication: log file:pos kind of replication. Non GTID, non-parallel. Good (?) old replication.

To quickly explain why not GTID: we’re using MySQL 5.5. We’ve had issues while evaluating 5.6; and besides, migrating to GTID is a mess (several solutions or proposed solutions seem to exist). On April’s Percona Live event, I realized only a few companies actually use MySQL 5.6, and a few of these use GTID. We’re not doing GTID (yet), as much as we would want to. Not there yet.

Read the FAQ for further questions on supported replication technologies.

How do you like it?

Orchestrator can run as a command line tool (no need for Web). It can server HTTP JSON API (no need for visualization) or it can server as HTTP web interface (no need to use command line options). Have it your way.

The technology stack

Orchestrator is written in Go, with Martini as web framework; MySQL as backend database; D3, jQuery & bootstrap for frontend.

License

Orchestrator is released as open source under the Apache 2.0 license and is available at: https://github.com/outbrain/orchestrator

Documentation

Read the Manual

Download

Get the bundled binary+web files tarball, RPM or DEB packages. Or just clone the project. It’s free.

q allows you to query your text files or standard input with SQL. You can:

SELECT c1, COUNT(*) FROM /home/shlomi/tmp/my_file.csv GROUP BY c1

And you can:

SELECT all.c2 FROM /tmp/all_engines.txt AS all LEFT JOIN /tmp/innodb_engines.txt AS inno USING (c1, c2) WHERE inno.c3 IS NULL

And you can also combine with your favourite shell commands and tools:

grep "my_term" /tmp/my_file.txt | q "SELECT c4 FROM - JOIN /home/shlomi/static.txt USING (c1)" | xargs touch

Some of q‘s functionality (and indeed, SQL functionality) can be found in command line tools. You can use grep for pseudo WHERE filtering, or cut for projecting, but you can only get so far with cat my_file.csv | sort | uniq -c | sort -n. SQL is way more powerful for working with tabulated data, and so q makes for a great addition into one’s toolbox.

The tool is authored by my colleague Harel Ben-Attia, and is in daily use over at our company (it is in fact installed on all production servers).

It is of course free and open source (get it on GitHub, where you can also find documentation), and very easy to setup. Enjoy!

With a different license in mind for rdebug, common_schema changes license to GPL (2 or above).

common_schema 2.0 is ready for download. All things rdebug, it is alpha — otherwise it’s a stable release.

rdebug

I’m very happy to release this alpha version of rdebug, and urge everyone to try it out.

The idea is to have an open, free, server side debugger and debugging API for MySQL stored routines. To elaborate:

• It’s server side by that it’s implemented by stored routines. Not by a connector; not an emulator; not a GUI tool hack. The entire functionality lies within common_schema, a schema in your server.
• It’s a debugger: you can debug your own stored routines (with limitations)
• It’s a debugging API: there’s a distinct specification and a set of calls which makes for a debugging process
• It’s open since the source code is yours to browse.
• It’s free as in free beer.
• It’s free as it makes you independent of a specific debugger. It provides an API that anyone can use. You can run the API yourself from the command line; or plugins for your favorite GUI editor can be developed to use this API.

On a separate blog post I will take you to a rdebug test drive.

As always, nothing is released before extensive documentation is in place.

I’d love to get input on this.

GPL

common_schema < 2.0 was released under the BSD license, which is less restrictive. I was pondering the BSD license for a couple years now, and with the arrival of rdebug have decided to switch to GPL. I’ve been through this thinking of change of license in other projects of mine; am generally agreeing that best not to change licensing throughout lifetime. I actually do see GPL as promoting open source software better than BSD, and with all the issues around GPL this actually means something to me. I write open source; I love people using it; I love people extending it; I want to be re-released as open source, or I want better control of the code.

So this turns out to be something that is important to me, and just before common_schema takes the world in storm (212 downloads today, 212,000,000 tomorrow), I want to have this settled. If no storm comes, well, I’ll have up to 212 people banging on my door (I provide free coffee).

Text routines

Two text routines are added:

• replace_sections(): replace a text given a from-to combination, and a replacement string, which could include a back-reference. For example:

mysql> select replace_sections('The <b>quick</b> brown <b>fox</b>', 
        '<b>', '</b>', 
        '<span>\\0</span>') as result;
+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| The <span>quick</span> brown <span>fox</span> |
+-----------------------------------------------+

• hexcode_text(): a convenience routine which shows a beautified hex-code of a given text. I get to need it when using UNICODE characters which are hard to detect, are visually identical to other characters, or are copied from MS Word.

mysql> call hexcode_text('the quick brown fox jumps over the lazy dog');
+-----------------------------------------------------------------------------------+
| >0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f   0 1 2 3 4 5 6 7 8 9 a b c d e f |
+-----------------------------------------------------------------------------------+
| 74 68 65 20 71 75 69 63 6B 20 62 72 6F 77 6E 20   t h e   q u i c k   b r o w n   |
| 66 6F 78 20 6A 75 6D 70 73 20 6F 76 65 72 20 74   f o x   j u m p s   o v e r   t |
| 68 65 20 6C 61 7A 79 20 64 6F 67                  h e   l a z y   d o g           |
+-----------------------------------------------------------------------------------+

Get it

Download common_schema here.

At this moment, it will refuse to install MariaDB 10.0 (alpha):

bash$ make_sandbox /tmp/mariadb-10.0.1-linux-x86_64.tar.gz 
unpacking /tmp/mariadb-10.0.1-linux-x86_64.tar.gz
unsupported version 10.0

This is perfectly legitimate, and I have no quarrel with this fact. However, I did want to setup MariaDB 10.0 as a sandbox.

As it turns out mysqlsandbox relies on MySQL package naming conventions to detect the version: the fact that a tgz file distribution is named mariadb-10.0.1-linux-x86_64.tar.gz and extracts onto mariadb-10.0.1-linux-x86_64, tells mysqlsandbox that this is version 10.0.1. Easy enough to cheat, then: pick a version that mysqlsandbox will work with, and which is compatible sandbox-wise with your own, and do the renames. For that matter, I picked 5.5, and, actually, made it 5.5.100:

bash:/tmp$ tar xzf mariadb-10.0.1-linux-x86_64.tar.gz
bash:/tmp$ mv mariadb-10.0.1-linux-x86_64 mariadb-5.5.100-linux-x86_64
bash:/tmp$ tar czf /tmp/mariadb-5.5.100-linux-x86_64.tar.gz ./mariadb-5.5.100-linux-x86_64
bash:/tmp$ make_sandbox mariadb-5.5.100-linux-x86_64.tar.gz 

unpacking /tmp/mariadb-5.5.100-linux-x86_64.tar.gz
Executing low_level_make_sandbox --basedir=/tmp/5.5.100 \
        --sandbox_directory=msb_5_5_100 \
        --install_version=5.5 \
        --sandbox_port=55100 \
        --no_ver_after_name \
        --my_clause=log-error=msandbox.err
>>/tmp
    The MySQL Sandbox,  version 3.0.29
    (C) 2006-2013 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /home/shlomi/sandboxes
sandbox_directory              = msb_5_5_100
sandbox_port                   = 55100
check_port                     = 
no_check_port                  = 
datadir_from                   = script
install_version                = 5.5
basedir                        = /tmp/5.5.100
tmpdir                         = 
my_file                        = 
operating_system_user          = shlomi
db_user                        = msandbox
remote_access                  = 127.%
ro_user                        = msandbox_ro
rw_user                        = msandbox_rw
repl_user                      = rsandbox
db_password                    = msandbox
repl_password                  = rsandbox
my_clause                      = log-error=msandbox.err
master                         = 
slaveof                        = 
high_performance               = 
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) > 
force                          = 
no_ver_after_name              = 1
verbose                        = 
load_grants                    = 1
no_load_grants                 = 
no_run                         = 
no_show                        = 
do you agree? ([Y],n)

It worked well for me; perhaps I’m missing on something, so watch your own setup.