Wednesday 11 November 2020

MySQL Cluster Backup and Restore

MySQL Ndb Cluster provides durability for data by default via logging and checkpointing.

In addition, users can take backups at any time, which allows for disaster recovery, replication synchronisation, data portability and other use cases.

This post looks at the backup and restore mechanisms in MySQL Ndb Cluster.

MySQL Ndb Cluster architecture recap

MySQL Ndb Cluster is a distributed SQL relational database :

  • Designed for low overhead read + write scale out, high availability, high throughput and low latency.
  • Providing distributed parallel joins, transactions, row locks, foreign keys.
  • Data is primarily stored and managed by a set of independent data node processes.
  • Data is accessed via distributed MySQL servers and other clients.
  • Performance
    • Scale out read + write : Tables and indices are distributed across data nodes.
    • Scale up read + write : Tables and indices are distributed across Local Data Manager (LDM) components within each data node, with parallel checkpointing and redo logs.
    • Scale out + up of read + write : Fully parallel and distributed transaction prepare + commit.
  • High availability :
    • Tables and indices transactionally replicated across data nodes for high availability, locality, read scaling.
    • Automatic logging, checkpointing, failure detection, failover, recovery, resynchronisation
    • Online operations : Backup, schema changes, upgrade.
  • MySQL compatibility, async replication between clusters and InnoDB, connectors, 3rd party support.

MySQL Ndb Cluster is a superset of the MySQL Server functionality, and users may use a mix of Ndb and InnoDB tables and features.

Ndb backup + ndb_restore

MySQL Ndb Cluster has a built-in backup mechanism called Ndb backup.  Ndb backup captures the state of all of the tables and indexes stored in the cluster data nodes, allowing them to be restored to a transactionally consistent point in time.  As this data is partitioned and distributed across the cluster data nodes, the backup is also partitioned and distributed, with each data node producing a standalone set of backup filesets containing a subset of the cluster's data.  Restoring all of the backup filesets results in restoring all of the cluster's data.

Parallel backup across and within data nodes

Ndb backup filesets are restored using a tool called ndb_restore.  This tool can read the filesets generated by Ndb backup, and uses NdbApi to efficiently restore the data to a target cluster.  The backup files contain a logical rather than physical format - restoration is effectively re-insertion and modification of rows.  

Parallel restore across and within data nodes

This design allows ndb_restore to be flexible about restoring data, supporting :
 - Restore to a different target schema (1, 2, 3, 4, 5, 6)
 - Restore of a subset of tables and indices (1, 2, 3, 4)
 - Restore to a different table or index distribution
 - Restore to a different cluster size or topology
 - Restore to a different cluster software version
 - Restore to a different hardware or OS platform
 - Remote restore across a network

 

Flexible restore - different target schema, cluster, parallelism etc

As the backup is partitioned into logical shared-nothing filesets, the restore is also naturally partitioned and parallelised for minimal restoration time.

Note that Ndb Backup captures only the data stored in the data nodes, e.g. tables with ENGINE=NDB[CLUSTER].  InnoDB tables and indices, and other MySQL Server local objects such as Triggers, Stored procedures and Views must be backed up and restored using different mechanisms such as mysqldump.

Binary logs

Each Ndb Backup captures the state of a cluster at a transactionally consistent point in time, represented in Ndb as an epoch number.  Epoch numbers are also used in MySQL Ndb Cluster as transactional Binary log markers, dividing the parallel flow of committed transactions into consistent units.  This transactional alignment between backups and binary logs makes it easy to setup and manage async replication between clusters, and implement point in time recovery solutions.  The binlog position corresponding to a backup can be precisely determined, and replication can be established from that point onwards.


Indexes and Constraints

MySQL Ndb Cluster supports secondary unique and ordered indexes, and transactional foreign key constraints.  Unique and foreign key constraints are not enforceable until backup restoration is complete, and maintaining secondary indexes during data restoration consumes resources.  ndb_restore has options to temporarily disable and then rebuild secondary indexes and constraints around the bulk data loading phase of restore allowing :
 - Optimised restoration without the need to maintain indexes, check constraints
 - Avoidance of dependency problems with e.g. circular foreign key references, self references etc
 - Optimised parallel index build

All constraints are naturally checked as part of the constraint rebuild, so integrity is enforced.

Schema changes

Ndb backup filesets capture the schema of tables and indices.  ndb_restore uses this information to recreate tables and indexes prior to restoring data.  

When restoring data, ndb_restore compares the schema of tables and indexes in the target cluster to those captured in the backup.  ndb_restore optionally allows a number of differences, e.g. in numbers of columns, type widths etc, so that data captured from an old schema version can be restored into a new schema without intermediate transformation steps.

Recent versions have added support for column remapping, where data can be transformed, and changes to the set of primary key columns, allowing distribution optimisations.

These features can be used alongside the schema flexibility features of MySQL asynchronous replication, allowing replication to be established and synchronised from an old schema cluster to a new schema cluster, and vice-versa.

Compression

Ndb backup filesets can optionally be compressed using a parameter in the cluster configuration.  Compression is performed by background IO threads in the data node processes, prior to writing data to disk.  ndb_restore handles compressed backups automatically during restoration.

Encryption

From the 8.0.22 release, Ndb backup filesets can be encrypted.  Backup encryption provides protection for data which must leave the cluster security perimeter, and combines with e.g. MySQL Binlog encryption to help implement a secure async replication mechanism.  Backup encryption occurs in background IO threads in the data node processes.

Backups are encrypted and decrypted using a user supplied password.  For each file in each backup fileset, a unique random salt value is generated, and combined with the password via a key derivation function to generate a symmetric encryption key for that file.  The key is then used to encrypt the backup data using AES 256 CBC inline.  For restoration, ndb_restore combines the user supplied password with the per-file salt values to determine the unique decryption key for each file.

Key = KDF(RandomSalt, Password)

EncryptedData = AES_256_CBC(Key, PlainData)


Using an external password in this way allows users to manage backup encryption secrets in alignment with existing policies and systems.


Backup and restore parallelism

Each Ndb backup runs in parallel across all data nodes, with each node producing independent filesets.  At restore time, each ndb_restore instance restores batches of rows concurrently, and multiple ndb_restore instances can be run in parallel, each operating on independent backup filesets.

From the 8.0.16 release onwards, each data node can produce multiple backup filesets - one per Local Data Manager (LDM) component, and ndb_restore restores multiple backup filesets in parallel by default.


In the 8.0.20 release, the ability to use multiple ndb_restore instances to restore slices of a single backup fileset was added.


Triggering a backup

Users trigger a new Ndb backup using the management api (ndb_mgm / mgmapi).  The command in the ndb_mgm shell (version 8.0.22) looks like this :

ndb_mgm > START BACKUP [<backup id>] [ENCRYPT PASSWORD='<password>']
              [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]


The backup id affects the name of the backup files on disk.  If no backup id is supplied, then the cluster generates one internally.

If an encryption password is supplied, then the generated backup files will be encrypted using this password.  If the RequireEncryptedBackup parameter is set in the cluster configuration, then a password must always be supplied.

SnapshotStart and SnapshotEnd give some control over the choice of the transactionally consistent point in time that the backup represents - either at the start of the backup, before the backup is STARTED, or at the end of the backup, before the backup is COMPLETED.  The default is SnapshotEnd, but SnapshotStart can be useful for coordinating the backup of an Ndb cluster with other clusters and external systems.

The NoWait, Wait Started and Wait Completed options affect when the START BACKUP command itself will return control to the user, with Wait Completed as the default.

Restoring a backup

The ndb_restore tool operates on one or more backup filesets generated by a node at backup time.  The main options are :

  • --restore-meta : Read the schema metadata from the backup and create the same schema in the target cluster.
  • --restore-data : Read the backup data and log files, and use NdbApi to insert, update and delete data to restore to the backup consistency point.

Options can be combined, so that a single ndb_restore invocation performs multiple actions.

All backup filesets redundantly contain the schema metadata, but the --restore-meta step need only be performed once.  This step will recreate the schema as it was at backup time, though in cases where the table distribution was not explicitly set, it will be automatically adjusted to suit the target cluster.

For the --restore-data step, the rows in the backup fileset's DATA file are first inserted into the target cluster, then the entries from the backup fileset's LOG file are idempotently applied to bring the target cluster to a transactionally consistent state.

For a SNAPSHOTEND backup, the backup log contains actions to redo, which are applied in the order they were logged.
For a SNAPSHOTSTART backup, the backup log contains actions to undo, which are applied in the reverse of the order that they were logged.

Where there are schema changes, or transformations applied, these are performed as data is restored.  One exception is where type changes convert to/from/between BLOB types - in this case ndb_restore automatically uses internal staging tables to perform the restore in two steps.

ndb_restore can be used to restore data from backup filesets while a cluster is in operation, with some caveats :

  • Data being restored is only consistent when the restore is complete, before which :
    • Other clients can observe partially restored data
    • Constraints may be temporarily violated


Some other relevant ndb_restore options :

The ndb_restore tool can also be used to show the content of backup filesets in various ways.


Restoration sequences 

ndb_restore is a flexible tool.  Full cluster restoration normally follows a sequence like this.

  1. Restore/create schema - serial

    ndb_restore --restore-meta ...

    or some other means of creating a schema :
    • Apply a mysqldump schema dump
    • Execute a SQL script
    • Use ndb_restore --restore-meta, then ALTER the tables
    •  ...
     
    Tables or databases can be included or excluded.



  2. [ Drop indices and constraints - serial ]

    ndb_restore --disable-indexes ...

    This step is only required if there are unique or foreign key constraints on the tables being restored.

    Note that if the target cluster schema does not reflect the schema in the backup, then extra steps may be needed here to ensure all indices and constraints are disabled and rebuilt.



  3. Restore data - parallel/serial

    ndb_restore --restore-data ...

    This step must be performed for each node which generated backup filesets, but these steps can be done serially or in parallel as they are independent to each other.

    Tables or databases can be included or excluded.

    If the schema in the target cluster is different, then schema flexibility options may be required.
     


  4. [ Restore epoch - serial ]

    ndb_restore --restore-epoch ...

    This step is only necessary when restoring a backup from another cluster which will act as an async replication source.


     
  5. [ Rebuild indices and constraints - serial ]

    ndb_restore --rebuild-indexes ...

    This step is only necessary when indices and constraints have been disabled (step 2).
    While the step itself must be run serially, internally it makes use of the parallelism of the cluster for fast index and constraint rebuilds.


For restoring a backup from a single data node cluster, with no schema changes, a single command can perform all of these steps in one pass :


ndb_restore --restore-meta --disable-indexes --restore-data --restore-epoch --rebuild-indexes ...

However, it is more common that a backup has come from a multi node cluster, and requires a number of invocations of ndb_restore to fully restore.  This enables efficient parallel restore independent of cluster scale.

Thursday 29 October 2020

State of the DolphiNDB

Software development is always moving forward, and the latest version is always the best ... until the next one arrives.  When you visit the MySQL Ndb Cluster downloads page, you are naturally recommended to pull the latest MySQL Cluster 8.0.22 (announcement), but sometimes it is good to look back at the journey taken to reach this point.

7.x release series

Prior to the 8.0 releases, MySQL Ndb Cluster had a sequence of 7.x (x=0..6) releases based on MySQL Server versions 5.1, 5.6, 5.7.  In each of the 7.x release series, MySQL Ndb Cluster was under feature development for some time, producing a number of minor releases, until eventually one minor release was validated as being acceptable as the first generally available (GA) version.  Once a release series 7.x was generally available, further changes in that series were confined to bug fixes and small improvements, with new feature development becoming only available in a new release series 7.x+1.

Each of the 7.x releases significantly improved and extended MySQL Ndb Cluster, improving SQL execution, threading, performance, async replication, checkpointing, recovery, functionality etc, as well as picking up a number of fixes and minor improvements discovered by customers, users and our own QA.

MySQL Ndb Cluster includes all of the functionality of MySQL Server, plus the unique Ndb Cluster software.  As a result, each MySQL Ndb Cluster release series has also benefited from improvements in the related MySQL Server version - new features, optimisations and bug fixes.

7.5 release series

The 7.5 release series was based on the MySQL Server 5.7 release, becoming GA (announcement) with version 7.5.4 in October 2016.

The new features included :

  • Support for JSON columns.
  • Stored or virtual generated columns.
  • Read + Join performance optimisations : read from any replica, read from any node.
  • Improved performance, memory management, usability.
  • Use of InnoDB for replication state management.

Since then we have released a further sixteen minor versions of the 7.5 series, one every quarter.  Each minor version includes small bug fixes and improvements to both the Cluster and MySQL Server software, with the current latest version being 7.5.20 released in October 2020.

7.6 release series

The 7.6 release series was also based on the MySQL Server 5.7 release series, becoming GA (announcement) with version 7.6.6 in May 2018.  

The new features included :

  • Optimised checkpoints giving reduced disk space usage, reduced write amplification, reduced recovery time - increased availability, increased practical data limits.
  • Improved distributed SQL join support.
  • Unified memory management.
  • Optimised node-local communication.
  • Optimised distribution awareness.
  • New parallel import utility .
  • Many performance improvements.

Since the 7.6.6 release of MySQL Cluster 7.6, we have released a further ten minor versions, with small bug fixes and improvements.  The current latest version is 7.6.16 released in October 2020.

8.0 release series

The MySQL Cluster 8.0 series is based on the MySQL Server 8.0 series, which first became generally available (announcement) as release 8.0.11 in April 2018.   

The MySQL Server 8.0 release included many features in this first version, and has been continuously extended in each of the eleven releases since.  MySQL Ndb Cluster is following this release model with its 8.0 releases, with new features arriving in each minor release.

The first generally available release of MySQL Cluster 8.0 was 8.0.19 (announcement), released in January 2020.

MySQL Cluster 8.0.19 (January 2020)

Features included :

  • Simplified configuration, elastic memory management.
  • Better support for 3 and 4 replica clusters.
  • Support for up to 144 data nodes in a cluster.
  • Improved multithreading for parallel backup and restore.
  • Increase row size limit.
  • Improved distributed SQL execution performance and efficiency.
  • Disk stored columns performance and stability improvements .
  • Integration with MySQL Server 8.0.19 (Transactional MySQL Data Dictionary, many other MySQL Server features).
  • Usability improvements.

Since 8.0.19 there have been three further releases of MySQL Ndb Cluster 8.0 : 

MySQL Cluster 8.0.20 (April 2020)

Features included :

  • Further backup restoration process parallelism
  • Parallel intra-nodegroup links for high write volume applications
  • Improved distributed SQL processing of outer and semi joins
  • Performance improvements
  • Data integrity tooling improvements

MySQL Cluster 8.0.21 (July 2020)

Features included :

  • Support for up to 16TB of in-memory data per Data node
  • Read any replica tables by default
  • Further disk stored column performance and monitoring improvements
  • Improved distributed schema synchronisation

MySQL Cluster 8.0.22 (October 2020)

Features included :

  • Support for generating and restoring encrypted backups
  • Support for IPv6 addressing between all cluster node types
  • Improved performance for Blob column Insert, Update, Delete
  • Improved replication applier performance for Blob columns

As you can see, MySQL Ndb Cluster is constantly evolving and improving with several new releases every quarter.  The 8.0 releases continue to bring new capabilities on a regular basis, and there is no shortage of ideas in the pipeline.

For more information about MySQL Ndb Cluster, you can check the official documentation, see the getting started guide, follow a tutorial, read blogs, watch a video, check out recent Ndb 8.0 101, architecture or performance slides, or even read a book or two.

Edit : typo

Wednesday 5 February 2020

FOSDEM 2020

My post-FOSDEM detox has started - despite preparing by reading some survival guides, I hadn't really fathomed the variety and quantity (and quality) of beer that would flow over four days.  On reflection however, the beer flow has been far exceeded by the flow of tech content and conversation.

On Thursday and Friday I attended the pre-FOSDEM MySQL Days fringe event, where there were two tracks of talks and tutorials on MySQL including sessions on :
 - MySQL Server simplification
 - MySQL replication tooling improvements
 - Configuring group replication
 - Troubleshooting group replication
 - Using DNS for loadbalancing and failover
 - Upgrading to MySQL 8.0
 - New hash join implementation
 - Indexing JSON arrays
 - Datetime types
 - Check constraints
 - New VALUES() syntax
 - Security-hardening MySQL
 - Document store
 - MySQL Analytics
 - MySQL Replication performance modelling
 - Machine learning for MySQL service automation
 - Using reduced durability modes
 - Benchmarking
 - Using Vitesse
 - Using InnoDB Cluster

There were sessions from Oracle, Percona, Planetscale, Facebook, TicketSolve, DBdeployer and Uber.

Naturally the highlights for me were the Friday sessions focused on MySQL Ndb Cluster including :


https://twitter.com/lefred/status/1223274289744547840


  • MySQL NDB 8.0 clusters in your laptop with DBdeployer - Giuseppe Maxia

    This was a great session from the Datacharmer Giuseppe, where he talked about DBdeployer, which is a tool for trying out different database software versions, configurations and topologies.  Giuseppe demonstrated how to use DBDeployer to setup two MySQL Ndb Clusters very simply, starting them in under 30 seconds and testing both the synchronous internal replication within each cluster and the asynchronous replication between them.

    If you want to experiment with MySQL Cluster then I think DBDeployer looks like the easiest and quickest way to get started.
https://twitter.com/lefred/status/1223204988148690946

 
  • SQL with MySQL Ndb 8.0 faster than your NoSQL allow - Bernd Ocklin

    This session was focused on one of Ndb's traditional strengths - performance.  Bernd presented an array of benchmarks showing that MySQL Ndb Cluster continues to be faster than the NoSQL products invented in response to the claim that 'SQL cannot scale'. 

    The presentation had lots of graphs and numbers showing linear scalability, hundreds of millions of reads per second from a single cluster, winning efficiency + performance comparisons on YCSB, scalability of parallel disk I/O and scalability of Ndb as a Hadoop metadata and small file store.

    Bernd then gave some insights into the unique architecture of Ndb, which allows it to achieve these numbers.

    Finally there were some more graphs and numbers :

    First looking at TPC-H query performance as data node internal parallelism scales, and showing how the query processing parallelism in Ndb allows a 2-node distributed (HA) Ndb setup to outperform a single node InnoDB on some queries.

    Next looking at DBT2 (TPC-C) scalability as # replicas and # NodeGroups were increased, and also looking at the behaviour of DBT2 when run using Optane memory for system scale.
https://twitter.com/lefred/status/1223222430623326208


  • Boosting MySQL Ndb Cluster & MySQL InnoDB Cluster with ProxySQL v2 - Marco Tusa

    Marco from Percona showed how having a layer of proxies in front of a cluster can simplify providing an HA MySQL service, smooth operations and allow new features not available in MySQL itself.  He laid out architectures for InnoDB Cluster and Ndb Cluster, and noted that Ndb cluster was the only cluster that could scale out writes. 

    He enthusiastically followed this up with numbers by showing how adding MySQL Servers to a cluster can allow it to scale beyond the limits imposed by a single MySQLD.

  • MySQL Ndb 8.0 Cluster tutorial - Frazer Clement

    In my session we stepped through installing, configuring, starting and using a MySQL Cluster, including looking at high availability features, push down joins, foreign keys, JSON, Binlogging, Synchronized privilieges, and online scale-out.

    The audience were generous with feedback and questions, complementing my mid-90's style use of a text file, and generally surprised at how easily they had a cluster running on their laptop, even when manually performing all the steps required.
https://twitter.com/lefred/status/1223255796802314240


After my tutorial, I was interested in locating and consuming a drink, and luckily the MySQL Community dinner was just starting in the event venue.  This was a very agreeable event, well attended by the wider community including colleagues from 'old MySQL' that I had not seen for over 10 years.  It is great to see that while there are differences of opinion, and competition, we still have more in common than not.

https://twitter.com/lefred/status/1223340364683251712





On Saturday the action moved to the FOSDEM conference itself, running in a University campus.  Despite looking at the program I had not quite appreciated the volume of tracks, sessions and experts that would be present and talking in the rooms and the corridors.  The MySQL developer room alone had 17 talks from across the community, one every half hour for eight and a half hours !

I spent most of my time here, but also attended some talks in the other tracks, including some interesting performance talks in the Software Defined Networking room including a nice one on vectorising packet processing in DPDK.  Unfortunately I was unable to attend the run a mainframe on your laptop session in the Retrocomputing room, but luckily everything was being streamed, and the videos are being uploaded here.  While wandering around the University campus I stumbled upon a bar and tried some of the (open-source?) FOSDEM beer.

I will not bore you by enumerating all of the sessions, following beers and conversations, but I can definitely recommend reviewing the schedule and viewing some of the captured videos to get a bit of the experience for yourself.  Even better, if this sounds like your sort of thing, why not see if you can find a way to attend yourself, in 2021?

Many thanks to lefred for organising, running, presenting, photographing and tweeting across all the events.

Tuesday 28 January 2020

MySQL Cluster at FOSDEM 2020


Tomorrow I depart for Brussels, where FOSDEM 2020 is taking place this weekend.

This will be my first FOSDEM and I have heard many good things about it - there are quite a few FOSDEM survival guides online which give a sense of what it is about.

As part of the main FOSDEM event there is a MySQL, MariaDB and friends developer room, with sessions from across the MySQL ecosystem.  Additionally there is also a Databases main track and a PostgreSQL developer room as well as a wide variety of other tracks and developer rooms.

As if that were not enough, there is also a FOSDEM Fringe, before, during and after FOSDEM itself.  This year MySQL are holding a two day fringe event called pre-FOSDEM MySQL Days on Thursday and Friday, where there are two tracks, with over thirty sessions from the MySQL community team, engineers, product managers, customers, contributors and others.

MySQL Cluster is well represented on Friday with four talks from Bernd Ocklin, Giuseppe Maxia and Marco Tusa about using the new MySQL Cluster 8.0.  Later on Friday afternoon I will be running a tutorial on MySQL Cluster 8.0, covering configuration, setup, and looking at some of the existing and new features.

If you are going to be there then maybe we will see each other.  If not then maybe you should try to get there next year?