MySQL InnoDB Cluster

Purpose

To attempt installing a 3 node cluster.

Process

Started with downloading and installing mysql80-community-release-el7-3.noarch.rpm on each node, and the workstation I used for the admin API.

• Install and configure mysql server on first node. In my case this is Amazon Linux 2

sudo yum install mysql-community-server
sudo systemctl enable --now mysqld.service

• Grab the temp password from /var/log/mysqld.log and run secure setup.
  • Root by default is restricted to localhost. I added an account for the mysqlsh to connect from my workstation. Also added the permissions to configure the clusteradmin

mysql> create user 'root'@'ip address' identified with mysql_native_password BY 'password';
mysql> grant all privileges on *.* to 'root'@'ip address';
mysql> GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'root'@'ip address' WITH GRANT OPTION;
mysql> flush privileges;

Configure Nodes with MySQL Shell

• Installed mysql shell on my Fedora 33 workstation.

sudo dnf install -y mysql-shell

• Set variables and run setup function.

mysqlsh
MySQL  JS > var values={'password':'<root password>', 'clusterAdmin':'<cluster admin name>', 'clusterAdminPassword':'<cluster password>'}
MySQL  JS > dba.configureInstance('root@node1:3306', values )

• At this point I exited mysql shell with ctrl+d and connected to node1 directly using shell (This can be reworked from the beginnging)

mysqlsh --mysql -u root -h nod1
MySQL  node1:3306 ssl  JS > var cluster = dba.createCluster('testcluster')

• I left shell open since it had the cluster functions configure. Using a separate ssh session I connected to the other two nodes, repeating the steps above until I got to the cluster config.
• Now we add the two additional nodes to the cluster from the first shell I already had open. I used the clone option for this.

MySQL  node1:3306 ssl  JS > cluster.addInstance('mysql02.domout.net')

MySQL Router

• Using the same shell as above, create an account for the mysql router.

cluster.setupRouterAccount('routerusername')

• On an app server needing access to mysql, install router and bootstrap. In this case I am running on my Fedora Workstation.
• NOTE : The --user switch is for the local user on the machine that the router will run as. I tried running it as myself but ran into many issues.

sudo dnf install -y mysql-router-community
sudo mysqlrouter --bootstrap dbclusteradmin@node01:3306 --account-host=local.hostname --account-create=if-not-exists --account=router_zabbix --user=mysqlrouter --name=routerusername --conf-bind-address 127.0.0.1

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449

Checking for Compatibility

All tables must have proper keys in place. Zabbix has a couple tables that are missing keys. This will prevent importing the database into the cluster.

• Check for keys

SELECT tables.table_schema , tables.table_name , tables.engine 
FROM information_schema.tables 
LEFT JOIN ( 
   SELECT table_schema , table_name 
   FROM information_schema.statistics 
   GROUP BY table_schema, table_name, index_name HAVING 
     SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks 
 ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name 
 WHERE puks.table_name is null 
   AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";

Example Zabbix

• I needed to add primary keys to zabbix tables that lacked them.
• I also needed to change from multi-primary to single primary. Too many limitations getting in the way

MariaDB [(none)]> SELECT tables.table_schema , tables.table_name , tables.engine 
    -> FROM information_schema.tables 
    -> LEFT JOIN ( 
    ->    SELECT table_schema , table_name 
    ->    FROM information_schema.statistics 
    ->    GROUP BY table_schema, table_name, index_name HAVING 
    ->      SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks 
    ->  ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name 
    ->  WHERE puks.table_name is null 
    ->    AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";
+--------------+--------------+--------+
| table_schema | table_name   | engine |
+--------------+--------------+--------+
| zabbix       | dbversion    | InnoDB |
| zabbix       | history      | InnoDB |
| zabbix       | history_log  | InnoDB |
| zabbix       | history_str  | InnoDB |
| zabbix       | history_text | InnoDB |
| zabbix       | history_uint | InnoDB |
+--------------+--------------+--------+
6 rows in set (0.02 sec)

• I then added the primary keys

alter table dbversion add primary key (mandatory);
alter table history add pkid int primary key auto_increment;
alter table history_log add pkid int primary key auto_increment;
alter table history_str add pkid int primary key auto_increment;
alter table history_text add pkid int primary key auto_increment;
alter table history_uint add pkid int primary key auto_increment;