Essential DBA Checklist: Avoid Common MySQL Mistakes and Master Production Environments

Introduction

Having worked as a dedicated DBA for over six years, I have witnessed many low‑level mistakes made by colleagues and myself, often due to insufficient knowledge of engine features, production environments, or lack of experience. This article summarizes the lessons learned and offers practical guidance for fellow DBAs and operations engineers.

1. Environment

A DBA must master a wide range of skills, including networking, operating systems, filesystems, databases, security, and programming.

1.1 Operating System

Understand the OS type (Linux or Windows), kernel optimizations for databases (file descriptors, NTP, RAID write‑cache), and monitor CPU, memory, I/O, and network usage.

1.2 Database

Familiarize yourself with the database environment to avoid misoperations such as charset issues, missing triggers/events during migration, or accidental data loss.

1.3 Deployment

Know where executables, data files, log files, and configuration files reside, and how to start and stop the database.

1.4 Engine

Identify the default storage engine and the engines used by existing tables, and understand their characteristics to prevent migration or startup problems.

Note: Although InnoDB is the default today, some installations still use MyISAM, Memory, Merge, Spider, TokuDB, etc.

1.5 Replication

MySQL typically uses binlog‑based replication. Common methods include:

Binlog and POS based

GTID based

Asynchronous

Semi‑synchronous

Single‑threaded

Multi‑threaded

Each method has distinct characteristics, especially when skipping positions during recovery.

1.6 Version

Know the major version of MySQL in use, as features can differ dramatically between versions, affecting migration, synchronization, and upgrade strategies.

1.7 Procedures and Events

Be aware of existing stored procedures and events; include them in backups and migrations, and disable events on the target before switching them back on.

1.8 Key Configuration

Critical MySQL parameters to review before migration or deployment:

innodb_buffer_pool_size  # 50‑80% of memory, huge performance impact
key_buffer_size          # MyISAM only, prefer InnoDB
innodb_flush_log_at_trx_commit  # usually set to 2 for performance
-bin                     # enable binlog
sync_binlog              # 0 for speed, >1 for safety
innodb_file_per_table
lower_case_table_names
character_set_server     # avoid charset issues
max_connections          # calculate based on session memory
transaction_isolation    # default Repeatable Read, may use Read Committed for row‑based binlog

Compare these settings with the source instance (e.g., using pt‑config‑diff) to prevent mismatches that cause restarts or failures.

1.9 Tools

pt‑mysql‑summary (Percona Toolkit) provides a quick overview of MySQL configuration and status.

2. Hardware

Assess CPU cores, memory size, and storage media (SAS, PCIe SSD, NVMe) and maintain benchmark data for common server models to evaluate capacity and identify bottlenecks.

3. Runtime Metrics

Key indicators to monitor:

Database and table size (especially large tables)

Instance load: CPU, I/O, system load

Slow queries

SQL latency

Lock statistics

Dirty pages

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

Tools such as innotop and orzdba can help visualize these metrics.

4. Data Security

4.1 Permission Security

Enforce strong passwords, avoid wildcard host entries, grant only the minimum privileges required, and restrict login hosts.

4.2 Data Consistency

Regularly run pt‑table‑checksum on master‑slave setups and use pt‑table‑sync to repair any inconsistencies.

4.3 Backup Strategy

Maintain full backups and binlog incremental backups, store them safely (e.g., distributed file systems), and periodically test restore procedures to ensure data is recoverable.

5. Routine Operations

Master common tasks such as start/stop, configuration changes, index optimization, backups, migrations, and failover. Document procedures, script repetitive actions, and keep quick‑reference notes for emergency use.

6. Architecture

Understand the high‑availability architecture in use (master‑slave, MHA, MMM, NDB Cluster, LVS, keepalived, etc.), the detection mechanisms, failover logic, and how components interact. Deep knowledge enables faster troubleshooting and automation.

7. Online Operations Experience

Backup before any modify or delete operation.

Always have a rollback plan for online changes.

Insert sleep intervals in batch operations.

Use pt‑online‑schema‑change for large DDL on big tables.

Test changes in a staging environment first.

Flush dirty pages before restarting the database.

Avoid bulk deletion of binlog files.

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

Confirm the environment again before pressing Enter.

Stop the operation if it may worsen the situation.

Handle disk‑full issues with tune2fs to release reserved blocks.

Increase max_connections via gdb without a restart:

gdb -p pid -ex "set max_connections=1000" -batch
# pid is the mysqld process ID

8. Mindset

8.1 Attention and Courage

DBA work is high‑risk; thorough preparation and detailed operation plans give you the confidence to act boldly.

8.2 Responsibility

Face problems directly, learn from them, and share case studies to prevent repeat mistakes.

8.3 Craftsmanship

Maintain a diligent, never‑slacking attitude toward monitoring, backup verification, and continuous skill accumulation.