MySQL cluster using ndbcluster engine under FreeBSD 11

!SPOILER ALERT!

Not to discourage you, but make sure you read and understand MySQL cluster limitations thoroughly prior to start building the cluster. You don’t want to spend time on building the whole thing just to discover at the very end that you hit some hard coded limitation that can’t be resolved. It’s very easy to be trapped into Catch-22 here: some third-party vendor might say “why would we want to adjust our software to overcome MySQL limitations?”, and I’m sure MySQL dev team has had valid reasons to introduce those. So you end up in the middle, and you’re basically stuck.

For example, the vanilla typo3 distribution won’t work with ndbcluster engine out of the box. You hit the Row size limitation almost immediately, and unless you’re willing to spend time to analyze and optimize the structure of the typo3 database you’re blocked. You might be lucky, and it could be just a small change from varchar(2000) to varchar(1000), but you might be not. In addition to that, you’ll most certainly need a separate instance of MySQL with InnoDB or MyISAM, so you can import the DB, dump it, and start feeding it to the ndbcluster engine in batches. All these contribute to the time spent, and during the course of the installation you start considering alternatives, like changing the Operating System, and/or trying Galera for instance, or even switching to PostgreSQL altogether, but we’re not looking for easy paths, are we? :)

Anyway, to build our cluster we’ll need four servers. All four will be running latest FreeBSD 11.0-STABLE: 2 CPU, 4GB of RAM, 40GB HDD each. Be warned, running MySQL cluster on FreeBSD is not supported, although the underlying MySQL 5.7 is. There are quite a lot of dependencies to be built, therefore I’d recommend you compile everything on one server, and then clone it.

In NDB cluster concept, there are three types of cluster nodes:

– Management node: as the name implies, this is a management instance dealing with handling configuration data, starting/stopping nodes, and etc.

– SQL node: this is a node that accesses the cluster data. It runs MySQL instance and uses the ndbcluster storage engine. This is the one you’ll have to provide for the application (for example typo3) to access the cluster.

– Data node: this is a node that stores cluster data.

Keep an eye on the order of starting nodes in the cluster: the Management node goes first, followed by the Data node, with the SQL node being the last.

In our case, we’re going to combine Management and SQL roles on one pair of servers, and Data role on another pair. Indeed, this is a minimalistic approach with only one replica present.

To summarize server names/roles/IPs:

1. CLU-MNGT-NODE1 : Management+SQL : 10.9.36.11
2. CLU-MNGT-NODE2 : Management+SQL : 10.9.36.12
3. CLU-DATA-NODE1 : Data : 10.9.17.11
4. CLU-DATA-NODE2 : Data : 10.9.17.12

Start with prerequisites on CLU-MNGT-NODE1 first, then clone the server.

  1. % cd /usr/ports/devel/cmake && make install clean
  2. % cd /usr/ports/devel/bison && make install clean
  3.  
  4. % mkdir -p /usr/src/mysql-cluster
  5. % mkdir -p /usr/local/boost
  6. % mkdir -p /var/db/mysql-cluster
  7. % mkdir -p /var/db/CLUSTER/DATA

Add mysql user/group and fix permissions:

  1. mysql:*:88:88:MySQL Daemon:/var/db/CLUSTER/DATA:/usr/sbin/nologin
  2.  
  3. mysql:*:88:
  1. % chown -R mysql:mysql /var/db/CLUSTER

Installing required boost libraries from the FreeBSD ports collection won’t be needed since they are quite new, and MySQL cluster requires exactly boost version 1.59.0.

  1. % cd /usr/local/boost
  2. % fetch https://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

Compile and install MySQL cluster package:

  1. % cd /usr/src/mysql-cluster
  2. % fetch https://dev.mysql.com/get/Downloads/MySQL-Cluster-7.5/mysql-cluster-gpl-7.5.5.tar.gz
  3. % tar zxvf mysql-cluster-gpl-7.5.5.tar.gz
  4. % cd mysql-cluster-gpl-7.5.5
  5.  
  6. % cmake –DBUILD_CONFIG=mysql_release \
  7. DMYSQL_DATADIR=/var/db/CLUSTER/DATA \
  8. DINSTALL_MANDIR=/usr/local/man \
  9. DINSTALL_SBINDIR=/usr/local/libexec \
  10. DINSTALL_MYSQLSHAREDIR=/usr/local/share/mysql \
  11. DINSTALL_SCRIPTDIR=/usr/local/bin \
  12. DINSTALL_LIBDIR=/usr/local/lib/mysql \
  13. DINSTALL_INCLUDEDIR=/usr/local/include/mysql \
  14. DINSTALL_BINDIR=/usr/local/bin \
  15. DDOWNLOAD_BOOST=1 \
  16. DWITH_BOOST=/usr/local/boost \
  17. DWITH_NDB_JAVA=OFF
  18.  
  19. % make install

If your servers are firewalled make sure to allow >= 1024/tcp between nodes (both in and out).

Clone CLU-MNGT-NODE1 to CLU-MNGT-NODE2, CLU-DATA-NODE1, and CLU-DATA-NODE2, and let’s start with the configuration.

On both CLU-MNGT-NODE1 and CLU-MNGT-NODE2:

  1. % cat /etc/my-clu.cnf
  1. [NDB_MGMD DEFAULT]
  2. Datadir=/var/db/mysql-cluster/
  3.  
  4. [NDB_MGMD]
  5. #Management Node CLU-MNGT-NODE1
  6. NodeId=1
  7. Hostname=10.9.36.11
  8.  
  9. [NDB_MGMD]
  10. #Management Node CLU-MNGT-NODE2
  11. NodeId=2
  12. Hostname=10.9.36.12
  13.  
  14. [NDBD DEFAULT]
  15. NoOfReplicas=2
  16. Datadir=/var/db/mysql-cluster
  17. DataMemory=256M
  18. IndexMemory=128M
  19. MemReportFrequency=30
  20. BackupReportFrequency=10
  21. LogLevelStartup=15
  22. LogLevelShutdown=15
  23. LogLevelCheckpoint=8
  24. LogLevelNodeRestart=15
  25. TimeBetweenWatchdogCheckInitial=60000
  26. MaxNoOfOrderedIndexes=27000
  27. MaxNoOfTables=9000
  28. MaxNoOfAttributes=25000
  29.  
  30. [NDBD]
  31. #Data Node CLU-DATA-NODE1
  32. Hostname=10.9.17.11
  33.  
  34. [NDBD]
  35. #Data Node CLU-DATA-NODE2
  36. Hostname=10.9.17.12
  37.  
  38. [MYSQLD]
  39. #MySQL Node CLU-MNGT-NODE1
  40. HostName=10.9.36.11
  41.  
  42. [MYSQLD]
  43. #MySQL Node CLU-MNGT-NODE2
  44. HostName=10.9.36.12

On CLU-MNGT-NODE1:

  1. % cat /etc/my.cnf
  2.  
  3. [MYSQLD]
  4. ndbcluster
  5. ndb-connectstring=127.0.0.1,10.9.36.12
  6. default_storage_engine=ndbcluster
  7. datadir=/var/db/CLUSTER/DATA
  8. user=mysql
  9.  
  10. [MYSQL_CLUSTER]
  11. ndb-connectstring=127.0.0.1,10.9.36.12

On CLU-MNGT-NODE2:

  1. % cat /etc/my.cnf
  2.  
  3. [MYSQLD]
  4. ndbcluster
  5. ndb-connectstring=127.0.0.1,10.9.36.11
  6. default_storage_engine=ndbcluster
  7. datadir=/var/db/CLUSTER/DATA
  8. user=mysql
  9.  
  10. [MYSQL_CLUSTER]
  11. ndb-connectstring=127.0.0.1,10.9.36.11

On CLU-DATA-NODE1:

  1. % cat /etc/my.cnf
  2.  
  3. [MYSQLD]
  4. ndbcluster
  5. ndb-connectstring=10.9.36.11,10.9.36.12
  6.  
  7. [MYSQL_CLUSTER]
  8. ndb-connectstring=10.9.36.11,10.9.36.12

On CLU-DATA-NODE2:

  1. % cat /etc/my.cnf
  2.  
  3. [MYSQLD]
  4. ndbcluster
  5. ndb-connectstring=10.9.36.12,10.9.36.11
  6.  
  7. [MYSQL_CLUSTER]
  8. ndb-connectstring=10.9.36.12,10.9.36.11

Time to fire up our Management nodes.

From both CLU-MNGT-NODE1 and CLU-MNGT-NODE2:

  1. % /usr/local/libexec/ndb_mgmd -f /etc/my-clu.cnf ––initial

Next go our Data nodes.

From both CLU-DATA-NODE1 and CLU-DATA-NODE2:

  1. % /usr/local/libexec/ndbmtd ––defaults-file=/etc/my.cnf ––initial

Check the status from CLU-MNGT-NODE1 or CLU-MNGT-NODE2:

  1. % ndb_mgm -e show
  2. Connected to Management Server at: 127.0.0.1:1186
  3. Cluster Configuration
  4. ———————
  5. [ndbd(NDB)]     2 node(s)
  6. id=3    @10.9.17.11  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0, *)
  7. id=4    @10.9.17.12  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0)
  8.  
  9. [ndb_mgmd(MGM)] 2 node(s)
  10. id=1    @10.9.36.11  (mysql-5.7.17 ndb-7.5.5)
  11. id=2    @10.9.36.12  (mysql-5.7.17 ndb-7.5.5)
  12.  
  13. [mysqld(API)]   2 node(s)
  14. id=5 (not connected, accepting connect from any)
  15. id=6 (not connected, accepting connect from any)

Let’s start our MySQL instances.

From both CLU-MNGT-NODE1 and CLU-MNGT-NODE2:

  1. % /usr/local/libexec/mysqld ––initialize ––user=mysql ––basedir=/var/db/CLUSTER ––datadir=/var/db/CLUSTER/DATA

Write down the temporary MySQL root password which will be stdio-ed after initialization. You’ll have to change it on both MySQL nodes.

From both CLU-MNGT-NODE1 and CLU-MNGT-NODE2:

  1. % /usr/local/bin/mysqld_safe ––defaults-extra-file=/etc/my.cnf ––mysqld=/usr/local/libexec/mysqld ––lc_messages_dir=/usr/local/share/mysql ––lc_messages=en_US ––ledir= &
  1. % mysql -u root -p
  2. Use the temporary password
  3.  
  4. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_strong_password');

Quit, log in with the new password, and finalize the access:

  1. % mysql -u root -p -D mysql
  2.  
  3. GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'new_strong_password';
  4. FLUSH PRIVILEGES;

Verify the status from CLU-MNGT-NODE1 or CLU-MNGT-NODE2:

  1. % ndb_mgm -e show
  2. Connected to Management Server at: 127.0.0.1:1186
  3. Cluster Configuration
  4. ———————
  5. [ndbd(NDB)]     2 node(s)
  6. id=3    @10.9.17.11  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0, *)
  7. id=4    @10.9.17.12  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0)
  8.  
  9. [ndb_mgmd(MGM)] 2 node(s)
  10. id=1    @10.9.36.11  (mysql-5.7.17 ndb-7.5.5)
  11. id=2    @10.9.36.12  (mysql-5.7.17 ndb-7.5.5)
  12.  
  13. [mysqld(API)]   2 node(s)
  14. id=5    @10.9.36.11  (mysql-5.7.17 ndb-7.5.5)
  15. id=6    @10.9.36.12  (mysql-5.7.17 ndb-7.5.5)

Now, at this stage the cluster is up, and any new database that you’re going to create will use ndbcluster as a storage engine hence replicated.

What won’t be replicated is the user privilege table under mysql database, because it must use the MyISAM storage engine. That means that if you add a new MySQL user on CLU-MNGT-NODE1 you’ll have to repeat the same on CLU-MNGT-NODE2.

To enable distributed MySQL privileges for NDB cluster use the following procedure.

Finally, should you require to troubleshoot, here is the location of log files:

Management nodes: /var/db/mysql-cluster > ndb_X_cluster.log
Data nodes: /var/db/mysql-cluster > ndb_X_out.log
SQL nodes: /var/db/CLUSTER/DATA > hostname.err

PS: More to come: start up scripts, rolling restarts, load balancing of SQL nodes, and how to make typo3 and ndbcluster become best friends.

Tags: , , , ,

One Response to “MySQL cluster using ndbcluster engine under FreeBSD 11”

  1. Use this patch for compiling mysql cluster latest version on freebsd 11.

    https://bugs.mysql.com/file.php?id=25058&bug_id=84495

Leave a Reply