Featured Posts

<< >>

MySQL Fabric/MySQL Utilities 1.4.4 released

The binary and source versions of MySQL Utilities/MySQL Fabric have now been made available at http://dev.mysql.com/downloads/utilities/. This release contains bug fixes and minor enhancements – full details can be found in the MySQL Fabric/MySQL Utilities release notes.

MySQL Cluster latest developments – webinar replay + Q&A

I recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here. At the end of this article you’ll find a full transcript of the Q&A from the live session. Details:

Discover the latest MySQL Cluster Developments – Upcoming webinar

On Thursday 17th July I’ll be hosting a webinar which explains what MySQL Clusrter is, what it can deliver and what the latest developments are. As always the webinar is free but please register here. Details: Join this technical webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making

MySQL Cluster 7.3.6 Released

The binary and source versions of MySQL Cluster 7.3.6 have now been made available at http://www.mysql.com/downloads/cluster/. Release notes MySQL Cluster NDB 7.3.6 is a new release of MySQL Cluster, based on MySQL Server 5.6 and including features from version 7.3 of the NDB storage engine, as well as fixing a number of recently discovered bugs

Sharding & HA – MySQL Fabric Webinar Replay + Q&A

On 19th June 2014, Mats Kindahl and I presented a free webinar on why and how you should be using MySQL Fabric to add Sharding (scaling out reads & writes) and High Availability to MySQL. The webinar replay is available here. This blog post includes a transcript of the questions raised during the live webinar

MySQL Fabric/MySQL Utilities 1.4.4 released

MySQL Utilities & Fabric The binary and source versions of MySQL Utilities/MySQL Fabric have now been made available at http://dev.mysql.com/downloads/utilities/.

This release contains bug fixes and minor enhancements – full details can be found in the MySQL Fabric/MySQL Utilities release notes.





MySQL Cluster latest developments – webinar replay + Q&A

MySQL Cluster LogoI recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here. At the end of this article you’ll find a full transcript of the Q&A from the live session.

Details:

View this webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can help your products and applications meet the needs of the most demanding markets. MySQL Cluster combines these capabilities and the affordability of open source, making it well suited for use as an embedded database.

In this replay you’ll learn about the following MySQL Cluster capabilities, including the latest innovations in the 7.3 GA release:

  • Auto-sharding (partitioning) across commodity hardware for extreme read and write scalability
  • Cross-data center geographic synchronous and asynchronous replication
  • Online scaling and schema upgrades, now with improved Connection Thread Scalability
  • Real-time optimizations for ultra-low, predictable latency
  • Foreign Key Support for tight referential integrity
  • SQL and NoSQL interfaces, now with support for Node.js
  • Support for MySQL 5.6, allowing use of the latest InnoDB and NDB engines within one database
  • Integrated HA for 99.999% availability
  • Auto-Installer that installs, configures, provisions and tunes a production grade cluster in minutes

In addition, you will get a sneak preview of some of the new features planned in MySQL Cluster 7.4 Come and learn how MySQL Cluster can help you differentiate your products and extend their reach into new markets, as well as deliver highly demanding web-based applications, either on premises or in the cloud.

Q&A Transcript

  • When using the Memcached API, can I use my existing Memcached connector? Yes. The Memcached API actually uses the regular memcached protocol but then has a custom plugin that acesses the MySQL Cluster data nodes rather than using its local in-memory store.
  • If I’m replicating between 2 Clusters in 2 data centres and the WAN fails for a minute – what happens? Because the replication between MySQL Cluster instances is asynchronous – the application isn’t impacted (for example, there will be no extra errors or latency). The changes will be stored in the binary log of the Cluster to which they were sent and then replicated to the other site once the WAN returns.
  • Can I scale back down as well as up? It’s an online operation to reduce the number of MySQL Servers (or other application nodes) but that isn’t currently possible for the data nodes. In reality, it’s very rare that applications need to reduce the amount of data they store.
  • Are there any MySQL connectors that don’t work with MySQL Cluster? No, any connector that works with MySQL will work just as well with MySQL Cluster.
  • Do you have more details on the benchmark results? Yes – take a look at the MySQL Cluster Benchmarks page.
  • I’ve been hearing about MySQL Fabric – does that also allow queries and joins ot span multiple shards? Currently, the only option for cross-shard queries is to use MySQL Cluster or implement them at the application layer.
  • Is the data is partioned over diffrent cluster nodes or do all cluster nodes hold the full data set. Each node group stores a subset of the rows from each table. The 2 data nodes within the node group will store the exact same set of rows.
  • Where can I find a definition of those different kinds of Foreign Key constraints? The wikipedia definition for Foreign Keys is a good place to start.
  • What is the diffrence between ndbcluster and MySQL Cluster ? None – they’re one and the same. When you hear any of “Cluster”, “MySQL Cluster”, “NDB” and “NDB Cluster” the meaning is the same.
  • Do I need to have a web server installed for the Auto-Installer to work? No – the MySQL Cluster auto-installer comes with a small web server built-in.
  • Are there any dependencies to meet before installing MySQL Cluster on RHEL Liunx? It should work out of the box. My preferred way of working is to use the generic Linux tar ball for MySQL Cluster (get it from the MySQL Cluster download page) – extract it and then run the auto-installer or configure it manually.
  • Is there any guide available to migrate mysql nodes to mysql cluster? Probably the closest we have is a white paper on how to get the best out of any PoC for MySQL Cluster (as it highlights what needs to be done differently in order to get the best results)… MySQL Cluster Evaluation Guide. Note that MySQL Cluster uses a different version of the mysqld binary and so you’ll need to stop your existing MySQL Server and start up the new one. To migrate a specific table to MySQL Cluster after that is done use “ALTER TABLE my-tab ENGINE=NDB;”.
  • Does drupal support MySQL Cluster? I’ve heard of people doing it but I suspect that minor tweaks to teh Drupal code may have been needed.
  • How do the NoSQL APIs map to the SQL database schemas? It varies slightly by API – in general, you provide some annotations or meta-data to specify how tables or columns should map to keys/objects/properties. With Memcached you have the option of being schema-less and having all data stored in one, big, generic table.
  • Where can I learn more about MySQL Fabric? The MySQL Fabric page is a good starting point; for an end-to-end example, take a look at this tutorial on adding HA and then sharding using MySQL Fabric.
  • What is difference between MySQL Fabric and MySQL Cluster? MySQL Fabric provides server farm management on top of ‘regular’ MySQL Servers storing data with the InnoDB storage engine it delivers HA and sharding. MySQL Cluster works below the MySQL Server, storing data in the NDB storage engine (on the data nodes). MySQL Cluster can deliver higher levels of High Availability; better application transparency and cross-shard queries, joins and transactions but it does mean using a different storage engine which of course comes with its own limitations (see the MySQL Cluster Evaluation Guide for details of those).
  • So, if I have any full table scans, should I forget about MySQL Cluster> Note necessarily. If every one of your high running operations is a full table scan then MySQL Cluster might not be ideal. However if most operations are simpler but you have some full table scans then that could be fine. The optimisations going into MySQL Cluster 7.4 should particularly benefit table scans.




Discover the latest MySQL Cluster Developments – Upcoming webinar

MySQL Cluster LogoOn Thursday 17th July I’ll be hosting a webinar which explains what MySQL Clusrter is, what it can deliver and what the latest developments are. As always the webinar is free but please register here.

Details:

Join this technical webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by making it far simpler and faster to build your products and web-based applications with MySQL Cluster. You’ll also learn how MySQL Cluster and its linear scalability, 99.999% uptime, real-time responsiveness, and ability to perform over 1 BILLION Writes per Minute can help your products and applications meet the needs of the most demanding markets. MySQL Cluster combines these capabilities and the affordability of open source, making it well suited for use as an embedded database.

In this webcast you’ll learn about the following MySQL Cluster capabilities, including the latest innovations in the 7.3 GA release:

  • Auto-sharding (partitioning) across commodity hardware for extreme read and write scalability
  • Cross-data center geographic synchronous and asynchronous replication
  • Online scaling and schema upgrades, now with improved Connection Thread Scalability
  • Real-time optimizations for ultra-low, predictable latency
  • Foreign Key Support for tight referential integrity
  • SQL and NoSQL interfaces, now with support for Node.js
  • Support for MySQL 5.6, allowing use of the latest InnoDB and NDB engines within one database
  • Integrated HA for 99.999% availability
  • Auto-Installer that installs, configures, provisions and tunes a production grade cluster in minutes

In addition, you will get a sneak preview of some of the new features planned in MySQL Cluster 7.4 Come and learn how MySQL Cluster can help you differentiate your products and extend their reach into new markets, as well as deliver highly demanding web-based applications, either on premises or in the cloud.

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.





MySQL Cluster 7.3.6 Released

MySQL Cluster Logo
The binary and source versions of MySQL Cluster 7.3.6 have now been made available at http://www.mysql.com/downloads/cluster/.

Release notes

MySQL Cluster NDB 7.3.6 is a new release of MySQL Cluster, based
on MySQL Server 5.6 and including features from version 7.3 of the
NDB storage engine, as well as fixing a number of recently
discovered bugs in previous MySQL Cluster releases.

Obtaining MySQL Cluster NDB 7.3. MySQL Cluster NDB 7.3 source
code and binaries can be obtained from
http://dev.mysql.com/downloads/cluster/.

For an overview of changes made in MySQL Cluster NDB 7.3, see
MySQL Cluster Development in MySQL Cluster NDB 7.3
(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-development-5-6-ndb-7-3.html).

This release also incorporates all bugfixes and changes made in
previous MySQL Cluster releases, as well as all bugfixes and
feature changes which were added in mainline MySQL 5.6 through
MySQL 5.6.19 (see Changes in MySQL 5.6.19 (2014-05-30)
(http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-19.html)).

Functionality Added or Changed

  • Cluster API: Added as an aid to debugging the ability to
    specify a human-readable name for a given Ndb object and later
    to retrieve it. These operations are implemented,
    respectively, as the setNdbObjectName() and getNdbObjectName()
    methods.
    To make tracing of event handling between a user application
    and NDB easier, you can use the reference (from getReference()
    followed by the name (if provided) in printouts; the reference
    ties together the application Ndb object, the event buffer,
    and the NDB storage engine’s SUMA block. (Bug #18419907)

Bugs Fixed

  • Cluster API: When two tables had different foreign keys with
    the same name, ndb_restore considered this a name conflict and
    failed to restore the schema. As a result of this fix, a slash
    character (/) is now expressly disallowed in foreign key
    names, and the naming format parent_id/child_id/fk_name is now
    enforced by the NDB API. (Bug #18824753)
  • Processing a NODE_FAILREP signal that contained an invalid
    node ID could cause a data node to fail. (Bug #18993037, Bug
    #73015)
    References: This bug is a regression of Bug #16007980.
  • When building out of source, some files were written to the
    source directory instead of the build dir. These included the
    manifest.mf files used for creating ClusterJ jars and the
    pom.xml file used by mvn_install_ndbjtie.sh. In addition,
    ndbinfo.sql was written to the build directory, but marked as
    output to the source directory in CMakeLists.txt. (Bug
    #18889568, Bug #72843)
  • Adding a foreign key failed with NDB Error 208 if the parent
    index was parent table’s primary key, the primary key was not
    on the table’s initial attributes, and the child table was not
    empty. (Bug #18825966)
  • When an NDB table served as both the parent table and a child
    table for 2 different foreign keys having the same name,
    dropping the foreign key on the child table could cause the
    foreign key on the parent table to be dropped instead, leading
    to a situation in which it was impossible to drop the
    remaining foreign key. This situation can be modelled using
    the following CREATE TABLE statements:
    CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=NDB;
    CREATE TABLE child (
    id INT NOT NULL,
    parent_id INT,
    PRIMARY KEY (id),
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
    REFERENCES parent(id)
    ) ENGINE=NDB;
    CREATE TABLE grandchild (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
    REFERENCES child(id)
    ) ENGINE=NDB;

    With the tables created as just shown, the issue occured when
    executing the statement ALTER TABLE child DROP FOREIGN KEY
    parent_id, because it was possible in some cases for NDB to
    drop the foreign key from the grandchild table instead. When
    this happened, any subsequent attempt to drop the foreign key
    from either the child or from the grandchild table failed.
    (Bug #18662582)
  • ndbmtd supports multiple parallel receiver threads, each of
    which performs signal reception for a subset of the remote
    node connections (transporters) with the mapping of
    remote_nodes to receiver threads decided at node startup.
    Connection control is managed by the multi-instance TRPMAN
    block, which is organized as a proxy and workers, and each
    receiver thread has a TRPMAN worker running locally.
    The QMGR block sends signals to TRPMAN to enable and disable
    communications with remote nodes. These signals are sent to
    the TRPMAN proxy, which forwards them to the workers. The
    workers themselves decide whether to act on signals, based on
    the set of remote nodes they manage.
    The current isuue arises because the mechanism used by the
    TRPMAN workers for determining which connections they are
    responsible for was implemented in such a way that each worker
    thought it was responsible for all connections. This resulted
    in the TRPMAN actions for OPEN_COMORD, ENABLE_COMREQ, and
    CLOSE_COMREQ being processed multiple times.
    The fix keeps TRPMAN instances (receiver threads) executing
    OPEN_COMORD, ENABLE_COMREQ and CLOSE_COMREQ requests. In
    addition, the correct TRPMAN instance is now chosen when
    routing from this instance for a specific remote connection.
    (Bug #18518037)
  • Executing ALTER TABLE … REORGANIZE PARTITION after
    increasing the number of data nodes in the cluster from 4 to
    16 led to a crash of the data nodes. This issue was shown to
    be a regression caused by previous fix which added a new dump
    handler using a dump code that was already in use (7019),
    which caused the command to execute two different handlers
    with different semantics. The new handler was assigned a new
    DUMP code (7024). (Bug #18550318)
    References: This bug is a regression of Bug #14220269.
  • When running with a very slow main thread, and one or more
    transaction coordinator threads, on different CPUs, it was
    possible to encounter a timeout when sending a
    DIH_SCAN_GET_NODESREQ signal, which could lead to a crash of
    the data node. Now in such cases the timeout is avoided. (Bug
    #18449222)
  • During data node failure handling, the transaction coordinator
    performing takeover gathers all known state information for
    any failed TC instance transactions, determines whether each
    transaction has been committed or aborted, and informs any
    involved API nodes so that they can report this accurately to
    their clients. The TC instance provides this information by
    sending TCKEY_FAILREF or TCKEY_FAILCONF signals to the API
    nodes as appropriate top each affected transaction.
    In the event that this TC instance does not have a direct
    connection to the API node, it attempts to deliver the signal
    by routing it through another data node in the same node group
    as the failing TC, and sends a GSN_TCKEY_FAILREFCONF_R signal
    to TC block instance 0 in that data node. A problem arose in
    the case of multiple transaction cooridnators, when this TC
    instance did not have a signal handler for such signals, which
    led it to fail.
    This issue has been corrected by adding a handler to the TC
    proxy block which in such cases forwards the signal to one of
    the local TC worker instances, which in turn attempts to
    forward the signal on to the API node. (Bug #18455971)
  • A local checkpoint (LCP) is tracked using a global LCP state
    (c_lcpState), and each NDB table has a status indicator which
    indicates the LCP status of that table (tabLcpStatus). If the
    global LCP state is LCP_STATUS_IDLE, then all the tables
    should have an LCP status of TLS_COMPLETED.
    When an LCP starts, the global LCP status is LCP_INIT_TABLES
    and the thread starts setting all the NDB tables to
    TLS_ACTIVE. If any tables are not ready for LCP, the LCP
    initialization procedure continues with CONTINUEB signals
    until all tables have become available and been marked
    TLS_ACTIVE. When this initialization is complete, the global
    LCP status is set to LCP_STATUS_ACTIVE.
    This bug occurred when the following conditions were met:

    • An LCP was in the LCP_INIT_TABLES state, and some but not
      all tables had been set to TLS_ACTIVE.
    • The master node failed before the global LCP state
      changed to LCP_STATUS_ACTIVE; that is, before the LCP
      could finish processing all tables.
    • The NODE_FAILREP signal resulting from the node failure
      was processed before the final CONTINUEB signal from the
      LCP initialization process, so that the node failure was
      processed while the LCP remained in the LCP_INIT_TABLES
      state.
      Following master node failure and selection of a new one, the
      new master queries the remaining nodes with a MASTER_LCPREQ
      signal to determine the state of the LCP. At this point, since
      the LCP status was LCP_INIT_TABLES, the LCP status was reset
      to LCP_STATUS_IDLE. However, the LCP status of the tables was
      not modified, so there remained tables with TLS_ACTIVE.
      Afterwards, the failed node is removed from the LCP. If the
      LCP status of a given table is TLS_ACTIVE, there is a check
      that the global LCP status is not LCP_STATUS_IDLE; this check
      failed and caused the data node to fail.
      Now the MASTER_LCPREQ handler ensures that the tabLcpStatus
      for all tables is updated to TLS_COMPLETED when the global LCP
      status is changed to LCP_STATUS_IDLE. (Bug #18044717)
  • When performing a copying ALTER TABLE operation, mysqld
    creates a new copy of the table to be altered. This
    intermediate table, which is given a name bearing the prefix
    #sql-, has an updated schema but contains no data. mysqld then
    copies the data from the original table to this intermediate
    table, drops the original table, and finally renames the
    intermediate table with the name of the original table.
    mysqld regards such a table as a temporary table and does not
    include it in the output from SHOW TABLES; mysqldump also
    ignores an intermediate table. However, NDB sees no difference
    between such an intermediate table and any other table. This
    difference in how intermediate tables are viewed by mysqld
    (and MySQL client programs) and by the NDB storage engine can
    give rise to problems when performing a backup and restore if
    an intermediate table existed in NDB, possibly left over from
    a failed ALTER TABLE that used copying. If a schema backup is
    performed using mysqldump and the mysql client, this table is
    not included. However, in the case where a data backup was
    done using the ndb_mgm client’s BACKUP command, the
    intermediate table was included, and was also included by
    ndb_restore, which then failed due to attempting to load data
    into a table which was not defined in the backed up schema.
    To prevent such failures from occurring, ndb_restore now by
    default ignores intermediate tables created during ALTER TABLE
    operations (that is, tables whose names begin with the prefix
    #sql-). A new option –exclude-intermediate-sql-tables is
    added that makes it possible to override the new behavior. The
    option’s default value is TRUE; to cause ndb_restore to revert
    to the old behavior and to attempt to restore intermediate
    tables, set this option to FALSE. (Bug #17882305)
  • The logging of insert failures has been improved. This is
    intended to help diagnose occasional issues seen when writing
    to the mysql.ndb_binlog_index table. (Bug #17461625)
  • The DEFINER column in the INFORMATION_SCHEMA.VIEWS table
    contained erroneous values for views contained in the ndbinfo
    information database. This could be seen in the result of a
    query such as SELECT TABLE_NAME, DEFINER FROM
    INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA=’ndbinfo’. (Bug
    #17018500)
  • Employing a CHAR column that used the UTF8 character set as a
    table’s primary key column led to node failure when restarting
    data nodes. Attempting to restore a table with such a primary
    key also caused ndb_restore to fail. (Bug #16895311, Bug
    #68893)
  • Disk Data: Setting the undo buffer size used by
    InitialLogFileGroup to a value greater than that set by
    SharedGlobalMemory prevented data nodes from starting; the
    data nodes failed with Error 1504 Out of logbuffer memory.
    While the failure itself is expected behavior, the error
    message did not provide sufficient information to diagnose the
    actual source of the problem; now in such cases, a more
    specific error message Out of logbuffer memory (specify
    smaller undo_buffer_size or increase SharedGlobalMemory) is
    supplied. (Bug #11762867, Bug #55515)
  • Cluster Replication: When using NDB$EPOCH_TRANS, conflicts
    between DELETE operations were handled like conflicts between
    updates, with the primary rejecting the transaction and
    dependents, and realigning the secondary. This meant that
    their behavior with regard to subsequent operations on any
    affected row or rows depended on whether they were in the same
    epoch or a different one: within the same epoch, they were
    considered conflicting events; in different epochs, they were
    not considered in conflict.
    This fix brings the handling of conflicts between deletes by
    NDB$EPOCH_TRANS with that performed when using NDB$EPOCH for
    conflict detection and resolution, and extends testing with
    NDB$EPOCH and NDB$EPOCH_TRANS to include “delete-delete”
    conflicts, and encapsulate the expected result, with
    transactional conflict handling modified so that a conflict
    between DELETE operations alone is not sufficient to cause a
    transaction to be considered in conflict. (Bug #18459944)
  • Cluster API: When an NDB data node indicates a buffer overflow
    via an empty epoch, the event buffer places an inconsistent
    data event in the event queue. When this was consumed, it was
    not removed from the event queue as expected, causing
    subsequent nextEvent() calls to return 0. This caused event
    consumption to stall because the inconsistency remained
    flagged forever, while event data accumulated in the queue.
    Event data belonging to an empty inconsistent epoch can be
    found either at the beginning or somewhere in the middle.
    pollEvents() returns 0 for the first case. This fix handles
    the second case: calling nextEvent() call dequeues the
    inconsistent event before it returns. In order to benefit from
    this fix, user applications must call nextEvent() even when
    pollEvents() returns 0. (Bug #18716991)
  • Cluster API: The pollEvents() method returned 1, even when
    called with a wait time equal to 0, and there were no events
    waiting in the queue. Now in such cases it returns 0 as
    expected. (Bug #18703871)




Sharding & HA – MySQL Fabric Webinar Replay + Q&A

MySQL Fabric - Sharding and High Availability

On 19th June 2014, Mats Kindahl and I presented a free webinar on why and how you should be using MySQL Fabric to add Sharding (scaling out reads & writes) and High Availability to MySQL. The webinar replay is available here. This blog post includes a transcript of the questions raised during the live webinar together with the responses given – if you’re questions aren’t answered already then please feel free to post them as comments here.

Abstract

MySQL Fabric is built around an extensible and open source framework for managing farms of MySQL Servers. Currently two features have been implemented – High Availability (built on top of MySQL Replication) and scaling out using data sharding. These features can be used in isolation or in combination. MySQL Fabric aware connectors allow transactions and queries to be routed to the correct servers without the need for a proxy node, so operations run as quickly as ever. In this webinar you will learn what MySQL Fabric is, what it can achieve and how it is used – by DBAs, Dev-Ops and developers. You’ll also be exposed to what is happening under the covers. In addition to the presentation, there will be live on-line Q&A with the engineering team. This is a great opportunity to learn about the latest developments directly from the people building them.

Q&A

  • Is High availability still based on asynchronous replication?: MySQL Fabric will set up asynchronous replication; you can then manually switch to semisynchronous replication. MySQL Cluster is the only option to get synchronous replication.
  • Is there any possibility of split brain (among slaves) when promoting a slave to be the new master?: For the current solution, based on MySQL Replication, there will at any point be at most a single write server. It can still be the case that one of the slaves are lagging (but if you enable semi-synchronous replication then the slave will be able to catch up as it has everything at least in its relay log), but no split brain situation.
  • Are there hooks for different stages of the master pivot to update other orchestration/configuration systems?: You’re able to add script files to a directory and they will be invoked; it’s not currently very elegant but if it’s something you want to do then we can help you with it. We hope to make it more elegant/intuitive in the futures.
  • Can we use the MySQL Fabric only for HA Solutions?: Yes – sharding is optional
  • What happens if the slave is lagging and the primary goes down?: If using semisynchronous replication then the slave will apply it’s queued replication events before becoming master; if using asynchronous replication then those changes will not be there once the slave is promoted to master.
  • For promoting slave, how does Fabric determine which slave to promote? How is it guaranteed to have all available binlog events applied?: Currently, MySQL Fabric picks the slave that has the most changes from the master and re-direct the other slaves to the promoted slave. The outstanding events in the relay log is processed as normal. Note that as a user, you can specify which slaves are candidates to be promoted to master and also which use semisynchronous rather than synchronous replication.
  • What is the typical time to detect primary failure, choose slave candidate and complete the promotion of the new primary?: We haven’t run any benchmarks, but the ping frequency is configurable, so the detection time depends on how often you check the servers. Choosing a new primary and promoting it is straightforward. The deciding factor is the number of outstanding events on the promoted slave and the time to process them.
  • For HA group, how do you prevent or mitigate false positives for failover of primary?: You can specify how many connector instances need to report the master as unavailable and how many times – in that way you can decide what constitues a failure vs. a transient or local issue.
  • Are the HA Groups based on standard replication techniques? GTID’s?: The HA group is based on a set of MySQL Servers – one is a replication master and the rest are replication slaves; for HA, GTIDs are used.
  • Do the HA groups rely on GTID’s setup in combination with mysqlrpladmin/mysqlfailover tools?: MySQL Fabric uses GTID’s to failover the slaves correctly, in a similar way to how mysqlfailover does, but has its own code for performing the failover.
  • Can you explain “Zero Data Loss” in MySQL 5.7 when using MySQL Replication?: The enhancement is that with semisynchronous replication in 5.7, the commit will not be externalised until the event has been receive by a slave. This means that if any application sees the result of the transaction then you’re guaranteed that the effects of that transaction will not be lost even if the master fails.
  • Is the Fabric Connector node a single point of failure or does it have failover as well?: Each application instance has an associated connector instance; if they fail then the other instances continue to provide service. Of course, if you have a single application process then you have a vulnerability – regardless of what the database provides.
  • Does MySQL Fabric choose a primary for us (i.e. we do not get to choose which instance is primary)?: You get to specify which of the slaves are *candidates* for promotion (others can be there to just act as read-slaves) and so you have control.
  • How many nodes are required to setup Sharding (Minimum number of servers)?: For sharding you need at least one server for each shard. If you want HA then each shard needs at least 2 servers (and the Fabric node should be on a different machine). i.e. minimum of 2 servers for sharding, minimum of 3 machines for HA, minimum of 4 servers/3 machines for HA *and* sharding
  • Do the shards support typical SQL constructs such as joins and FKs across nodes?: Queries (including joins) are local to a shard. If you have global tables (all data replicated to all shards) then you can have joins between that data and the data held within a particular shard.
  • If I would start with, 2 shards (so 2 HA groups). Can I expand this to 4 shards and let Fabric rebuild the shards based on the ranges?: Yes – there is a MySQL Fabric shard splitting command – mysql fabric sharding split_shard command which splits a single shard into 2 shards (on 2 different HA groups)
  • Does a shard split trigger write locks?: Writes can continue through most of the shard split *but* there is a period at the end where they will be locked – the longest time this can be is the tie of your longest transaction.
  • Can we run global SELECT query againts multiple shards and get result from all shards?: Currently, a single query can only access global (unsharded) tables or a single shard; the application would need to send in multiple queries if it wanted to get results from multple shards.
  • Can we have a query for both global data and one of the shard data combied together?: Absolutely – yes
  • This appears to be using inline queries. What about stored procedures?: Any stored procedures would be executed locally on the MySQL Server selected by the connector (i.e. it would not be run accross multiple shards).
  • Does MySQL Fabric support the Memcache API to InnoDB?: Theres’s no support for the Memcached API at this point. However, your app can access the Fabric mapping/state data and use that to route to the correct server.
  • Are there plans to make the connector sharding logic automatic (i.e. application wouldn’t need to break out the sharding key) in the many cases where there is no ambiguity (returning with an error when the shard cannon be derived)? ie: A non-transactional select on a single table with shard key in WHERE could always go to a read-slave in the proper shard.: This is something we’d like to add but we can’t make any commitments.
  • Do MySQL Fabric-aware connectors implement automatic failover?: Yes, the MySQL Fabric-aware connectors fail over by themselves after MySQL Fabric has promoted the new master.
  • In the future will there be a transparant connector for other languages (most applications use default (and dumb) mysql connectors): We plan to make other connectors MySQL Fabric-aware and for those that aren’t owned by Oracle, we’d love to advise the community owners how to adapt them.
  • A lot of Open Source applications don’t care about the MySQL back-end, whether it is replicated or not. They use the standard MySQL connector that is supplied in their programming language. A transparant gateway-like connector would benefit here for migration.: We hope to get Fabric support into as many connectors as possible. Note that these aren’t ‘special’ PHP, Java or Python connectors – we add Fabric support to the “standard connectors”
  • are there any size limitations on the mysql databases that are being used?: There are no additional constraints imposed on the MySQL servers by MySQL Fabric.
  • Are there any plan for a fabric GUI management interface?: It would be nice to have but we don’t have anything at present; the command-line-interface and XML/RPC APIs were the priority so that the functionality can be embedded within a user’s wider infrastructure (e.g. invoking the MySQL Fabric commands from an existing script).
  • Do all machines need to have the same resources?: No, all of the MySQL Servers in a HA group (or the machines they’re running on) do not have to be equal. You can specify which servers are candidates to become the master and also specify weights so that more powerful servers receiver more queries.
  • Do you need any specific MySQL setup for using MySQL Fabric?: MySQL Fabric operate using normal MySQL servers and rely on standard replication to handle high-availability and sharding.
  • Are there any limitations wrt AWS RDS for MySQL Fabric?: The usual limitation mentioned when using AWS is when virtual IP support is required. MySQL Fabric *does not rely on virtual IP support* and handles the routing in the connectors.
  • Do we need GTID for MySQL Fabric setup?: In the current GA version of MySQL Fabric, GTID’s have to be enabled on the servers being managed in order for the HA features to work.
  • Do we need MySQL GTID for MySQL Fabric ?: For the HA aspects – yes.
  • Any specific MySQL parameters required for MySQL Fabric?: You need to enable GTIDs and you need to have a binary log on the servers that are candidates for being master. You also need to enable UUID support, since MySQL Fabric distinguishes between the servers using their UUID.
  • Can MySQL Fabric support removing nodes?: Yes, MySQL Fabric supports removing servers from HA Groups as well as adding new ones.
  • It was mentioned that the Fabric controller is a single node. How do you avoid SPOF on the controller?: The connectors hold a cache of the mapping and state data and so they can continue to route requests when the Fabric node is unavailable. Of course we would like to add redundancy for the Fabric node but queries and transactions can continue while it is unavailable.
  • Is there any Oracle RAC like (active-active) HA solution for MySQL?: MySQL offers MySQL Cluster as an active-active HA solution; unlike Oracle RAC though it does not depend on shared storage.
  • Is it useful if I use mysql cluster 7.3 instead of replication mode? since cluster has HA functionality itself: At the moment, you cannot use MySQL Cluster with MySQL Fabric. MySQL Cluster does offer transparrent sharding and High Availability. MySQL Fabric is ideal when you want simple HA and sharding but you want to continue using InnoDB as your storage engine.
  • Is MySQL Fabric stable for production deployment ?: It is Generally Available which means that we’ve completed our testing and are confident that it’s ready for live deployment but of course you should do your own testing to satisfy yourself that it’s working correctly *in your environment and with your application*
  • Does fabric require a commercial license/subscription?: It’s available under the GPL2 Open Source license or if you want a commercial license/subscription then it’s also part of MySQL Enterprise Edition and MysQL Carrier Grade Edition
  • How about Enterprise support for Fabric?: It’s in the community version of MySQL but also part of MySQL Enterprise Edition.




SQL & NoSQL, The Best of Both Worlds with MySQL Cluster – webinar replay now available

MySQL Cluster Logo

I recently presented a webinar explaining how you can enjoy the key benefits of NoSQL data stores without giving up all of the great features provided by a mature RDBMS.

In case you weren’t able to attend (or wanted to refresh your memory) then the webinar replay and charts are now available.

There’s often a lot of excitement around NoSQL Data Stores with the promise of simple access patterns, flexible schemas, scalability and High Availability. The downside can come in the form of losing ACID transactions, consistency, flexible queries and data integrity checks. What if you could have the best of both worlds?

This webinar showed how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data, with a simple key-value API (Memcached), REST, JavaScript, Java or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance, 99.999% availability, on-line maintenance and linear, horizontal scalability through transparent auto-sharding.

These webinars are always a good opportunity to get your questions answered; here’s a catch up of the Q&A from this session:

  • Would you suggest using mysql cluster to store graph data (150k writes second)? For graph data, you always have to choose between a specialized graph database and a more general-purpose database. If it’s “almost” relational with a few graph-like connections, your decision might be different than if it’s purely graph-like. In any case, your write load of 150K writes per second can certainly be managed in MySQL Cluster. It only requires a little care to get an appropriate cluster configuration as far as number of data nodes, number of API nodes, memory, disk, and networking. Also, the total eventual size of the data is an important factor in the decision about whether to use Cluster, since indexes must always fit in the total distributed memory of the data nodes.
  • Can you please explain the RAM requirements for MySQL Cluster, for example if my database is 10GBs in disc space, will it require 10GBs of RAM in MySQL Cluster? There is additional overhead in addition to the raw data. It’s tricky to try to summarize, but there is fixed overhead per row plus space for re-do logs and indexes. Details are in online documents. All indexed columns must be in memory but other columns can be on disk if you choose. Remember that each row has to be stored on 2 data nodes and so you need to figure out your total memory requirement, double it and then divide by the number of data nodes to find how much memory would be needed for each data node. MySQL Cluster Evaluation Guide – Designing, Evaluating and Benchmarking MySQL Cluster is a good white paper to refer to in order to decide if MySQL Cluster is the right database for your application as well as what you’ll need and what you should do to get the best results.
  • Is there a wizard to migrate innoDB to MySQL Cluster? There’s not a “wizard” per se, but “ALTER TABLE x ENGINE=ndb” will convert a particular table. (It’s only tricky if you have foreign keys which might have to be dropped at the beginning and reenabled at the end of the process).
  • Can this be deployed on EC2 instances, or is this for bare metal? MySQL Cluster has been successfully deployed (e.g. by PayPal)
  • How difficult is it to do a hardware upgrade? Do you have to do it all at once or can you do each machine in turn? Both hardware and software upgrades are online operations. You can add nodes to a running cluster, and upgrade the software on nodes individually. If you use the MySQL Cluster Manager, many of the upgrade operations can be automated. You won’t be able to exploit some upgrades (e.g. extra hardware on a data node) until you’ve upgraded.
  • Does MySQL Cluster store all data in memory? What scenarios available for swaping data to disk? Can we differentiate which tables/columns are stored on memory/disk? All indexes are in memory. A table can be all in-memory, or it can have non-indexed columns stored on disk. That’s a per-column choice.
  • Can mysql be subscribed/notified when some data is changed/updated? There is a notification API. It is currently only supported in C NDB API (this is the “Event API”), not in MySQL server or others. There are plans to also support it in Node.JS, but no actual support at this time. If using SQL then triggers can be defined in the MySQL Server – just like for InnoDB tables.




Sharding & HA – MySQL Fabric Webinar

MySQL Fabric - Sharding and High Availability

On Thursday (19th June), Mats Kindahl and I will be presenting a free webinar on why and how you should be using MySQL Fabric to add Sharding (scaling out reads & writes) and High Availability to MySQL. This product has only recently gone GA and so this is a good chance to discover it’s for you and to get your questions answered by the people who wrote the software! All you need to do is register for the MySQL Fabric webinar here.

Abstract

MySQL Fabric is built around an extensible and open source framework for managing farms of MySQL Servers. Currently two features have been implemented – High Availability (built on top of MySQL Replication) and scaling out using data sharding. These features can be used in isolation or in combination. MySQL Fabric aware connectors allow transactions and queries to be routed to the correct servers without the need for a proxy node, so operations run as quickly as ever. In this webinar you will learn what MySQL Fabric is, what it can achieve and how it is used – by DBAs, Dev-Ops and developers. You’ll also be exposed to what is happening under the covers. In addition to the presentation, there will be live on-line Q&A with the engineering team. This is a great opportunity to learn about the latest developments directly from the people building them.

When

  • Thu, Jun 19: 09:00 Pacific time (America)
  • Thu, Jun 19: 10:00 Mountain time (America)
  • Thu, Jun 19: 11:00 Central time (America)
  • Thu, Jun 19: 12:00 Eastern time (America)
  • Thu, Jun 19: 13:00 São Paulo time
  • Thu, Jun 19: 16:00 UTC
  • Thu, Jun 19: 17:00 Western European time
  • Thu, Jun 19: 18:00 Central European time
  • Thu, Jun 19: 19:00 Eastern European time
  • Thu, Jun 19: 21:30 India, Sri Lanka
  • Fri, Jun 20: 00:00 Singapore/Malaysia/Philippines time
  • Fri, Jun 20: 00:00 China time
  • Fri, Jun 20: 01:00 日本
  • Fri, Jun 20: 02:00 NSW, ACT, Victoria, Tasmania (Australia)




I’m speaking at OUG Scotland this week

ougscot14-resourcepk-isa-v1
If you’re going to be near Edinburgh this week then consider registering for OUG Scotland. I’ll be presenting on how to acheive the benefits of NoSQL (scalability, HA, ease of use. simple APIs) while at the same time still benefiting from the RDBMS features people have grown to rely on (ACID transactions, rich schemas, flexible access patterns) – the presentation will be at 11:25 on Wednesday as part of the developers’ track.

Hint for those that can’t make it – MySQL Cluster is the key :)





MySQL Fabric now Generally Available – Automating High Availability and Sharding for MySQL

MySQL Fabric - High Availability and Scalability for MySQL
MySQL Fabric is a new framework that automates High Availability (HA) and/or sharding (scaling-out) for MySQL and it has just been declared Generally Available.

This post focuses on MySQL Fabric as a whole – both High Availability and scaling out (sharding). It starts with an introductions to HA and scaling out (by partitioning/sharding data) and how MySQL Fabric achieves it before going on to work through a full example of deploying HA with MySQL Fabric and then adding sharding on top.

Download and try MySQL Fabric now!

This post focuses on MySQL Fabric as a whole – both High Availability and scaling out (sharding). It starts with introductions to HA and scaling out (by partitioning/sharding data) and how MySQL Fabric achieves it before going on to work through a full example of deploying HA with MySQL Fabric and then adding sharding on top.

What MySQL Fabric Provides

MySQL Fabric is built around an extensible framework for managing farms of MySQL Servers. Currently two features have been implemented – High Availability and scaling out using data sharding. Either of these features can be used in isolation or in combination.

Both features are implemented in two layers:

    • The mysqlfabric process which processes any management requests – whether received through the mysqlfabric command-line-interface (the manual for which can be found at http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html) or from another process via the supplied XML/RPC interface. When using the HA feature, this process can also be made responsible for monitoring the master server and initiating failover to promote a slave to be the new master should it fail. The state of the server farm is held in the state store (a MySQL database) and the mysqlfabric process is responsible for providing the state and routing information to the connectors.
    • MySQL Connectors are used by the application code to access the database(s), converting instructions from a specific programming language to the MySQL wire protocol, which is used to communicate with the MySQL Server processes. A ‘Fabric-aware’ connector stores a cache of the routing information that it has received from the mysqlfabric process and then uses that information to send transactions or queries to the correct MySQL Server. Currently the three supported Fabric-aware MySQL connectors are for PHP, Python and Java (and in turn the Doctrine and Hibernate Object-Relational Mapping frameworks). This approach means that the latency and potential bottleneck of sending all requests via a proxy can be avoided.

High Availability

High Availability (HA) refers to the ability for a system to provide continuous service – a system is available while that service can be utilized. The level of availability is often expressed in terms of the “number of nines” – for example, a HA level of 99.999% means that the service can be used for 99.999% of the time, in other words, on average, the service is only unavailable for 5.25 minutes per year (and that includes all scheduled as well as unscheduled down-time).

Different Points of High Availability

Layers in architecture where High Availability is needed

The figure shows the different layers in the system that need to be available for service to be provided.

At the bottom is the data that the service relies on. Obviously, if that data is lost then the service cannot function correctly and so it’s important to make sure that there is at least one extra copy of that data. This data can be duplicated at the storage layer itself but with MySQL, it’s most commonly replicated by the layer above – the MySQL Server using MySQL Replication. The MySQL Server provides access to the data – there is no point in the data being there if you can’t get at it! It’s a common misconception that having redundancy at these two levels is enough to have a HA system but you also need to look at the system from the top-down.

To have a HA service, there needs to be redundancy at the application layer; in itself this is very straight-forward, just load balance all of the service requests over a pool of application servers which are all running the same application logic. If the service were something as simple as a random number generator then this would be fine but most useful applications need to access data and as soon as you move beyond a single database server (for example because it needs to be HA) then a way is needed to connect the application server to the correct data source. In a HA system, the routing isn’t a static function, if one database server should fail (or be taken down for maintenance) the application should be directed instead to an alternate database. Some HA systems implement this routing function by introducing a proxy process between the application and the database servers; others use a virtual IP address which can be migrated to the correct server. When using MySQL Fabric, this routing function is implemented within the Fabric-aware MySQL connector library that’s used by the application server processes.

What MySQL Fabric Adds in Terms of High Availability

MySQL Fabric has the concept of a HA group which is a pool of two or more MySQL Servers; at any point in time, one of those servers is the Primary (MySQL Replication master) and the others are Secondaries (MySQL Replication slaves). The role of a HA group is to ensure that access to the data held within that group is always available.

While MySQL Replication allows the data to be made safe by duplicating it, for a HA solution two extra components are needed and MySQL Fabric provides these:

      • Failure detection and promotion – the MySQL Fabric process monitors the Primary within the HA group and should that server fail then it selects one of the Secondaries and promotes it to be the Primary (with all of the other slaves in the HA group then receiving updates from the new master). Note that the connectors can inform MySQL Fabric when they observe a problem with the Primary and the MySQL Fabric process uses that information as part of its decision making process surrounding the state of the servers in the farm.
      • Routing of database requests – When MySQL Fabric promotes the new Primary, it updates the state store and notifies the connectors so that they can refresh their caches with the updated routing information. In this way, the application does not need to be aware that the topology has changed and that writes need to be sent to a different destination.

Scaling Out – Sharding

When nearing the capacity or write performance limit of a single MySQL Server (or HA group), MySQL Fabric can be used to scale-out the database servers by partitioning the data across multiple MySQL Server “groups”. Note that a group could contain a single MySQL Server or it could be a HA group.

MySQL Fabric Architecture

The administrator defines how data should be partitioned/sharded between these servers; this is done by creating shard mappings. A shard mapping applies to a set of tables and for each table the administrator specifies which column from those tables should be used as a shard key (the shard key will subsequently be used by MySQL Fabric to calculate which shard a specific row from one of those tables should be part of). Because all of these tables use the same shard key and mapping, the use of the same column value in those tables will result in those rows being in the same shard – allowing a single transaction to access all of them. For example, if using the subscriber-id column from multiple tables then all of the data for a specific subscriber will be in the same shard. The administrator then defines how that shard key should be used to calculate the shard number:

      • HASH: A hash function is run on the shard key to generate the shard number. If values held in the column used as the sharding key don’t tend to have too many repeated values then this should result in an even partitioning of rows across the shards.
      • RANGE: The administrator defines an explicit mapping between ranges of values for the sharding key and shards. This gives maximum control to the user of how data is partitioned and which rows should be co-located.

When the application needs to access the sharded database, it sets a property for the connection that specifies the sharding key – the Fabric-aware connector will then apply the correct range or hash mapping and route the transaction to the correct shard.

If further shards/groups are needed then MySQL Fabric can split an existing shard into two and then update the state-store and the caches of routing data held by the connectors. Similarly, a shard can be moved from one HA group to another.

Note that a single transaction or query can only access a single shard and so it is important to select shard keys based on an understanding of the data and the application’s access patterns. It doesn’t always make sense to shard all tables as some may be relatively small and having their full contents available in each group can be beneficial given the rule about no cross-shard queries. These global tables are written to a ‘global group’ and any additions or changes to data in those tables are automatically replicated to all of the other groups. Schema changes are also made to the global group and replicated to all of the others to ensure consistency.

To get the best mapping, it may also be necessary to modify the schema if there isn’t already a ‘natural choice’ for the sharding keys.

Worked Example

The example that this post steps through starts by setting up MySQL Fabric itself and then uses it to manage HA using a group of MySQL Servers. An example application will store data in this new configuration. After that, the example will introduce shards to the server farm in order to scale out capacity and read/write performance.

The following sections set up the sharded MySQL configuration shown here before running some (Python) code against – with queries and transactions routed to the correct MySQL Server.

Building the MySQL Fabric Framework

Note that this section is repeated from the earlier two posts and so can be skipped if you’ve already worked through one of them.

The machines being used already have MySQL 5.6 installed (though in a custom location) and so the only software pre-requisite is to install the MySQL connector for Python from the connector download page and MySQL Fabric (part of MySQL Utilities) from the MySQL Fabric download page:

[root@fab1 mysql ~]# rpm -i mysql-connector-python-1.2.2-1.el6.noarch.rpm
[root@fab1 mysql ~]# rpm -i mysql-utilities-1.4.3-1.el6.noarch.rpm

MySQL Fabric needs access to a MySQL Database to store state and routing information for the farm of servers; if there isn’t already a running MySQL Server instance that can be used for this then it’s simple to set one up:

[mysql@fab1 ~]$ mkdir myfab
[mysql@fab1 ~]$ cd myfab/
[mysql@fab1 myfab]$ mkdir data  
[mysql@fab1 myfab]$ cat my.cnf
[mysqld]
datadir=/home/mysql/myfab/data
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab1
report-port=3306
server-id=1
log-bin=fab-bin.log
[mysql@fab1 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --datadir=/home/mysql/myfab/data/ 

2014-02-12 16:55:45 1298 [Note] Binlog end
2014-02-12 16:55:45 1298 [Note] InnoDB: FTS optimize thread exiting.
2014-02-12 16:55:45 1298 [Note] InnoDB: Starting shutdown...
2014-02-12 16:55:46 1298 [Note] InnoDB: Shutdown completed; log sequence number 1600607
2014-02-12 16:55:46 1298 [Note] /home/mysql/mysql//bin/mysqld: Shutdown complete

[mysql@fab1 ~]$ mysqld --defaults-file=/home/mysql/myfab/my.cnf &

MySQL Fabric needs to be able to access this state store and so a dedicated user is created (note that the fabric database hasn’t yet been created – that will be done soon using the mysqlfabric command):

[mysql@fab1 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON fabric.* \
    TO fabric@localhost";

All of the management requests that we make for MySQL Fabric will be issued via the mysqlfabric command. This command is documented in the MySQL Fabric User Guide but sub-commands can be viewed from the terminal using the list-commands option:

[mysql@fab1 ~]$ mysqlfabric help commands
group activate              Activate failure detector for server(s) in a group.
group description           Update group's description.
group deactivate            Deactivate failure detector for server(s) in a
group.
group create                Create a group.
group remove                Remove a server from a group.
group add                   Add a server into group.
group health                Check if any server within a group has failed and report health information.
group lookup_servers        Return information on existing server(s) in a group.
group destroy               Remove a group.
group demote                Demote the current master if there is one.
group promote               Promote a server into master.
group lookup_groups         Return information on existing group(s).
dump fabric_nodes           Return a list of Fabric servers.
dump shard_index            Return information about the index for all mappings matching any of the patterns provided.
dump sharding_information   Return all the sharding information about the tables passed as patterns.
dump servers                Return information about servers.
dump shard_tables           Return information about all tables belonging to mappings matching any of the provided patterns.
dump shard_maps             Return information about all shard mappings matching any of the provided patterns.
manage teardown             Teardown Fabric Storage System.
manage stop                 Stop the Fabric server.
manage setup                Setup Fabric Storage System.
manage ping                 Check whether Fabric server is running or not.
manage start                Start the Fabric server.
manage logging_level        Set logging level.
server set_weight           Set a server's weight.
server lookup_uuid          Return server's uuid.
server set_mode             Set a server's mode.
server set_status           Set a server's status.
role list                   List roles and associated permissions
user add                    Add a new Fabric user.
user password               Change password of a Fabric user.
user list                   List users and their roles
user roles                  Change roles for a Fabric user  * protocol: Protocol of the user (for example 'xmlrpc') * roles: Comma separated list of roles, IDs or names (see `role list`)
user delete                 Delete a Fabric user.
threat report_error         Report a server error.
threat report_failure       Report with certantity that a server has failed or is unreachable.
sharding list_definitions   Lists all the shard mapping definitions.
sharding remove_definition  Remove the shard mapping definition represented by the Shard Mapping ID.
sharding move_shard         Move the shard represented by the shard_id to the destination group.
sharding disable_shard      Disable a shard.
sharding remove_table       Remove the shard mapping represented by the Shard Mapping object.
sharding split_shard        Split the shard represented by the shard_id into the destination group.
sharding create_definition  Define a shard mapping.
sharding add_shard          Add a shard.
sharding add_table          Add a table to a shard mapping.
sharding lookup_table       Fetch the shard specification mapping for the given table
sharding enable_shard       Enable a shard.
sharding remove_shard       Remove a Shard.
sharding list_tables        Returns all the shard mappings of a particular sharding_type.
sharding prune_shard        Given the table name prune the tables according to the defined sharding specification for the table.
sharding lookup_servers     Lookup a shard based on the give sharding key.
event trigger               Trigger an event.
event wait_for_procedures   Wait until procedures, which are identified through their uuid in a list and separated by comma, finish 
							their execution.

MySQL Fabric has its own configuration file (note that its location can vary depending on your platform and how MySQL Utilities were installed). The contents of this configuration file should be reviewed before starting the MySQL Fabric process (in this case, the mysqldump_program and mysqlclient_program settings needed to be changed as MySQL was installed in a user’s directory) and the MySQL Fabric management port was changed to 8080 and authentication for the management interface was disabled:

[root@fab1 mysql]# cat /etc/mysql/fabric.cfg
[DEFAULT]
prefix =
sysconfdir = /etc
logdir = /var/log

[logging]
url = file:///var/log/fabric.log
level = INFO

[storage]
auth_plugin = mysql_native_password
database = fabric
user = fabric
address = localhost:3306
connection_delay = 1
connection_timeout = 6
password =
connection_attempts = 6

[failure_tracking]
notification_interval = 60
notification_clients = 50
detection_timeout = 1
detection_interval = 6
notifications = 300
detections = 3
failover_interval = 0
prune_time = 3600

[servers]
password =
user = fabric

[connector]
ttl = 1

[protocol.xmlrpc]
disable_authentication = no
ssl_cert =
realm = MySQL Fabric
ssl_key =
ssl_ca =
threads = 5
user = admin
address = localhost:32274
password = admin

[executor]
executors = 5

[sharding]
mysqldump_program = /home/mysql/mysql/bin/mysqldump
mysqlclient_program = /home/mysql/mysql/bin/mysql

The final step before starting the MySQL Fabric process is to create the MySQL Fabric schema within the state store:

[root@fab1 mysql]# mysqlfabric manage setup --param=storage.user=fabric
[INFO] 1399476439.536728 - MainThread - Initializing persister: user \
	(fabric), server (localhost:3306), database (fabric).
[INFO] 1399476451.330008 - MainThread - Initial password for admin/xmlrpc \
	set
Password set for admin/xmlrpc from configuration file.
[INFO] 1399476451.333563 - MainThread - Password set for admin/xmlrpc \
	from configuration file.

An optional step is then to check for yourself that the schema is indeed there:

[mysql@fab1 ~]$ mysql --protocol=tcp -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| fabric             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use fabric;show tables;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+-------------------+
| Tables_in_fabric  |
+-------------------+
| checkpoints       |
| error_log         |
| group_replication |
| groups            |
| permissions       |
| role_permissions  |
| roles             |
| servers           |
| shard_maps        |
| shard_ranges      |
| shard_tables      |
| shards            |
| user_roles        |
| users             |
+-------------------+
14 rows in set (0.00 sec)

The MySQL Fabric process can now be started; in this case the process will run from the terminal from which it’s started but the --daemonize option can be used to make it run as a daemon.

[mysql@fab1 ~]$ mysqlfabric manage start
[INFO] 1399476805.324729 - MainThread - Fabric node starting.
[INFO] 1399476805.327456 - MainThread - Initializing persister: user \
	(fabric), server (localhost:3306), database (fabric).
[INFO] 1399476805.335908 - MainThread - Initial password for admin/xmlrpc \
	set
Password set for admin/xmlrpc from configuration file.
[INFO] 1399476805.339028 - MainThread - Password set for admin/xmlrpc \
	from configuration file.
[INFO] 1399476805.339868 - MainThread - Loading Services.
[INFO] 1399476805.359542 - MainThread - Starting Executor.
[INFO] 1399476805.360668 - MainThread - Setting 5 executor(s).
[INFO] 1399476805.363478 - Executor-0 - Started.
[INFO] 1399476805.366553 - Executor-1 - Started.
[INFO] 1399476805.368680 - Executor-2 - Started.
[INFO] 1399476805.372392 - Executor-3 - Started.
[INFO] 1399476805.376179 - MainThread - Executor started.
[INFO] 1399476805.382025 - Executor-4 - Started.
[INFO] 1399476805.385570 - MainThread - Starting failure detector.
[INFO] 1399476805.389736 - XML-RPC-Server - XML-RPC protocol server \
	('127.0.0.1', 8080) started.
[INFO] 1399476805.390695 - XML-RPC-Server - Setting 5 XML-RPC session(s).
[INFO] 1399476805.393918 - XML-RPC-Session-0 - Started XML-RPC-Session.
[INFO] 1399476805.396812 - XML-RPC-Session-1 - Started XML-RPC-Session.
[INFO] 1399476805.399596 - XML-RPC-Session-2 - Started XML-RPC-Session.
[INFO] 1399476805.402650 - XML-RPC-Session-3 - Started XML-RPC-Session.
[INFO] 1399476805.405305 - XML-RPC-Session-4 - Started XML-RPC-Session.

If the process had been run as a daemon then it’s useful to be able to check if it’s actually running:

[mysql@fab1 ~]$ mysqlfabric manage ping
Command :
{ success     = True
  return      = True
  activities  =
}

Adding MySQL Servers to Create a HA Farm

At this point, MySQL Fabric is up and running but it has no MySQL Servers to manage. This figure shows the what the configuration will look like once MySQL Servers have been added to create a HA server farm.

Single shard HA with MySQL Fabric

Three MySQL Servers will make up the managed HA group – each running on a different machine – these are the configuration files for each (there’s no detailed commentary as this is standard MySQL stuff):


[mysql@fab2 myfab]$ cat my2.cnf
[mysqld]
datadir=/home/mysql/myfab/data2
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab2.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab2
report-port=3306
server-id=20
log-bin=fab2-bin.log

[mysql@fab3 myfab]$ cat my3.cnf
[mysqld]
datadir=/home/mysql/myfab/data3
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab3.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab3
report-port=3306
server-id=30
log-bin=fab3-bin.log

[mysql@fab4 myfab]$ cat my4.cnf
[mysqld]
datadir=/home/mysql/myfab/data4
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab4.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab4
report-port=3306
server-id=40
log-bin=fab3-bin.log

These MySQL Servers can then be bootstrapped and started:

[mysql@fab2 myfab]$ mkdir data2
[mysql@fab3 myfab]$ mkdir data3
[mysql@fab4 myfab]$ mkdir data3

[mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my2.cnf \
    --datadir=/home/mysql/myfab/data2/
[mysql@fab3 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my3.cnf \
    --datadir=/home/mysql/myfab/data3/
[mysql@fab4 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my4.cnf \
    --datadir=/home/mysql/myfab/data4/

[mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my2.cnf &
[mysql@fab3 ~]$ mysqld --defaults-file=/home/mysql/myfab/my3.cnf &
[mysql@fab4 ~]$ mysqld --defaults-file=/home/mysql/myfab/my4.cnf &

[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab3 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab4 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab3 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab4 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"

At this point, the MySQL Fabric process (and its associate state store) is up and running, as are the MySQL Servers that will become part of the HA group. MySQL Fabric is now able to access and manipulate those MySQL Servers and so they can now be added to a HA group called group_id-1.

[mysql@fab1 myfab]$ mysqlfabric group create group_id-1
Procedure :
{ uuid        = 7e0c90ec-f81f-4ff6-80d3-ae4a8e533979,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 myfab]$ mysqlfabric group add group_id-1 \
    192.168.56.102:3306
Procedure :
{ uuid        = 073f421a-9559-4413-98fd-b839131ea026,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 myfab]$ mysqlfabric group add group_id-1 \
	192.168.56.103:3306
Procedure :
{ uuid        = b0f5b04a-27e6-46ce-adff-bf1c046829f7,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 myfab]$ mysqlfabric group add group_id-1 \
	192.168.56.104:3306
Procedure :
{ uuid        = 520d1a7d-1824-4678-bbe4-002d0bae5aaa,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

The mysqlfabric command can then be used to confirm the list of servers that are part of the HA group:

[mysql@fab1 ~]$ mysqlfabric group lookup_groups
Command :
{ success     = True
  return      = [{'group_id': 'group_id-1', 'description': '', 'master_uuid': '', 'failure_detector': False}]
  activities  =
}

[mysql@fab1 ~]$ mysqlfabric group lookup_servers group_id-1
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': \
  			'00f9831f-d602-11e3-b65e-0800271119cb', 'mode': 'READ_ONLY', \
  			'weight': 1.0, 'address': '192.168.56.104:3306'}, \
  		{'status': 'SECONDARY', 'server_uuid': \
  			'f6fe224e-d601-11e3-b65d-0800275185c2', 'mode': 'READ_ONLY', \
  			'weight': 1.0, 'address': '192.168.56.102:3306'}, 
  		{'status': 'SECONDARY', 'server_uuid': \
  			'fbb5c440-d601-11e3-b65d-0800278bafa8', 'mode': 'READ_ONLY', \
  			'weight': 1.0, 'address': '192.168.56.103:3306'}]
  activities  =
}

Note that all of the MySQL Servers are reported as being Secondaries (in other words, none of them is acting as the MySQL Replication master). The next step is to promote one of the servers to be the Primary; in this case the uuid of the server we want to promote is provided but that isn’t required – in which case MySQL Fabric will select one.

[mysql@fab1 myfab]$ mysqlfabric group promote group_id-1 \
	--slave_id 00f9831f-d602-11e3-b65e-0800271119cb
Procedure :
{ uuid        = c875371b-890c-49ff-b0a5-6bbc38be7097,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 myfab]$ mysqlfabric group lookup_servers group_id-1
Command :
{ success     = True
  return      = [
	{'status': 'PRIMARY', 'server_uuid': '00f9831f-d602-11e3-b65e-0800271119cb', \
	  	'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.56.104:3306'}, \
  	{'status': 'SECONDARY', 'server_uuid': 'f6fe224e-d601-11e3-b65d-0800275185c2', \
  		'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.102:3306'}, \
	{'status': 'SECONDARY', 'server_uuid': 'fbb5c440-d601-11e3-b65d-0800278bafa8', \
		'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.103:3306'}]
  activities  =
}

Note that fab4 is now showing as the Primary; any of the Secondary servers can also be queried to confirm that they are indeed MySQL replication slaves of the Primary.

[mysql@fab1 ~]$ mysql -h 192.168.56.103 -P3306 -u root -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.104
                  Master_User: fabric
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: fab3-bin.000003
          Read_Master_Log_Pos: 487
               Relay_Log_File: fab3-relay-bin.000002
                Relay_Log_Pos: 695
        Relay_Master_Log_File: fab3-bin.000003
             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: 487
              Relay_Log_Space: 898
              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: 40
                  Master_UUID: 00f9831f-d602-11e3-b65e-0800271119cb
             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 the slave I/O \
      							thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 00f9831f-d602-11e3-b65e-0800271119cb:1-2
            Executed_Gtid_Set: 00f9831f-d602-11e3-b65e-0800271119cb:1-2,\
            					fbb5c440-d601-11e3-b65d-0800278bafa8:1-2
                Auto_Position: 1

At this stage, the MySQL replication relationship is configured and running but there isn’t yet High Availability as MySQL Fabric is not monitoring the state of the servers – the final configuration step fixes that:

[mysql@fab1 ~]$ mysqlfabric group activate group_id-1
Procedure :
{ uuid        = 40a5e023-06ba-4e1e-93de-4d4195f87851,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

Everything is now set up to detect if the Primary (master) should fail and in the event that it does, promote one of the Secondaries to be the new Primary. If using one of the MySQL Fabric-aware connectors (initially PHP, Python and Java) then that failover can be transparent to the application.

Run an Application Against the HA Farm

The code that follows shows how an application can accesses the new HA group – in this case, using the Python connector. First an application table is created:

[mysql@fab1 myfab]$ cat setup_table_ha.py
import mysql.connector
from mysql.connector import fabric

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", \
    	"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
)

conn.set_property(mode=fabric.MODE_READWRITE, group="group_id-1")
cur = conn.cursor()
cur.execute(
"CREATE TABLE IF NOT EXISTS subscribers ("
"   sub_no INT, "
"   first_name CHAR(40), "
"   last_name CHAR(40)"
")"
) 

Note the following about that code sample:

      • The connector is provided with the address for the MySQL Fabric process localhost:32274 rather than any of the MySQL Servers
      • The mode property for the connection is set to fabric.MODE_READWRITE which the connector will interpret as meaning that the transaction should be sent to the Primary (as that’s where all writes must be executed so that they can be replicated to the Secondaries)
      • The group property is set to group_id-1 which is the name that was given to the single HA Group

This code can now be executed and then a check made on one of the Secondaries that the table creation has indeed been replicated from the Primary.

[mysql@fab1 myfab]$ python setup_table_ha.py
[mysql@fab1 myfab]$ mysql -h 192.168.56.103 -P3306 -u root \
    -e "use test;show tables"
+----------------+
| Tables_in_test |
+----------------+
| subscribers    |
+----------------+

The next step is to add some rows to the table:

[mysql@fab1 myfab]$ cat add_subs_ha.py
import mysql.connector
from mysql.connector import fabric

def add_subscriber(conn, sub_no, first_name, last_name):
    conn.set_property(group="group_id-1", mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO subscribers VALUES (%s, %s, %s)",
        (sub_no, first_name, last_name)
        )

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", \
    	"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
    )

conn.set_property(group="group_id-1", mode=fabric.MODE_READWRITE)

add_subscriber(conn, 72, "Billy", "Fish")
add_subscriber(conn, 500, "Billy", "Joel")
add_subscriber(conn, 1500, "Arthur", "Askey")
add_subscriber(conn, 5000, "Billy", "Fish")
add_subscriber(conn, 15000, "Jimmy", "White")
add_subscriber(conn, 17542, "Bobby", "Ball")
[mysql@fab1 myfab]$ python add_subs_ha.py
[mysql@fab1 myfab]$ mysql -h 192.168.56.103 -P3306 -u root \
    -e "select * from test.subscribers"
+--------+------------+-----------+
| sub_no | first_name | last_name |
+--------+------------+-----------+
|     72 | Billy      | Fish      |
|    500 | Billy      | Joel      |
|   1500 | Arthur     | Askey     |
|   5000 | Billy      | Fish      |
|  15000 | Jimmy      | White     |
|  17542 | Bobby      | Ball      |
+--------+------------+-----------+

And then the data can be retrieved (note that the mode parameter for the connection is set to fabric.MODE_READONLY and so the connector knows that it can load balance the requests across any MySQL Servers in the HA Group).

mysql@fab1 myfab]$ cat read_table_ha.py
import mysql.connector
from mysql.connector import fabric

def find_subscriber(conn, sub_no):
    conn.set_property(group="group_id-1", mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    cur.execute(
        "SELECT first_name, last_name FROM subscribers "
        "WHERE sub_no = %s", (sub_no, )
        )
    for row in cur:
        print row

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", \
    	"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
    )

find_subscriber(conn, 72)
find_subscriber(conn, 500)
find_subscriber(conn, 1500)
find_subscriber(conn, 5000)
find_subscriber(conn, 15000)
find_subscriber(conn, 17542)

[mysql@fab1 myfab]$ python read_table_ha.py
(u'Billy', u'Fish')
(u'Billy', u'Joel')
(u'Arthur', u'Askey')
(u'Billy', u'Fish')
(u'Jimmy', u'White')
(u'Bobby', u'Ball')

Note that if the Secondary servers don’t all have the same performance then you can skew the ratio for how many reads are sent to each one using the mysqlfabric server set_weight command – specifying a value between 0 and 1 (default is 1 for all servers). Additionally, the mysqlfabric server set_mode command can be used to specify if the Primary should receive some of the reads (READ_WRITE) or only writes (WRITE_ONLY).

The next section describes how this configuration can be extended to add scalability by sharding the table data (and it can be skipped if that isn’t needed).

Adding Scale-Out with Sharding

The example in this section builds upon the previous one by adding more servers in order to scale out the capacity and read/write performance of the database. The first step is to create a new group (which is named global-group in this example) – the Global Group is a special HA group that performs two critical functions:

      • Any data schema changes are applied to the Global Group and from there they will be replicated to each of the other HA Groups
      • If there are tables that contain data that should be replicated to all HA groups (rather than sharded) then any inserts, updates or deletes will be made on the Global Group and then replicated to the others. Those tables are referred to as global tables.

The following figure illustrates what the configuration will look like once the Global Group has been created.

MySQL Fabric - HA Group with Global Group

The global group will contain three MySQL Servers running on the same host (in a product environment they would be split over multiple machines) and so the first step is to configure, bootstrap and start those servers.

[mysql@fab2 myfab]$ cat my2.1.cnf
[mysqld]
datadir=/home/mysql/myfab/data2.1
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab2.1.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3316
report-host=fab2
report-port=3316
server-id=21
log-bin=fab2.1-bin.log

[mysql@fab2 myfab]$ cat my2.2.cnf
[mysqld]
datadir=/home/mysql/myfab/data2.2
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab2.2.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3317
report-host=fab2
report-port=3316
server-id=22
log-bin=fab2.2-bin.log

[mysql@fab2 myfab]$ cat my2.3.cnf
[mysqld]
datadir=/home/mysql/myfab/data2.3
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab2.3.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3318
report-host=fab2
report-port=3318
server-id=23
log-bin=fab2.3-bin.log
[mysql@fab2 myfab]$ mkdir data2.1
[mysql@fab2 myfab]$ mkdir data2.2
[mysql@fab2 myfab]$ mkdir data2.3

[mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my2.1.cnf \
    --datadir=/home/mysql/myfab/data2.1/
[mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my2.2.cnf \
    --datadir=/home/mysql/myfab/data2.2/
mysql@fab2 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my2.3.cnf \
    --datadir=/home/mysql/myfab/data2.3/

[mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my2.1.cnf &
[mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my2.2.cnf &
[mysql@fab2 ~]$ mysqld --defaults-file=/home/mysql/myfab/my2.3.cnf &

[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3316 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3317 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3318 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3316 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3317 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab2 ~]$ mysql -h 127.0.0.1 -P3318 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"

The Global Group is defined and populated with MySQL Servers and then a Primary is promoted in the following steps:

[mysql@fab1]$ mysqlfabric group create global-group
Procedure :
{ uuid        = 5f07e324-ec0a-42b4-98d0-46112f607143,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

[mysql@fab1 ~]$ mysqlfabric group add global-group \
    192.168.56.102:3316
Procedure :
{ uuid        = ccf699f5-ba2c-4400-a8a6-f951e10d4315,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group add global-group \
    192.168.56.102:3317
Procedure :
{ uuid        = 7c476dda-3985-442a-b94d-4b9e650e5dfe,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group add global-group \
    192.168.56.102:3318
Procedure :
{ uuid        = 476fadd4-ca4f-49b3-a633-25dbe0ffdd11,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

[mysql@fab1 ~]$ mysqlfabric group promote global-group
Procedure :
{ uuid        = e818708e-6e5e-4b90-aff1-79b0b2492c75,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group lookup_servers global-group
Command :
{ success     = True
  return      = [
  	{'status': 'PRIMARY', 'server_uuid': '56a08135-d60b-11e3-b69a-0800275185c2',\
  		'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.56.102:3316'}, \
	{'status': 'SECONDARY', 'server_uuid': '5d5f5cf6-d60b-11e3-b69b-0800275185c2', \
		'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.102:3317'}, \
	{'status': 'SECONDARY', 'server_uuid': '630616f4-d60b-11e3-b69b-0800275185c2', \
		'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.102:3318'}]
  activities  =
}

As an application table has already been created within the original HA group, that will need to copied to the new Global Group:

mysql@fab1 myfab]$ mysqldump -d -u root --single-transaction \
    -h 192.168.56.102 -P3306 --all-databases > my-schema.sql
[mysql@fab1 myfab]$ mysql -h 192.168.56.102 -P3317 -u root \
    -e 'reset master'
[mysql@fab1 myfab]$ mysql -h 192.168.56.102 -P3317 -u root \
	< my-schema.sql
[mysql@fab1 ~]$ mysql -h 192.168.56.102 -P3317 -u root -e \
	'show create table test.subscribers\G'
*************************** 1. row ***************************
       Table: subscribers
Create Table: CREATE TABLE `subscribers` (
  `sub_no` int(11) DEFAULT NULL,
  `first_name` char(40) DEFAULT NULL,
  `last_name` char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

A shard mapping is an entity that is used to define how certain tables should be sharded between a set of HA groups. It is possible to have multiple shard mappings but in this example, only one will be used. When defining the shard mapping, there are two key parameters:

      • The type of mapping – can be either HASH or RANGE
      • The global group that will be used

The commands that follow define the mapping and identify the index number assigned to this mapping (in this example – 3) – in fact that same index is recovered in two different ways: using the mysqlfabric command and then reading the data directly from the state store:

[mysql@fab1 ~]$ mysqlfabric sharding create_definition HASH global-group
Procedure :
{ uuid        = 78ea7209-b073-4d03-9d8b-bda92cc76f32,
  finished    = True,
  success     = True,
  return      = 1,
  activities  =
}

[mysql@fab1 ~]$ mysqlfabric sharding list_definitions
Command :
{ success     = True
  return      = [[1, 'HASH', 'global-group']]
  activities  =
}

[mysql@fab1 ~]$ mysql -h 127.0.0.1 -P3306 -u root \
	-e 'SELECT * FROM fabric.shard_maps'
+------------------+-----------+--------------+
| shard_mapping_id | type_name | global_group |
+------------------+-----------+--------------+
|                1 | HASH      | global-group |
+------------------+-----------+--------------+

The next step is to define what columns from which tables should be used as the sharding key (the value on which the HASH function is executed or is compared with the defined RANGEs). In this example, only one table is being sharded (the subscribers table with the sub_no column being used as the sharding key) but the command can simply be re-executed for further tables. Note that the identifier for the shard mapping (1) is passed on the command-line:

[mysql@fab1 ~]$ mysqlfabric sharding add_table 1 test.subscribers sub_no
Procedure :
{ uuid        = 446aadd1-ffa6-4d19-8d52-4683f3d7c998,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

At this point, the shard mapping has been defined but no shards have been created and so the next step is to create a single shard and that shard will be stored in the existing HA group (group_id-1):

[mysql@fab1 ~]$ mysqlfabric sharding add_shard 1 group_id-1 --state=enabled
Procedure :
{ uuid        = 4efc038c-cd18-448d-be32-ca797c4c006f,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

[mysql@fab1]$ mysql -h 127.0.0.1 -P3306 -u root \
    -e 'select * from fabric.shards'
+----------+------------+---------+
| shard_id | group_id   | state   |
+----------+------------+---------+
|        1 | group_id-1 | ENABLED |
+----------+------------+---------+

At this point, the database has technically been sharded but of course it offers no scalability as there is only a single shard. The steps that follow evolve that configuration into one containing two shards as shown in the following figure.

MySQL Fabric with HA and sharding

Another HA group (group_id-2) is created, from three newly created MySQL Servers then one of the servers is promoted to be the Primary:

[mysql@fab5 myfab]$ cat my5.cnf
[mysqld]
datadir=/home/mysql/myfab/data5
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab5.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab5
report-port=3306
server-id=50
log-bin=fab5-bin.log

[mysql@fab6 myfab]$ cat my6.cnf
[mysqld]
datadir=/home/mysql/myfab/data6
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab6.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab6
report-port=3306
server-id=60
log-bin=fab6-bin.log

[mysql@fab7 myfab]$ cat my7.cnf
[mysqld]
datadir=/home/mysql/myfab/data7
basedir=/home/mysql/mysql
socket=/home/mysql/myfab/mysqlfab7.socket
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=fab7
report-port=3306
server-id=70
log-bin=fab7-bin.log
[mysql@fab5 myfab]$ mkdir data5
[mysql@fab6 myfab]$ mkdir data6
[mysql@fab7 myfab]$ mkdir data7

[mysql@fab5 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my5.cnf \
    --datadir=/home/mysql/myfab/data5/
[mysql@fab6 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my6.cnf \
    --datadir=/home/mysql/myfab/data6/
[mysql@fab7 mysql]$ scripts/mysql_install_db --basedir=/home/mysql/mysql/ \
    --defaults-file=/home/mysql/myfab/my7.cnf \
    --datadir=/home/mysql/myfab/data7/

[mysql@fab5 ~]$ mysqld --defaults-file=/home/mysql/myfab/my5.cnf &
[mysql@fab6 ~]$ mysqld --defaults-file=/home/mysql/myfab/my6.cnf &
[mysql@fab7 ~]$ mysqld --defaults-file=/home/mysql/myfab/my7.cnf &

[mysql@fab5 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab6 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab7 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO root@'%'"
[mysql@fab5 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab6 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab7 ~]$ mysql -h 127.0.0.1 -P3306 -u root -e "GRANT ALL ON *.* \
    TO fabric@'%'"
[mysql@fab1 ~]$ mysqlfabric group create group_id-2
Procedure :
{ uuid        = 4ec6237a-ca38-497c-b54d-73d1fa7bbe03,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

[mysql@fab1 ~]$ mysqlfabric group add group_id-2 192.168.56.105:3306
Procedure :
{ uuid        = fe679280-81ed-436c-9b7f-3d6f46987492,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group add group_id-2 192.168.56.106:3306
Procedure :
{ uuid        = 6fcf7e0c-c092-4d81-9898-448abf2b113c,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group add group_id-2 192.168.56.107:3306
Procedure :
{ uuid        = 8e9d4fbb-58ef-470d-81eb-8d92813427ae,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}
[mysql@fab1 ~]$ mysqlfabric group promote group_id-2
Procedure :
{ uuid        = 21569d7f-93a3-4bdc-b22b-2125e9b75fca,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

At this point, the new HA group exists but is missing the application schema and data. Before allocating a shard to the group, a reset master needs to be executed on the Primary for the group (this is required because changes have already been made on that server – if nothing else, to grant permissions for one or more users to connect remotely). The mysqlfabric group lookup_server command is used to first check which of the three servers is currently the Primary.

[mysql@fab1 ~]$ mysqlfabric group lookup_servers group_id-2
Command :
{ success     = True
  return      = [
  	{'status': 'PRIMARY', 'server_uuid': '10b086b5-d617-11e3-b6e7-08002767aedd', \
  		'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.56.105:3306'}, \
	{'status': 'SECONDARY', 'server_uuid': '5dc81563-d617-11e3-b6e9-08002717142f', \
		'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.106:3306'}, \
	{'status': 'SECONDARY', 'server_uuid': '83cae7b2-d617-11e3-b6ea-08002763b127', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.56.107:3306'}]
  activities  =
}
		
[mysql@fab1 myfab]$ mysql -h 192.168.56.105 -P3306 -uroot -e 'reset master'

Splitting the Shard

The next step is to split the existing shard, specifying the shard id (in this case 1) and the name of the HA group where the new shard will be stored:

[mysql@fab1]$ mysqlfabric sharding split_shard 1 group_id-2
Procedure :
{ uuid        = 4c559f6c-0b08-4a57-b095-364755636b7b,
  finished    = True,
  success     = True,
  return      = True,
  activities  =
}

Before looking at the application code changes that are needed to cope with the sharded data, a simple test can be run to confirm that the table’s existing data has indeed been split between the two shards:

[mysql@fab1]$ mysql -h 192.168.56.102 -P3306 -uroot \
    -e 'select * from test.subscribers'
+--------+------------+-----------+
| sub_no | first_name | last_name |
+--------+------------+-----------+
|    500 | Billy      | Joel      |
|   1500 | Arthur     | Askey     |
|   5000 | Billy      | Fish      |
|  17542 | Bobby      | Ball      |
+--------+------------+-----------+

[mysql@fab1]$ mysql -h 192.168.56.107 -P3306 -uroot \
    -e 'select * from test.subscribers'
+--------+------------+-----------+
| sub_no | first_name | last_name |
+--------+------------+-----------+
|     72 | Billy      | Fish      |
|  15000 | Jimmy      | White     |
+--------+------------+-----------+

The next example Python code adds some new rows to the subscribers table. Note that the tables property for the connection is set to test.subscribers and the key to the value of the sub_no column for that table – this is enough information for the Fabric-aware connector to choose the correct shard/HA group and then the fact that the mode property is set to fabric.MODE_READWRITE further tells the connector that the transaction should be sent to the Primary within that HA group.

 
[mysql@fab1 myfab]$ cat add_subs_shards2.py
import mysql.connector
from mysql.connector import fabric

def add_subscriber(conn, sub_no, first_name, last_name):
    conn.set_property(tables=["test.subscribers"], key=sub_no, \
    	mode=fabric.MODE_READWRITE)
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO subscribers VALUES (%s, %s, %s)",
        (sub_no, first_name, last_name)
        )

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", \
    	"password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
)

conn.set_property(tables=["test.subscribers"], scope=fabric.SCOPE_LOCAL)

add_subscriber(conn, 22, "Billy", "Bob")
add_subscriber(conn, 8372, "Banana", "Man")
add_subscriber(conn, 93846, "Bill", "Ben")
add_subscriber(conn, 5006, "Andy", "Pandy")
add_subscriber(conn, 15050, "John", "Smith")
add_subscriber(conn, 83467, "Tommy", "Cannon")
[mysql@fab1 myfab]$ python add_subs_shards2.py

[mysql@fab1 myfab]$ python add_subs_shards2.py

The mysql client can then be used to confirm that the new data has also been partitioned between the two shards/HA groups.

[mysql@fab1 myfab]$ mysql -h 192.168.56.103 -P3306 -uroot \
    -e 'select * from test.subscribers'
+--------+------------+-----------+
| sub_no | first_name | last_name |
+--------+------------+-----------+
|    500 | Billy      | Joel      |
|   1500 | Arthur     | Askey     |
|   5000 | Billy      | Fish      |
|  17542 | Bobby      | Ball      |
|     22 | Billy      | Bob       |
|   8372 | Banana     | Man       |
|  93846 | Bill       | Ben       |
|  15050 | John       | Smith     |
+--------+------------+-----------+

[mysql@fab1 myfab]$ mysql -h 192.168.56.107 -P3306 -uroot \
    -e 'select * from test.subscribers'
+--------+------------+-----------+
| sub_no | first_name | last_name |
+--------+------------+-----------+
|     72 | Billy      | Fish      |
|  15000 | Jimmy      | White     |
|   5006 | Andy       | Pandy     |
|  83467 | Tommy      | Cannon    |
+--------+------------+-----------+

Example Application Code (Includes Sharding)

The final example application code reads the row for each of the records that have been added, the key thing to note here is that the mode property for the connection has been set to fabric.MODE_READONLY so that the Fabric-aware Python connector knows that it can load balance requests over the Secondaries within the HA groups rather than sending everything to the Primary.

[mysql@fab1 myfab]$ cat read_table_shards2.py
import mysql.connector
from mysql.connector import fabric

def find_subscriber(conn, sub_no):
    conn.set_property(tables=["test.subscribers"], key=sub_no, mode=fabric.MODE_READONLY)
    cur = conn.cursor()
    cur.execute(
        "SELECT first_name, last_name FROM subscribers "
        "WHERE sub_no = %s", (sub_no, )
        )
    for row in cur:
        print row

conn = mysql.connector.connect(
    fabric={"host" : "localhost", "port" : 32274, "username": "admin", "password" : "admin"},
    user="root", database="test", password="",
    autocommit=True
    )

find_subscriber(conn, 22)
find_subscriber(conn, 72)
find_subscriber(conn, 500)
find_subscriber(conn, 1500)
find_subscriber(conn, 8372)
find_subscriber(conn, 5000)
find_subscriber(conn, 5006)
find_subscriber(conn, 93846)
find_subscriber(conn, 15000)
find_subscriber(conn, 15050)
find_subscriber(conn, 17542)
find_subscriber(conn, 83467)
[mysql@fab1 myfab]$ python read_table_shards2.py
(u'Billy', u'Bob')
(u'Billy', u'Fish')
(u'Billy', u'Joel')
(u'Arthur', u'Askey')
(u'Banana', u'Man')
(u'Billy', u'Fish')
(u'Andy', u'Pandy')
(u'Bill', u'Ben')
(u'Jimmy', u'White')
(u'John', u'Smith')
(u'Bobby', u'Ball')
(u'Tommy', u'Cannon')

Current Limitations

The initial version of MySQL Fabric is designed to be simple, robust and able to scale to thousands of MySQL Servers. This approach means that this version has a number of limitations, which are described here:

      • Sharding is not completely transparent to the application. While the application need not be aware of which server stores a set of rows and it doesn’t need to be concerned when that data is moved, it does need to provide the sharding key when accessing the database.
      • All transactions and queries need to be limited in scope to the rows held in a single shard, together with the global (non-sharded) tables. For example, Joins involving multiple shards are not supported.
      • Because the connectors perform the routing function, the extra latency involved in proxy-based solutions is avoided but it does mean that Fabric-aware connectors are required – at the time of writing these exist for PHP, Python and Java
      • The MySQL Fabric process itself is not fault-tolerant and must be restarted in the event of it failing. Note that this does not represent a single-point-of-failure for the server farm (HA and/or sharding) as the connectors are able to continue routing operations using their local caches while the MySQL Fabric process is unavailable.

MySQL Fabric Architecture & Extensibility

MySQL Fabric has been architected for extensibility at a number of levels. For example, in the first release the only option for implementing HA is based on MySQL Replication but in future releases we hope to add further options (for example, MySQL Cluster). We also hope to see completely new applications around the managing of farms of MySQL Servers – both from Oracle and the wider MySQL community.

The following diagram illustrates how new applications and protocols can be added using the pluggable framework.

MySQL Fabric - Extensible Architecture

Next Steps

We really hope that people try out MySQL Fabric and let us know how you get on; one way is to comment on this post, another is to post to the MySQL Fabric forum or if you think you’ve found a bug then raise a bug report.





MySQL & NoSQL – Best of Both Worlds. Upcoming webinar

MySQL Cluster LogoOn Thursday 22nd May I’ll be hosting a webinar explaining how you can get the best from the NoSQL world while still getting all of the benefits of a proven RDBMS. As always the webinar is free but please register here.

There’s often a lot of excitement around NoSQL Data Stores with the promise of simple access patterns, flexible schemas, scalability and High Availability. The downside can come in the form of losing ACID transactions, consistency, flexible queries and data integrity checks. What if you could have the best of both worlds?

This webinar shows how MySQL Cluster provides simultaneous SQL and native NoSQL access to your data, with a simple key-value API (Memcached), REST, JavaScript, Java or C++. You will hear how the MySQL Cluster architecture delivers in-memory real-time performance, 99.999% availability, on-line maintenance and linear, horizontal scalability through transparent auto-sharding.

This is also an opportunity to pick the brains of the MySQL Cluster engineering team and get your technical questions answered.

Times:

  • Thu, May 22: 09:00 Pacific time (America)
  • Thu, May 22: 10:00 Mountain time (America)
  • Thu, May 22: 11:00 Central time (America)
  • Thu, May 22: 12:00 Eastern time (America)
  • Thu, May 22: 13:00 São Paulo time
  • Thu, May 22: 16:00 UTC
  • Thu, May 22: 17:00 Western European time
  • Thu, May 22: 18:00 Central European time
  • Thu, May 22: 19:00 Eastern European time
  • Thu, May 22: 21:30 India, Sri Lanka
  • Fri, May 23: 00:00 Singapore/Malaysia/Philippines time
  • Fri, May 23: 00:00 China time
  • Fri, May 23: 01:00 日本
  • Fri, May 23: 02:00 NSW, ACT, Victoria, Tasmania (Australia)

Even if you can’t join the live webinar, it’s worth registering as you’ll be emailed a link to the replay as soon as it’s available.