Cheat SheetsMySQLAdministration

Administration — Cheat Sheet

MySQL · 5 topics. Download the PDF or the Instagram carousel and share it.

Cheat Sheet · AiCanCode.org

Administration

MySQL5 topicsQuick revision reference

Backup & Recovery

mysqldump for logical backups; Percona XtraBackup for hot physical backups; binary log point-in-time recovery complements full backups to minimise data loss on failure.

✓mysqldump --single-transaction provides InnoDB consistent logical backup without locking; --master-data=2 records binlog position
✓Percona XtraBackup creates hot physical backups without locking — the standard for large production InnoDB databases
✓PITR requires binlog_format=ROW, log_bin enabled, and binlogs retained for the full recovery window
✓Full backup alone gives you an RPO of "since last backup"; adding binlog replay improves RPO to near zero
✓Always test restores regularly — a backup that has never been restored is an untested backup
✓sync_binlog=1 + innodb_flush_log_at_trx_commit=1 is the safest durability setting but has a write throughput cost

Shell — mysqldump backup and restore with binlog position recording

# Full logical backup — InnoDB consistent snapshot
mysqldump   --single-transaction   --master-data=2   --routines --triggers --events   --all-databases   --user=backup_user --password   | gzip > backup_$(date +%Y%m%d).sql.gz

# Single database backup
mysqldump --single-transaction --master-data=2 \
  --databases shop \
  -u backup_user -p > shop_$(date +%Y%m%d).sql

# Restore
gunzip < backup_20240101.sql.gz | mysql -u root -p

# Check binlog position recorded in backup
head -50 backup_20240101.sql | grep CHANGE_MASTER
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=4567;
# Use this position for PITR replay starting point

Performance Schema

Performance Schema instruments the server to collect metrics on query execution, lock waits, I/O, and memory allocation — low overhead, high-detail insight without external tooling.

✓Performance Schema collects real-time metrics with < 5% CPU overhead — enabled by default since MySQL 5.7
✓sys.statement_analysis aggregates queries by digest — showing avg/max latency, full_scan, tmp_disk_tables per query pattern
✓rows_examined / rows_sent ratio > 100 indicates a query scanning far more rows than it returns — missing index candidate
✓sys.innodb_lock_waits shows blocking and waiting transactions with the blocking query — use KILL to unblock
✓table_io_waits_summary_by_table identifies tables with the most I/O, guiding index additions and denormalisation decisions
✓Always reset statistics before profiling: CALL sys.ps_truncate_all_tables(FALSE) or TRUNCATE specific summary tables

SQL — top slow queries via sys.statement_analysis

-- Top 10 slowest queries by average latency (sys schema)
SELECT
    query,
    exec_count,
    avg_latency,
    max_latency,
    rows_examined_avg / NULLIF(rows_sent_avg, 0) AS rows_per_sent,
    full_scan,
    tmp_tables,
    tmp_disk_tables
FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;

-- Raw Performance Schema query (more detail)
SELECT
    DIGEST_TEXT,
    COUNT_STAR                            AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1e9, 2)       AS avg_ms,
    ROUND(MAX_TIMER_WAIT / 1e9, 2)       AS max_ms,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT,
    SUM_NO_INDEX_USED                     AS full_scan_count
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

MySQL Security Best Practices

Grant least-privilege, use SSL/TLS, hash passwords with caching_sha2_password, rename the root account, disable remote root login, and audit access with the audit log plugin.

✓Use caching_sha2_password (MySQL 8.0 default) — never use mysql_native_password for new accounts.
✓Never use root for application connections; create per-service accounts with minimal privileges.
✓REQUIRE SSL on user accounts enforces encrypted connections.
✓Set require_secure_transport=ON in my.cnf to reject all non-TLS connections.
✓Use roles (MySQL 8.0+) to simplify privilege management and auditing.
✓Enable the Audit Log plugin for compliance; never leave general_log ON in production.

SQL — user creation, roles, least privilege

-- Create application user restricted to localhost or app subnet
CREATE USER 'order_app'@'10.0.1.%'
    IDENTIFIED WITH caching_sha2_password BY 'StrongPass!123'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

-- Grant only required privileges (no DROP, no CREATE)
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO 'order_app'@'10.0.1.%';
GRANT SELECT ON shop.products TO 'order_app'@'10.0.1.%';

-- Role-based access control (MySQL 8.0+)
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON shop.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON shop.* TO 'app_write';

GRANT 'app_read', 'app_write' TO 'order_app'@'10.0.1.%';
SET DEFAULT ROLE ALL TO 'order_app'@'10.0.1.%';

-- Disable remote root login
DELETE FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
FLUSH PRIVILEGES;

-- Check open accounts (blank password is a security risk)
SELECT User, Host, plugin, password_expired
FROM mysql.user
WHERE authentication_string = '' OR plugin = 'mysql_native_password';

MySQL Configuration Tuning

Key InnoDB variables: innodb_buffer_pool_size (60–80% of RAM), innodb_log_file_size, max_connections, thread_cache_size — benchmark with sysbench before and after tuning.

✓innodb_buffer_pool_size is the single most impactful setting — set to 60–80% of RAM; buffer pool hit ratio should be > 99%
✓innodb_redo_log_capacity controls write throughput — larger = faster writes, but longer crash recovery on restart
✓innodb_flush_log_at_trx_commit=1 + sync_binlog=1 is the safest durability setting, at a throughput cost
✓max_connections × ~1 MB RAM = total memory for connections — do not set to 10,000 on a 16 GB server
✓tmp_table_size controls when temp tables spill to disk — monitor Created_tmp_disk_tables / Created_tmp_tables ratio
✓Always benchmark with sysbench or application workload replay before and after tuning to verify improvement

Config + SQL — buffer pool sizing and hit ratio monitoring

# my.cnf — InnoDB buffer pool configuration
[mysqld]
# Set to 60-80% of available RAM
# Example: 16 GB server → 10-12 GB buffer pool
innodb_buffer_pool_size = 10G

# Multiple instances reduce mutex contention (1 per 1 GB)
innodb_buffer_pool_instances = 10

# Warm buffer pool on restart (avoids cold-start I/O storm)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# Monitor buffer pool hit ratio
SELECT
    (1 - (SELECT variable_value FROM performance_schema.global_status
          WHERE variable_name = 'Innodb_buffer_pool_reads') /
         (SELECT variable_value FROM performance_schema.global_status
          WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    ) * 100 AS buffer_pool_hit_ratio_pct;
-- Target: > 99%

# Shortcut via sys schema
SELECT * FROM sys.innodb_buffer_stats_by_schema
ORDER BY pages_allocated DESC LIMIT 10;

MySQL with Spring Boot / JDBC

Configure spring.datasource.url with the JDBC URL, driver-class-name=com.mysql.cj.jdbc.Driver, and HikariCP pool settings; use Flyway or Liquibase for schema migrations.

✓mysql-connector-j is the modern driver; use jdbc:mysql://host/db?serverTimezone=UTC.
✓HikariCP is Spring Boot's default pool — tune maximum-pool-size based on DB max_connections.
✓Pool size formula: (2 × CPU cores) + effective_spindle_count for I/O-bound workloads.
✓Use Flyway or Liquibase for schema migrations — never rely on ddl-auto=update in production.
✓spring.jpa.open-in-view=false prevents the OSIV anti-pattern and lazy loading in web layer.
✓rewriteBatchedStatements=true in the JDBC URL enables true MySQL multi-row INSERT batching.

XML + Properties — MySQL datasource and HikariCP

<!-- pom.xml -->
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <!-- version managed by Spring Boot BOM -->
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

# application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/shopdb\
  ?serverTimezone=UTC\
  &characterEncoding=utf8mb4\
  &useSSL=false\
  &allowPublicKeyRetrieval=true\
  &rewriteBatchedStatements=true

spring.datasource.username=shop_app
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# HikariCP pool tuning
spring.datasource.hikari.pool-name=ShopHikariPool
spring.datasource.hikari.maximum-pool-size=10     # max DB connections
spring.datasource.hikari.minimum-idle=5           # min idle connections
spring.datasource.hikari.connection-timeout=30000 # ms before throwing if no conn
spring.datasource.hikari.idle-timeout=600000      # ms before idle conn released
spring.datasource.hikari.max-lifetime=1800000     # ms max conn lifetime

Learn this free with Aria, your AI tutor → AiCanCode.org/learn/mysql

Cheat Sheet

Administration

MySQL

5 topics · swipe →

AiCanCode.org

Administration

Backup & Recovery

✓mysqldump --single-transaction provides InnoDB consistent logical backup without locking; --master-data=2 records binlog position
✓Percona XtraBackup creates hot physical backups without locking — the standard for large production InnoDB databases
✓PITR requires binlog_format=ROW, log_bin enabled, and binlogs retained for the full recovery window
✓Full backup alone gives you an RPO of "since last backup"; adding binlog replay improves RPO to near zero
✓Always test restores regularly — a backup that has never been restored is an untested backup

# Full logical backup — InnoDB consistent snapshot
mysqldump   --single-transaction   --master-data=2   --routines --triggers --events   --all-databases   --user=backup_user --password   | gzip > backup_$(date +%Y%m%d).sql.gz

# Single database backup
mysqldump --single-transaction --master-data=2 \
  --databases shop \
  -u backup_user -p > shop_$(date +%Y%m%d).sql

# Restore
…

AiCanCode.org

Administration

Performance Schema

✓Performance Schema collects real-time metrics with < 5% CPU overhead — enabled by default since MySQL 5.7
✓sys.statement_analysis aggregates queries by digest — showing avg/max latency, full_scan, tmp_disk_tables per query pattern
✓rows_examined / rows_sent ratio > 100 indicates a query scanning far more rows than it returns — missing index candidate
✓sys.innodb_lock_waits shows blocking and waiting transactions with the blocking query — use KILL to unblock
✓table_io_waits_summary_by_table identifies tables with the most I/O, guiding index additions and denormalisation decisions

-- Top 10 slowest queries by average latency (sys schema)
SELECT
    query,
    exec_count,
    avg_latency,
    max_latency,
    rows_examined_avg / NULLIF(rows_sent_avg, 0) AS rows_per_sent,
    full_scan,
    tmp_tables,
…

AiCanCode.org

Administration

MySQL Security Best Practices

✓Use caching_sha2_password (MySQL 8.0 default) — never use mysql_native_password for new accounts.
✓Never use root for application connections; create per-service accounts with minimal privileges.
✓REQUIRE SSL on user accounts enforces encrypted connections.
✓Set require_secure_transport=ON in my.cnf to reject all non-TLS connections.
✓Use roles (MySQL 8.0+) to simplify privilege management and auditing.

-- Create application user restricted to localhost or app subnet
CREATE USER 'order_app'@'10.0.1.%'
    IDENTIFIED WITH caching_sha2_password BY 'StrongPass!123'
    PASSWORD EXPIRE INTERVAL 90 DAY
    FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 2;

-- Grant only required privileges (no DROP, no CREATE)
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.orders TO 'order_app'@'10.0.1.%';
GRANT SELECT ON shop.products TO 'order_app'@'10.0.1.%';
…

AiCanCode.org

Administration

MySQL Configuration Tuning

✓innodb_buffer_pool_size is the single most impactful setting — set to 60–80% of RAM; buffer pool hit ratio should be > 99%
✓innodb_redo_log_capacity controls write throughput — larger = faster writes, but longer crash recovery on restart
✓innodb_flush_log_at_trx_commit=1 + sync_binlog=1 is the safest durability setting, at a throughput cost
✓max_connections × ~1 MB RAM = total memory for connections — do not set to 10,000 on a 16 GB server
✓tmp_table_size controls when temp tables spill to disk — monitor Created_tmp_disk_tables / Created_tmp_tables ratio

# my.cnf — InnoDB buffer pool configuration
[mysqld]
# Set to 60-80% of available RAM
# Example: 16 GB server → 10-12 GB buffer pool
innodb_buffer_pool_size = 10G

# Multiple instances reduce mutex contention (1 per 1 GB)
innodb_buffer_pool_instances = 10

…

AiCanCode.org

Administration

MySQL with Spring Boot / JDBC

✓mysql-connector-j is the modern driver; use jdbc:mysql://host/db?serverTimezone=UTC.
✓HikariCP is Spring Boot's default pool — tune maximum-pool-size based on DB max_connections.
✓Pool size formula: (2 × CPU cores) + effective_spindle_count for I/O-bound workloads.
✓Use Flyway or Liquibase for schema migrations — never rely on ddl-auto=update in production.
✓spring.jpa.open-in-view=false prevents the OSIV anti-pattern and lazy loading in web layer.

<!-- pom.xml -->
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <!-- version managed by Spring Boot BOM -->
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
…

AiCanCode.org

Master Administration

Full lessons + an AI tutor, free.

AiCanCode.org

/learn/mysql