Essential DBA Checklist: Avoid Common MySQL Mistakes and Boost Reliability

Environment Essentials

A DBA must understand the full stack—network, operating system (Linux/Windows), file system, MySQL deployment layout, storage engines, and replication method. Key OS tweaks include file‑descriptor limits, NTP synchronization, and RAID write‑cache settings. For MySQL, know the locations of binaries, data files, log files, and the start/stop procedures. Identify the storage engines in use (InnoDB, MyISAM, Memory, Spider, TokuDB, etc.) and the replication mode (binlog/GTID, async, semi‑sync, single‑threaded or multi‑threaded). Record the MySQL major version because feature sets differ across releases.

Critical configuration variables to verify before migrations include: innodb_buffer_pool_size – typically 50‑80 % of RAM key_buffer_size – relevant for MyISAM, usually set to 0 when InnoDB is dominant innodb_flush_log_at_trx_commit – 1 for full durability, 2 for a balance log-bin – enable binary logging sync_binlog – 0 for performance, >0 for stronger consistency innodb_file_per_table – use separate tablespaces lower_case_table_names – case‑sensitivity handling character_set_server – ensure correct charset to avoid garbled data max_connections – calculate based on session memory plus a safety margin transaction_isolation – default REPEATABLE‑READ; consider READ‑COMMITTED for row‑based binlog

Compare these settings with the source instance using pt-config-diff to avoid configuration drift.

Hardware Considerations

Document CPU core count, total RAM, and storage media (SAS, PCIe SSD, NVMe). Establish baseline QPS/TPS numbers for each hardware class using load‑testing tools such as sysbench, mysqlslap, or fio on idle machines. This baseline helps identify bottlenecks during capacity planning.

Runtime Status Monitoring

Key metrics to track on a running instance:

Total data size and per‑table size

CPU, I/O, and system load

Slow‑query count and overall SQL latency

Lock statistics and dirty‑page ratio

Access pattern (read‑heavy vs. write‑heavy)

Tools such as pt-mysql-summary, innotop, and orzdb provide real‑time insight.

Data Security

Permission hygiene : enforce strong passwords, avoid wildcard host entries ("%"), and grant only the minimal privileges required for each account.

Consistency checks : run pt-table-checksum regularly on master‑slave setups and repair mismatches with pt-table-sync.

Backup strategy : maintain full logical or physical backups together with incremental binlog backups, store them on separate machines or distributed file systems, and periodically test restore procedures to verify data availability.

Routine Operations

Common DBA tasks include start/stop, schema changes, index optimization, configuration tweaks, backups, migrations, and master‑slave switchover. Document commands, tag them for quick search, and script repetitive actions to ensure consistency.

Fault‑Prevention Plans

Develop pre‑mortems for typical failures (e.g., master crash) and extreme scenarios (both master and slaves down). Write step‑by‑step runbooks, validate them in a test environment, and conduct regular drills so any team member can follow the procedures confidently.

Architecture Awareness

Understand the full database architecture, including high‑availability solutions such as master‑slave, MHA, MMM, NDB Cluster, LVS, and keepalived. Study how each component detects failures, coordinates failover, and maintains data consistency. Reviewing source code or configuration files of these components accelerates troubleshooting and enables automation.

Online Operation Tips

Backup before any data‑modifying operation.

Always have a rollback plan for online changes.

Insert sleep pauses in bulk scripts to reduce load spikes.

Use pt-online-schema-change for large ALTER on big tables.

Test changes in a staging environment first.

Flush dirty pages before restarting the server.

Avoid bulk deletion of binlog files.

Document detailed steps and conduct peer reviews.

Double‑check the environment before pressing Enter.

If an operation may worsen the situation, stop immediately.

Free disk space quickly with tune2fs to release reserved blocks.

When connections are saturated, increase max_connections via live GDB injection instead of restarting: gdb -p <em>pid</em> -ex "set max_connections=1000" -batch Replace pid with the MySQL process ID.