måndag 17 februari 2020

Getting started with MySQL Cluster using MySQL Cluster Manager

Creating a MySQL NDB Cluster is really easy when using MySQL Cluster Manager aka "MCM".
In this blog post we will look at how to install and configure MCM and then create a new cluster using MCM commands.

Steps to install and and setup MCM as a service is explained here.

Once you have completed the setup of MCM next step is to install MySQL NDB Cluster.
First step is to download latest Cluster 7.6 binaries from: https://dev.mysql.com/downloads/cluster/
(7.6.13 as of today)

cd /tmp
wget https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.6/mysql-cluster-gpl-7.6.13-linux-glibc2.12-x86_64.tar.gz

cd /opt
tar xzf /tmp/mysql-cluster-gpl-7.6.13-linux-glibc2.12-x86_64.tar.gz
mv mysql-cluster-gpl-7.6.13-linux-glibc2.12-x86_64 mysql-cluster-7.6.13
Now it's time to start the MCM client and create our cluster.
Start mcm: PATH=$PATH:/opt/mysql-cluster-7.6.13/bin /opt/mcm/mcm1.4.8/bin/mcm
(For future need I recommend that you set the $PATH variable:
export PATH=$PATH:/opt/mysql-cluster-7.6.13/bin:/opt/mcm/mcm1.4.7/bin)

Run commands below in mcm client (change basedir to folder with cluster binaries):
(for details on commands below look here)
create site --hosts=127.0.0.1 mysite;
add package --basedir=/opt/mysql-cluster-7.6.13 cluster7613;
create cluster --package=cluster7613 --processhosts=ndb_mgmd@127.0.0.1,ndbmtd@127.0.0.1,ndbmtd@127.0.0.1 mycluster;
add process --processhosts=mysqld@127.0.0.1,mysqld@127.0.0.1 mycluster;
add process --processhosts=ndbapi@127.0.0.1,ndbapi@127.0.0.1 mycluster;
add process --processhosts=ndbapi@127.0.0.1,ndbapi@127.0.0.1 mycluster;
set port:mysqld:50=3310 mycluster;
set port:mysqld:51=3311 mycluster;
start  cluster mycluster;
show status -r mycluster;
Output from last command "show status" should look like:
+--------+----------+------------+---------+-----------+-------------+
| NodeId | Process  | Host       | Status  | Nodegroup | Package     |
+--------+----------+------------+---------+-----------+-------------+
| 49     | ndb_mgmd | 127.0.0.1  | running |           | cluster7611 |
| 1      | ndbmtd   | 127.0.0.1  | running | 0         | cluster7611 |
| 2      | ndbmtd   | 127.0.0.1  | running | 0         | cluster7611 |
| 50     | mysqld   | 127.0.0.1  | running |           | cluster7611 |
| 51     | mysqld   | 127.0.0.1  | running |           | cluster7611 |

Congratulations, you have installed and created your first MySQL NDB Cluster!!

If you want to learn more:
https://github.com/wwwted/ndb-cluster-workshop
https://dev.mysql.com/doc/mysql-cluster-manager/1.4/en/
https://www.slideshare.net/ocklin/mysql-ndb-cluster-101
https://www.slideshare.net/frazerClement/mysql-cluster-80-tutorial
https://www.slideshare.net/ocklin/mysql-ndb-cluster-80-sql-faster-than-nosql
https://www.slideshare.net/datacharmer/mysql-ndb-80-clusters-in-your-laptop-with-dbdeployer
http://mikaelronstrom.blogspot.com/

måndag 27 januari 2020

How to clone a MySQL test or development instance from InnoDB Cluster?


Introduction to InnoDB Cluster

If you have not heard about MySQL InnoDB Cluster MySQL, InnoDB Cluster is a built-in high-availability solution for MySQL. The key benefit over old high-availability solutions is that InnoDB Cluster is built into MySQL and supported on all platforms where MySQL is supported.

The key components of MySQL InnoDB Cluster:
- MySQL Group Replication
- MySQL Shell
- MySQL Router

MySQL Group Replication is a plugin that makes sure that; data is distributed to all nodes, conflicts are handled and also recovery if needed.
MySQL Shell makes is easy to configure and administer your InnoDB Cluster.
MySQL Router is the last part of InnoDB cluster, it's a lightweight middleware that provides transparent routing between the application and back-end MySQL Servers part of group replication.

If you want to get started with InnoDB Cluster:
- https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-userguide.html
- https://github.com/wwwted/MySQL-InnoDB-Cluster-3VM-Setup
- https://lefred.be/content/category/mysql/innodb-cluster/

Clone a test/development environment from InnoDB Cluster

Most users need some way to periodically refresh test and development environments from production. In the past many users solved thus using traditional master-slave replication, the solution was to setup a MySQL slave that use LVM to leverage snapshots to create new test/developer environments.

For InnoDB Cluster we can use MySQL Clone (available from MySQL 8.0.l7) to creating a test or development environment much easier.

Steps needed on InnoDB Cluster (create a dedicated clone user for the donor):
CREATE USER clone_user@'%' IDENTIFIED BY 'clone_password';
GRANT BACKUP_ADMIN ON *.* to clone_user@'%';
GRANT SELECT ON performance_schema.* TO clone_user@'%';
GRANT EXECUTE ON *.* to clone_user@'%';
You might limit the clone user to only be able to connect from some sub-net and not from all host (%). The InnoDB Cluster must run MySQL 8.0.17 or later.

Next we need to create the test or development server.
1) Start a new MySQL instance (using MySQL 8.0.17 or later).
2) Provision data using clone:
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL clone_valid_donor_list = '10.0.0.17:3306';
SET global log_error_verbosity=3;
CLONE INSTANCE FROM clone_user@10.0.0.17:3306 identified by 'clone_password';
UNINSTALL PLUGIN clone;
UNINSTALL PLUGIN group_replication;
Some notes on above, you need to configure the clone_valid_donor_list to the IP of the donor instance in the cluster you have selected. The log level is set to 3 so you can monitor progress of clone in the log file.
There are some controls in the MySQL Server that forces us to load the group replication plugin prior to executing the clone command. If you do not load the group replication plugin you will get an error like: ERROR 3870 (HY000): Clone Donor plugin group replication is not active in Recipient

Of course, the final step will be to clean/wash the data before handing over the MySQL instance to test or development, this step is not covered by this blog but using our masking and De-Identification can be used to solve this step ;)

I have tested above procedures using MySQL 8.0.18.

Happy Clustering!