Install MySQL Cluster Manager "MCM" using Systemd

For more information on how to create a service look at man systemd.service

Install

Directory for MCM installation is /opt Download latest tar package of MCM from edelivery.oracle.com or support.oracle.com (today this is package: MySQL Cluster Manager 1.4.8 TAR for Generic Linux x86 (64bit))

Run commands below once you have downloaded the MCM package:

sudo su -
cd /opt
tar xzf /tmp/mcm-1.4.8-linux-glibc2.12-x86-64bit.tar.gz
mv mcm-1.4.8-linux-glibc2.12-x86-64bit mcm
cp /opt/mcm/mcm1.4.8/etc/mcmd.ini /opt/mcm/
mkdir /opt/mcm/mcm_data

Set manager-directory to folder /opt/mcm/mcm_data in configuration file:

cat /opt/mcm/mcmd.ini | sed 's/^##manager-directory.*/manager-directory = \/opt\/mcm\/mcm_data/' >> /opt/mcm/mcmd.ini

Structure should be:

 /opt/mcm
         mcmd.ini
         mcm1.4.8
         mcm_data

 Folder mcm_data is where all data on disk will be stored by default.
 Folder mcmN.N.N contains installed version of MCM.

Systemd configuration

Create user to run the MCM service:

sudo useradd --no-create-home -s /bin/false mcm
sudo chown -R mcm:mcm /opt/mcm

Create systemd file:

Make sure to update path to correct version of mcm binaries in mcm service file below.

Create file /etc/systemd/system/mcm.service like below.

[Unit]
Description=MySQL Cluster Manager
Documentation=https://dev.mysql.com/doc/mysql-cluster-manager/en/
After=network-online.target

[Service]
User=mcm
Group=mcm
Restart=always
Type=simple

ExecStart=/opt/mcm/mcm1.4.8/bin/mcmd --defaults-file=/opt/mcm/mcmd.ini

[Install]
WantedBy=multi-user.target

Start and enable service:

sudo systemctl daemon-reload
sudo systemctl start mcm
sudo systemctl enable mcm
sudo systemctl status mcm

If the service is not started correctly, look in messages file: sudo tail -150f /var/log/messages  

Remember to set option StopOnError to 0 when creating your cluster so mcmd can restart failed data nodes (set StopOnError:ndbmtd=0 mycluster;).
You can find this and many other MySQL guides here: https://github.com/wwwted

Happy Clustering!

Database change tracking using MySQL EE Audit

Use MySQL Enterprise Audut plugin to track data changes on specific tables.

Scripts and documentation on github: https://github.com/wwwted/Database-change-tracking

Tracked data (audit logs) can then be inserted into reporting db using parser scripts.

Limitations:

• Changing tracking rules stopps all tracking for existing connections until the reconnect. This is a limitation in MySQL EE Audit filter handling.
• No filtering for different users, easy to implement if needed in change_tracking.sql SP (START/STOP).
• Only tracking INSERT/UPDATE/DELETE statements. Easy to modify if needed.

1 Enable tracking on target server

1.1 Enable audit plugin

Add below to my.cnf for JSON format: loose_audit_log_format = JSON

Load audit plugin:

mysql -uroot -proot < ./mysqlsrc/share/audit_log_filter_linux_install.sql
mysql -uroot -proot -se"show plugins" | grep audit

Set audit log to rotate on size (for test set it low so you get some files to read):

mysql> set persist audit_log_rotate_on_size=4096;

1.2 Install tracking script

mysql -uroot -proot < change_tracking.sql

2 Setup reporting server

Reporting server will host all audit logs from all sources and be the "reporting" database.

2.1 Add reporting schema and tables

mysql -umsandbox -pmsandbox -h127.0.0.1 -P8017 < report_schemas.sql

2.2 Import data into reporting database

Update script with database connection variables and path to audit files Run import: ./audit-parser-json.pl

Imported files are recorded in table audit_information.audit_jobs

Sample output:

mysql> select * from audit_information.audit_jobs;
+----+---------------------------+---------------------+-------------+
| ID | AUDIT_LOG_NAME            | PARSED_DATE_TIME    | LOG_ENTRIES |
+----+---------------------------+---------------------+-------------+
|  1 | audit.20200429T095031.log | 2020-04-29 11:51:21 |          27 |
|  2 | audit.20200429T102434.log | 2020-04-29 12:24:40 |          12 |
+----+---------------------------+---------------------+-------------+

Data is stored in table audit_information.audit_data. Scripts are not inserting all data from audit logs, just some sample columns.

3 USAGE

• Start tracking:CALL tracking.START_TRACKING("db","table");
• List tracked tables:CALL tracking.LIST_TRACKING();
• Stop tracking: CALL tracking.STOP_TRACKING("db","table");

Tracking uses database tracking and one interal tables CHANGE_TRACKING.

4 Test

CREATE DATABASE test;
use test;
CREATE TABLE slafs (i INT);
CALL tracking.START_TRACKING("test","slafs");
--  (you need to log out and in again to have filter activated)
--  (filters are stored in select * from mysql.audit_log_filter;)
CALL tracking.LIST_TRACKING();
INSERT INTO test.slafs VALUES (6);
--  (run tail -f on audit-log)
CALL tracking.STOP_TRACKING("test","slafs");

Misc

Audit filters:

• SELECT * FROM mysql.audit_log_filter;
• SELECT * FROM mysql.audit_log_user;