How to Modernize DBA Operations: Building an Automated Database Management Platform

1. Why Database Management Needs Modernization

In most software projects, data ultimately resides in databases, making DBA work a potential bottleneck. At an e‑commerce company, databases support multiple projects, require strict security, timely access, and proper permission control, especially during high‑traffic events like Double‑11 flash sales.

2. Four Modernization Goals

Identify which information can be safely shared with development teams.

Automate repeatable DBA tasks that meet standard policies.

Ensure real‑time data accuracy.

Provide a unified platform for information display and DBA tools.

3. Pain Points from Real‑World Experience

Project expansion often requires deploying new replicas or migrating databases.

Hardware failures demand rapid failover.

Over 500 database instances mean manual operations are multiplied hundreds of times.

DBAs must export data per ticket, leading to excessive permission grants.

Staff turnover creates unclear permissions and weak data security.

4. Platform Overview

The platform consists of two main parts: Information Collection & Display and DBA Management Tools . Metadata is made readable to all technical staff, while detailed data remains restricted and requires approval.

4.1 Information Collection & Display

Metadata is harvested via shell and python scripts on a schedule, then archived for analysis.

Provides quick, fuzzy search across all projects.

Tracks storage growth trends.

Enables statistical identification of tables needing vertical/horizontal splitting, duplicate indexes, or auto‑increment expansion.

Data access is limited to 500 rows per query for non‑privileged users. Authorized users can view restricted details after permission approval.

Server scanning gathers fixed ports, login tests, database names, and roles (master/slave) for each host.

Performance and monitoring data are collected from the Zabbix monitoring database, allowing the platform to highlight frequently alarming metrics that need scaling or tuning.

4.2 DBA Management Tools

Installation and deployment scripts have been automated. Key functionalities include:

Standard‑compliant system initialization that installs required packages, prefers non‑domain replicas, and deploys backups to appropriate nodes.

Backup space estimation based on data size; if insufficient, backups are redirected to a large‑capacity server.

Multiple backup cycles are retained, with older sets pruned when space is tight.

MySQL initialization that generates environment checks, calculates innoDB_buffer_pool_size = memory * 80%, and creates server-id = last two IP octets + three random numbers.

Creation of common users with import validation.

Restore scripts that can rebuild an instance from logical or XtraBackup files, or clone a replica directly from a master or another replica.

DML execution for bug fixes using selected inception features; the platform routes SQL to the appropriate master after audit (e.g., row‑count limits) and records execution history.

5. Q&A Highlights

High‑availability solution: Planned use of MHA (not yet deployed).

Cross‑data‑center replication: Avoided due to latency concerns; large transactions are split at the development level.

Handling massive DDL or batch deletes/updates: Use pt‑online‑schema‑change for online schema changes and large‑scale deletions.

Sharding recommendation: Consider sharding‑JDBC (see GitHub project).

The author hopes the platform and tools inspire others and plans to open‑source a sanitized version in the future.