Administration

User Management

Create users with CREATE USER and authenticate via mysql_native_password or caching_sha2_password (default in MySQL 8). Assign minimum privileges with GRANT on specific databases and tables.

Drop unused accounts and rotate passwords on schedule. Use separate users for application, migration, backup, and monitoring tools.

CREATE USER 'app'@'%' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app'@'%';
FLUSH PRIVILEGES;

Privileges

Global privileges (SUPER, FILE) are dangerous—avoid granting to applications. Use SHOW GRANTS to audit permissions. Roles (MySQL 8) bundle privileges for easier assignment.

Revoke with REVOKE and re-flush privileges. Least privilege limits blast radius of compromised credentials.

• Restrict app users from DDL unless migrations use dedicated credentials
• PROCESS privilege allows seeing all queries—limit to DBAs
• Use DEFINER carefully on views and procedures to avoid privilege escalation

Monitoring

Monitor connections, threads_running, slow queries, replication lag, and InnoDB metrics. Performance Schema and sys schema provide ready-made diagnostic views.

Alert on disk usage, connection exhaustion, and replication stopped states. Integrate with Prometheus mysqld_exporter or cloud provider monitoring.

SELECT * FROM sys.schema_tables_with_full_table_scans LIMIT 10;
SHOW GLOBAL STATUS LIKE 'Threads_connected';

Maintenance Tasks

Apply minor version upgrades during maintenance windows with backup first. Major upgrades may require mysql_upgrade or logical dump/restore for compatibility.

Purge old binary logs per retention policy. Archive historical data to cold storage when tables exceed operational size thresholds.

• Schedule pt-online-schema-change for large table ALTERs with minimal locking
• Review max_allowed_packet for bulk imports
• Validate time synchronization (NTP) across replication topology

Configuration Management

Store my.cnf settings in infrastructure as code. Key settings include innodb_buffer_pool_size, max_connections, log_bin, and character set defaults.

Change one variable at a time in production and measure impact. Some settings require restart; others are dynamic with SET GLOBAL.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;