Docs / Connections: MySQL

Set up MySQL

Verbiqo needs one account that reads performance_schema and sys, and nothing that writes. MySQL 8.0 and later, Percona Server, Amazon RDS and Aurora are supported.

MySQL 5.7 is not supported. Verbiqo still connects, and every answer says first that the version is older than it supports and what it cannot read there: the slowest statements, when settings were changed, settings saved for the next restart, and Group Replication members. The rest of the answer is not a full review. Recommendations use SET PERSIST, which 5.7 lacks: run them as SET GLOBAL and add the setting to my.cnf.

At a glance

1. Create the monitoring account

As an administrator, on the source or primary. Replace 10.% with the addresses Verbiqo connects from, and shop with each application schema.

CREATE USER 'verbiqo_monitor'@'10.%' IDENTIFIED BY 'choose-a-password' WITH MAX_USER_CONNECTIONS 5;

-- Level 2, required: monitoring
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'verbiqo_monitor'@'10.%';
GRANT SELECT ON performance_schema.* TO 'verbiqo_monitor'@'10.%';
GRANT SELECT, EXECUTE, SHOW VIEW ON sys.* TO 'verbiqo_monitor'@'10.%';

-- Level 3, recommended: sizes, keys and indexes (no rows)
GRANT REFERENCES ON shop.* TO 'verbiqo_monitor'@'10.%';

-- Level 4, InnoDB Cluster only
GRANT SELECT ON mysql_innodb_cluster_metadata.* TO 'verbiqo_monitor'@'10.%';

To require TLS for the account, add REQUIRE SSL. For a certificate login, REQUIRE X509.

2. Add the connection

1. Open the form

   Extensions… › MySQL.

2. Fill it in

   Name, host, port (3306), user verbiqo_monitor, password. Schema is optional.

3. Choose TLS

   With a CA-signed certificate, Verify identity and its CA. MySQL’s own certificate is self-signed: Required encrypts without checking it. Keep Preferred for a lab, because it falls back to no encryption when the server offers none.

4. Test, then save

   The test shows the product, version, role (source, replica, group member) and TLS in use.

verbiqo mysql add orders-primary --host db1 --port 3306 --user verbiqo_monitor --password-stdin --ssl-mode REQUIRED
verbiqo mysql test orders-primary

Every verb takes --json. One statement, under the same read-only rules as a question:

verbiqo mysql query orders-primary "SELECT * FROM performance_schema.replication_connection_status"

For a replica set or cluster, save a connection to each server. Verbiqo finds the others from whichever one you attach. Through MySQL Router, each connection may land on a different member: connect to members directly.

TLS and certificates

Verbiqo checks the host name itself in Verify identity, IP addresses included. Details: Encrypted connections.

Permission levels

Checked against MySQL 8.0 and 8.4 with an account holding exactly these grants.

Refused: any privilege that writes or administers (ALL, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, SUPER, RELOAD, FILE, GRANT OPTION, dynamic *_ADMIN privileges), including through a role. The RDS master user is refused. Create a separate account. Verbiqo never reads mysql.user.

Settings that help

Ask what should I enable? to see each item as ready or not.

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
 WHERE NAME IN ('statements_digest', 'events_statements_history');
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 1;
SET PERSIST log_output = 'FILE';
SET PERSIST innodb_print_all_deadlocks = ON;
SET PERSIST binlog_expire_logs_seconds = 604800;   -- a week of binary logs; log_bin itself is set in my.cnf

Query plans, only if your policy allows the account to read the tables (Verbiqo still only plans):

GRANT SELECT ON `your_schema`.* TO 'verbiqo_monitor'@'%';

Put the consumer setting in my.cnf too, so it survives a restart. On RDS and Aurora, set these in the parameter group and turn performance_schema on there (reboot).

Logs in Splunk

• Send both logs, the error log and the slow query log, each with its host. Any sourcetype name works.
• One slow-log entry per Splunk event. Verbiqo names the sourcetype fix when entries are split.
• Messages in English. What it finds: Log findings.

Next: Ask MySQL.

Common errors

The connection test and the answer say what went wrong. Find the message below, then follow the fix.