Introduction: where we standPrevious episodes:

• MySQL replication monitoring 101

MySQL replication in action - Part 1: GTID & Co
In the latest releases of MySQL and MariaDB we have seen several replication improvements. One of the most exciting additions is the ability to enhance basic replication with multiple sources. Those who have used replication for a while should remember that one of the tenets of the “old” replication was that a slave couldn’t have more than one master. This was The Law and there was no escape ... until now. The only way to work around that prohibition was to use circular replication, also known …

In the theoretical part of this series, we have seen the basics of monitoring. In that article, though, we have barely mentioned the new tools available in MySQL 5.7 and MariaDB 10. Let’s start from something that has the potential of dramatically changing replication as we know it.
Crash-safe tables and Global transaction identifiers in MySQL 5.6 and 5.7Global transaction identifiers (GTID) is a feature that has been in my wish list for long time, since the times I was working with the MySQL team. By the time I left Oracle, this feature was not even in the plans.
When MySQL 5.6 was first disclosed, the biggest improvement for replication was the introduction of crash-safe tables (see Status persistence in …

I’m excited to be at LinuxCon North America in Seattle next week (August 17-19 2015). I’ve spoken at many LinuxCon events, and this one won’t be any different. Part of the appeal of the conference is being able to visit a new place every year.

MariaDB Corporation will have a booth, so you’ll always be able to see friendly Rod Allen camped there. In between talks and meetings, there will also be Max Mether and quite possibly all the other folk that live in Seattle (Kolbe Kegel, Patrick Crews, Gerry Narvaja).

For those in the database space, don’t forget to come attend some of our talks (represented by …

The latest MySQL Sandbox, version 3.0.66 is out. It has a few new features (as always, when I find myself doing the same thing many times, I script it) and improved support for latest releases of MySQL. You can now install, among other versions, MySQL 5.7.8 and MariaDB 10.1.x

Some notable additions in this release are in the scripts that are created and customized for each sandbox. There are many of them and when one more arrives, it's easy to overlook it. So, here are the new arrivals.



./show_binlog
When I am troubleshooting replication behavior, I often need to inspect the latest binary log. The sandbox has a shortcut that gives me the right version of mysqlbinlog for the deployment:

MariaDB 10.1.6 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.6/

This is the 4th beta, and 7th overall, release of MariaDB 10.1. There were not many major changes in this release, but a few notable items, as well as many overall bugs fixed (I counted 156, down ~50% from 10.1.5).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights:

• RESET_MASTER is extended with TO # clause which allows one to specify the number of the first binary log. (MDEV-8469)
• Added support for …

MySQL 5.6.26 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there are 3 “Functionality Added or Changed” items, 1 “Security Fix”, and 36 other bug fixes.

Out of those other 36 bugs, 13 are InnoDB, 1 Partitioning, 3 Replication, and 19 misc. (including 3 potentially crashing bug fixes, and 1 performance-related fix) Here are the ones of note:

• Functionality Added/Changed: Replication: When using a multi-threaded slave, each worker thread has its own queue of transactions to process. In previous MySQL versions, STOP SLAVE waited for all workers to process their entire queue. This logic has been changed so that STOP SLAVE first finds the newest transaction that was committed by any worker thread. Then, it waits for all workers to complete …

MySQL 5.5.45 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://dev.mysql.com/downloads/mysql/5.5.html

This release, similar to the last 5.5 release, is mostly uneventful.

There were 0 “Functionality Added or Changed” items this time, 1 “Security Fix”, and just 9 bugs overall fixed.

Out of the 9 bugs, there were 3 InnoDB bugs, 1 security-related bug, and 1 potential crashing bug. Here are the ones worth noting:

• InnoDB: An index record was not found on rollback due to inconsistencies in the purge_node_t structure.
• InnoDB: An assertion was raised when InnoDB attempted to dereference a NULL foreign key object.
• InnoDB: On Unix-like platforms, os_file_create_simple_no_error_handling_func and …

Today I released version 1.1 of myoradump for download from sourceforge. If you don't know what myoradump is, this is a utility for exporting data from an Oracle database in some relevant text format so that it can be imported to some other database.

The main thing in version 1.1 is that I have added a whole bunch of new output formats, so make it even easier to get your data out of expensive Oracle and into something more effective. The new formats supported are:

• MySQL - The format of this is a bunch of INSERT statements that you get when you use mysqldump for example and is useful for import into MariaDB (and MySQL). INSERT arrays are supported as a bunch of more options.
• JSON - This format is rather obvious, the output is a file consisting of one JSON object per row. To support binary …

This is getting more and more common, so I wanted to provide the steps required to get LDAP authentication working with MariaDB PAM plugin.

Unless you’re already familiar with setting up the MariaDB PAM plugin, I’d first recommend getting this to work with a standard Linux user (steps 1-4), then once all is working fine, progress to the LDAP users (steps 5-10). (And if you do not want to test this for the Linux user account, then you may skip steps #2 and #3.)

1. Enable plugin by running the following from the command line client:

   INSTALL SONAME 'auth_pam';

   You should see an entry like this afterward in SHOW PLUGINS:

       | pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL |

2. Create the mysql user account (note it does not have a password, as it will obtain this from your Linux user, and eventually the LDAP account) and provide it with the GRANTS you want it to …

http://dom.as/2015/07/30/on-order-by-optimization/

An insightful exploration by Domas (Facebook) on how some of the MySQL optimiser’s decision logic is sometimes naive, in this case regarding ORDER BY optimisation.

Quite often, “simple” logic can work better than complex logic as chasing all the corner cases can just make things worse – but sometimes, logic can be too simple.

Everything must be made as simple as possible, but no simpler.
— Albert Einstein / Roger Sessions