Essential DBA Playbook: Avoid Common MySQL Mistakes and Master Database Operations

Environment Overview

DBAs must master the full stack—network, OS, filesystem, database, security, and scripting—to avoid low‑level errors. Understanding the MySQL deployment (directories, binaries, data files, logs) and engine specifics (InnoDB, MyISAM, memory, spider, etc.) prevents accidental data loss during ALTER, charset, or migration tasks.

Key OS considerations include file descriptor limits, NTP configuration, RAID write‑cache mode, and monitoring CPU, memory, I/O, and network usage.

Critical MySQL Configuration

Important parameters to review before migration or deployment:

innodb_buffer_pool_size (50‑80% of RAM)

key_buffer_size (MyISAM, recommend disabling)

innodb_flush_log_at_trx_commit (set to 2 for performance)

binlog enablement

sync_binlog (0 for speed, >0 for safety)

innodb_file_per_table

lower_case_table_names

character_set_server

max_connections (calculate based on session memory)

transaction_isolation (Read‑Committed for row‑based binlog)

Use tools like pt-config-diff to compare source and target instances.

Database Security

Enforce strong passwords, avoid wildcard host permissions, and grant only minimal privileges required for each account.

Regularly verify data consistency in master‑slave setups with pt-table-checksum and repair discrepancies using pt-table-sync.

Implement robust backup strategies covering full backups and binlog increments, store backups on separate media, and routinely test restore procedures.

Routine Operations

Start/stop instances safely.

Perform schema changes with tools like pt-online-schema-change for large tables.

Optimize indexes, adjust configurations, backup databases, migrate data, and handle failover switches.

Document procedures, review steps, and consider scripting repetitive tasks for consistency.

Failure Pre‑Planning

Develop detailed runbooks for common failures (master crash, binlog overflow, connection saturation) and conduct regular drills to validate them.

For extreme scenarios (e.g., all replicas down), involve developers early to design viable recovery paths.

Architecture Insight

Understand high‑availability designs (MHA, MMM, NDB Cluster, LVS, Keepalived) beyond their nominal operation—know detection mechanisms, failover decision logic, and data‑consistency guarantees.

Explore the underlying services your DBA tasks interact with, and consider building small automation scripts in Python or Go to deepen that knowledge.

Business Awareness

Grasp the read/write patterns, traffic models, and business impact of database changes to provide better optimization advice and faster incident triage.

Online Operations Checklist

Backup data before any modification.

Always have a rollback plan.

Insert sleep in large batch jobs.

Test changes in staging first.

Flush dirty pages before restarts.

Avoid bulk binlog deletions.

Write detailed step‑by‑step procedures and have them reviewed.

Confirm the environment right before hitting Enter .

Stop if the operation may worsen the situation.

Use tune2fs to free reserved blocks on full disks.

Adjust max_connections via gdb without restarting:

gdb -p pid -ex "set max_connections=1000" -batch

Mindset

Adopt meticulousness, confidence, and accountability: understand the full impact of each click, prepare thorough runbooks, and continuously practice to build the “craftsman spirit” essential for reliable DBA work.

Original article sourced from ChinaUnix blog (http://blog.chinaunix.net/uid-20639775-id-5765237.html).