MySQL Cluster set up for Kea 1.0
  • 21 Sep 2018
  • 25 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

MySQL Cluster set up for Kea 1.0

  • Dark
    Light
  • PDF

Article Summary

Following are step-by-step instructions for setting up a MySQL Cluster for use as a Kea 1.0 backend.  When set up this way, Kea will write leases to the MySQL Cluster before confirming the assignment with the client, so the shared database will always have the current lease status.

Newer Kea versions
The following mechanism has been tested with Kea 1.0.0, which is now obsolete. The article explains how to adapt a similar approach to newer, supported Kea versions. However, it has not been tested with newer versions.

There may be other ways to do this, but this is one way that was tested with Kea 1.0 and it works. This implementation was done for failover testing, to verify that the DHCP service would continue if a Kea server failed, or a cluster component failed. Below is a diagram of the initial configuration of this failover test bed.  The MySQL API Servers function as translators, connecting via MySQL to the Kea servers, and via the NDB API to the database servers. There is also a separate management node, for a total of 3 unique elements in the MySQL Cluster, the MySQL API Server, the MySQLNDB Node, and the MySQL Management Server.

4e4ceee1-188a-4052-afb5-b63d42611dc8.jpg

Official Oracle documentation can be found at this link: https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-installation.html

Set up the MySQL Cluster as follows (64 bits only).  All commands and operations for DB installs are done as root.

1.  Download MySQL Cluster Software

Download the MySQL Community Version Cluster software.  Note that the software versions may be different when you download your software. 

https://www.oracle.com/us/products/mysql/mysqlcommunityserver/overview/index.html

For example: MySQL-Cluster-gpl-7.4.11-1.el7.x86_64.rpm-bundle.tar at https://dev.mysql.com/downloads/cluster/

Alternatively, use wget from the Linux servers and extract; if you're using MySQL community edition you can use this command to download a tarball of the RPM's used for all the nodes:

wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

To unpack the tarball you can use the tar utility with this command:

tar -xvf MySQL-Cluster-gpl-7.4.10-1.el7.x86_64.rpm-bundle.tar

2.  Pre-Requisites prior to MySQL Cluster Software Install

  1. Set up DNS
  • All nodes and the Kea nodes need to be in DNS. If DNS is unavailable the local hosts file (etc/hosts) on every node can be carefully populated with hostname resolution information for the participating nodes. If nodes are multi-homed (more than one network interface) make certain that hostnames resolve to the specific interface addresses you wish to use and that there is no ambiguity (avoid same hostname for multiple interfaces).
  1. Monitor Server Requirements - min requirements are 2 core 2GB DRAM (8 recommended)

  2. Disable Centos7 firewall - (as root)

We do not recommend disabling your firewall in production!
You may however find that you need to disable your firewall in your test environment for testing purposes.

We disabled the firewall for our test bed using the procedure shown below:

systemctl stop firewalld (stops firewall)

systemctl disable firewalld (disables firewall from start on boot)

edit /etc/selinx/config and set to disable and reboot

show selinux status with sestatus
  1. Install yum bind utils
  • You may wish to install the bind utilities (nslookup, dig etc.) for hostname troubleshooting. Use this command:
yum install bind-utils
  1. Edit hosts file
  • Sample:
127.0.0.1   centos7-64-11-dbfailover.isc.org centos7-64-8-dbfailover localhost localhost.localdomain localhost4localhost4.localdomain4
::1         centos7-64-11-dbfailover.isc.org centos7-64-8-dbfailover localhost localhost.localdomain localhost6localhost6.localdomain6
192.168.0.17    centos7-64-8-dbfailover centos7-64-8-dbfailover.isc.org
192.168.0.19    centos7-64-10-dbfailover centos7-64-10-dbfailover.isc.org
192.168.0.20    centos7-64-11-dbfailover centos7-64-6-dbfailover.isc.org
192.168.0.21    centos7-64-12-dbfailover centos7-64-7-dbfailover.isc.org
192.168.0.18    centos7-64-9-dbfailover centos7-64-9-dbfailover.isc.org

The sample /etc/hosts file shows a configuration if DNS is not being used
If DNS is available, then include only the lines for the local host, those being 127.0.0.1, ::1 and the local IP address.
  1. Ensure that time server synchronization is set

  2. /etc/hosts loopback address must have a unique full hostname

  • For example
127.0.0.1 centos7-64-11-dbfailover.isc.org ....

(as shown in the example above)

  1. Confirm that perl data dumper is installing on MySQL database cluster member servers.  Use this command:
yum -y install perl-Data-Dumper
  1. Remove mariadb-libs from the MySQL database cluster members servers.
  • The default Centos7 build includes libraries for a fork of MySQL called Maria-DB that must be removed (we are not using it in this configuration). Use this command to remove:
yum -y remove mariadb-libs

3.  Install MySQL Cluster packages on the Management Server Node:

  1. Configure the Management Node. The CentOS server designated for the Manager node is:

        db1 = 192.168.0.18 (MySQL Management 1)

Install the rpms using these commands:

rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Check for possible errors.

Make sure that you keep a copy of the auto-generated password for the database user 'root'
The output of the 2nd RPM contains a unique auto-generated password for the database user “root” (not to be confused with the operating system user “root”). For every node install you will need to copy this password down for first-time login to MySQL, root password change is immediately required on 1st login. You don't want to lose this password.

In this example you will collect 5 passwords, one for each node: 1 management, 2 SQL and 2 database.
  1. Configure MySQLCluster

Create a new directory for the configuration files.  For example, to create /var/lib/mysql-cluster:

mkdir -p /var/lib/mysql-cluster

Create new configuration file for the cluster managementnamed "config.ini" in the mysql-cluster directory - for example:

cd /var/lib/mysql-cluster
vi config.ini

A sample configuration would be:

[ndb_mgmddefault]
# Directory for MGM node log files
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]
#Management Node db1
HostName=192.168.0.18

[ndbd default]
NoOfReplicas=2      # Number of replicas
DataMemory=256M     # Memory allocate for data storage
IndexMemory=128M    # Memory allocate for index storage
#Directory for Data Node
DataDir=/var/lib/mysql-cluster

[ndbd]
#Data Node db2
HostName=192.168.0.17

[ndbd]
#Data Node db3
HostName=192.168.0.19

[mysqld]
#SQL Node db4
HostName=192.168.0.20

[mysqld]
#SQL Node db5
HostName=192.168.0.21

Save the file and exit.

4. Start the Management Node

Start the management node with this command:

ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini

Check the output, which will look something like the following.

MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
[MgmtSrvr] INFO     -- The default config directory'/usr/mysql-cluster' does not exist. Trying to create it...
[MgmtSrvr] INFO     -- Successfully created configdirectory

Once the Management node is started, launch the node manager with this command:

ndb_mgm

While in the manager your prompt changes to ndb_mgmr>

  • You can display the cluster status with the “show” command.
  • Other commands are listed by entering a question mark.

At this time show should list the manager node [ndbd(MGM)] with the name id=1. The other 4 nodes (2 MySQLD and 2 NDB) will be shown as “not connected, accepting connections from (ip address)”.  As nodes are added they should appear as connected in the show output.

5. Set up the MySQL Cluster Data Nodes

  The 2 CentOS servers designated for our data nodes are:

db2 = 192.168.0.17 (MySQL NDB1)

db3 = 192.168.0.19 (MySQL NDB2)

Install the MySQL Cluster packages for the Data Nodes.

rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Check for errors. 

Make sure that you keep a copy of the auto-generated password for the database user 'root'
Capture the auto-generated root password as done previously in the section Install MySQL Cluster packages on the Management Server Node above.

Configure Data Node.  Create a new configuration file in the /etc directory.

vi /etc/my.cnf

Edit / paste the sample configuration below.

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.18     # IP address of Management Node

[mysql_cluster]
ndb-connectstring=192.168.0.18     # IP address of Management Node

Save the file and exit.

Create a new directory for the database data that previously defined in the management node config file config.ini :

mkdir -p/var/lib/mysql-cluster

Start the data node ndbd:

ndbd

Sample output:

[ndbd] INFO     -- Angel connected to '192.168.0.18:1186'
[ndbd] INFO     -- Angel allocated nodeid: 2

Data Node db2 connected to the management node ip 192.168.0.18.   Repeat steps above for db3 NDB Server (MySQL NDB2) , the second data node.

6.  Set up the MySQL Cluster SQL Nodes

The 2 CentOS servers designated for our SQL nodes are:

db4 = 192.168.0.20 (MySQL API Server 1)

db5 = 192.168.0.21 (MySQL API Server 2)

Install the MySQL Cluster packages for the Data Nodes.

rpm -Uvh MySQL-Cluster-client-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-server-gpl-7.4.10-1.el7.x86_64.rpm
rpm -Uvh MySQL-Cluster-shared-gpl-7.4.10-1.el7.x86_64.rpm

Check RPM output for errors.

Make sure that you keep a copy of the auto-generated password for the database user 'root'
Capture the auto-generated root password as previously done in the section Install MySQL Cluster packages on the Management Server Node above.

Configure the SQL Node. Create a new my.cnf file in the /etc directory:

vi /etc/my.cnf

Edit / paste sample configuration below:

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.18      # IP address for server management node
default_storage_engine=ndbcluster     # Define default Storage Engine used by MySQL

[mysql_cluster]
ndb-connectstring=192.168.0.18     # IP address for server management node

Save the file and exit.

Start the MySQL server SQL Node.

service mysql start

Repeat steps above for db5 SQL Server (MySQL API Server 2) , the second SQL Node.

7.  Check / Monitor the Cluster Status

Log into the Management Node db1 and check the cluster status.

ssh root@192.168.0.18

Use the ndb_mgm commands to check the cluster status:

ndb_mgm
ndb_mgm> show

Other useful Manager commands are:

ndb_mgm> all status
ndb_mgm> all report memory

8.  Test the Cluster

To test the MySQL Cluster login to the SQL Nodes db4 or db5 servers.

Login to the db4 server:

ssh root@192.168.0.20

Change the default MySQL password that is stored in .mysql_secret file in the root directory:

cd ~
cat .mysql_secret

For example:

# The random password set for the root user at Tue Mar 22 19:44:072016 (local time): qna3AwbJMuOnw23T

Change the password:

mysql_secure_installation

Type your old mysql password and then type the new one; press enter to confirm all.

If done correctly, you can login to the MySQL shell with your password:

mysql -u root -p

After you've logged in, create a new user with host "@",to enable Kea to access the MySQL. Something like "Keauser" is a good choice.

CREATE USER 'Keauser'@'%' IDENTIFIED BY 'xxxx789';

Replace xxxx789 with your own secure password.

You can see the new root user with host "@" on the MySQL user list:

select user, host, password from mysql.user;

Grant the new user read and write access from the remote node:

GRANT ALL PRIVILEGES ON *.* TO 'Keauser'@'%' IDENTIFIED BY PASSWORD '*49AB 7ZX280983456E887G13...(Encrypted PASSWORD)' WITH GRANT OPTION;

Next, create a new database called “testdb” from one SQLserver and you will see the database on the other SQL server.

  1. Log into the sql node 1 (not the NDB nodes) with mysql -uroot -p

  2. Create DB with mysql>CREATE DATABASE testdb;

  3. Log into sql node 2 with mysql -u root -p

  4. Show available databases on node 2 with mysql>show databases;

  5. You should see database “testdb” on node 2.

  6. Delete database “testdb” on node 2 with mysql> drop database testdb;

  7. Show databases on sql node 1 and “testdb” should be gone.

9.  Additional Post-Installation Notes & Suggestions

  • DB usernames and db names should not be the same to avoid mistakes in scripts.
  • Don't use punctuation in names because some applications use regular expressions (regex).
  • Run global user permission mod (single point mod).

10.  User Permission Modifications

The Single-Point User Management Script (below) contains a series of SQL scripts and a test that will unify DB user management across the cluster. Without this modification, updating a user's rights on one node will not result in user rights changes on other nodes. Each node will have to be managed individually. With this modification any changes made on a single node member will be propagated across the cluster.

11.  MySQL Cluster Schema

Below you will find the MySQL Cluster Schema Setup Script used to set up the tables for Kea. The provided setup script is specific to a cluster configuration and makes use of the NDBCLUSTER engine rather than the INNODB engine used in non-cluster Kea configurations.

Make sure that you have replaced the *ENGINE=INNODB* statement in the Kea Schema setup script with *ENGINE=NDBCLUSTER*
This is the key difference between the standard setup script and the one required for using a cluster. This change must be made in multiple places in the setup script. See a sample setup script below.

Following the script if you log into both SQL nodes and show tables for the Kea database you should see this output:

mysql>show tables;
+--------------------+
|Tables in Kea       |
+--------------------+
|dhcp4 options       |
|dhcp6 options       |
|host_identifier_type|
|hosts               |
|ipv6_reservations   |
|lease4              |
|lease6              |
|lease6_types        |
|lease_hw_address    |
|lease_state         |
|schema_version      |
+--------------------+
11 rows in set (0.00 sec)

Note the newer Kea versions may have additional tables.

12.  Interconnect Kea servers with MySQL SQL server

Follow the Kea installation instructions to set up communications between the Kea servers and the SQL nodes.  (The MySQL DB Schema step in the Kea instructions have already been performed in the prior steps in this document).

REFERENCES

MySQL Cluster Schema Set Up Script

Make sure that you have replaced the *ENGINE=INNODB* statement in the Kea Schema set up script with *ENGINE=NDBCLUSTER*
This is the key difference between the standard set up script and the one required for using a cluster. This change must be made in multiple places in the set up script. Following is the sample set-up script with the places where you need to make this change highlighted.

The following script is from the Kea 1.0.0 release that was used during testing. The script will likely be different in different releases of Kea, you are likely to need to edit the version of the file that came with your release.

Make sure you use the script from your Kea installation. The script can be found in your Kea sources in src/share/database/scripts/mysql/dhcpdb_create.mysql file. Using the wrong schema script will cause Kea to fail to operate properly.
# Copyright (C) 2012-2016 Internet Systems Consortium.
#
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
# This is the Kea schema specification for MySQL.
#
# The schema is reasonably portable (with the exception of the engine
# specification, which is MySQL-specific).  Minor changes might be needed for
# other databases.
# To create the schema, either type the command:
#
# mysql -u <user> -p <password> <database> < dhcpdb_create.mysql
#
# ... at the command prompt, or log in to the MySQL database and at the "mysql>"
# prompt, issue the command:
#
# source dhcpdb_create.mysql
#
# This script is also called from kea-admin, see kea-admin init mysql
#
# Over time, Kea database schema will evolve. Each version is marked with
# major.minor version. This file is organized sequentially, i.e. database
# is initialized to 1.0, then upgraded to 2.0 etc. This may be somewhat
# sub-optimal, but it ensues consistency with upgrade scripts. (It is much
# easier to maintain init and upgrade scripts if they look the same).
# Since initialization is done only once, its performance is not an issue.

# This line starts database initialization to 1.0.

# Holds the IPv4 leases.
CREATE TABLE lease4 (
     address INT UNSIGNED PRIMARY KEY NOT NULL,  # IPv4 address
     hwaddr VARBINARY(20),                       # Hardware address
     client_id VARBINARY(128),                   # Client ID
     valid_lifetime INT UNSIGNED,                # Length of the lease (seconds)
     expire TIMESTAMP,                           # Expiration time of the lease
     subnet_id INT UNSIGNED,                     # Subnet identification
     fqdn_fwd BOOL,                              # Has forward DNS update been performed by a server
     fqdn_rev BOOL,                              # Has reverse DNS update been performed by a server
     hostname VARCHAR(255)                       # The FQDN of the client
     ) ENGINE = NDBCLUSTER;

# Create search indexes for lease4 table
# index by hwaddr and subnet_id
CREATE INDEX lease4_by_hwaddr_subnet_id ON lease4 (hwaddr, subnet_id);

# index by client_id and subnet_id
CREATE INDEX lease4_by_client_id_subnet_id ON lease4 (client_id, subnet_id);

# Holds the IPv6 leases.
# N.B. The use of a VARCHAR for the address is temporary for development:
# it will eventually be replaced by BINARY(16).
CREATE TABLE lease6 (
     address VARCHAR(39) PRIMARY KEY NOT NULL,   # IPv6 address
     duid VARBINARY(128),                        # DUID
     valid_lifetime INT UNSIGNED,                # Length of the lease (seconds)
     expire TIMESTAMP,                           # Expiration time of the lease
     subnet_id INT UNSIGNED,                     # Subnet identification
     pref_lifetime INT UNSIGNED,                 # Preferred lifetime
     lease_type TINYINT,                         # Lease type (see lease6_types
                                                 #    table for possible values)
     iaid INT UNSIGNED,                          # See Section 10 of RFC 3315
     prefix_len TINYINT UNSIGNED,                # For IA_PD only
     fqdn_fwd BOOL,                              # Has forward DNS update been performed by a server
     fqdn_rev BOOL,                              # Has reverse DNS update been performed by a server
     hostname VARCHAR(255)                       # The FQDN of the client
     
     ) ENGINE = NDBCLUSTER;

# Create search indexes for lease4 table
# index by iaid, subnet_id, and duid
CREATE INDEX lease6_by_iaid_subnet_id_duid ON lease6 (iaid, subnet_id, duid);

# ... and a definition of lease6 types.  This table is a convenience for
# users of the database - if they want to view the lease table and use the
# type names, they can join this table with the lease6 table.
# Make sure those values match Lease6::LeaseType enum (see src/bin/dhcpsrv/
# lease_mgr.h)
CREATE TABLE lease6_types (
     lease_type TINYINT PRIMARY KEY NOT NULL,    # Lease type code.
     name VARCHAR(5)                             # Name of the lease type
     ) ENGINE = NDBCLUSTER;

START TRANSACTION;
INSERT INTO lease6_types VALUES (0, "IA_NA");   # Non-temporary v6 addresses
INSERT INTO lease6_types VALUES (1, "IA_TA");   # Temporary v6 addresses
INSERT INTO lease6_types VALUES (2, "IA_PD");   # Prefix delegations
COMMIT;

# Finally, the version of the schema.  We start at 1.0 during development.
# This table is only modified during schema upgrades.  For historical reasons
# (related to the names of the columns in the BIND 10 DNS database file), the
# first column is called "version" and not "major".
#
# NOTE: this MUST be kept in step with src/lib/dhcpsrv/tests/schema_copy.h,
#       which defines the schema for the unit tests.  If you are updating
#       the version number, the schema has changed: please ensure that
#       schema_copy.h has been updated as well.
CREATE TABLE schema_version (
     version INT PRIMARY KEY NOT NULL,       # Major version number
     minor INT                               # Minor version number
     ) ENGINE = NDBCLUSTER;
START TRANSACTION;
INSERT INTO schema_version VALUES (1, 0);
COMMIT;

# This line concludes database initalization to version 1.0.

# This line starts database upgrade to version 2.0.
ALTER TABLE lease6
     ADD COLUMN hwaddr varbinary(20), # Hardware/MAC address, typically only 6
                    # bytes is used, but some hardware (e.g.                                     
                    # Infiniband) use up to 20.
     ADD COLUMN hwtype smallint unsigned, # hardware type (16 bits)
     ADD COLUMN hwaddr_source int unsigned; # Hardware source. See description
                    # of lease_hwaddr_source below.

# Kea keeps track of the hardware/MAC address source, i.e. how the address
# was obtained. Depending on the technique and your network topology, it may
# be more or less trustworthy. This table is a convenience for
# users of the database - if they want to view the lease table and use the
# type names, they can join this table with the lease6 table. For details,
# see constants defined in src/lib/dhcp/dhcp/pkt.h for detailed explanation.
CREATE TABLE lease_hwaddr_source (
     hwaddr_source INT PRIMARY KEY NOT NULL,
     name VARCHAR(40)
) ENGINE = NDBCLUSTER;

# Hardware address obtained from raw sockets
INSERT INTO lease_hwaddr_source VALUES (1, "HWADDR_SOURCE_RAW");

# Hardware address converted from IPv6 link-local address with EUI-64
INSERT INTO lease_hwaddr_source VALUES (2, "HWADDR_SOURCE_IPV6_LINK_LOCAL");

# Hardware address extracted from client-id (duid)
INSERT INTO lease_hwaddr_source VALUES (4, "HWADDR_SOURCE_DUID");

# Hardware address extracted from client address relay option (RFC6939)
INSERT INTO lease_hwaddr_source VALUES (8, "HWADDR_SOURCE_CLIENT_ADDR_RELAY_OPTION");

# Hardware address extracted from remote-id option (RFC4649)
INSERT INTO lease_hwaddr_source VALUES (16, "HWADDR_SOURCE_REMOTE_ID");

# Hardware address extracted from subscriber-id option (RFC4580)
INSERT INTO lease_hwaddr_source VALUES (32, "HWADDR_SOURCE_SUBSCRIBER_ID");

# Hardware address extracted from docsis options
INSERT INTO lease_hwaddr_source VALUES (64, "HWADDR_SOURCE_DOCSIS");

UPDATE schema_version SET version="2", minor="0";

# This line concludes database upgrade to version 2.0.

# This line starts database upgrade to version 3.0.
# Upgrade extending MySQL schema with the ability to store hosts.

CREATE TABLE IF NOT EXISTS hosts (
     host_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     dhcp_identifier VARBINARY(128) NOT NULL,
     dhcp_identifier_type TINYINT NOT NULL,
     dhcp4_subnet_id INT UNSIGNED NULL,
     dhcp6_subnet_id INT UNSIGNED NULL,
     ipv4_address INT UNSIGNED NULL,
     hostname VARCHAR(255) NULL,
     dhcp4_client_classes VARCHAR(255) NULL,
     dhcp6_client_classes VARCHAR(255) NULL,
     PRIMARY KEY (host_id),
     INDEX key_dhcp4_identifier_subnet_id (dhcp_identifier ASC ,
     dhcp_identifier_type ASC),
     INDEX key_dhcp6_identifier_subnet_id (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC)
)  ENGINE=NDBCLUSTER;
-- -----------------------------------------------------
-- Table `ipv6_reservations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS ipv6_reservations (
     reservation_id INT NOT NULL AUTO_INCREMENT,
     address VARCHAR(39) NOT NULL,
     prefix_len TINYINT(3) UNSIGNED NOT NULL DEFAULT 128,
     type TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
     dhcp6_iaid INT UNSIGNED NULL,
     host_id INT UNSIGNED NOT NULL,
     PRIMARY KEY (reservation_id),
     INDEX fk_ipv6_reservations_host_idx (host_id ASC),
     CONSTRAINT fk_ipv6_reservations_Host FOREIGN KEY (host_id)
          REFERENCES hosts (host_id)
          ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=NDBCLUSTER;
-- -----------------------------------------------------
--Table `dhcp4_options`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS dhcp4_options (
     option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     code TINYINT UNSIGNED NOT NULL,
     value BLOB NULL,
     formatted_value TEXT NULL,
     space VARCHAR(128) NULL,
     persistent TINYINT(1) NOT NULL DEFAULT 0,
     dhcp_client_class VARCHAR(128) NULL,
     dhcp4_subnet_id INT NULL,
     host_id INT UNSIGNED NULL,    PRIMARY KEY (option_id),
     UNIQUE INDEX option_id_UNIQUE (option_id ASC),
     INDEX fk_options_host1_idx (host_id ASC),
     CONSTRAINT fk_options_host1 FOREIGN KEY (host_id)
          REFERENCES hosts (host_id)
          ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=NDBCLUSTER;
-- -----------------------------------------------------
-- Table `dhcp6_options`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS dhcp6_options (
     option_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     code INT UNSIGNED NOT NULL,
     value BLOB NULL,
     formatted_value TEXT NULL,
     space VARCHAR(128) NULL,
     persistent TINYINT(1) NOT NULL DEFAULT 0,
     dhcp_client_class VARCHAR(128) NULL,
     dhcp6_subnet_id INT NULL,
     host_id INT UNSIGNED NULL,
     PRIMARY KEY (option_id),
     UNIQUE INDEX option_id_UNIQUE (option_id ASC),
     INDEX fk_options_host1_idx (host_id ASC),
     CONSTRAINT fk_options_host10 FOREIGN KEY (host_id)
          REFERENCES hosts (host_id)
          ON DELETE NO ACTION ON UPDATE NO ACTION
)  ENGINE=NDBCLUSTER;

DELIMITER $$
CREATE TRIGGER host_BDEL BEFORE DELETE ON hosts FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
DELETE FROM ipv6_reservations WHERE ipv6_reservations.host_id = OLD.host_id;
END
$$
DELIMITER ;

UPDATE schema_version
SET version = '3', minor = '0';
# This line concludes database upgrade to version 3.0.

# This line starts database upgrade to version 4.0.
# Upgrade extending MySQL schema with the state columns for lease tables.

# Add state column to the lease4 table.
ALTER TABLE lease4
     ADD COLUMN state INT UNSIGNED DEFAULT 0;

# Add state column to the lease6 table.
ALTER TABLE lease6
     ADD COLUMN state INT UNSIGNED DEFAULT 0;

# Create indexes for querying leases in a given state and segregated
# by the expiration time. One of the applications is to retrieve all
# expired leases. However, these indexes can be also used to retrieve
# leases in a given state regardless of the expiration time.
CREATE INDEX lease4_by_state_expire ON lease4 (state ASC, expire ASC);
CREATE INDEX lease6_by_state_expire ON lease6 (state ASC, expire ASC);

# Create table holding mapping of the lease states to their names.
# This is not used in queries from the DHCP server but rather in
# direct queries from the lease database management tools.
CREATE TABLE IF NOT EXISTS lease_state (
  state INT UNSIGNED PRIMARY KEY NOT NULL,
  name VARCHAR(64) NOT NULL
) ENGINE=NDBCLUSTER;

# Insert currently defined state names.
INSERT INTO lease_state VALUES (0, "default");
INSERT INTO lease_state VALUES (1, "declined");
INSERT INTO lease_state VALUES (2, "expired-reclaimed");

# Add a constraint that any state value added to the lease4 must
# map to a value in the lease_state table.
ALTER TABLE lease4
     ADD CONSTRAINT fk_lease4_state FOREIGN KEY (state)
     REFERENCES lease_state (state);

# Add a constraint that any state value added to the lease6 must
# map to a value in the lease_state table.
ALTER TABLE lease6
     ADD CONSTRAINT fk_lease6_state FOREIGN KEY (state)
     REFERENCES lease_state (state);

# Add a constraint that lease type in the lease6 table must map
# to a lease type defined in the lease6_types table.
ALTER TABLE lease6
     ADD CONSTRAINT fk_lease6_type FOREIGN KEY (lease_type)
     REFERENCES lease6_types (lease_type);

# Modify the name of one of the HW address sources, and add a new one.
UPDATE lease_hwaddr_source
     SET name = 'HWADDR_SOURCE_DOCSIS_CMTS'
     WHERE hwaddr_source = 64;

INSERT INTO lease_hwaddr_source VALUES (128, 'HWADDR_SOURCE_DOCSIS_MODEM');

# Add UNSIGNED to match with the lease6.
ALTER TABLE lease_hwaddr_source
     MODIFY COLUMN hwaddr_source INT UNSIGNED NOT NULL;

# Add a constraint that non-null hwaddr_source in the lease6 table
# must map to an entry in the lease_hwaddr_source.
ALTER TABLE lease6
     ADD CONSTRAINT fk_lease6_hwaddr_source FOREIGN KEY (hwaddr_source)
     REFERENCES lease_hwaddr_source (hwaddr_source);

# FUNCTION that returns a result set containing the column names for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease4DumpHeader()
BEGIN
SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state';
END  $$
DELIMITER ;

# FUNCTION that returns a result set containing the data for lease4 dumps
DROP PROCEDURE IF EXISTS lease4DumpData;
DELIMITER $$
CREATE PROCEDURE lease4DumpData()
BEGIN
SELECT
     INET_NTOA(l.address),
     IFNULL(HEX(l.hwaddr), ''),
     IFNULL(HEX(l.client_id), ''),
     l.valid_lifetime,
     l.expire,
     l.subnet_id,
     l.fqdn_fwd,
     l.fqdn_rev,
     l.hostname,
     s.name
FROM
     lease4 l
     LEFT OUTER JOIN lease_state s on (l.state = s.state)
ORDER BY l.address;
END $$
DELIMITER ;

# FUNCTION that returns a result set containing the column names for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpHeader;
DELIMITER $$
CREATE PROCEDURE lease6DumpHeader()
BEGIN
SELECT 'address,duid,valid_lifetime,expire,subnet_id,pref_lifetime,lease_type,iaid,prefix_len,fqdn_fwd,fqdn_rev,hostname,hwaddr,hwtype,hwaddr_source,state';
END  $$
DELIMITER ;

# FUNCTION that returns a result set containing the data for lease6 dumps
DROP PROCEDURE IF EXISTS lease6DumpData;
DELIMITER $$
CREATE PROCEDURE lease6DumpData()
BEGIN
SELECT
     l.address,
     IFNULL(HEX(l.duid), ''),
     l.valid_lifetime,
     l.expire,
     l.subnet_id,
     l.pref_lifetime,
     IFNULL(t.name, ''),
     l.iaid,
     l.prefix_len,
     l.fqdn_fwd,
     l.fqdn_rev,
     l.hostname,
     IFNULL(HEX(l.hwaddr), ''),
     IFNULL(l.hwtype, ''),
     IFNULL(h.name, ''),
     IFNULL(s.name, '')
FROM lease6 l
     left outer join lease6_types t on (l.lease_type = t.lease_type)
     left outer join lease_state s on (l.state = s.state)
     left outer join lease_hwaddr_source h on (l.hwaddr_source = h.hwaddr_source)
ORDER BY l.address;
END $$
DELIMITER ;

# Update the schema version number
UPDATE schema_version
SET version = '4', minor = '0';

# This line concludes database upgrade to version 4.0.

# In the event hardware address cannot be determined, we need to satisfy
# foreign key constraint between lease6 and lease_hardware_source
INSERT INTO lease_hwaddr_source VALUES (0, "HWADDR_SOURCE_UNKNOWN");

# Update the schema version number
UPDATE schema_version
SET version = '4', minor = '1';

# This line concludes database upgrade to version 4.1.

# Update index used for searching DHCPv4 reservations by identifier and subnet id.
# This index is now unique (to prevent duplicates) and includes DHCPv4 subnet
# identifier.
DROP INDEX key_dhcp4_identifier_subnet_id ON hosts;
CREATE UNIQUE INDEX key_dhcp4_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp4_subnet_id ASC);

# Update index used for searching DHCPv6 reservations by identifier and subnet id.
# This index is now unique to prevent duplicates.
DROP INDEX key_dhcp6_identifier_subnet_id ON hosts;
CREATE UNIQUE INDEX key_dhcp6_identifier_subnet_id ON hosts (dhcp_identifier ASC , dhcp_identifier_type ASC , dhcp6_subnet_id ASC);

# Create index to search for reservations using IP address and subnet id.
# This unique index guarantees that there is only one occurence of the
# particular IPv4 address for a given subnet.
CREATE UNIQUE INDEX key_dhcp4_ipv4_address_subnet_id ON hosts (ipv4_address ASC , dhcp4_subnet_id ASC);

# Create index to search for reservations using address/prefix and prefix
# length.
CREATE UNIQUE INDEX key_dhcp6_address_prefix_len ON ipv6_reservations (address ASC , prefix_len ASC);

# Create a table mapping host identifiers to their names. Values in this
# table are used as a foreign key in hosts table to guarantee that only
# identifiers present in host_identifier_type table are used in hosts
# table.
CREATE TABLE IF NOT EXISTS host_identifier_type (
     type TINYINT PRIMARY KEY NOT NULL,   # Lease type code.
     name VARCHAR(32)                     # Name of the lease type
) ENGINE = NDBCLUSTER;

START TRANSACTION;
INSERT INTO host_identifier_type VALUES (0, "hw-address"); # Non-temporary v6 addresses
INSERT INTO host_identifier_type VALUES (1, "duid");       # Temporary v6 addresses
INSERT INTO host_identifier_type VALUES (2, "circuit-id"); # Prefix delegations
COMMIT;

# Add a constraint that any identifier type value added to the hosts
# must map to a value in the host_identifier_type table.
ALTER TABLE hosts
     ADD CONSTRAINT fk_host_identifier_type FOREIGN KEY (dhcp_identifier_type)
     REFERENCES host_identifier_type (type);

# Update the schema version number
UPDATE schema_version
SET version = '4', minor = '2';
# This line concludes database upgrade to version 4.2.

# Notes:
#
# Indexes
# =======
# It is likely that additional indexes will be needed.  However, the
# increase in lookup performance from these will come at the expense
# of a decrease in performance during insert operations due to the need
# to update the indexes.  For this reason, the need for additional indexes
# will be determined by experiment during performance tests.
#
# The most likely additional indexes will cover the following columns:
#
# hwaddr and client_id
# For lease stability: if a client requests a new lease, try to find an
# existing or recently expired lease for it so that it can keep using the
# same IP address.
#
# Field Sizes
# ===========
# If any of the VARxxx field sizes are altered, the lengths in the MySQL
# backend source file (mysql_lease_mgr.cc) must be correspondingly changed.
#
# Portability
# ===========
# The "ENGINE = NDBCLUSTER" on some tables is not portable to another database
# and will need to be removed.
#
# Some columns contain binary data so are stored as VARBINARY instead of
# VARCHAR.  This may be non-portable between databases: in this case, the
# definition should be changed to VARCHAR.
Single-Point User Management Script

Below are the required steps to modify the database for single-point user management. The information shows several database inputs and expected results in several steps. At the end is a test of the modification.

mysql>SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'mysql_cluster%' ORDER BYROUTINE_TYPE;

Emptyset (0.00 sec)

mysql>SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='mysql' AND TABLE_NAME LIKE '%backup'ORDER BY ENGINE;

Emptyset (0.00 sec)

mysql>source ndb_dist_priv.sql

QueryOK, 0 rows affected, 1 warning (0.00 sec)

....

mysql>SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE FROMINFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'mysql_cluster%' ORDER BYROUTINE_TYPE;
+---------------------------------------------+----------------+--------------+
|ROUTINE_NAME                                 | ROUTINE_SCHEMA | ROUTINE_TYPE |
+---------------------------------------------+----------------+--------------+
|mysql_cluster_privileges_are_distributed     | mysql          | FUNCTION     |
|mysql_cluster_backup_privileges              | mysql          | PROCEDURE    |
|mysql_cluster_move_grant_tables              | mysql          | PROCEDURE    |
|mysql_cluster_move_privileges                | mysql          | PROCEDURE    |
|mysql_cluster_restore_local_privileges       | mysql          | PROCEDURE    |
|mysql_cluster_restore_privileges             | mysql          | PROCEDURE    |
|mysql_cluster_restore_privileges_from_local  | mysql          | PROCEDURE    |
+---------------------------------------------+----------------+--------------+
7 rows in set (0.00 sec)
mysql>CALL mysql.mysql_cluster_move_privileges();

Query OK, 2 rows affected (3.17 sec)

mysql>CALL mysql.mysql_cluster_move_privileges();

Query OK, 2 rows affected (3.17 sec)

mysql>SELECT CONCAT('Conversion ',IF(mysql.mysql_cluster_privileges_are_distributed(), 'succeeded', 'failed'),'.') AS Result;
+-----------------------+
|Result                |
+-----------------------+
|Conversion succeeded. |
+-----------------------+
1 row inset (0.00 sec)

  Query OK, 2 rows affected (3.17 sec)
mysql>SELECT TABLE_NAME, ENGINE 
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='mysql' AND 
TABLE_NAME LIKE '%backup' ORDER BY ENGINE;
+-------------------------+------------+
|TABLE_NAME               | ENGINE     |
+-------------------------+------------+
|db_backup                | MyISAM     |
|user_backup              | MyISAM     |
|columns_priv_backup      | MyISAM     |
|tables_priv_backup       | MyISAM     |
|proxies_priv_backup      | MyISAM     |
|procs_priv_backup        | MyISAM     |
|ndb_proxies_priv_backup  | ndbcluster |
|ndb_procs_priv_backup    | ndbcluster |
|ndb_db_backup            | ndbcluster |
|ndb_columns_priv_backup  | ndbcluster |
|ndb_user_backup          | ndbcluster |
|ndb_tables_priv_backup   | ndbcluster |
+-------------------------+------------+
12 rows in set (0.01 sec)

>>>>>log in to server 2 as db-root<<<<<<<<<<<<<

mysql>select table_name,engine from information_schema.tables wheretable_schema='mysql' and engine='ndbcluster';
+-------------------------+------------+
|table_name               | engine     |
+-------------------------+------------+
|columns_priv             | ndbcluster |
|db                       | ndbcluster |
|ndb_apply_status         | ndbcluster |
|ndb_columns_priv_backup  | ndbcluster |
| ndb_db_backup           | ndbcluster |
|ndb_index_stat_head      | ndbcluster |
|ndb_index_stat_sample    | ndbcluster |
|ndb_procs_priv_backup    | ndbcluster |
|ndb_proxies_priv_backup  | ndbcluster |
|ndb_tables_priv_backup   | ndbcluster |
|ndb_user_backup          | ndbcluster |
|procs_priv               | ndbcluster |
|proxies_priv             | ndbcluster |
|tables_priv              | ndbcluster |
|user                     | ndbcluster |
+-------------------------+------------+
15 rows in set (0.01 sec)sql' and engine='ndbcluster';