MySQL recipes
- Rotate the binary logBinlog & Log
- Purge old binary logs (PURGE)Binlog & Log
- View binary log statusBinlog & Log
- Enable the general and slow query logsBinlog & Log
- Change the InnoDB redo log sizeStorage
- Add / manage an InnoDB undo tablespaceStorage
- Add a general tablespaceStorage
- OPTIMIZE a tableStorage
- Query table and schema sizesStorage
- Update table statistics (ANALYZE TABLE)Maintenance
- Rebuild a table/indexesMaintenance
- Check table integrity (CHECK TABLE)Maintenance
- Change a global variable and persist itParameters
- View the current value of a variableParameters
- Reload the grant tables (FLUSH PRIVILEGES)Parameters
- Set up a replica (CHANGE REPLICATION SOURCE)Replication
- Start / stop a replicaReplication
- View replica status (SHOW REPLICA STATUS)Replication
- Skip a broken transaction (GTID)Replication
- Kill a query or connection (KILL)Session
- View the process listSession
- View blocking locks (data_lock_waits)Session
- Monitor replica lag and applier worker errors (performance_schema)Replication
- Tune the multi-threaded replica (replica_parallel_workers)Replication
- Repair a broken replica: 1062 / 1032 row conflictsReplication
- Find and neutralize errant GTID transactionsReplication
- Rebuild a replica from scratch with the CLONE pluginReplication
- Recover from a bad DROP / DELETE with a delayed replica (SOURCE_DELAY + UNTIL)Replication
- Take a consistent logical backup with GTID info using mysqldumpBackup & PITR
- Parallel dump / load with MySQL Shell (util.dumpInstance, loadDump)Backup & PITR
- Full + incremental physical backup and restore with XtraBackupBackup & PITR
- Point-in-time recovery with binlogs: find and skip the bad transactionBackup & PITR
- Check InnoDB Cluster / Group Replication healthInnoDB Cluster
- Planned primary switchover in InnoDB ClusterInnoDB Cluster
- Bring the cluster back after a complete outage (rebootClusterFromCompleteOutage)InnoDB Cluster
- Bootstrap MySQL Router against an InnoDB ClusterRouter & Proxy
- Read/write split and replica health checks with ProxySQLRouter & Proxy
- Online DDL: ALGORITHM=INSTANT / INPLACE, LOCK=NONE and progress trackingOnline Operations
- Non-blocking ALTER on a large table with pt-online-schema-changeOnline Operations
- Triggerless online schema change with gh-ostOnline Operations
- Find the most expensive queries (performance_schema digest)Performance
- What is running right now: active sessions, latency, locks and transactions (sys.session)Performance
- Diagnose 'Waiting for table metadata lock' and find the real blockerPerformance
- Analyze InnoDB deadlocks and log all of themPerformance
- Long-running transactions and history list length (purge lag)Performance
- Capture a short full slow log and analyze it with pt-query-digestPerformance
- Debug a bad plan with EXPLAIN ANALYZE and the optimizer tracePerformance
- Create and manage histograms for non-indexed columnsIndex & Statistics
- Find unused / redundant indexes, make them invisible, drop safelyIndex & Statistics
- Is the buffer pool big enough: hit ratio, wait_free and online resizeMemory
- RANGE partition maintenance: add future partitions, archive / drop old onesPartitioning
- Build a least-privilege model with roles (CREATE ROLE, DEFAULT ROLE)Security
- Password policy, failed-login lockout and zero-downtime password rotationSecurity
- Enforce TLS, find non-TLS connections, renew certificates without restartSecurity
- Audit risky accounts: empty password, '%' host, SUPER, mysql_native_passwordSecurity
- 8.0 → 8.4 LTS upgrade pre-check (util.checkForServerUpgrade)Upgrade
- Convert latin1 / utf8mb3 tables to utf8mb4Maintenance