Avoid Costly DBA Mistakes: Essential MySQL Operations and Best Practices

Preface

After six years as a full‑time DBA, I have witnessed many low‑level mistakes caused by unfamiliarity with engine features, production environments, or insufficient experience. This article distills those lessons into practical advice for fellow DBAs and operations engineers.

1. Environment

A DBA must master networking, OS, filesystem, database, security, and scripting knowledge. Familiarity with the entire stack—especially MySQL—prevents errors such as accidental ALTER failures, charset issues, or missing triggers/events during migration.

1.1 Operating System

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

1.2 Database Deployment

Understand directory layout, executable, data, log, and configuration file locations, as well as start/stop procedures.

1.3 Engine Usage

Identify the default and per‑table storage engines (InnoDB, MyISAM, Memory, Spider, TokuDB, etc.) and their characteristics to avoid migration or corruption problems.

Note: Although InnoDB is dominant, other engines still appear in some deployments.

1.4 Synchronization Methods

Binlog & POS based

GTID based

Asynchronous

Semi‑synchronous

Single‑threaded

Multi‑threaded

Each method has distinct recovery steps; knowing the differences is essential.

1.5 Critical MySQL Parameters

innodb_buffer_pool_size  # 50‑80% of memory for InnoDB
key_buffer_size          # MyISAM buffer, recommend using InnoDB
innodb_flush_log_at_trx_commit  # redo log flush, usually 2
-bin                     # enable binlog
sync_binlog              # binlog sync level, 0‑1‑>strong consistency
innodb_file_per_table   # separate tablespaces
lower_case_table_names   # case sensitivity
character_set_server     # charset to avoid garbled data
max_connections          # calculate based on session memory
transaction_isolation    # default REPEATABLE READ, may set READ COMMITTED

Compare these settings with the source instance (e.g., using pt-config-diff) before migration.

1.6 Tools for Environment Collection

Use pt-mysql-summary to gather comprehensive MySQL metrics.

https://www.percona.com/doc/percona-toolkit/2.1/pt-mysql-summary.html

2. Data Security

2.1 Permission Management

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

2.2 Data Consistency

Periodically run pt-table-checksum on master‑slave setups and fix discrepancies with pt-table-sync.

2.3 Backup Strategy

Maintain full backups and binlog incremental backups, store them on separate machines or distributed file systems, and regularly test restore procedures.

3. Routine Operations

Master common tasks such as start/stop, schema changes, index optimization, configuration tweaks, backups, migrations, and failover. Document procedures, script repetitive actions, and keep them searchable.

3.1 Incident Playbooks

Prepare detailed response plans for master failures, extreme outages, and other scenarios; conduct regular drills to ensure confidence.

4. Architecture

Understand the full database and business architecture, including high‑availability solutions (MHA, MMM, NDB Cluster, LVS, Keepalived). Dive into the mechanics of detection, failover, and data consistency to enable faster troubleshooting and automation.

5. Online Operations (Tips)

Backup before any data modification.

Always have a rollback plan.

Insert SLEEP between batch statements.

Use pt-online-schema-change for large table DDL.

Test changes in staging first.

Flush dirty pages before restart.

Avoid bulk binlog deletions.

Document and review every change.

Double‑check the environment before pressing Enter .

Stop if the operation may worsen the situation.

Handle disk‑full quickly (e.g., tune2fs).

Adjust max_connections on‑the‑fly without restart:

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

6. Mindset

6.1 Attention and Courage

DBA work is high‑risk; thorough preparation and clear rollback steps build confidence.

6.2 Responsibility

Face problems head‑on, learn from failures, and share case studies to prevent repeat mistakes.

6.3 Craftsmanship

Maintain diligence in routine checks, backup verification, and continuous skill accumulation.

Article originally from ChinaUnix: http://blog.chinaunix.net/uid-20639775-id-5765237.html