Planet MySQL

MySQL Enterprise Monitor 3.4.8 has been released

We are pleased to announce that MySQL Enterprise Monitor 3.4.8 is now available for download on the My Oracle Support (MOS) web site. This is a maintenance release that includes a few new features and fixes a number of bugs. You can find more information on the contents of this release in the change log.

You will find binaries for the new release on My Oracle Support. Choose the "Patches & Updates" tab, and then choose the "Product or Family (Advanced Search)" side tab in the "Patch Search" portlet.

Important: MySQL Enterprise Monitor (MEM) 8.0 offers many significant improvements over MEM 3.4 and 4.0 and we highly recommend that you consider upgrading. More information on MEM 8.0 is available here:

Please open a bug or a ticket on My Oracle Support to report problems, request features, or give us general feedback about how this release meets your needs.

If you are not a MySQL Enterprise customer and want to try the Monitor and Query Analyzer using our 30-day free customer trial, go to http://www.mysql.com/trials, or contact Sales at http://www.mysql.com/about/contact.

Thanks and Happy Monitoring!

- The MySQL Enterprise Tools Development Team

Useful URLs

On Some Problematic Oracle MySQL Server Features

In one of my previous posts I stated that in Oracle's MySQL server some old enough features remain half-backed, not well tested, not properly integrated with each other, and not documented properly. It's time to prove this statement.

I should highlight from the very beginning that most of the features I am going to list are not that much improved by other vendors. But they at least have an option of providing other, fully supported storage engines that may overcome the problems in these features, while Oracle's trend to get rid of most engines but InnoDB makes MySQL users more seriously affected by any problems related to InnoDB.

The Royal Pavilion in Brighton looks nice from the outside and is based on some great engineering decisions, but the decorations had never been completed, some interiors were ruined and never restored, and the building was used for too many different purposes over years. The list of problematic MySQL server features includes (but is not limited to) the following:
  • InnoDB's data compression

    Classical InnoDB compression (row_format=compressed) has limited efficiency and does not get any attention from developers recently. Transparent page compression for InnoDB seems to be originally more like a proof of concept in MySQL that may not work well in production on commodity hardware and filesystems, and was not integrated with backup tools.
  • Partitioning

    Bugs reported for this feature by MySQL Community do not get proper attention. DDL against partitioned tables and partition pruning do not work the way DBAs may expect. We still miss parallel processing for partitioned tables (even though proof of concept for parallel DDL and some kinds of SELECTs was ready and working 10 years ago). Lack of careful testing of partitioning integration with other features is also visible.
  • InnoDB's FULLTEXT indexes
    This feature appeared in MySQL 5.6, but 5 years later there are still all kinds of serious bugs in it, from wrong results to hangs, debug assertions and crashes. There are performance regressions and missing features comparing to MyISAM FULLTEXT indexes, and this makes the idea to use InnoDB for everything even more problematic. Current implementation is not designed to work with really large tables and result sets. DBAs should expect problems during routine maintenance activities, like ALTERing tables or dumps and restores when any table with InnoDB FULLTEXT index is involved.

  • InnoDB's "online" DDL implementation
    It is not really "online" in too many important practical cases and senses. Replication ignores LOCK=NONE and slave starts to apply "concurrent" DML only after commit, and this may lead to a huge replication lag. The entire table is often rebuilt (data are (re-)written) to often, in place or by creating a copy. One recent improvement in MySQL 8, "instant ADD COLUMN", was actually contributed by Community. The size of the "online log" (that is kept in memory and in temporary file) created per table altered or index created, depends on concurrent DML workload and is hard to predict. For most practical purposes good old pt-online-schema-change or gh-ost tool work better.

  • InnoDB's persistent optimizer statistics

    Automatic statistics recalculation does not work as expected, and to get proper statistics explicit ANALYZE TABLE calls are still needed. The implementation is complicated and introduced separate implicit transactions (in dirty reads mode) against statistics tables. Bugs in the implementation do not seem to get proper priority and are not fixed.
I listed only those features I recently studied in some details in my previous blog posts. I've included main problems with each feature according to my older posts. Click on the links in the list above to find the details.

The Royal Pavilion of InnoDB in MySQL is beautiful from the outside (and somewhere inside), but is far from being completed, and some historical design decisions do not seem to be improved over years. We are lucky that it is still used and works nice for many current purposes, but there are too many dark corners and background threads there where even Oracle engineers rarely look and even less are improving them...

Asynchronous Replication Between MySQL Galera Clusters - Failover and Failback

Galera Cluster enforces strong data consistency, where all nodes in the cluster are tightly coupled. Although network segmentation is supported, replication performance is still bound by two factors:

  • Round trip time (RTT) to the farthest node in the cluster from the originator node.
  • The size of a writeset to be transferred and certified for conflict on the receiver node.

While there are ways to boost the performance of Galera, it is not possible to work around these 2 limiting factors.

Luckily, Galera Cluster was built on top of MySQL, which also comes with its built-in replication feature (duh!). Both Galera replication and MySQL replication exist in the same server software independently. We can make use of these technologies to work together, where all replication within a datacenter will be on Galera while inter-datacenter replication will be on standard MySQL Replication. The slave site can act as a hot-standby site, ready to serve data once the applications are redirected to the backup site. We covered this in a previous blog on MySQL architectures for disaster recovery.

In this blog post, we’ll see how straightforward it is to set up replication between two Galera Clusters (PXC 5.7). Then we’ll look at the more challenging part, that is, handling failures at both node and cluster levels. Failover and failback operations are crucial in order to preserve data integrity across the system.

Cluster Deployment

For the sake of our example, we’ll need at least two clusters and two sites - one for the primary and another one for the secondary. It works similarly to traditional MySQL master-slave replication, but on a bigger scale with three nodes in each site. With ClusterControl, you would achieve this by deploying two separate clusters, one on each site. Then, you would configure asynchronous replication between designed nodes from each cluster.

The following diagram illustrates our default architecture:

We have 6 nodes in total, 3 on the primary site and another 3 on the disaster recovery site. To simplify the node representation, we will use the following notations:

  • Primary site: galera1-P, galera2-P, galera3-P (master)
  • Disaster recovery site: galera1-DR, galera2-DR (slave), galera3-DR

Once the Galera Cluster is deployed, simply pick one node on each site to set up the asynchronous replication link. Take note that ALL Galera nodes must be configured with binary logging and log_slave_updates enabled. Enabling GTID is highly recommended, although not compulsory. On all nodes, configure with the following parameters inside my.cnf:

server_id=40 # this number must be different on every node. binlog_format=ROW log_bin = /var/lib/mysql-binlog/binlog log_slave_updates = ON gtid_mode = ON enforce_gtid_consistency = true expire_logs_days = 7

If you are using ClusterControl, from the web interface, pick Nodes -> the chosen Galera node -> Enable Binary Logging. You might then have to change the server-id on the DR site manually to make sure every node is holding a distinct server-id value.

Then on galera3-P, create the replication user:

mysql> GRANT REPLICATION SLAVE ON *.* to slave@'%' IDENTIFIED BY 'slavepassword';

On galera2-DR, point the slave to the current master, galera3-P:

mysql> CHANGE MASTER TO MASTER_HOST = 'galera3-primary', MASTER_USER = 'slave', MASTER_PASSWORD = 'slavepassword' , MASTER_AUTO_POSITION=1;

Start the replication slave on galera2-DR:

mysql> START SLAVE;

From ClusterControl dashboard, once the replication is established, you should see the DR site has a slave and the Primary site got 3 masters (nodes that produce binary logs):

The deployment is now complete. Applications should send writes to the Primary Site only, as the replication direction goes from Primary Site to DR site. Reads can be sent to both sites, although the DR site might be lagging behind. Assuming that writes only reach the Primary Site, it should not be necessary to set the DR site to read-only (although it can be a good precaution).

This setup will make the primary and disaster recovery site independent of each other, loosely connected with asynchronous replication. One of the Galera nodes in the DR site will be a slave, that replicates from one of the Galera nodes (master) in the primary site. Ensure that both sites are producing binary logs with GTID, and that log_slave_updates is enabled - the updates that come from the asynchronous replication stream will be applied to the other nodes in the cluster.

We now have a system where a cluster failure on the primary site will not affect the backup site. Performance-wise, WAN latency will not impact updates on the active cluster. These are shipped asynchronously to the backup site.

As a side note, it’s also possible to have a dedicated slave instance as replication relay, instead of using one of the Galera nodes as slave.

Node Failover Procedure

In case the current master (galera3-P) fails and the remaining nodes in the Primary Site are still up, the slave on the Disaster Recovery site (galera2-DR) should be directed to any available masters, as shown in the following diagram:

With GTID-based replication, this is peanut. Simply run the following on galera2-DR:

mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST = 'galera1-P', MASTER_AUTO_POSITION=1; mysql> START SLAVE;

Verify the slave status with:

mysql> SHOW SLAVE STATUS\G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Retrieved_Gtid_Set: f66a6152-74d6-ee17-62b0-6117d2e643de:2043395-2047231 Executed_Gtid_Set: d551839f-74d6-ee17-78f8-c14bd8b1e4ed:1-4, f66a6152-74d6-ee17-62b0-6117d2e643de:1-2047231 ...

Ensure the above values are reporting correctly. The executed GTID set should have 2 sets of GTID at this point, one from all transactions executed on the old master and the other for the new master.

Cluster Failover Procedure

If the primary cluster goes down, crashes, or simply loses connectivity from the application standpoint, the application can be directed to the DR site instantly. No database failover is necessary to continue the operation. If the application has connected to the DR site and starts to write, it is important to ensure no other writes are happening on the Primary Site once the DR site is activated.

The following diagram shows our architecture after application is failed over to the DR site:

Assuming the Primary Site is still down, at this point, there is no replication between sites until we re-configure one of the nodes in the Primary Site once it comes back up.

For clean up purposes, the slave process on the DR site has to be stopped. On galera2-DR, stop replication slave:

mysql> STOP SLAVE;

The failover to the DR site is now considered complete.

Cluster Failback Procedure

To failback to the Primary Site, one of the Galera nodes must become a slave to catch up on changes that happened on the DR site. The procedure would be something like the following:

  1. Pick one node to become a slave in the Primary Site (galera3-P).
  2. Stop all nodes other than the chosen one (galera1-P and galera2-P). Keep the chosen slave up (galera3-P).
  3. Create a backup from the new master (galera2-DR) in the DR site and transfer it over to the chosen slave (galera3-P).
  4. Restore the backup.
  5. Start the replication slave.
  6. Start the remaining Galera node in the Primary Site, with grastate.dat removed.

The below steps can then be performed to fail back the system to its original architecture - Primary is the master and DR is the slave.

1) Shut down all nodes other than the chosen slave:

$ systemctl stop mysql # galera1-P $ systemctl stop mysql # galera2-P

Or from the ClusterControl interface, simply pick the node from the UI and click "Shutdown Node".

2) Pick a node in the DR site to be the new master (galera2-DR). Create a mysqldump backup with the necessary parameters (for PXC, pxc_strict_mode has to be set other than ENFORCING):

$ mysql -uroot -p -e 'set global pxc_strict_mode = "PERMISSIVE"' $ mysqldump -uroot -p --all-databases --triggers --routines --events > dump.sql $ mysql -uroot -p -e 'set global pxc_strict_mode = "ENFORCING"'

3) Transfer the backup to the chosen slave, galera3-P via your preferred remote copy tool:

$ scp dump.sql galera3-primary:~

4) In order to perform RESET MASTER on a Galera node, Galera replication plugin must be turned off. On galera3-P, disable Galera write-set replication temporarily and then restore the dump file in the very same session:

mysql> SET GLOBAL pxc_strict_mode = 'PERMISSIVE'; mysql> SET wsrep_on=OFF; mysql> RESET MASTER; mysql> SOURCE /root/dump.sql;

Variable wsrep_on is a session variable. Therefore, we have to perform the restore operation within the same session using SOURCE statement. Otherwise, restoring using standard mysql client would require wsrep_on=OFF or commenting wsrep_provider inside my.cnf set during MySQL startup.

5) Start the replication thread on the chosen slave, galera3-P:

mysql> CHANGE MASTER TO MASTER_HOST = 'galera2-DR', MASTER_USER = 'slave', MASTER_PASSWORD = 'slavepassword', MASTER_AUTO_POSITION = 1; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G

6) Start the remaining of the nodes in the cluster (one node at a time), and force an SST by removing grastate.dat beforehand:

$ rm -Rf /var/lib/mysql/grastate.dat $ systemctl start mysql

Or from ClusterControl, simply pick the node -> Start Node -> check "Perform an Initial Start".

The above will force other Galera nodes to re-sync with galera3-P through SST and get the most up-to-date data. At this point, the replication direction has switched, from DR to Primary. Write operations are coming to the DR site and the Primary Site has become the replicating site:

From ClusterControl dashboard, you would notice the Primary Site has a slave configured while the DR site are all masters. In ClusterControl, MASTER indicator means all Galera nodes are generating binary logs:

7) Optionally, we can clean up slave's entries on galera2-DR since it's already become a master:

mysql> RESET SLAVE ALL;

8) Once the Primary site catches up, we may switch the database traffic from application back to the primary cluster:

At this point, all writes must go to the Primary Site only. The replication link should be stopped as described under the "Cluster Failover Procedure" section above.

The above mentioned failback steps should be applied when staging back the DR site from the Primary Site:

  • Stop replication between primary site and DR site.
  • Re-slave one of the Galera nodes on the DR site to replicate from the Primary Site.
  • Start replication between both sites.

Once done, the replication direction has gone back to its original configuration, from Primary to DR. Writes operations are coming to the Primary Site and the DR Site is now the replicating site:

Finally, perform some clean ups on the newly promoted master by running "RESET SLAVE ALL".

Advantages

Cluster-to-cluster asynchronous replication comes with a number of advantages:

  • Minimal downtime during database failover operation. Basically, you can redirect the write almost instantly to the slave site, only and only if you can protect writes to not reach the master site (as these writes would not be replicated, and will probably be overwritten when re-syncing from the DR site).
  • No performance impact on the primary site since it is independent from the backup (DR) site. Replication from master to slave is performed asynchronously. The master site generates binary logs, the slave site replicates the events and applies the events at some later time.
  • Disaster recovery site can be used for other purposes, e.g., database backup, binary logs backup and reporting or heavy analytical queries (OLAP). Both sites can be used simultaneously, with exceptions on the replication lag and read-only operations on the slave side.
  • The DR cluster could potentially run on smaller instances in a public cloud environment, as long as they can keep up with the primary cluster. The instances can be upgraded if needed. In certain scenarios, it can save you some costs.
  • You only need one extra site for Disaster Recovery, as opposed to active-active Galera multi-site replication setup, which requires at least 3 sites to operate correctly.
Disadvantages Related resources  Architecting for Failure - Disaster Recovery of MySQL/MariaDB Galera Cluster  Architecting for Failure - Disaster Recovery of MySQL/MariaDB Galera Cluster  How to Control Replication Failover for MySQL and MariaDB

There are also drawbacks having this setup:

  • There is a chance of missing some data during failover if the slave was behind, since replication is asynchronous. This could be improved with semi-synchronous and multi-threaded slaves replication, albeit there will be another set of challenges waiting (network overhead, replication gap, etc).
  • Despite the failover operation being fairly simple, the failback operation can be tricky and prone to human error. It requires some expertise on switching master/slave role back to the primary site. It's recommended to keep the procedures documented, rehearse the failover/failback operation regularly and use accurate reporting and monitoring tools.
  • There is no built-in failure detection and notification process. You may need to automate and send out notifications to the relevant person once the unwanted event occurs. One good way is to regularly check the slave's status from other available node's point-of-view on the master's site before raising an alarm for the operation team.
  • Pretty costly, as you have to setup a similar number of nodes on the disaster recovery site. This is not black and white, as the cost justification usually comes from the requirements of your business. With some planning, it is possible to maximize usage of database resources at both sites, regardless of the database roles.
Tags:  galera MySQL MariaDB asynchronous replication failover failback

Advanced MySQL JSON_TABLE

JSON_TABLE is one of the more complex functions that arrived in MySQL 8.  It takes schemaless JSON data and turns it into a relational table. So your NoSQL data becomes SQL data and you can use good ol' SQL where clauses on that temporary relational table! 

I stated writing about JSON_TABLE here and here last December.  And you can find details on using JSON_TABLE in my book.  The following examples cover what to do when key/value pairs are missing or bad, traversing nested paths, and adding an ordinal number to nested values.  These operations provide a great deal of muscle when wrestling NoSQL data into a relational format.
JSON_TABLEThe first argument to JSON_TABLE is the name of the JSON column in the table.  In the following example '$" denotes the entire JSON data type column or JSON document. 

COLUMNS is used to call out the various key/values and assign them to a temporary 'column' and a definition of that column.  In this example the JSON key Population is being called out as an integer to be named Pop.  Oh, if the values are missing for Population, JSON_TABLE will insert the DEFAULT value, here 999, to the results. Or if there is an error in the data, like a string instead of something integer-ish, it output a NULL, a valid JSON string, or the DEFAULT value, which is used in this case.  And if the field is empty (NULL), a valid JSON string, or the DEFAULT value, as you direct -- '987' in the example that follows.
Example 1 -Handling bad or missing dataThis example has four records where two of the records need some direction.  The has a bad value ('fish') and an empty value (no Population key in the JSON column). 

mysql> SELECT name, 
          Info->>"$.Population", 
          Pop FROM city2,   
          JSON_TABLE(Info,"$" COLUMNS 
          ( Pop INT PATH "$.Population" 
          DEFAULT '999' 
          ON ERROR DEFAULT 
          '987' ON EMPTY))  
         AS x1;
+-------+-----------------------+------+
| name  | Info->>"$.Population" | Pop  |
+-------+-----------------------+------+
| alpha | 100                   |  100 |
| beta  | fish                  |  999 |
| delta | 15                    |   15 |
| gamma | NULL                  |  987 |
+-------+-----------------------+------+
4 rows in set, 1 warning (0.00 sec)

So 'fish' becomes '999' and NULL becomes '987'.
Example 2Nested values in JSON data are very common and there is a NESTED PATH operator to allow extracting those values.  The restaurants data set from the MongoDB world has some ratings scores in an array named 'grades' that are nested.

{"_id": "00005b2176ae0000000000000001",
"name": "Morris Park Bake Shop",
"grades": [
{"date": {"$date": 1393804800000}, "grade": "A", "score": 2}, 
{"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, 
{"date": {"$date": 1358985600000}, "grade": "A", "score": 10}, 
{"date": {"$date": 1322006400000}, "grade": "A", "score": 9}, 
{"date": {"$date": 1299715200000}, "grade": "B", "score": 14}],
"address": {"coord": [-73.856077, 40.848447],
"street": "Morris Park Ave",
"zipcode": "10462", "
"cuisine": "Bakery",
"restaurant_id": "30075445"}

The NESTED PATH operator allows access to each of the grades.

mysql> select aaaa.*  from restaurants,  
             json_table(doc, "$" COLUMNS
             (name char(50) path "$.name",   
              style varchar(50) path "$.cuisine",  
             NESTED PATH '$.grades[*]' COLUMNS 
             (Grading char(10) path "$.grade", 
             Score INT path "$.score"))) 
      as aaaa ;
+--------------------------------+------------+---------+-------+
| name                           | style      | Grading | Score |
+--------------------------------+------------+---------+-------+
| Morris Park Bake Shop          | Bakery     | A       |     2 |
| Morris Park Bake Shop          | Bakery     | A       |     6 |
| Morris Park Bake Shop          | Bakery     | A       |    10 |
| Morris Park Bake Shop          | Bakery     | A       |     9 |
| Morris Park Bake Shop          | Bakery     | B       |    14 |
| Wendy'S                        | Hamburgers | A       |     8 |
| Wendy'S                        | Hamburgers | B       |    23 |
| Wendy'S                        | Hamburgers | A       |    12 |
| Wendy'S                        | Hamburgers | A       |    12 |
| Dj Reynolds Pub And Restaurant | Irish      | A       |     2 |
+--------------------------------+------------+---------+-------+
10 rows in set (0.00 sec)

From here the average scores could be computed with a Windowing Function and a Common Table Expression. A big tip of the hat to LeFred for the following

WITH cte1 AS (SELECT doc->>"$.name" AS 'name',
doc->>"$.cuisine" AS 'cuisine',
        (SELECT AVG(score) FROM 
        JSON_TABLE(doc, "$.grades[*]"
        COLUMNS (score INT PATH "$.score")) as r ) AS avg_score
 FROM restaurants)
 SELECT *, rank() OVER 
  (PARTITION BY cuisine ORDER BY avg_score) AS `rank`
  FROM cte1 
  ORDER by `rank`, avg_score DESC limit 10;


Example 3 Not all the restaurant have the same number of grades. But it is very easy to see the number of grades. We can get ordinal numbers next to each grade with  FOR ORDINALITY

mysql> select aaaa.name, aaaa.ordinal, aaaa.Grading          FROM restaurants,  json_table(doc, "$" COLUMNS(          name char(50) path "$.name",             style varchar(50) path "$.cuisine",            NESTED PATH '$.grades[*]'    COLUMNS (            ordinal FOR ORDINALITY,             Grading char(10) path "$.grade",             Score INT path "$.score")))          as aaaa  limit 10; +--------------------------------+---------+---------+ | name                           | ordinal | Grading | +--------------------------------+---------+---------+ | Morris Park Bake Shop          |       1 | A       | | Morris Park Bake Shop          |       2 | A       | | Morris Park Bake Shop          |       3 | A       | | Morris Park Bake Shop          |       4 | A       | | Morris Park Bake Shop          |       5 | B       | | Wendy'S                        |       1 | A       | | Wendy'S                        |       2 | B       | | Wendy'S                        |       3 | A       | | Wendy'S                        |       4 | A       | | Dj Reynolds Pub And Restaurant |       1 | A       | +--------------------------------+---------+---------+ 10 rows in set (0.00 sec)
Conclusion JSON_TABLE provides a way to structure and manipulate unstructured, NoSQL data.  It is a powerful way to present data at the database level that might hamper an application.  Plus it provides a best of both the SQL and NoSQL worlds that leverages the best of MySQL.

Webinar Weds 7/25: XA Transactions

Please join Percona Senior MySQL DBA for Managed Services, Dov Endress, as he presents XA Transactions on Wednesday, July 25th, 2018 at 12:00 PM PDT (UTC-7) / 3:00 PM EDT (UTC-4).

Register Now

Distributed transactions (XA) are becoming more and more vital as applications evolve. In this webinar, we will learn what distributed transactions are and how MySQL implements the XA specification. We will learn the investigatory and debugging techniques necessary to ensure high availability and data consistency across disparate environments.

This webinar is not intended to be an in-depth look at transaction managers, but focuses on resource managers only. It is primarily intended for database administrators and site reliability engineers.

Register Now

Dov Endress, Senior MySQL DBA in Managed Services

Dov joined Percona in the fall of 2015 as a senior support engineer. He learned BASIC in elementary school on an Apple IIE, and his first computer was a Commodore 64. Dov started working in the LAMP stack in 1999 and has been doing so ever since. He lives in northern Nevada with his wife, step-daughter, grandson, a clowder of cats and Macy – the best dog a person could meet. In his free time, he can be found somewhere outdoors or making things in the garage.

The post Webinar Weds 7/25: XA Transactions appeared first on Percona Database Performance Blog.

Mastering Continuent Clustering Series: Manual Switch Behavior Tuning in the Tungsten Connector

In this blog post, we talk about how existing client connections are handled by the Tungsten Connector when a manual master role switch is invoked and how to adjust that behavior.

When a graceful switch is invoked via cctrl or the Tungsten Dashboard, by default the Connector will wait for five (5) seconds to allow in-flight activities to complete before forcibly disconnecting all active connections from the application side, no matter what type of query was in use.

If connections still exist after the timeout interval, they are forcibly closed, and the application will get back an error.

This configuration setting ONLY applies to a manual switch. During a failover caused by loss of MySQL availability, there is no wait and all connections are force-closed immediately.

This timeout is adjusted via the tpm option --connector-disconnect-timeout (connector-disconnect-timeout inside INI files).

If you increase this value, you delay the manual switch!

ONLY change this if you accept the fact that the manual switch process will last at least as long as this setting in seconds.

Please note that updating these values require a connector restart (usually happens automatically via tpm update) for the changes to be recognized. This wil disconnect all existing client application sessions.

Do not set this value to zero (0) or there will be no attempt to disconnect at all.

If you wish to disable the wait entirely, set --property=waitForDisconnect=false in your configuration on the connector nodes and run tpm update.

For example, to change the delay to 10 seconds instead of the default 5 seconds:

shell> ./tools/tpm configure alpha --connector-disconnect-timeout=10 shell> ./tools/tpm update

You may grep for the setting waitForDisconnectTimeout located in cluster-home/conf/router.properties to confirm the change has been applied.

Click here for the documentation page: Adjusting the Client Disconnect Delay During Manual Switch

In future articles, we will continue to cover more advanced subjects of interest!

Questions? Contact Continuent

Setting up MySQL Encrypted Replication on MySQL 5.7 with GTID

In this blog post, I’ll walk you through setting up encrypted replication on MySQL 5.7 with GTID enabled. I will walk you through how to create sample certificates and keys, and then configure MySQL to only use replication via an encrypted SSL tunnel.

For simplicity, the credentials and certificates I used in this tutorial are very basic. I would suggest, of course, you use stronger passwords and accounts.

Let’s get started.

Create a folder where you will keep the certificates and keys

mkdir /etc/newcerts/ cd /etc/newcerts/

Create CA certificate

[root@po-mysql2 newcerts]# openssl genrsa 2048 > ca-key.pem Generating RSA private key, 2048 bit long modulus .............+++ ..................+++ e is 65537 (0x10001) [root@po-mysql2 newcerts]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []: Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []: Email Address []:

Create server certificate

server-cert.pem = public key, server-key.pem = private key

NOTE: The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate otherwise the certificate and key files will not work for servers compiled using OpenSSL.

[root@po-mysql2 newcerts]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem Generating a 2048 bit RSA private key ....................................................................+++ .+++ writing new private key to 'server-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []: Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []:server Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@po-mysql2 newcerts]# openssl rsa -in server-key.pem -out server-key.pem writing RSA key [root@po-mysql2 newcerts]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem Signature ok subject=/C=XX/L=Default City/O=Default Company Ltd/CN=server Getting CA Private Key

Create client certificate

client-cert.pem = public key, client-key.pem = private key

NOTE: The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate otherwise the certificate and key files will not work for servers compiled using OpenSSL.

[root@po-mysql2 newcerts]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem Generating a 2048 bit RSA private key .....................+++ ....................................................................................+++ writing new private key to 'client-key.pem' ----- You are about to be asked to enter information that will be incorporated into your certificate request. What you are about to enter is what is called a Distinguished Name or a DN. There are quite a few fields but you can leave some blank For some fields there will be a default value, If you enter '.', the field will be left blank. ----- Country Name (2 letter code) [XX]: State or Province Name (full name) []: Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []:client Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@po-mysql2 newcerts]# openssl rsa -in client-key.pem -out client-key.pem writing RSA key [root@po-mysql2 newcerts]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem Signature ok subject=/C=XX/L=Default City/O=Default Company Ltd/CN=client Getting CA Private Key

Verify both client and server certificates

[root@po-mysql2 newcerts]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK

Copy certificates, adjust permissions and restart MySQL

Add the server cert files and key to all hosts.
Add the entry below to my.cnf on all hosts.
Make sure the folder and files are owned by MySQL user and group.
Restart MySQL.

scp *.pem master:/etc/newcerts/ scp *.pem slave:/etc/newcerts/ chown -R mysql:mysql /etc/newcerts/ [mysqld] ssl-ca=/etc/newcerts/ca.pem ssl-cert=/etc/newcerts/server-cert.pem ssl-key=/etc/newcerts/server-key.pem service mysql restart

Verify SSL is enabled and key and certs are shown (check both master and slave)

(root@localhost) [(none)]>SHOW VARIABLES LIKE '%ssl%'; +---------------+-------------------------------+ | Variable_name | Value | +---------------+-------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/newcerts/ca.pem | | ssl_capath | | | ssl_cert | /etc/newcerts/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/newcerts/server-key.pem | +---------------+-------------------------------+ 9 rows in set (0.01 sec)

Verify you are able to connect from slave to master

From command line, issue the following commands and look for this output:
“SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256”

[root@po-mysql2 ~]# mysql -urepluser -p -P53306 --host po-mysql1 --ssl-cert=/etc/newcerts/client-cert.pem --ssl-key=/etc/newcerts/client-key.pem -e '\s' Enter password: -------------- mysql Ver 14.14 Distrib 5.7.21-20, for Linux (x86_64) using 6.2 Connection id: 421 Current database: Current user: repluser@192.168.56.101 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.21-21-log Percona Server (GPL), Release 21, Revision 2a37e4e Protocol version: 10 Connection: po-mysql1 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 53306 Uptime: 13 min 38 sec Threads: 6 Questions: 6138 Slow queries: 4 Opens: 112 Flush tables: 1 Open tables: 106 Queries per second avg: 7.503 --------------

Enable encrypted replication.

We are using GTID in this example, so adjust the command below if you are not using GTID based replication.
Go to the slave host and run the following: (details below)
stop slave
change master
start slave
verify replication is working and using an encrypted connection

(root@localhost) [(none)]>select @@hostname; +------------+ | @@hostname | +------------+ | po-mysql2 | +------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]>STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec) (root@localhost) [(none)]>CHANGE MASTER TO MASTER_HOST="po-mysql1", MASTER_PORT=53306, MASTER_USER="repluser", MASTER_AUTO_POSITION = 1, MASTER_PASSWORD='replpassword', -> MASTER_SSL=1, MASTER_SSL_CA = '/etc/newcerts/ca.pem', MASTER_SSL_CERT = '/etc/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/newcerts/client-key.pem'; Query OK, 0 rows affected, 2 warnings (0.16 sec) (root@localhost) [(none)]>START SLAVE; Query OK, 0 rows affected (0.01 sec) (root@localhost) [(none)]>SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: po-mysql1 Master_User: repluser Master_Port: 53306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 491351 Relay_Log_File: relay.000002 Relay_Log_Pos: 208950 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 257004 Relay_Log_Space: 443534 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/newcerts/ca.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/newcerts/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/newcerts/client-key.pem Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Reading event from the relay log Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:82150-83149 Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842, 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-82620, 85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3, cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-3 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Congratulations, you have configured encrypted replication

This process was only to enable SSL replication; however, if you wish to limit replication to only use SSL connections, you’ll need to alter the replication account accordingly, as shown below.

Go to the master and alter the replication user.

NOTE: For some reason, the SHOW GRANTS command does not show REQUIRE SSL as part of the output, even after changing the account

(root@localhost) [(none)]>SHOW GRANTS FOR 'repluser'@'%'; +----------------------------------------------+ | Grants for repluser@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]>ALTER USER 'repluser'@'%' REQUIRE SSL; Query OK, 0 rows affected (0.04 sec) (root@localhost) [(none)]>SHOW GRANTS FOR 'repluser'@'%'; +----------------------------------------------+ | Grants for repl@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' | +----------------------------------------------+ 1 row in set (0.00 sec)

Test from a slave which has not yet been configured to use encrypted replication.

Notice the error below from this slave, so we know for sure, we can only connect via SSL and replication will not work until we make the required changes:

Last_IO_Error: error connecting to master ‘repluser@po-mysql1:53306’ – retry-time: 60 retries: 1

(root@localhost) [(none)]>select @@hostname; +------------+ | @@hostname | +------------+ | po-mysql3 | +------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]>stop slave; Query OK, 0 rows affected (0.00 sec) (root@localhost) [(none)]>start slave; Query OK, 0 rows affected (0.01 sec) (root@localhost) [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: po-mysql1 Master_User: repluser Master_Port: 53306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 730732 Relay_Log_File: relay.000003 Relay_Log_Pos: 730825 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 730732 Relay_Log_Space: 7465275 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'repluser@po-mysql1:53306' - retry-time: 60 retries: 1 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 180719 23:29:07 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:66868-83690 Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842, 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-83690, 85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3, cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-134 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Setup encrypted replication on another slave

Now we just need to follow the same steps as documented above to copy the certs and keys. We restart MySQL, stop slave and reset replication, and then replication will work again, this time using SSL.

(root@localhost) [(none)]>SELECT @@hostname; +------------+ | @@hostname | +------------+ | po-mysql3 | +------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]>STOP SLAVE; Query OK, 0 rows affected (0.02 sec) (root@localhost) [(none)]>CHANGE MASTER TO MASTER_HOST="po-mysql1", MASTER_PORT=53306, MASTER_USER="repluser", MASTER_AUTO_POSITION = 1, MASTER_PASSWORD='r3pl', -> MASTER_SSL=1, MASTER_SSL_CA = '/etc/newcerts/ca.pem', MASTER_SSL_CERT = '/etc/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/newcerts/client-key.pem'; Query OK, 0 rows affected, 2 warnings (0.01 sec) (root@localhost) [(none)]>START SLAVE; Query OK, 0 rows affected (0.04 sec) (root@localhost) [(none)]>SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: po-mysql1 Master_User: repluser Master_Port: 53306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 1128836 Relay_Log_File: relay.000002 Relay_Log_Pos: 398518 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1128836 Relay_Log_Space: 398755 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/newcerts/ca.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/newcerts/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/newcerts/client-key.pem Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:83691-84588 Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842, 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-84588, 85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3, cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-134 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

Congratulations, you now have SSL replication enabled. MySQL replication will now only work with encryption.

30 mins with MySQL JSON functions

JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases. Starting with 5.7 MySQL implemented a native JSON data type and a set of JSON functions that allows you to perform operations on JSON values.

Problems with Oracle's Way of MySQL Bugs Database Maintenance

In one of my previous posts I stated that Oracle does not care enough to maintain public MySQL bugs database properly. I think it's time to explain this statement in details.

The fact that https://bugs.mysql.com/ still exists and community bug reports there are still processed on a regular basis by my former colleagues, Miguel Solorzano, Sinisa Milivojevic, Umesh Shastry, Bogdan Kecman and others, is awesome. Some probably had not expected this to still be the case for 8+ years since Oracle took over the software and procedures around it. My former bugs verification team still seems to exist and even get some new members. Moreover, today we have less "Open" bugs than 6 years ago, when I was preparing to leave Oracle to join (and build) the best MySQL support team in the industry elsewhere...

That's all good and beyond the best expectation of many. Now, what's wrong then with the way Oracle engineers process community bug reports these days?

On the photo above that I made last autumn we see the West Pier in Brighton, England. It keeps collapsing after major damages it got in 2002 and 2003 from storms and fires, and this spring I've seen even more ruins happened. Same happens to MySQL public bugs database - we see it is used less than before, and severe damage to the Community is made by some usual and even relatively simple actions and practices. Let me summarize them.
  1. "Security" bugs are handled in such a way that they never becomes public back, even after the problem is fixed in all affected versions and this fix is documented.

    Moreover, often it takes bug reporter to check "security" flag by mistake or for whatever reason for nobody else ever to be able to find out what exactly the bug was about. This is done even in cases when all other vendors keep the information public or open it after the fix is published. Even worse, sometimes when somebody "escalates" some public bug forgotten for a long time (or wrongly handled) to Oracle engineers, in public, the bug immediately becomes private, even though nobody cared about it for months before. I have complained about this many times everywhere.
    I would so much like to publish all the details of mishandling of Bug #91118, for example, but the bug was made private, so you'd have to just trust my words and quotes, while I prefer to provide arguments everyone can verify... Remember the bug number though and this (top) part of the stack trace:
    ...
    /home/openxs/dbs/8.0/bin/ mysqld(row_search_mvcc(unsigned char*,
    page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x2b76)
    [0x1dc78a6]
    /home/openxs/dbs/8.0/bin/mysqld(ha_innobase::general_fetch(unsigned
    char*, unsigned int, unsigned int)+0x15f) [0x1c98c1f]
    /home/openxs/dbs/8.0/bin/mysqld(handler::ha_index_next_same(unsigned
    char*, unsigned char const*, unsigned int)+0x1e4) [0xe909a4]
    /home/openxs/dbs/8.0/bin/mysqld() [0xc5636a]
    ... The practice of hiding bugs once and forever was advocated by some MySQL engineers well before Oracle's acquisition, but in Oracle it became a rule that only bravest engineers sometimes can afford NOT to follow.

  2. Some older bug reports are never processed or never revisited back by Oracle engineers.

    Consider this simple example, Bug #70237 - "the mysqladmin shutdown hangs". It was reported almost 5 years ago by a famous Community member, Zhai Weixiang, who even suggested a patch. This bug had not got a single public comment from anyone in Oracle.
    The oldest open server bug today is Bug #44411  - "some Unicode text garbled in LOAD DATA INFILE with user variables". It was reported more than 9 years ago. Moreover, it was once "Verified", but then silently became "Not a bug" and then was reopened by the bug reporter. Nobody cares, even though it's clear that many Oracle engineers should get notification emails whenever any change to public bug report happens. This is also fundamentally wrong, no matter what happened to assignee or engineer who worked on the bug in the past.

    This specific problem with bugs handling is not new, we always had a backlog of bugs to verify and some bugs were re-checked maybe once in many years, but Oracle now has all kinds of resources to fix this problem, and not just reduce the number of open reports by closing them by all fair and not that fair means... See the next item also.

  3. Recently some bug reports are handled wrongly, with a trend of wasting bug reporter time on irrelevant clarifications or closing the bug too early when there is a problem to reproduce it.

    If you report some MySQL problem to Oracle, be ready to see your report closed soon with a suggestion to contact Support. Check this recent example, Bug #90375 - "Significantly improve performance of simple functions". OK, this is a known limitation, but user suggested several workarounds that are valid feature requests for optimizer, that can be smart enough to inline the stored function if it just returns some simple expression. Why not to verify this as a valid feature request?
    Another great example is Bug #80919 - "MySQL Crashes when Droping Indexes - Long semaphore wait". It was closed (after wasting more than a year on waiting) as a "Duplicate" with such a nice comment:"[16 Jan 15:53] Sinisa Milivojevic Hi!

    This is a duplicate bug, because it is very similar to an internal-only bug, that is not present in the public bugs database.

    I will provide the internal bug number in the hidden comment, but will update this page with public comment, once when that bug is fixed.
    " No reference to the internal bug number, just nothing. If you are wondering what is the real problem, check MDEV-14637.
    Yet another case of "Not a bug", where the decision is questionable and further statements from the bug reporter are ignored is Bug #89065 - "sync_binlog=1 on a busy server and slow binary log filesystem stalls slaves".
    The bug may be "Verified" finally, but after some time wasted on discussions and clarifications when the problem is obvious and bug report contains everything needed to understand this. Nice example is Bug #91386 - "Index for group-by is not used with primary key for SELECT COUNT(DISTINCT a)". Yet another example is Bug #91010 - "WolfSSL build broken due to cmake typo". Knowing parties involved in that discussions in person, I wish they spent their time on something more useful than arguing on the obvious problems.

    This practice discourage users from reporting bugs to Oracle. Not that bug handling mistakes never happened before Oracle (I did many myself), but recently I see more and more wrongly handled bugs. This trend is scary!

  4. Oracle mostly fixes bugs reported internally.

    Just check any recent Release Notes and make your own conclusion. One may say it means that internal QA and developers find bugs even before Community notices them, but the presence of all kinds of test failures, regression bugs etc in the same recent versions still tells me that Community QA is essential for MySQL quality. But it does not set the agenda for the bug fixing process, for many years.
    One may also say that bug fixing agenda is defined by Oracle customers mostly. I let Oracle customers to comment here if they are happy with what they get. Some of the were not so happy with the speed of resolution even for their real security related bugs.
I can continue with more items, but let's stop for now.

If we want MySQL public bugs database to never reach the state of the West Pier (declared to be beyond repair in 2004), we should force Oracle to do something to fix the problems above. Otherwise we'll see further decline of bugs database and Community activity switched elsewhere (to Percona's and MariaDB's public bugs databases, or somewhere else). It would be sad to not have any central public location for all problem reports about core MySQL server functionality...

Auditing MariaDB for Secured Database Infrastructure Operations

When you are building Database Infrastructure for an data sensitive business (like financial services, digital commerce, advertising media solutions, healthcare etc. ) governed by compliance and policies, You are expected to maintain the audit log of the transactions to investigate, if you ever suspect something unacceptable (i.e., user updating / deleting data) happening to your database . MariaDB provides Audit Plugin (MariaDB started including by default the Audit Plugin from versions 10.0.10 and 5.5.37, and it can be installed in any version from MariaDB 5.5.20.) to log the server activity, Although the MariaDB Audit Plugin has some unique features available only for MariaDB, it can be used also with MySQL. MariaDB Audit Plugin log the details like who connected to server (i.e., username and host), what queries were executed, the tables accessed and server variables changed. This information is retained in a rotating log file or sent to local syslogd. This blog is a fully hands-on guide to “Auditing MariaDB for Secured Database Infrastructure Operations”.

MariaDB Audit Plugin installation

The MariaDB Audit Plugin is provided as a dynamic library: server_audit.so (server_audit.dll for Windows). The file path of the plugin library is stored in the plugin_dir system variable:

MariaDB [(none)]> select @@plugin_dir; +--------------------------+ | @@plugin_dir | +--------------------------+ | /usr/lib64/mysql/plugin/ | +--------------------------+ 1 row in set (0.000 sec)

One way to install this plug-in is to execute the INSTALL SONAME statement while logged into MariaDB. You must use an administrative account with INSERT privilege for the mysql.plugin table:

MariaDB [(none)]> INSTALL SONAME 'server_audit';

Loading Plugin at Start-Up

You can also load the plugin from the command-line as a startup parameter by configuring my.cnf or my.ini in /etc/my.cnf or /etc/mysql/my.cnf , We have copied below the configuration of my.cnf for enabling MariaDB Audit Plugin (please add these variables after [mysqld] or [mariadb] ):

plugin_load=server_audit=server_audit.so server_audit_events=CONNECT,QUERY,TABLE server_audit_logging=ON server_audit=FORCE_PLUS_PERMANENT

We don’t want somebody uninstall MariaDB Audit Plugin so enabled system variable, server_audit=FORCE_PLUS_PERMANENT , The example below explains this scenario much better:

MariaDB [(none)]> UNINSTALL PLUGIN server_audit; ERROR 1702 (HY000): Plugin 'server_audit' is force_plus_permanent and can not be unloaded

To see the list of audit plugin-related variables in your MariaDB server, execute the command below:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_audit%'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | server_audit_events | CONNECT,QUERY,TABLE | | server_audit_excl_users | | | server_audit_file_path | server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 1000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_logging | ON | | server_audit_mode | 0 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+-----------------------+ 15 rows in set (0.002 sec)

Uncontrolled MariaDB Audit Plugins are major concerns in any MariaDB database infrastructure operations, I strongly recommend our customers to consider log rotate “server_audit.log” file, You can force a rotation by enabling the server_audit_file_rotate_now :

MariaDB [(none)]> SET GLOBAL server_audit_file_rotate_now = ON; Query OK, 0 rows affected (0.015 sec)

You can configure the size limit of MariaDB Audit Plugin by setting variable, server_audit_file_rotate_size . To limit the number of log files created, set the variable, server_audit_file_rotations. To force log file rotations you can set the variable, server_audit_file_rotate_now to ON:

[mariadb] .. server_audit_file_rotate_now=ON server_audit_file_rotate_size=1000000 server_audit_file_rotations=10 ...

MariaDB Audit Plugin report:

[root@localhost mysql]# tail -f server_audit.log 20180720 20:39:22,localhost.localdomain,root,localhost,13,1501,QUERY,,'SELECT DATABASE()',0 20180720 20:39:22,localhost.localdomain,root,localhost,13,1503,QUERY,sakila,'show databases',0 20180720 20:39:22,localhost.localdomain,root,localhost,13,1504,QUERY,sakila,'show tables',0 20180720 20:39:27,localhost.localdomain,root,localhost,13,1528,QUERY,sakila,'show tables',0 20180720 20:39:43,localhost.localdomain,root,localhost,13,1529,READ,sakila,customer, 20180720 20:39:43,localhost.localdomain,root,localhost,13,1529,QUERY,sakila,'select * from customer limit 100',0 20180720 20:39:52,localhost.localdomain,root,localhost,13,1530,QUERY,sakila,'show tables',0 20180720 20:40:07,localhost.localdomain,root,localhost,13,1531,READ,sakila,actor, 20180720 20:40:07,localhost.localdomain,root,localhost,13,1531,QUERY,sakila,'select * from actor limit 100',0 20180720 20:40:30,localhost.localdomain,root,localhost,13,0,DISCONNECT,sakila,,0

Conclusion

We recommend most of our customers (using MariaDB) to enable MariaDB Audit Plugin to closely monitor what is happening to their database infrastructure, This really helps to proactively troubleshoot if anything going wrong with their MariaDB operations. Reliable and secured database operations is equally important like performance and scalability.

The post Auditing MariaDB for Secured Database Infrastructure Operations appeared first on MySQL Consulting, Support and Remote DBA Services.

MySQL Router HA with Keepalived

After having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after having illustrated how to use Pacemaker, this article explains how to setup HA for MySQL Router using keepalived.

Keepalived is very popular, maybe because it’s also very easy to use. We can of course use 2 or more servers. The principle is the same as on the previous articles, if the router dies, the virtual IP used by the application server(s) to connect to MySQL is sent to another machine where mysqlrouter is still running.

Let’s have a look at the configuration, in this case we use 2 machines, mysql1 and mysql2.

Configuration

Let’s configure our 2 routers. The configuration file is /etc/keepalived/keepalived.conf and the VIP are using to contact the router is 192.168.87.5

We also have to decide which one will act as master and which one will the backup: mysqlrouter1 will be the master and mysqlrouter2 the backup.

mysqlrouter1 global_defs { notification_email { lefred @ lefred.be } notification_email_from mycluster @ lefred.be smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script chk_mysqlrouter { script "/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process interval 2 # every 2 seconds weight 2 # add 2 points if OK fall 2 } vrrp_instance VI_1 { state MASTER interface eth1 virtual_router_id 51 priority 102 advert_int 1 virtual_ipaddress { 192.168.87.5 } track_script { chk_mysqlrouter } }

The important here is the state which is set to MASTER and the priority.

mysqlrouter2 global_defs { notification_email { lefred @ lefred.be } notification_email_from mycluster @ lefred.be smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_script chk_mysqlrouter { script "/bin/killall -0 /usr/bin/mysqlrouter" # check the haproxy process interval 2 # every 2 seconds weight 2 # add 2 points if OK fall 2 } vrrp_instance VI_1 { state BACKUP interface eth1 virtual_router_id 51 priority 101 advert_int 1 virtual_ipaddress { 192.168.87.5 } track_script { chk_mysqlrouter } }

We can see that the state and the priority are different.

Now we can start keepalived and see that the VIP will be enabled on the master (mysqlrouter1):

[root@mysqlrouter1 ~]# systemctl start keepalived [root@mysqlrouter1 ~]# ip add sho eth1 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 08:00:27:ab:eb:b4 brd ff:ff:ff:ff:ff:ff inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1 valid_lft forever preferred_lft forever inet 192.168.87.5/32 scope global eth1 valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:feab:ebb4/64 scope link valid_lft forever preferred_lft forever

We can see that 192.168.87.5 is now available on eth1.

If we stop mysqlrouter on mysqlrouter1, we will see after 2 seconds max that the ip will be moved to mysqlrouter2:

[root@mysqlrouter1 ~]# systemctl stop mysqlrouter [root@mysqlrouter1 ~]# ip add sho eth1 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 08:00:27:ab:eb:b4 brd ff:ff:ff:ff:ff:ff inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1 valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:feab:ebb4/64 scope link valid_lft forever preferred_lft forever [root@mysqlrouter2 ~]# ip add sho eth1 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 08:00:27:f9:23:f1 brd ff:ff:ff:ff:ff:ff inet 192.168.87.4/24 brd 192.168.87.255 scope global eth1 valid_lft forever preferred_lft forever inet 192.168.87.5/32 scope global eth1 valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:fef9:23f1/64 scope link valid_lft forever preferred_lft forever Conclusion

Once again, we reached MySQL Router High Availability easily, very quickly and with standard tools included in most GNU/Linux distributions.

Let me know if you are interested in other open source tools available to achieve MySQL Router HA.

MySQL Router HA with Pacemaker

This article will explain how to setup HA for MySQL Router as described in a previous article about where should the router stand.

For this setup, I will use Pacemaker (part of RedHat High Availability Add-on and available on RHEL, CentOS, Oracle Linux, …).

Of course we need a MySQL InnoDB Cluster but we won’t really use it for the HA setup of the MySQL Router.

Installing Pacemaker

The first step is to install pacemaker on all the machines we will use for our “MySQL Router Cluster”:

# yum install pacemaker pcs resource-agents

Now we need to start the pcsd service and enable it at boot (on all machines):

# systemctl start pcsd.service # systemctl enable pcsd.service

It’s time now to setup authentication, this operation is again executed on all machines part of the MySQL Router Cluster:

# echo MyStrongPassw0rd | passwd --stdin hacluster Changing password for user hacluster. passwd: all authentication tokens updated successfully.

And on one of the nodes, we can now execute the following command:

[root@mysql1 ~]# pcs cluster auth mysql1 mysql2 mysql3 -u hacluster -p MyStrongPassw0rd --force mysql1: Authorized mysql2: Authorized mysql3: Authorized

Note that mysql1, mysql2 and mysql3 are the 3 nodes that will be used for the MySQL Router Cluster.

We will now create the cluster (the name cannot exceed 15 chars) by launching the following command on one node:

[root@mysql1 ~]# pcs cluster setup --force --name routercluster mysql1 mysql2 mysql3

The output of the command should looks like this:

Destroying cluster on nodes: mysql1, mysql2, mysql3... mysql3: Stopping Cluster (pacemaker)... mysql1: Stopping Cluster (pacemaker)... mysql2: Stopping Cluster (pacemaker)... mysql1: Successfully destroyed cluster mysql2: Successfully destroyed cluster mysql3: Successfully destroyed cluster Sending 'pacemaker_remote authkey' to 'mysql1', 'mysql2', 'mysql3' mysql1: successful distribution of the file 'pacemaker_remote authkey' mysql3: successful distribution of the file 'pacemaker_remote authkey' mysql2: successful distribution of the file 'pacemaker_remote authkey' Sending cluster config files to the nodes... mysql1: Succeeded mysql2: Succeeded mysql3: Succeeded Synchronizing pcsd certificates on nodes mysql1, mysql2, mysql3... mysql1: Success mysql2: Success mysql3: Success Restarting pcsd on the nodes in order to reload the certificates... mysql3: Success mysql1: Success mysql2: Success Starting the cluster

We can now start the cluster (running the following command on one node):

[root@mysql1 ~]# pcs cluster start --all mysql3: Starting Cluster... mysql1: Starting Cluster... mysql2: Starting Cluster...

And we can already check its status:

[root@mysql1 ~]# crm_mon -1 Stack: corosync Current DC: mysql1 (version 1.1.18-11.el7_5.3-2b07d5c5a9) - partition with quorum Last updated: Fri Jul 20 10:00:36 2018 Last change: Fri Jul 20 10:00:08 2018 by hacluster via crmd on mysql1 3 nodes configured 0 resources configured Online: [ mysql1 mysql2 mysql3 ] No active resources Properties

Now we can set some properties to our cluster:

[root@mysql1 ~]# pcs property set stonith-enabled=false [root@mysql1 ~]# pcs property set no-quorum-policy=ignore [root@mysql1 ~]# pcs resource defaults migration-threshold=1 Adding resources VIP

The first resource we will use is the VIP (Virtual IP) that the application will use to connect to MySQL (to the router):

[root@mysql1 ~]# pcs resource create Router_VIP ocf:heartbeat:IPaddr2 \ ip=192.168.87.5 cidr_netmask=24 op monitor interval=5s

We can check if our resource runs in the cluster now:

[root@mysql1 ~]# crm_mon -1 Stack: corosync Current DC: mysql1 (version 1.1.18-11.el7_5.3-2b07d5c5a9) - partition with quorum Last updated: Fri Jul 20 10:20:43 2018 Last change: Fri Jul 20 10:19:44 2018 by root via cibadmin on mysql1 3 nodes configured 1 resource configured Online: [ mysql1 mysql2 mysql3 ] Active resources: Router_VIP (ocf::heartbeat:IPaddr2): Started mysql1

This is perfect !

MySQL Router

Before adding the MySQL Router as resource, we need to be sure that the router is installed and bootstrapped on each nodes. As reminder, this is how to proceed:

[root@mysql1 ~]# mysqlrouter --bootstrap clusteradmin@mysql1:3306 --user=mysqlrouter --force Please enter MySQL password for clusteradmin: Bootstrapping system MySQL Router instance... MySQL Router has now been configured for the InnoDB cluster 'mycluster'. The following connection information can be used to connect to the cluster. Classic MySQL protocol connections to cluster 'mycluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'mycluster': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470

Now we can add the resource that will manage MySQL Router in our pacemaker cluster. We will use systemd.

You can verify if systemd service for mysqlrouter is available using the following command:

[root@mysql1 ~]# pcs resource list | grep router service:mysqlrouter - systemd unit file for mysqlrouter systemd:mysqlrouter - systemd unit file for mysqlrouter

OK, so let’s add the resource and let’s it run on all nodes (clone), no need to start mysqlrouter only if the running one has issue:

[root@mysql1 ~]# pcs resource create mysqlrouter systemd:mysqlrouter clone

Let’s verify if the resource is now added:

[root@mysql1 ~]# crm_mon -1 Stack: corosync Current DC: mysql1 (version 1.1.18-11.el7_5.3-2b07d5c5a9) - partition with quorum Last updated: Fri Jul 20 12:12:39 2018 Last change: Fri Jul 20 12:12:31 2018 by root via cibadmin on mysql1 3 nodes configured 4 resources configured Online: [ mysql1 mysql2 mysql3 ] Active resources: Router_VIP (ocf::heartbeat:IPaddr2): Started mysql1 Clone Set: mysqlrouter-clone [mysqlrouter] Started: [ mysql1 mysql2 mysql3 ]

Now we need to tell the cluster that the VIP needs to stand where a router instance also runs:

[root@mysql1 ~]# pcs constraint colocation add Router_VIP \ with mysqlrouter-clone score=INFINITY Test

Let’s verify that the VIP is located on mysql1 as crm shows it:

[root@mysql2 ~]# ip add sh eth1 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 08:00:27:f9:23:f1 brd ff:ff:ff:ff:ff:ff inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1 valid_lft forever preferred_lft forever inet 192.168.87.5/24 brd 192.168.87.255 scope global secondary eth1 valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:fef9:23f1/64 scope link valid_lft forever preferred_lft forever

Perfect !

Now we will kill mysqlrouter process on mysql1 and see what the cluster does:

[root@mysql2 ~]# crm_mon -1 Stack: corosync Current DC: mysql1 (version 1.1.18-11.el7_5.3-2b07d5c5a9) - partition with quorum Last updated: Fri Jul 20 12:37:29 2018 Last change: Fri Jul 20 12:36:51 2018 by hacluster via crmd on mysql1 3 nodes configured 4 resources configured Online: [ mysql1 mysql2 mysql3 ] Active resources: Router_VIP (ocf::heartbeat:IPaddr2): Started mysql2 Clone Set: mysqlrouter-clone [mysqlrouter] Started: [ mysql1 mysql2 mysql3 ]

Excellent, now we see that the VIP is located on mysql2, let’s verify:

[root@mysql2 ~]# ip add sh eth1 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 08:00:27:f9:23:f1 brd ff:ff:ff:ff:ff:ff inet 192.168.87.3/24 brd 192.168.87.255 scope global eth1 valid_lft forever preferred_lft forever inet 192.168.87.5/24 brd 192.168.87.255 scope global secondary eth1 valid_lft forever preferred_lft forever inet6 fe80::a00:27ff:fef9:23f1/64 scope link valid_lft forever preferred_lft forever

So now that the VIP runs on mysql2 we will shutdown the machine and see what happens:

root@mysql3 ~]# crm_mon -1 Stack: corosync Current DC: mysql1 (version 1.1.18-11.el7_5.3-2b07d5c5a9) - partition with quorum Last updated: Fri Jul 20 12:38:09 2018 Last change: Fri Jul 20 12:36:51 2018 by hacluster via crmd on mysql1 3 nodes configured 4 resources configured Online: [ mysql1 mysql3 ] OFFLINE: [ mysql2 ] Active resources: Router_VIP (ocf::heartbeat:IPaddr2): Starting mysql1 Clone Set: mysqlrouter-clone [mysqlrouter] Started: [ mysql1 mysql3 ]

We can see that the cluster does its job and that MySQL Router even if moved out of the application server is not anymore a single point of failure !

Hackathon process per week Sprints Idea

I like hackathons. Hackathons provide the freedom to build outside the process. The forced speed to deliver something to demo and the fun self-deprecation of "ooh this is really ugly/bad TODO don't do this." in the source/commit logs which tells a great story. Also, a great side effect; people are really interested in refactoring and fixing the code especially if the demo went well.

So, I started thinking what if we can take this naturally formed fun process and define a weekly sprint, with a daily standup reporting on the process to achieve the product goal, using a hackathon method.

Day 1 and 2"How much can you get done in two days for the demo"

  • This portion is no more than an hour planing. You talk to your team and divide up tasks for the hack you want to demo-in two days. For instance, "Johnny says I'll write the service" and "Amanda says I'll provide the data-it will be in MySQL". Sammy says "I'll write the front end to demo, Johnny let's agree what you'll send me, for now, I will simulate some pho data."
  • Then each person builds their part.
  • During the process, Johnny is building the interface from an un-authenticated HTTP Get request that has a JSON response to define what his service will return. Amanda finishes the process of testing some queries for functionality she checks in her part of how to get data, massage it and what tables are what, NOT performance.
  • Johnny sends a sample interface to Sammy so some dynamic data can be injected into the mockup when Sammy requests data. They agreed that a REST API using GET with a JSON response.
  • There are PR requests when sharing the same addition to the same place otherwise frequent merges
  • When fixing something that made it into master fix forward so check into master :P
  • Each check-in should be filled with a series of TODO, FIXME or "TODO don't do this" statements for speed until that's not needed when you have a refined process.
  • Demo

What does the individual developer each get?  Each developer produced something quick to verify the viability of the idea. A vested interest to fix the hacks and beautify the code, reusing reusable parts, etc. 
What does the team get? The team feels that they got something out pretty quick, the team has some talking points of what to fix next and what systems the team envisions that could possibly be used in other parts of the code.  Finally,  the chance to learn something new in the knowledge transfer or the ability to fix an approach before going too far down the rabbit hole.
Day 3  The next day is mapping out what the developer wants to refactor, has to change and gets to delete. With knowledge transfer of the good, bad, and embarrassing things with an idea of the direction each person took. It is fun.
  • This is looking over the queries to make they make sense.  
  • Are the correct indexes there? 
  • Are we really answering the correct questions efficiently if not how can we? 
  • What hacks do we need to undo to provide what we delivered?
  • How do I test this thing? I need to make sure before I refactor I have reproducible tests. 
Day 4  Document, Test, Refactor agree more as a team and focus on a code structure that enables adding the next round of features while setting standards of the direction going forward or revisiting them if need be.
Day 5 Do more of the same or get a beer with the team.

This process makes me feel that I am building something fast. The reason for the speed was to validate the idea or approach. Time is built into the process for testing, refactoring and documenting. The refactoring takes into account how to add new things faster. 50% building 50% testing, documenting, refactoring, making better. Producing a 4 day work week with daily standups
What about a really big project and delivering constantly
  • Whiteboard what is needed to deliver such as what the product is, what does it solve, what are the features. 
  • Answer what is alpha
  • Answer what is beta.
  • Divide and conquer the vision for each "hackathon period"
  • Adjust projection of delivery based on the previous hackathon progress
  • Keep working and visit each hackathon period to verify the correct thing is built correctly.
  • Profit from a fun fast paced delivery of code that treats features and delivery of great code the team all validates as equal partners.

De-Normalization of Your Database with JSON

One of the humbling things about working at Oracle with the various MySQL personnel is that you are often blown away by something one of them says or does.  And that is on a regular basis.  In this case it is Dr. Charles Bell who gave a great series of presentations last June at the Southeast Linuxfest.
In particular he presented in a full formed state some ideas that had been rattling around in my  skull (but no way near as coherent) on how to take advantage of the MySQL JSON data type.  Below are his points from his slide deck.  I was reviewing my notes from his presentation when I realized that this information really needs to be more widely disseminated.   And I would like your feedback on these ideas?
1.. We can use a JSON field to eliminate one of the issues of traditional database solutions: many-to-many-joins
  • This allows more freedom to store unstructured data (data with pieces missing)
  • You still use SQL to work with the data via a database connector but the JSON documents in the table can be manipulated directly in code.
  • Joins can be expensive. Reducing how many places you need to join data can help speed up your queries.  Removing joins may result in some level of denormalization but can result in fast access to the data.
2. Plan For Mutability
  • Schemaless designs are focused on mutability. Build your applications with the ability to modify the document as needed (and within reason)
3. Remove Many-to-Many Relationships
  • Use embedded arrays and lists to store relationships among documents.  This can be as simple as embedding the data in the document or embedding an array of document ids in the document.
  • In the first case data is available as soon as you can read the document and in the second it only takes one additional step to retrieve the data. In cases of seldom read (used) relationships, having the data linked with an array of ids can be more efficient (less data to read on the first pass)
This presentation and others from the Southeast Linuxfest will be available online and I will be sure to post about that when it happens.
And a big thank you to Dr. Chuck for these ideas.

MariaDB Connector/Node.js First Alpha Now Available

MariaDB Connector/Node.js First Alpha Now Available diego Dupin Fri, 07/20/2018 - 09:33

MariaDB is pleased to announce the immediate availability of MariaDB Connector/Node.js alpha version 0.7.0. This is a non-blocking MariaDB client for Node.js, 100 percent JavaScript, compatible with Node.js 6+.

Why a new client? While there are existing clients that work with MariaDB, (such as the mysql and mysql2 clients), the MariaDB Node.js Connector offers new functionality, like insert Streaming and Pipelining while making no compromises on performance.

Insert Streaming

Using a Readable stream in your application, you can stream INSERT statements to MariaDB through the Connector.

https.get('https://someContent', readableStream => { //readableStream implement Readable, driver will stream data to database connection.query("INSERT INTO myTable VALUE (?)", [readableStream]); }); Pipelining

With Pipelining, the Connector sends commands without waiting for server results, preserving order. For instance, consider the use of executing two INSERT statements.

The Connector doesn't wait for query results before sending the next INSERT statement. Instead, it sends queries one after the other, avoiding much of the network latency.

Quick Start

The MariaDB Connector is available through the Node.js repositories. You can install it using npm.

$ npm install mariadb

Using ECMAScript 2017:

const mariadb = require('mariadb'); const pool = mariadb.createPool({host: 'mydb.com', user:'myUser', connectionLimit: 5}); async function asyncFunction() { let conn; try { conn = await pool.getConnection(); const rows = await conn.query("SELECT 1 as val"); console.log(rows); //[ {val: 1}, meta: ... ] const res = await conn.query("INSERT INTO myTable value (?, ?)", [1, "mariadb"]); console.log(res); // { affectedRows: 1, insertId: 1, warningStatus: 0 } } catch (err) { throw err; } finally { if (conn) return conn.end(); } }


Documentation can be found on the  MariaDB knowledge base and sources are on GitHub.

Benchmarks

Comparing the MariaDB Connector with other Node.js clients:

promise-mysql : 1,366 ops/sec ±1.42% mysql2 : 1,469 ops/sec ±1.63% mariadb : 1,802 ops/sec ±1.19%

Benchmarks for the MariaDB Node.js Connector are done using the popular benchmark.js package. You can find the source code for our benchmarks in the benchmarks/ folder.

Roadmap

The MariaDB Node.js Connector remains in development. This is an alpha release so we do not recommend using it in production. Below is a list of features being developed for future connector releases.

  • PoolCluster
  • MariaDB ed25519 plugin authentication
  • Query Timeouts
  • Bulk Insertion, (that is, fast batch).

Download the MariaDB Node.js Connector directly.

MariaDB is pleased to announce the immediate availability of MariaDB Connector/Node.js 0.7.0, which is the first alpha version. See the release notes and changelog for details.

Login or Register to post comments

InnoDB Cluster in a Nutshell Part 3: MySQL Shell

Welcome to the third part of this series. I’m glad you’re still reading, as hopefully this means you find this subject interesting at least. Previously we presented the first two components of MySQL InnoDB Cluster: Group Replication and MySQL Router and now we will discuss the last component, MySQL Shell.

MySQL Shell

This is the last component in the cluster and I love it. Oracle have created this tool to centralize cluster management, providing a friendly, command-line based user interface.

The tool can be defined as an advanced MySQL shell, which is much more powerful than the well known MySQL client. With the capacity to work with both relational and document (JSON) data, the tool provides an extended capability to interact with the database from a single place.

MySQL Shell is also able to understand different languages:

  • JavaScript (default) which includes several built-in functions to administer the cluster—create, destroy, restart, etc.—in a very easy way.
  • Python it provides an easy way to write Python code to interact with the database. This is particularly useful for developers who don’t need to have SQL skills or run applications to test code.
  • SQL to work in classic mode to query database as we used to do with the old MySQL client.

A very interesting feature provided with MySQL Shell is the ability to establish different connections to different servers/clusters from within the same shell. There is no need to exit to connect to a different server, just issuing the command \connect will make this happen. As DBA, I find this pretty useful when handling multiple clusters/servers.

Some of the features present in this tool:

  • Capacity to use both Classic and X protocols.
  • Online switch mode to change languages (JavaScript, Python and SQL)
  • Auto-completion of commands using tab, a super expected feature in MySQL client.
  • Colored formatting output that also supports different formats like Table, Tab-separated and Json formats.
  • Batch mode that processes batches of commands allowing also an interactive mode to print output according each line is processed.
Some sample commands

Samples of new tool and execution modes:

#switch modes \sql \js \py #connect to instance \connect user@host:[port] #create a cluster (better to handle through variables) var cluster=dba.createCluster('percona') #add instances to cluster cluster.addInstance(‘root@192.168.70.2:3306’) #check cluster status cluster.status() #using another variable var cluster2=dba.getCluster(‘percona’) cluster.status() #get cluster structure cluster.describe() #rejoin instance to cluster - needs to be executed locally to the instance cluster.rejoinInstance() #rejoin instance to cluster - needs to be executed locally to the instance cluster.rejoinInstance() #recover from lost quorum cluster.forceQuorumUsingPartitionOf(‘root@localhost:3306’) #recover from lost quorum cluster.rebootClusterFromCompleteOutage() #destroy cluster cluster.dissolve({force:true});

Personally, I think this tool is a very good replacement for the classic MySQL client. Sadly, mysql-server installations do not include MySQL shell by default, but it is worth getting used to. I recommend you try it.

Conclusion

We finally reached the end of this series. I hope you have enjoyed this short introduction to what seems to be Oracle’s bid to have a built-in High Availability solution based on InnoDB. It may become a good competitor to Galera-based solutions. Still, there is a long way to go, as the tool was only just released as GA (April 2018). There are a bunch of things that need to be addressed before it becomes consistent enough to be production-ready. In my personal opinion, it is not—yet. Nevertheless, I think it is a great tool that will eventually be a serious player in the HA field as it’s an excellent, flexible and easy to deploy solution.

The post InnoDB Cluster in a Nutshell Part 3: MySQL Shell appeared first on Percona Database Performance Blog.

Mastering Continuent Clustering Series: Experience the Power of the Tungsten Connector, an Intelligent MySQL Proxy

In this blog post, we talk about the basic function and features of the Tungsten Connector.

The Tungsten Connector is an intelligent MySQL proxy that provides key high-availability and read-scaling features. This includes the ability to route MySQL queries by inspecting them in-flight.

The most important function of the Connector is failover handling. When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node.

Next is the ability to route MySQL queries based on various factors. In the default Bridge mode, traffic is routed at the TCP layer, and read-only queries must be directed to a different port (normally 3306 for writes and 3307 for reads).

There are additional modes, Proxy/Direct and Proxy/SmartScale. In both cases, queries are intercepted and inspected by the Connector. The decisions made are tunable based on configuration parameters.

MySQL must be configured for autocommit=1 (MySQL Docs) for this to work. Additionally, any query transaction wrapped with BEGIN and COMMIT will be sent to the Master directly.

In Proxy/Direct mode, a SELECT-only statement that does no writes will be sent to a read slave automatically. Unlike SmartScale, Direct routing pays no attention to the session state, or replicated data consistency.

This means that performing a write and immediately trying to read the information through a Direct routing connection may fail, because the Connector does not ensure that the written transaction exists on the selected slave.

Direct routing is therefore ideal in applications where:

  • Applications perform few writes, but a high number of reads.
  • High proportion of reads on ‘old’ data. For example, blogs, stores, or machine logging information

In Proxy/SmartScale mode, an additional check is made on the read slave to determine data “staleness”. In this read-write splitting mode, the Connector intelligently determines if slaves are up-to-date with respect to the master, and selects them in such a way that reads are always strictly consistent with the last write of their current session. This is extremely useful when doing read-behind-write operations.

Configuration of Proxy and SmartScale modes relies upon a text file called user.map which, at it’s most basic, defines the users that are connecting through.

Each user has three required and one optional field:
{user} {password} {service} [affinity]

For example:

app_user secret global west

Without at least one user entry, communications via the Connector will fail.

Best of all, each Connector node may be configured differently, so that it is possible to have all of the following available at once using four separate node instances:

  • Bridge mode read/write (very fast)
  • Bridge mode read-only (very fast)
  • Proxy/Direct auto-r/w splitting (slower due to inspection)
  • Proxy/SmartScale automatic session and latency-sensitive r/w splitting (slower still due to slave status query)

In summary, the Connector allows for both proper failover handing as well as a variety of ways to route MySQL queries to read slaves.

In future articles, we will cover more advanced subjects like failover behavior tuning and other MySQL query routing methods, like SQL-based, port-based and hostname-based.

Questions? Contact Continuent

Data Streaming with MariaDB

Data Streaming with MariaDB Faisal Thu, 07/19/2018 - 12:07 Big Data vs Fast Data

While Big Data is being used across the globe by companies to solve their analytical problems, sometimes it becomes a hassle to extract data from a bunch of data sources, do the necessary transformation and then eventually load it into an analytical platform such as Hadoop or something else.

This obviously takes time and effort, instead of a bunch of ETL jobs, MariaDB provides a data streaming solution directly from OLTP MariaDB TX to OLAP MariaDB AX.

Fast real-time data streaming is achieved with the help of a CDC (Change Data Capture) framework that streams data from MariaDB to MariaDB ColumnStore using MariaDB MaxScale 2.2.x as a bin-log router. MaxScale makes use of the Binary Logs from a MariaDB TX server and steams the data directly to MariaDB AX (MariaDB ColumnStore) for analytics.

This is achieved with the MaxScale CDC ConnectorCDC Adapter, and the ColumnStore API package. This sounds a bit complex but in reality, it's quite simple.

Here is a quick look at how the data streaming setup works:

MariaDB-server → MaxScale → MaxScale-CDC-Connector → MaxScale-CDC-Adapter → ColumnStore-API → ColumnStore Database The Setup Requirements

Here is a quick look at the setup that we are going to be working on, I am using Oracle Virtual Machines with CentOS 7

  • 1x CentOS 7 VM for MariaDB TX 3.0

  • 1x CentOS 7 VM for MaxScale as a Replication Router

  • 1x CentOS 7 VM for CDC Adapter + Connector and ColumnStore API

  • 1x CentOS 7 VM for ColumnStore, we will be using a single node "combined" ColumnStore setup for simplicity, refer to MariaDB Columnstore Installation Guide page for detailed setup instruction 

  • Our Setup will look like this

    • MariaDB (192.168.56.101)
      • MaxScale (192.168.56.102)
        • CDC Server (192.168.56.103)
          • ColumnStore (192.168.56.104)

Note: We will assume that you already have a MariaDB (Source of our data streaming) and ColumnStore (Data's final destination) readily available for use. 

Preparing the VM OS

There are a few important things that are required before we start the installations.

Note: the following steps must be performed and validated on all the VMs

Disable SELinux

For the purposes of testing, we want SELinux disabled. Make sure that your SELinux configuration, in the file /etc/selinux/config, looks something like this on all the nodes:

# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of these two values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. SELINUXTYPE=targeted

The change here is the SELinux setting of course.

After a reboot of the server, check if the SELinux has actually been disabled, use either of the two commands (sestatus/getenforce) to confirm

[root@localhost ~] sestatus SELinux status: disabled [root@localhost ~] getenforce Disabled Disable firewalld

Firewalld is a standard service that is disabled using the systemctl command on the REHL 7/CETOS 7. After disabling double check using systemctl status firewalld on all the nodes

[root@localhost ~] systemctl stop firewalld [root@localhost ~] systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@localhost ~] systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) Setup up the MariaDB account

The mariadb user and group are required to be created on all the nodes. sudo privilege for the mariadb user is also mandatory.

Following this, all steps will be done using mariadb user with the help of sudo unless specified differently.

[root@localhost ~] groupadd mysql [root@localhost ~] useradd -g mysql mysql [root@localhost ~] echo "mysql ALL=(ALL) ALL" >> /etc/sudoers [root@localhost ~] passwd mysql Changing password for user mysql. New password: Retype new password: passwd: all authentication tokens updated successfully. Enable Networking on the VirtualBox VMs

This is important so that the VMs could use Static IP addresses and also enable them to use Internet access of the host operating system.

Note: Make sure that NAT and Host-Only Adapters are both enabled for all the VMs.

Network setting within the VMs will need to be modified as follows for NAT:

[root@localhost ~] cat /etc/sysconfig/network-scripts/ifcfg-enp0s3 TYPE=Ethernet BOOTPROTO=dhcp DEFROUTE=yes IPV4_FAILURE_FATAL=no IPV6INIT=no NAME=enp0s3 DNS1=8.8.8.8 DNS2=8.8.4.4 UUID=89bfb7a3-17c3-49f9-aaa6-4d66c47ea6fb DEVICE=enp0s3 ONBOOT=yes PEERDNS=yes PEERROUTES=yes

For Host-Only Adapter edit the IP address accordingly for each VM:

[root@localhost ~] cat /etc/sysconfig/network-scripts/ifcfg-enp0s8 TYPE=Ethernet BOOTPROTO=none DEFROUTE=yes IPV4_FAILURE_FATAL=no IPV6INIT=no NAME=enp0s8 PREFIX=24 IPADDR=192.168.56.101 UUID=21a845ad-8cae-4a2c-b563-164a1f8a30cf DEVICE=enp0s8 ONBOOT=yes

Note: Make sure the above two files (ifcfg-enp0s3m, ifcfg-enp0s8) are changed on each Node/VM according and has it's own distinct Static IP defined in the file (ifcfg-enp0s8).

Set Hostnames on each VM for a better setup, this step is not mandatory but a good practice.

MariaDB VM:

[root@localhost ~] hostnamectl set-hostname mariadb101

MaxScale VM:

[root@localhost ~] hostnamectl set-hostname maxscale102

CDC VM:

[root@localhost ~] hostnamectl set-hostname cdc103 Install MariaDB TX 3.0 Setup the Repository

Login to the MariaDB Server (192.168.56.101)

Note: Not all the Linux distros are supported by the curl tool

  • Once the repository has been set up, simply use "yum install"
    • yum -y install MariaDB-server
      • Take care with the Unix's case sensitivity

We will be using the curl script to set up the MariaDB repositories, on production environments internet access is normally not available, in that case, we can download the RPMs externally and transfer the files to the servers using your favorite secure file transfer tools.

+[root@mariadb101 ~] su - mysql [mysql@mariadb101 ~]$ sudo curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo. [info] Adding trusted package signing keys... [info] Succeessfully added trusted package signing keys. [mysql@mariadb101 ~]$ sudo yum install MariaDB-server mariadb-main | 2.9 kB 00:00:00 mariadb-maxscale | 2.4 kB 00:00:00 mariadb-tools | 2.9 kB 00:00:00 (1/3): mariadb-maxscale/7/x86_64/primary_db | 6.3 kB 00:00:01 (2/3): mariadb-tools/7/x86_64/primary_db | 9.6 kB 00:00:01 (3/3): mariadb-main/7/x86_64/primary_db | 48 kB 00:00:01 Resolving Dependencies ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: MariaDB-compat x86_64 10.3.7-1.el7.centos mariadb-main 2.8 M replacing mariadb-libs.x86_64 1:5.5.56-2.el7 MariaDB-server x86_64 10.3.7-1.el7.centos mariadb-main 123 M Installing for dependencies: MariaDB-client x86_64 10.3.7-1.el7.centos mariadb-main 53 M MariaDB-common x86_64 10.3.7-1.el7.centos mariadb-main 157 k galera x86_64 25.3.23-1.rhel7.el7.centos mariadb-main 8.0 M Transaction Summary ================================================================================================= Install 2 Packages (+40 Dependent packages) Total download size: 200 M Is this ok [y/d/N]: Y ... ... Complete!

 

Installation

We will use mariadb user to install MariaDB on the MariaDB server.

[root@mariadb101 ~] su - mysql [mysql@mariadb101 ~]$ sudo curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo. [info] Adding trusted package signing keys... [info] Succeessfully added trusted package signing keys. [mysql@mariadb101 ~]$ sudo yum install MariaDB-server mariadb-main | 2.9 kB 00:00:00 mariadb-maxscale | 2.4 kB 00:00:00 mariadb-tools | 2.9 kB 00:00:00 (1/3): mariadb-maxscale/7/x86_64/primary_db | 6.3 kB 00:00:01 (2/3): mariadb-tools/7/x86_64/primary_db | 9.6 kB 00:00:01 (3/3): mariadb-main/7/x86_64/primary_db | 48 kB 00:00:01 Resolving Dependencies ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: MariaDB-compat x86_64 10.3.7-1.el7.centos mariadb-main 2.8 M replacing mariadb-libs.x86_64 1:5.5.56-2.el7 MariaDB-server x86_64 10.3.7-1.el7.centos mariadb-main 123 M Installing for dependencies: MariaDB-client x86_64 10.3.7-1.el7.centos mariadb-main 53 M MariaDB-common x86_64 10.3.7-1.el7.centos mariadb-main 157 k galera x86_64 25.3.23-1.rhel7.el7.centos mariadb-main 8.0 M Transaction Summary ================================================================================================ Install 2 Packages (+40 Dependent packages) Total download size: 200 M Is this ok [y/d/N]: Y ... ... Complete!

Enter "Y" when prompted, it will download and install MariaDB server and Client along with all its dependencies.

Install MariaDB MaxScale 2.2.x Setup the Repository

Login to the MaxScale server (102.168.56.102) and Use "curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash"

Just like with MariaDB, we will use mariadb user to set up the MariaDB repositories and install MaxScale using yum

[root@maxscale102 ~] curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo. [info] Adding trusted package signing keys... [info] Succeessfully added trusted package signing keys. [root@maxscale102 ~] su - mysql Last login: Fri Jun 22 14:39:53 EDT 2018 on pts/0 [mysql@maxscale102 ~]$ sudo yum install maxscale mariadb-main | 2.9 kB 00:00:00 mariadb-maxscale | 2.4 kB 00:00:00 mariadb-tools | 2.9 kB 00:00:00 (1/3): mariadb-maxscale/7/x86_64/primary_db | 6.3 kB 00:00:01 (2/3): mariadb-tools/7/x86_64/primary_db | 9.6 kB 00:00:01 (3/3): mariadb-main/7/x86_64/primary_db | 48 kB 00:00:02 Resolving Dependencies Dependencies Resolved ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: maxscale x86_64 2.2.9-1 mariadb-maxscale 18 M Installing for dependencies: gnutls x86_64 3.3.26-9.el7 base 677 k nettle x86_64 2.7.1-8.el7 base 327 k trousers x86_64 0.3.14-2.el7 base 289 k Transaction Summary ================================================================================================ Install 1 Package (+3 Dependent packages) Total download size: 19 M Installed size: 70 M Is this ok [y/d/N]: Y ... ... Complete! [mysql@maxscale102 ~]$ sudo systemctl enable maxscale Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service. [mysql@maxscale102 ~]$ systemctl status maxscale ● maxscale.service - MariaDB MaxScale Database Proxy Loaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; vendor preset: disabled) Active: inactive (dead) Install CDC Server Setup the Repository

Login to the CDC Server (102.168.56.103) we will need to set up two extra repositories that are currently not automatically added by the curl script. CDC Adapter, however, will automatically be taken care of by it.

Using the above URLs, install the API and Adapter directly from the path, the connector, however, should automatically download based on the repository setup.

  • Sequence of Installation
    • install maxscale-cdc-connector
    • install epel-release
      • install libuv
    • install columnstore-api
    • install cdc-adapters
Install MaxScale CDC Connector [root@cdc103 ~] yum install maxscale-cdc-connector Resolving Dependencies Dependencies Resolved ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: maxscale-cdc-connector x86_64 2.2.9-1 mariadb-maxscale 214 k Transaction Summary ================================================================================================ Install 1 Package Total download size: 214 k Installed size: 1.4 M Is this ok [y/d/N]: y ... ... Complete! Install Extra Packages for Enterprise Linux epel [root@cdc103 ~] yum install epel-release Resolving Dependencies Dependencies Resolved ============================================================================================== Package Arch Version Repository Size ============================================================================================== Installing: epel-release noarch 7-11 extras 15 k Transaction Summary ============================================================================================== Install 1 Package Total download size: 15 k Installed size: 24 k Is this ok [y/d/N]: y ... ... Complete! [root@cdc103 ~] yum install libuv Resolving Dependencies Dependencies Resolved ============================================================================================== Package Arch Version Repository Size ============================================================================================== Installing: libuv x86_64 1:1.19.2-1.el7 epel 121 k Transaction Summary ============================================================================================== Install 1 Package Total download size: 121 k Installed size: 308 k Is this ok [y/d/N]: y Downloading packages: ... ... Complete! Install ColumnStore API Package

You can Install the API directly from the source or download the RPM using " wget " and then install it locally.

[root@cdc103 ~] yum install https://downloads.mariadb.com/MariaDB/mariadb-columnstore-api/latest/yum/centos/7/x86_64/mariadb-columnstore-api-1.1.5-1-x86_64-centos7.rpm Resolving Dependencies Dependencies Resolved ============================================================================================= Package Arch Version Repository Size ============================================================================================= Installing: mariadb-columnstore-api x86_64 1.1.5-1 /mariadb-columnstore-api-1.1.5-1-x86_64 5.4 M Transaction Summary ============================================================================================= Install 1 Package ... ... Complete! Install CDC Adapter

You can Install the CDC Adapter directly from the source or download the RPM using " wget " and then install it locally.

[root@cdc103 ~] yum install https://downloads.mariadb.com/MariaDB/data-adapters/mariadb-streaming-data-adapters/latest/yum/centos/7/x86_64/mariadb-columnstore-maxscale-cdc-adapters-1.1.5-1-x86_64-centos7.rpm Resolving Dependencies Dependencies Resolved ================================================================================================ Package Arch Version Repository Size ================================================================================================ Installing: mariadb-columnstore-data-adapters x86_64 1.1.5-1 /mariadb-columnstore-maxscale 77 k Installing for dependencies: maxscale-cdc-connector x86_64 2.2.9-1 mariadb-maxscale 214 k Transaction Summary ================================================================================================ Install 1 Package (+1 Dependent package) Total size: 291 k Total download size: 214 k Installed size: 1.5 M Is this ok [y/d/N]: y ... ... Complete! Setup Communication Between CDC and ColumnStore

Now that the CDC Adapter, API, and Connectors have been installed, we can start to set up communication between the CDC and ColumnStore servers.

In this exercise, we are using single instance ColumnStore, in case of a distributed install, we will be working with ColumnStore UM1 Node.

ColumnStore Configuration

Connect to CDC server using ssh client and pull the /home/mysql/mariadb/columnstore/etc/Columnstore.xml from ColumnStore server using scp, rsync or any other method available for file transfer between the servers. Columnstore.xml should be downloaded to /etc and owned by root user.

The file can be downloaded from ColumnStore or any node will do as the Columnstore.xml is automatically synchronized between all the nodes. Use mariadb as the remote user to connect to ColumnStore.

As this is the first time connecting to ColumnStore from CDC server, Linux will ask for yes/no and for mariadb password.

Once the file is downloaded, ensure that it has 644 permission so that everyone can read it.

[root@cdc103 ~] scp mysql@192.168.56.104:/home/mysql/mariadb/columnstore/etc/Columnstore.xml /etc The authenticity of host '192.168.56.104 (192.168.56.104)' can't be established. ECDSA key fingerprint is SHA256:Jle/edRpKz9ysV8xp1K9TlIGvbg8Sb1p+GbDob3Id0g. ECDSA key fingerprint is MD5:a1:ce:9d:58:80:c6:ed:5a:95:7b:33:82:68:cb:0f:40. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.56.104' (ECDSA) to the list of known hosts. mysql@192.168.56.104's password: Columnstore.xml 100% 21KB 13.3MB/s 00:00 [root@cdc103 ~] [root@cdc103 ~] ls -rlt /etc/Columnstore.xml -rw-r--r-- 1 root root 21089 Jul 1 05:09 /etc/Columnstore.xml [root@cdc103 ~]

Search and replace 127.0.0.1 with ColumnStore's IP address in the newly downloaded Columnstore.xml file using root user as the file is under /etc/ folder.

[root@cdc103 etc] sed -i 's/127.0.0.1/192.168.56.104/g' /etc/Columnstore.xml [root@cdc103 etc] Setup Master MariaDB Replication Master

Now that the CDC node is ready, it's time to setup MariaDB Server as the replication Master for MaxScale.

MaxScale will be working as a Replication Slave for this data streaming setup.

Login to the MariaDB Master server using the root user and add the following contents to the /etc/my.cnf.d/server.cnf file under [mysqld] section and restart MariaDB process.

[mysqld] server_id=1 log-bin = mariadb-bin binlog-format = ROW gtid_strict_mode = 1 log_error log-slave-updates [root@mariadb101 ~] systemctl restart mariadb

The server_id will be used when configuring MaxScale as a replication slave node.

Setup Replication User

After MariaDB restarts, login to MariaDB using root user and setup replication user maxuser and grant REPLICATION SLAVE privilege to it.

The second step is to create a new database and a test table with some data that we are going to use for testing data streaming to ColumnStore.

[mysql@mariadb101 ~] mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.3.7-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> RESET MASTER; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> CREATE USER 'maxuser'@'%' IDENTIFIED BY 'maxpwd'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'maxuser'@'%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> CREATE DATABASE cdc_test; Query OK, 1 row affected (0.000 sec) MariaDB [(none)]> USE cdc_test; Database changed MariaDB [cdc_test]> CREATE TABLE cdc_tab(id serial, col varchar(100)); Query OK, 0 rows affected (0.010 sec) MariaDB [cdc_test]> INSERT INTO cdc_tab(col) values ('Row 1'), ('Row 2'), ('Row 3'); Query OK, 3 rows affected (0.004 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [cdc_test]> UPDATE cdc_tab SET col = 'Updated Row 2' WHERE id = 2; Query OK, 1 row affected (0.004 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [cdc_test]> INSERT INTO cdc_tab(col) values ('Row 4'), ('Row 5'), ('Row 6'); Query OK, 3 rows affected (0.004 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [cdc_test]> DELETE FROM cdc_tab where id=5; Query OK, 1 row affected (0.005 sec) MariaDB [cdc_test]>

Events that are captured in the binary logs of the master MariaDB Server:

  • INSERT 3 Records to cdc_tab.
  • UPDATE col for a row in cdc_tab.
  • INSERT 3 Records to cdc_tab.
  • DELETE a row from cdc_tab.
Setup MaxScale

Log on to the MaxScale server, edit the /etc/maxscale.cnf file, remove everything under the [maxscale] threads=auto section and add the following configuration to it. This will set MaxScale for data streaming to ColumnStore using Avro Listener.

Take note of the server_id in the [replication-router] section, this points to the master MariaDB server's ID that we defined earlier while setting up MariaDB as a master.

#Replication configuration that points to a particular server_id and binlog [replication-router] type=service router=binlogrouter user=maxuser passwd=maxpwd server_id=2 master_id=1 binlogdir=/var/lib/maxscale mariadb10-compatibility=1 filestem=mariadb-bin #Replication listener that will listen to the Master DB using the port #6603 [replication-listener] type=listener service=replication-router protocol=MySQLClient port=6603 #Avro service that will generate JSON files form the Bin-Logs that were received from the MasterDB and store them in the using the replication-router [avro-router] type=service router=avrorouter source=replication-router avrodir=/var/lib/maxscale #Avro listener that is used by the Avro router on a specific port to be used by CDC [avro-listener] type=listener service=avro-router protocol=cdc port=4001 [replication-router]
  • This section defines a bin-log router from Master MariaDB to MaxScale Slave using replication user maxuser which was created earlier.
[replication-listener]
  • This is the mysql client listener service. Any server with MariaDB client can connect to MaxScale on the port 6603 specified in this section. We will connect to MaxScale later to set it up as a REPLICATION SLAVE using this port.
[avro-router]
  • This is the router service that routes the bin-log data into AVRO (JSON) files. CDC will use these AVRO files to generate bulk loading scripts for ColumnStore database.
[avro-listener]
  • Avro Listener uses avro-router service, this listener is used by CDC Adapter to get the AVRO files and stream them to ColumnStore.

Once /etc/maxscale.cnf has been modified on the MaxScale server, restart MaxScale service.

[root@maxscale102 ~] systemctl restart maxscale Add CDC User / Password for avro-router service in MaxScale [mysql@maxscale102 ~]$ maxctrl call command cdc add_user avro-router cdcuser cdcpassword

This user will be the user that MaxScale uses to generate Avro schema files to be transferred and loaded into ColumnStore by the MaxScale CDC Adapter.

Setup MaxScale as a REPLICATION SLAVE

Install MariaDB client on the MaxScale Server. We will use this client to connect to MaxScale MariaDB listener and set up MaxScale as a replication SLAVE for MariaDB TX.

[mysql@maxscale102 ~]$ sudo yum -y install MariaDB-client Resolving Dependencies Dependencies Resolved ============================================================================================== Package Arch Version Repository Size ============================================================================================== Installing: MariaDB-client x86_64 10.3.7-1.el7.centos mariadb-main 53 M MariaDB-compat x86_64 10.3.7-1.el7.centos mariadb-main 2.8 M replacing mariadb-libs.x86_64 1:5.5.56-2.el7 Installing for dependencies: MariaDB-common x86_64 10.3.7-1.el7.centos mariadb-main 157 k Running transaction Installing : MariaDB-common-10.3.7-1.el7.centos.x86_64 1/31 Installing : MariaDB-compat-10.3.7-1.el7.centos.x86_64 2/31 ============================================================================================== Installed: MariaDB-client.x86_64 0:10.3.7-1.el7.centos MariaDB-compat.x86_64 0:10.3.7-1.el7.centos Dependency Installed: MariaDB-common.x86_64 0:10.3.7-1.el7.centos perl.x86_64 4:5.16.3-292.el7 perl-Carp.noarch 0:1.26-244.el7 perl-Encode.x86_64 0:2.51-7.el7 perl-Exporter.noarch 0:5.68-3.el7 perl-File-Path.noarch 0:2.09-2.el7 perl-File-Temp.noarch 0:0.23.01-3.el7 perl-Filter.x86_64 0:1.49-3.el7 perl-Getopt-Long.noarch 0:2.40-3.el7 perl-HTTP-Tiny.noarch 0:0.033-3.el7 perl-PathTools.x86_64 0:3.40-5.el7 perl-Pod-Escapes.noarch 1:1.04-292.el7 perl-Pod-Perldoc.noarch 0:3.20-4.el7 perl-Pod-Simple.noarch 1:3.28-4.el7 perl-Pod-Usage.noarch 0:1.63-3.el7 perl-Scalar-List-Utils.x86_64 0:1.27-248.el7 perl-Socket.x86_64 0:2.010-4.el7 perl-Storable.x86_64 0:2.45-3.el7 perl-Text-ParseWords.noarch 0:3.29-4.el7 perl-Time-HiRes.x86_64 4:1.9725-3.el7 perl-Time-Local.noarch 0:1.2300-2.el7 perl-constant.noarch 0:1.27-2.el7 perl-libs.x86_64 4:5.16.3-292.el7 perl-macros.x86_64 4:5.16.3-292.el7 perl-parent.noarch 1:0.225-244.el7 perl-podlators.noarch 0:2.5.1-3.el7 perl-threads.x86_64 0:1.87-4.el7 perl-threads-shared.x86_64 0:1.43-6.el7 Replaced: mariadb-libs.x86_64 1:5.5.56-2.el7 Complete!

Now we can Connect to MaxScale MariaDB service using the installed client and setup MaxScale as a REPLICATION SLAVE; use proper Master MariaDB IP in the CHANGE MASTER TO MASTER_HOST followed by START SLAVE and SHOW SLAVE STATUS to ensure the slave is running without any issues.

This setup is just like setting up a MariaDB Master/Slave replication, the only difference, in this case, is that we are setting up MaxScale as a bin-log router slave. Take note that, we have used -h 192.168.56.102 argument to pass in MaxScale's IP address and -P 6603 argument to pass in the [replication-listener] port from the /etc/maxscale.cnf file. That port is for mysqlclient service, that is why we are able to connect to MaxScale's MariaDB interface.

[mysql@maxscale102 ~]$ mysql -h 192.168.56.102 -u maxuser -p maxpwd -P 6603 Enter password: ERROR 1049 (42000): Unknown database 'maxpwd' [mysql@maxscale102 ~]$ mysql -h192.168.56.102 -umaxuser -pmaxpwd -P6603 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 10.2.12 2.2.9-maxscale Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.56.101', MASTER_PORT=3306, MASTER_USER='maxuser', MASTER_PASSWORD='maxpwd', MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=4; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> START SLAVE; Query OK, 0 rows affected (0.201 sec) MySQL [(none)]> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Binlog Dump Master_Host: 192.168.56.101 Master_User: maxuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 2047 Relay_Log_File: mariadb-bin.000001 Relay_Log_Pos: 2047 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 2047 Relay_Log_Space: 2047 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 5a2189b0-7d1a-11e8-b04f-080027ad63ed Master_Info_File: /var/lib/maxscale/master.ini SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave running Master_Retry_Count: 1000 Master_Bind: Last_IO_Error_TimeStamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.000 sec) MySQL [(none)]> Start Data Streaming

With all the settings in place, we can now start data streaming from MariaDB TX to ColumnStore in MariaDB AX using the CDC and Avro data.

Log in to CDC Server and execute mxs_adapter to start streaming.

[mysql@cdc103 ~]$ mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h 192.168.56.102 -P 4001 cdc_test cdc_tab

Let's review this command and its arguments:

  • -c indicates the path of the Columnstore.xml configuration file
  • -u / -p indicates the CDC User and Password that were created earlier
  • -h is the IP address of the MaxScale server
  • -P is the port on which MaxScale [avro-listener] service is listening to
  • cdc_test is the database name on the source (MariaDB) and target (ColumnStore)
  • cdc_tab is the table name on the source (MariaDB) and target (ColumnStore)

The target database/table should be already created before starting mxs_adapter, if not, mxs_adapter will provide a script to create the table in ColumnStore automatically. Let's start the service and see what happens.

[mysql@cdc103 ~]$ mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h 192.168.56.102 -P 4001 cdc_test cdc_tab Table not found, create with: CREATE TABLE cdc_test.cdc_tab (col varchar(100), domain int, event_number int, event_type varchar(50), id serial, sequence int, server_id int, timestamp int) ENGINE=ColumnStore; [mysql@cdc103 ~]$

As expected, the error message "Table not found", copy the CREATE TABLE script and execute it on the ColumnStore node using mcsmysql interface.

[root@um1 ~] mcsmysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 43 Server version: 10.2.15-MariaDB-log Columnstore 1.1.5-1 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE cdc_test; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> CREATE TABLE cdc_test.cdc_tab (col varchar(100), domain int, event_number int, event_type varchar(50), id serial, sequence int, server_id int, timestamp int) ENGINE=ColumnStore; ERROR 1069 (42000): Too many keys specified; max 0 keys allowed MariaDB [(none)]>

There is another problem! Our source table had a serial column which MariaDB treats as a Primary Key. Since ColumnStore does not support Primary Keys / Indexes we will need to change the CREATE TABLE script before executing it in the ColumnStore DB.

Simply change, serial to bigint unsigned .

Let's log back into ColumnStore mcsmysql and recreate the table.

MariaDB [(none)]> DROP TABLE cdc_test.cdc_tab; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> CREATE TABLE cdc_test.cdc_tab (col varchar(100), domain int, event_number int, event_type varchar(50), id bigint unsigned, sequence int, server_id int, timestamp int) ENGINE=ColumnStore; Query OK, 0 rows affected (0.55 sec) MariaDB [(none)]>

Another thing to note here is that the table structure in ColumnStore is quite different from the source. There are a few additional columns. This table is an event table that captures all the INSERT, UPDATE and DELETE events. We can use this data to identify the latest row for each specific ID and run some analytics.

One more thing to notice here is that at the moment, one mxs_adapter can only handle one table at a time. This is to prevent streaming the entire MariaDB databases to ColumnStore. Instead, we could create aggregate tables on MariaDB TX and stream that data to ColumnStore for better analytics use case.

Let's start the mxs_adapter once more and see if it can stream the data from our source MariaDB TX to target MariaDB AX.

[mysql@cdc103 ~]$ mxs_adapter -c /etc/Columnstore.xml -u cdcuser -p cdcpassword -h 192.168.56.102 -P 4001 cdc_test cdc_tab 4 rows and 1 transactions inserted in 3.3203 seconds. GTID = 0-1-6 2 rows and 1 transactions inserted in 0.792351 seconds. GTID = 0-1-7 3 rows and 1 transactions inserted in 0.604614 seconds. GTID = 0-1-8

Success! The three events are captured, INSERT, UPDATE and DELETE and their GTIDs. Let's log in to ColumnStore and review the data in the cdc_tab table.

MariaDB [cdc_test]> SHOW TABLES; +--------------------+ | Tables_in_cdc_test | +--------------------+ | cdc_tab | +--------------------+ 1 row in set (0.00 sec) MariaDB [cdc_test]> SELECT * FROM cdc_tab; +---------------+--------+--------------+---------------+------+----------+-----------+------------+ | col | domain | event_number | event_type | id | sequence | server_id | timestamp | +---------------+--------+--------------+---------------+------+----------+-----------+------------+ | Row 1 | 0 | 1 | insert | 1 | 5 | 1 | 1530439436 | | Row 2 | 0 | 2 | insert | 2 | 5 | 1 | 1530439436 | | Row 3 | 0 | 3 | insert | 3 | 5 | 1 | 1530439436 | | Row 2 | 0 | 1 | update_before | 2 | 6 | 1 | 1530439675 | | Updated Row 2 | 0 | 2 | update_after | 2 | 6 | 1 | 1530439675 | | Row 4 | 0 | 1 | insert | 4 | 7 | 1 | 1530439697 | | Row 5 | 0 | 2 | insert | 5 | 7 | 1 | 1530439697 | | Row 6 | 0 | 3 | insert | 6 | 7 | 1 | 1530439697 | | Row 5 | 0 | 1 | delete | 5 | 8 | 1 | 1530439714 | +---------------+--------+--------------+---------------+------+----------+-----------+------------+ 9 rows in set (0.17 sec) MariaDB [cdc_test]>

We can see the INSERT, UPDATE (Before and After) and a DELETE. SEQUENCE column indicates the sequence in which these events were triggered and the timestamp column indicates the time as and when these events took place.

Avro files

While setting up MaxScale we specified avrodir as /var/lib/mysql . This can be any different location.

[avro-router] type=service router=avrorouter source=replication-router avrodir=/var/lib/maxscale

Let's see the contents of the avrodir folder. We can see two files generated .avsc contains the data structure while .avro contains the actual data from the bin logs.

[root@maxscale102 maxscale] pwd /var/lib/maxscale [root@maxscale102 maxscale] ls -rlt total 40 drwxr-xr-x 2 maxscale maxscale 6 Jun 22 14:44 maxscale.cnf.d -rw-r--r-- 1 maxscale maxscale 54 Jun 22 14:44 maxadmin-users -rw-r--r-- 1 maxscale maxscale 84 Jun 22 14:44 passwd drwxr--r-- 2 maxscale maxscale 25 Jul 1 04:25 MariaDB-Monitor drwxr-xr-x 2 maxscale maxscale 6 Jul 1 06:34 data1288 drwxr-xr-x 2 maxscale maxscale 22 Jul 1 06:46 avro-router -rw------- 1 maxscale maxscale 183 Jul 1 06:50 master.ini drwx------ 2 maxscale maxscale 199 Jul 1 06:50 cache -rw-r--r-- 1 maxscale maxscale 4096 Jul 1 06:50 gtid_maps.db -rw-r--r-- 1 maxscale maxscale 2047 Jul 1 06:50 mariadb-bin.000001 -rw-r--r-- 1 maxscale maxscale 572 Jul 1 06:50 cdc_test.cdc_tab.000001.avsc -rw-r--r-- 1 maxscale maxscale 797 Jul 1 06:50 cdc_test.cdc_tab.000001.avro -rw-r--r-- 1 maxscale maxscale 5120 Jul 1 06:50 avro.index -rw-r--r-- 1 maxscale maxscale 69 Jul 1 07:29 avro-conversion.ini [root@maxscale102 maxscale]

In case of some issue with the data streaming, one can try to restart MaxScale service. If there's still a failure, as a last resort one can delete *.avsc, *.avro, avro.index and avro-conversion.ini and restart the MaxScale service. It should be able to recover.

Since the mxs_adapter is running, as and when new data is inserted in the cdc_test.cdc_tab table, it will automatically be streamed into ColumnStore.

Conclusion

Using this setup, we can stream data directly from OLTP MariaDB TX not only to MariaDB AX but also to other sources that can take Avro/JSON data. There is a Kafka Adapter already available in the Data Adapters download page.

References

Thanks.

This blog is about setting up Data Streaming from MariaDB TX to MariaDB AX. We will be using Virtual Box CentOS images.

Login or Register to post comments

Controlling Replication Failover for MySQL and MariaDB with Pre- or Post-Failover Scripts

In a previous post, we discussed how you can take control of the failover process in ClusterControl by utilizing whitelists and blacklists. In this post, we are going to discuss a similar concept. But this time we will focus on integrations with external scripts and applications through numerous hooks made available by ClusterControl.

Infrastructure environments can be built in different ways, as oftentimes there are many options to choose from for a given piece of the puzzle. How do we define which database node to write to? Do you use virtual IP? Do you use some sort of service discovery? Maybe you go with DNS entries and change the A records when needed? What about the proxy layer? Do you rely on ‘read_only’ value for your proxies to decide on the writer, or maybe you make the required changes directly in the configuration of the proxy? How does your environment handle switchovers? Can you just go ahead and execute it, or maybe you have to take some preliminary actions beforehand? For instance, halting some other processes before you can actually do the switch?

It is not possible for a failover software to be preconfigured to cover all of the different setups that people can create. This is main reason to provide different ways of hooking into the failover process. This way you can customize it and make it possible to handle all of the subtleties of your setup. In this blog post, we will look into how ClusterControl’s failover process can be customized using different pre- and post-failover scripts. We will also discuss some examples of what can be accomplished with such customization.

Integrating ClusterControl

ClusterControl provides several hooks that can be used to plug in external scripts. Below you will find a list of those with some explanation.

  1. Replication_onfail_failover_script - this script executes as soon as it has been discovered that a failover is needed. If the script returns non-zero, it will force the failover to abort. If the script is defined but not found, the failover will be aborted. Four arguments are supplied to the script: arg1='all servers' arg2='oldmaster' arg3='candidate', arg4='slaves of oldmaster' and passed like this: 'scripname arg1 arg2 arg3 arg4'. The script must be accessible on the controller and be executable.
  2. Replication_pre_failover_script - this script executes before the failover happens, but after a candidate has been elected and it is possible to continue the failover process. If the script returns non-zero it will force the failover to abort. If the script is defined but not found, the failover will be aborted. The script must be accessible on the controller and be executable.
  3. Replication_post_failover_script - this script executes after the failover happened. If the script returns non-zero, a Warning will be written in the job log. The script must be accessible on the controller and be executable.
  4. Replication_post_unsuccessful_failover_script - This script is executed after the failover attempt failed. If the script returns non-zero, a Warning will be written in the job log. The script must be accessible on the controller and be executable.
  5. Replication_failed_reslave_failover_script - this script is executed after that a new master has been promoted and if the reslaving of the slaves to the new master fails. If the script returns non-zero, a Warning will be written in the job log. The script must be accessible on the controller and be executable.
  6. Replication_pre_switchover_script - this script executes before the switchover happens. If the script returns non-zero, it will force the switchover to fail. If the script is defined but not found, the switchover will be aborted. The script must be accessible on the controller and be executable.
  7. Replication_post_switchover_script - this script executes after the switchover happened. If the script returns non-zero, a Warning will be written in the job log. The script must be accessible on the controller and be executable.

As you can see, the hooks cover most of the cases where you may want to take some actions - before and after a switchover, before and after a failover, when the reslave has failed or when the failover has failed. All of the scripts are invoked with four arguments (which may or may not be handled in the script, it is not required for the script to utilize all of them): all servers, hostname (or IP - as it is defined in ClusterControl) of the old master, hostname (or IP - as it is defined in ClusterControl) of the master candidate and the fourth one, all replicas of the old master. Those options should make it possible to handle the majority of the cases.

All of those hooks should be defined in a configuration file for a given cluster (/etc/cmon.d/cmon_X.cnf where X is the id of the cluster). An example may look like this:

replication_pre_failover_script=/usr/bin/stonith.py replication_post_failover_script=/usr/bin/vipmove.sh

Of course, invoked scripts have to be executable, otherwise cmon won’t be able to execute them. Let’s now take a moment and go through the failover process in ClusterControl and see when the external scripts are executed.

Failover process in ClusterControl

We defined all of the hooks that are available:

replication_onfail_failover_script=/tmp/1.sh replication_pre_failover_script=/tmp/2.sh replication_post_failover_script=/tmp/3.sh replication_post_unsuccessful_failover_script=/tmp/4.sh replication_failed_reslave_failover_script=/tmp/5.sh replication_pre_switchover_script=/tmp/6.sh replication_post_switchover_script=/tmp/7.sh

After this, you have to restart the cmon process. Once it’s done, we are ready to test the failover. The original topology looks like this:

A master has been killed and the failover process started. Please note, the more recent log entries are at the top so you want to follow the failover from bottom to the top.

As you can see, immediately after the failover job started, it triggers the ‘replication_onfail_failover_script’ hook. Then, all reachable hosts are marked as read_only and ClusterControl attempts to prevent the old master from running.

Next, the master candidate is picked, sanity checks are executed. Once it is confirmed the master candidate can be used as a new master, the ‘replication_pre_failover_script’ is executed.

More checks are performed, replicas are stopped and slaved off the new master. Finally, after the failover completed, a final hook, ‘replication_post_failover_script’, is triggered.

When hooks can be useful?

In this section, we’ll go through a couple of examples cases where it might be a good idea to implement external scripts. We will not get into any details as those are too closely related to a particular environment. It will be more of a list of suggestions that might be useful to implement.

STONITH script

Shoot The Other Node In The Head (STONITH) is a process of making sure that the old master, which is dead, will stay dead (and yes.. we don’t like zombies roaming about in our infrastructure). The last thing you probably want is to have an unresponsive old master which then gets back online and, as a result, you end up with two writable masters. There are precautions you can take to make sure the old master will not be used even if shows up again, and it is safer for it to stay offline. Ways on how to ensure it will differ from environment to environment. Therefore, most likely, there will be no built-in support for STONITH in the failover tool. Depending on the environment, you may want to execute CLI command which will stop (and even remove) a VM on which the old master is running. If you have an on-prem setup, you may have more control over the hardware. It might be possible to utilize some sort of remote management (integrated Lights-out or some other remote access to the server). You may have also access to manageable power sockets and turn off the power in one of them to make sure server will never start again without human intervention.

Service discovery

We already mentioned a bit about service discovery. There are numerous ways one can store information about a replication topology and detect which host is a master. Definitely, one of the more popular options is to use etc.d or Consul to store data about current topology. With it, an application or proxy can rely in this data to send the traffic to the correct node. ClusterControl (just like most of the tools which do support failover handling) does not have a direct integration with either etc.d or Consul. The task to update the topology data is on the user. She can use hooks like replication_post_failover_script or replication_post_switchover_script to invoke some of the scripts and do the required changes. Another pretty common solution is to use DNS to direct traffic to correct instances. If you will keep the Time-To-Live of a DNS record low, you should be able to define a domain, which will point to your master (i.e. writes.cluster1.example.com). This requires a change to the DNS records and, again, hooks like replication_post_failover_script or replication_post_switchover_script can be really helpful to make required modifications after a failover happened.

Proxy reconfiguration Related resources  How to Control Replication Failover for MySQL and MariaDB  MySQL Replication Blueprint  MySQL Replication for High Availability

Each proxy server that is used has to send traffic to correct instances. Depending on the proxy itself, how a master detection is performed can be either (partially) hardcoded or can be up to the user to define whatever she likes. ClusterControl failover mechanism is designed in a way it integrates well with proxies that it deployed and configured. It still may happen that there are proxies in place, which were not installed by ClusterControl and they require some manual actions to take place while failover is being executed. Such proxies can also be integrated with the ClusterControl failover process through external scripts and hooks like replication_post_failover_script or replication_post_switchover_script.

Additional logging

It may happen that you’d like to collect data of the failover process for debugging purposes. ClusterControl has extensive printouts to make sure it is possible to follow the process and figure out what happened and why. It still may happen that you would like to collect some additional, custom information. Basically all of the hooks can be utilized here - you can collect the initial state, before the failover, you can track the state of the environment at all stages of the failover.

Tags:  MySQL MariaDB replication failover scripts

MySQL 8.0 support in dotConnect for MySQL 8.12

We are glad to announce the release of dotConnect for MySQL 8.12 — a powerful ADO.NET provider for MySQL with advanced support for Entity Framework, Entity Framework Core, and LinqConnect ORMs. The new version of dotConnect for MySQL features MySQL 8.0 support. We have supported the new default MySQL authentication plugin – caching_sha2_password. The list […]

Pages