Scaling MySQL Management with a Fully Automated DBA Platform

Background

In 2016 the author joined Ping An Good Doctor as a DBA and, over the next few years, the DBA team built a comprehensive database automation platform that supports MySQL, Redis, PostgreSQL, Oracle, MongoDB, and HBase. The number of managed instances grew three‑fold while the DBA headcount remained at four.

Standardization as a Foundation

Standardization was the first prerequisite for automation. The team standardized both the operating system (OS version, filesystem layout, disk mounts, pre‑installed tools, kernel parameters) and the database layer (configuration file templates, directory structures, naming conventions). A single reference server was prepared, optimized, and then packaged into an image that all subsequent servers were built from.

Database deployment standards included unified configuration templates with only a few variable parameters, and consistent locations for MySQL binaries, data, binary logs, temporary files, and error logs. Strict adherence to these rules enabled fully automated, repeatable deployments.

Automated Instance Creation

Using a Python script, the team can create a new MySQL instance with a single command:

#pythonInstall_MySQL_Multi.py --ip=xx.xx.xx.xx --port=3306 --mem=10240 --device=/storage/fioa --mysql-version=MySQL-5.6.28-OS7-x86_64 --character=utf8

The script generates standardized configuration files (e.g., /etc/my3306.cnf, /etc/my3307.cnf) and creates a uniform directory layout for each instance:

/storage/fioa/mysql3306/
  binlog
  data
  mysql-error.log
  mysql-tmpdir

Because every instance follows the same layout, a DBA only needs the IP and port to locate all relevant information, providing a solid base for automation.

Automation Task Platform Architecture

The platform consists of three layers:

Web control layer (frontend UI)

Task management and data collection layer (scheduling, data exchange)

Work‑module layer (scripts that perform actions such as instance installation, replication setup, MHA configuration, database creation, and permission granting)

RESTful APIs expose internal data updates, while HTTP APIs allow external systems (CMDB, release platforms) to integrate. Message notifications and task reporting keep the system closed‑loop.

Integration with other teams (middleware, configuration center, DNS) enables automatic propagation of instance metadata, DNS updates, and deployment results to downstream services.

Modular Design and Three‑Level Module Hierarchy

The backend follows a high‑cohesion, low‑coupling modular approach, organized into three levels:

Level 1 – Support modules: SSH operations, MySQL client wrappers, messaging, logging, external interfaces (DNS, CDMD), metadata maintenance.

Level 2 – Basic functional modules: Install MySQL node, configure master‑slave replication, set up MHA, create databases, grant permissions. These modules call only Level 1 services.

Level 3 – Service modules: High‑level workflows that orchestrate multiple Level 2 modules, such as one‑click deployment of a full MySQL cluster or bulk TDDL sharding deployment.

By restricting calls to lower‑level modules, the design ensures reusability and easy maintenance.

Benefits and Reflections

The automation reduced a half‑day manual DBA task to a six‑minute automated job, eliminated configuration drift across DBAs, and allowed a small team to manage over a thousand MySQL and 1,500+ Redis instances. While automation may shrink the number of traditional DBA tasks, it also frees DBAs to focus on innovation, platform development, and advanced skills such as big data or machine learning.