MySQL

Step 1: Edit my.cnf or my.ini

[mysqld]
# Enable Performance Schema
performance_schema = ON

# Enable specific instruments
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-statements-digest=ON

Step 2: Restart MySQL

sudo systemctl restart mysql

Step 3: Verify Performance Schema is enabled

SHOW VARIABLES LIKE 'performance_schema';

For MySQL 8.0+

-- Create monitoring user
CREATE USER 'db24x7_monitor'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant required privileges
GRANT SELECT ON performance_schema.* TO 'db24x7_monitor'@'%';
GRANT SELECT ON mysql.* TO 'db24x7_monitor'@'%';
GRANT PROCESS ON *.* TO 'db24x7_monitor'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'db24x7_monitor'@'%';

-- For query analysis
GRANT SELECT ON information_schema.* TO 'db24x7_monitor'@'%';

-- Apply changes
FLUSH PRIVILEGES;

For MySQL 5.7

-- Create monitoring user
CREATE USER 'db24x7_monitor'@'%' IDENTIFIED BY 'your_secure_password';

-- Grant required privileges
GRANT SELECT ON performance_schema.* TO 'db24x7_monitor'@'%';
GRANT SELECT ON mysql.* TO 'db24x7_monitor'@'%';
GRANT PROCESS ON *.* TO 'db24x7_monitor'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'db24x7_monitor'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Standard Connection String

mysql://db24x7_monitor:your_secure_password@hostname:3306/database_name

With SSL/TLS Encryption

mysql://db24x7_monitor:password@hostname:3306/dbname?ssl-mode=REQUIRED

AWS RDS MySQL Connection

mysql://db24x7_monitor:password@mydb.abc123.us-east-1.rds.amazonaws.com:3306/mysql?ssl-mode=REQUIRED

Connection Parameters

Connection Refused

If you cannot connect to MySQL, verify:

• •MySQL is listening on the correct port (check bind-address in my.cnf)
• •Firewall allows port 3306
• •User has remote access permissions
• •Password authentication is correct

Performance Schema Disabled

Verify Performance Schema is enabled:

-- Check if Performance Schema is enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Check enabled consumers
SELECT * FROM performance_schema.setup_consumers
WHERE NAME LIKE '%statements%';

-- Enable statement consumers if needed
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

Access Denied Errors

Verify user privileges:

-- Check user privileges
SHOW GRANTS FOR 'db24x7_monitor'@'%';

-- Verify user can connect
SELECT User, Host FROM mysql.user WHERE User = 'db24x7_monitor';

SSL/TLS Connection Issues

Check SSL configuration:

-- Check if SSL is enabled
SHOW VARIABLES LIKE '%ssl%';

-- Verify SSL connections
SHOW STATUS LIKE 'Ssl_cipher';