MySQL Cluster 7.6 is GA!
If you’re eager to give the recently announced MySQL Cluster 7.6 GA a spin, I’ll walk you through a quick and simple way to get started using MySQL Cluster Manager 1.4.6.
Bootstrap and go!
- Download and install MCM and MySQL Cluster
- Bootstrap a cluster with MCM
$ mcmd –bootstrap - Connect to the database, and have at it
$ mysql -h 127.0.0.1 -P 3306 -u root
That simple.
Download and install
Grab the latest MCM and MySQL Cluster tarballs for your platform, and install.
I’ll be using the latest MCM 1.4.6 and MySQL Cluster 7.6.6 on linux, and unpacked the tarballs in their respective subfolders under /foo for simplicity. The top level directory of /foo needs the MySQL Cluster 7.6 binaries in ./cluster, and MySQL Cluster Manager binaries in ./mcm1.4.6 for the next step to work. The user running mcmd also needs write access to /foo.
|
1
2
3
4
|
$ tree -L 2 /foo foo ├── cluster └── mcm1.4.6 |
Should you need a download location, My Oracle Support (MOS) or eDelivery are the places to visit.
Bootstrap
The easiest way to get going with MySQL Cluster 7.6 is using the –bootstrap option for the mcmd.
This option will bootstrap a simple cluster containing 2 datanodes, 2 mysqlds, 1 ndb_mgmd, and 1 ndbapi slot for additional tools such as ndb_restore, on the local host. Do note that the bootstrapped cluster will have a mysqld running on the default mysqld port 3306, so if you already have a vanilla MySQL Server (non-cluster) running then you will need to stop it before proceeding.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ ./mcm1.4.6/bin/mcmd --bootstrap MySQL Cluster Manager 1.4.6 (64bit) started Connect to MySQL Cluster Manager by running "/foo/mcm1.4.6/bin/mcm" -a thinkpad:1862 Configuring default cluster 'mycluster'... Setting default_storage_engine to ndbcluster... Starting default cluster 'mycluster' version '5.7.22-ndb-7.6.6-cluster-commercial-advanced'... Cluster 'mycluster' started successfully ndb_mgmd thinkpad:1186 ndbmtd thinkpad ndbmtd thinkpad mysqld thinkpad:3306 mysqld thinkpad:3307 ndbapi * Connect to the database by running "/foo/cluster/bin/mysql" -h 127.0.0.1 -P 3306 -u root |
Cluster bootstrapped in about 30 seconds, and is now ready to play with. Have at it!
Follow the white rabbit
The mcmd –bootstrap output left us two hints – how to connect to the database, and how to connect to the mcmd.
Let’s try to connect to the database as suggested at the very end:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ "/foo/cluster/bin/mysql" -h 127.0.0.1 -P 3306 -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.22-ndb-7.6.6-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial) Copyright (c) 2000, 2018, 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 variables like "ndb_node%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | ndb_nodeid | 50 | +---------------+-------+ 1 row in set (0.01 sec) mysql> |
The mysqld we connected to on port 3306 is running the expected server version, and is connected to the cluster as nodeid 50. Looking good!
Poking around
Let’s have a look at mcmd as well. After all, that’s where we’ll execute our management commands. The connect string is printed at the top of the bootstrap output:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
$ "/foo/mcm1.4.6/bin/mcm" -a thinkpad:1862 MySQL Cluster Manager client started. This wrapper will spawn the mysql client to connect to mcmd Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 1.4.6 MySQL Cluster Manager Copyright (c) 2000, 2018, 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. mcm> version; +-------------------------------------+ | Version | +-------------------------------------+ | MySQL Cluster Manager 1.4.6 (64bit) | +-------------------------------------+ 1 row in set (0.00 sec) mcm> |
So, we have a connection to the mcmd. Let’s poke around some more
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
mcm> show settings; +-------------------+---------------+ | Setting | Value | +-------------------+---------------+ | copy-port | 0 | | log-file | /foo/mcmd.log | | log-level | message | | log-use-syslog | FALSE | | manager-directory | /foo/mcm_data | | manager-username | mcmd | | manager-password | ******** | | manager-port | 1862 | | xcom-port | 18620 | +-------------------+---------------+ 9 rows in set (0.00 sec) mcm> show settings --hostinfo; +-----------------+-------------------------+ | Property | Value | +-----------------+-------------------------+ | Hostname | thinkpad | | Platform | Linux 4.15.0-22-generic | | Processor cores | 4 | | Total memory | 7856 Mb | +-----------------+-------------------------+ 4 rows in set (0.00 sec) mcm> |
Apparently we’re on a 4.15 linux kernel with 4 cores and 8Gb RAM. Yup, sounds like my laptop.
Bootstrap – behind the scenes
Bootstrap will call the following set of commands behind the scenes:
- Create management site with a single host
- Define a package abstraction pointing to the installed cluster version
- Create a default cluster, using with default package
- Apply the minimal settings needed to the cluster
- Start the cluster
|
1
2
3
4
5
6
7
8
9
10
|
mcm> create site --hosts=thinkpad mysite; ... mcm> add package --basedir=/foo/cluster mypackage; ... mcm> create cluster -P mypackage -R ndb_mgmd@thinkpad,ndbmtd@thinkpad,ndbmtd@thinkpad,mysqld@thinkpad,mysqld@thinkpad,ndbapi@*,ndbapi@* mycluster; ... mcm> set portnumber:ndb_mgmd=49,port:mysqld:50=3306,port:mysqld:51=3307 mycluster; ... mcm> start cluster mycluster; ... |
If the bootstrap cluster doesn’t fit your needs, you can skip –bootstrap and tailor the site, package and cluster definition to your needs.
What else?
What else can mcmd do? A lot. The list commands command will give you a brief overview:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
|
mcm> list commands; +-------------------------------------------------------------------------------------+ | Help | +-------------------------------------------------------------------------------------+ | COMMANDS | | | | abort backup Abort an ongoing cluster backup. | | add hosts Add hosts to site. | | add package Add a package alias. | | add process Add cluster process. | | autotune Autotune a cluster to given use-case template. | | backup agents Backup the agents repository and metadata. | | backup cluster Backup a cluster. | | change log-level Change the log-level | | change process Change process type. | | collect logs Collect log files. | | create cluster Create a cluster. | | create site Create a site. | | delete cluster Delete a cluster. | | delete package Delete a package. | | delete site Delete a site. | | get Get configuration variables. | | import cluster Import a running cluster. | | import config Import the configuration of a running cluster. | | list backups List backup images. | | list clusters List all clusters. | | list commands List the help text. | | list hosts List hosts in site. | | list nextnodeids List next nodeids to be allocated. | | list packages List all packages. | | list processes List processes. | | list sites List all sites. | | remove hosts Remove hosts from site. | | remove process Remove a cluster process. | | reset Reset configuration variables. | | restart cluster Restart a cluster. | | restore cluster Restore a cluster. | | rotate log Rotate the mcmd log. | | set Set configuration variables. | | show settings Show agent settings. | | show status Show cluster, process, operation, progress or backup status. | | start cluster Start a cluster. | | start process Start a cluster process. | | stop agents Stop agents in site. | | stop cluster Stop a cluster. | | stop process Stop a cluster process. | | update process Update a cluster process. | | upgrade cluster Upgrade a cluster. | | version Print version information. | | | | GLOBAL OPTIONS | | Options that can be used with all commands | | | | --help|-? Print detailed help. | | | | Use ' --help' to see verbose help for individual commands. | +-------------------------------------------------------------------------------------+ 52 rows in set (0.00 sec) mcm> |
The –help option can be used with any command to provide further information.
Knobs and dials
Let’s change something. Assume my test dataset has 150 small tables. By default MySQL Cluster is limited to a maximum of 128 tables.
There was a SomethingTables setting in cluster to overcome this… I can’t remember what… Let’s use a wildcard query, including defaults with option -d, to find out:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mcm> get -d *Tables mycluster; +----------------------------------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +----------------------------------+-------+----------+---------+----------+---------+---------+---------+ | MaxNoOfTables | 128 | ndbmtd | 1 | | | Default | | | MaxNoOfTables | 128 | ndbmtd | 2 | | | Defalut | | | big_tables | false | mysqld | 50 | | | Default | | | ndb_show_foreign_key_mock_tables | false | mysqld | 50 | | | Default | | | skip_grant_tables | false | mysqld | 50 | | | Default | | | big_tables | false | mysqld | 51 | | | Default | | | ndb_show_foreign_key_mock_tables | false | mysqld | 51 | | | Default | | | skip_grant_tables | false | mysqld | 51 | | | Default | | +----------------------------------+-------+----------+---------+----------+---------+---------+---------+ 8 rows in set (0.06 sec) mcm> |
That’s the one – MaxNoOfTables. Doubling it should do fine, and leave me some headway.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mcm> set MaxNoOfTables:ndbmtd=256 mycluster; +-----------------------------------+ | Command result | +-----------------------------------+ | Cluster reconfigured successfully | +-----------------------------------+ 1 row in set (1 min 20.22 sec) mcm> get -d MaxNoOfTables:ndbmtd mycluster; +-----------------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +-----------------+-------+----------+---------+----------+---------+---------+---------+ | MaxNoOfTables | 256 | ndbmtd | 1 | | | Process | | | MaxNoOfTables | 256 | ndbmtd | 2 | | | Process | | +-----------------+-------+----------+---------+----------+---------+---------+---------+ 4 rows in set (0.10 sec) mcm> |
Great. Now all my test tables will fit.
There are a lot of knobs and dials to tweak, if you want. If you go astray, you can change back to the default setting using the reset command:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mcm> reset MaxNoOfTables:ndbmtd mycluster; +-----------------------------------+ | Command result | +-----------------------------------+ | Cluster reconfigured successfully | +-----------------------------------+ 1 row in set (1 min 10.05 sec) mcm> get -d MaxNoOfTables mycluster; +---------------+-------+----------+---------+----------+---------+---------+---------+ | Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment | +---------------+-------+----------+---------+----------+---------+---------+---------+ | MaxNoOfTables | 128 | ndbmtd | 1 | | | Default | | | MaxNoOfTables | 128 | ndbmtd | 2 | | | Default | | +---------------+-------+----------+---------+----------+---------+---------+---------+ 2 rows in set (0.01 sec) mcm> |
Back where we started.
Summary
Bootstrapping a MySQL Cluster using MCM is simple and quick, and MCM allows easy management of MySQL Cluster!
Go and give MySQL Cluster 7.6 GA a spin!