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.
• NOTE : I wanted to use --account-host=local.hostname, but was unable to connect as the IP address was used and reverse lookup was not working. It would have been ideal to restrict routers to specific hosts.

sudo dnf install -y mysql-router-community
sudo mysqlrouter --bootstrap dbclusteradmin@node01:3306 --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;

Troubleshooting

mysqlsh --mysql -u root -h mysql01.domain.net
cluster=dba.getCluster()
cluster.status()
cluster.rejoinInstance("mysql02.domain.net")
cluster.setPrimaryInstance("mysql01.domain.net")