How DBMP Automates MySQL Management and Cuts DBA Workload

Why a DBMP Platform Is Needed

As the company’s business and MySQL instances grew, DBAs spent excessive time on routine tasks like installation, data repair, migration, and SQL review, and manual failover caused long downtime. Limited staff also prevented DBAs from focusing on performance optimization, prompting the creation of an automated, platform‑based solution.

DBMP Architecture

The backend is written in Python, while the frontend management UI uses PHP.

Platform Components

The platform consists of a server side, DNS server, and MySQL cluster.

Two high‑availability servers (keepalived + VIP) manage the master‑slave clusters.

Each server runs MySQL (dual‑master), the web UI, and management scripts for MHA and MySQL.

Clients run an agent on each host to manage the MySQL cluster.

Agent responsibilities: environment initialization, MySQL installation, MHA installation, status checks.

Failover process: MHA detects an unavailable master, syncs slaves to a new master, notifies the server, which switches VIPs and updates DNS. Server‑side scripts handle slave failures.

Read/write separation is achieved via distinct domain names; DNS CNAME/A records route traffic. Load balancing among slaves uses DNS round‑robin.

Platform UI and Core Functions

1. Host Management

Provides environment initialization for new servers, agent installation, host start/stop, status display, and host removal.

2. Instance Groups

Each group represents a MySQL cluster, showing health checks, MHA status, and allowing automated MySQL installation, configuration editing, MHA management, and user management.

Instance List: shows group status, start/stop, online DB, and edit configuration.

Two failover methods are supported: MHA tool (default) and a custom

checkdbmp.py

script used when MHA is unavailable.

Configuration Files: edit MySQL or MHA config templates per instance.

MHA Management: install, start, stop, and view status of MHA on each server.

User Management: add, modify, delete MySQL users; master users have read/write, slaves read‑only. Users are recreated on each DB restart to prevent unauthorized creation.

Priority determines failover order when MHA is disabled; higher numbers have higher priority, 0 means excluded.

3. Backup

Backups are scheduled via crontab and support local and remote storage. MyISAM tables are copied directly; InnoDB uses Percona XtraBackup or mysqldump, with configurable parameters.

4. Slow Query

Slow‑query extraction uses Percona Toolkit, stores results in a server‑side database, and emails configured recipients. Queries can be added and viewed directly within an instance group.

5. Scheduled Tasks

Allows adding periodic scripts to the platform, making current online tasks visible. Logs are collected via a dedicated API with defined formats for normal and alert conditions.

Future Optimization Directions

Integrate Zabbix monitoring for real‑time MySQL performance and resource usage.

Introduce LVS load balancing to overcome DNS caching and uneven distribution issues.

Provide a UI for developers to execute SQL statements directly, reducing ticket overhead.

Incorporate SQL review tools such as Inception for automated code checks.

Technical Q&A

Q1: DNS round‑robin distribution only?

Initial version used only round‑robin; future releases will add LVS for better load balancing.

Q2: Internal or external DNS? Any timeout issues?

DNS is internal across IDC locations; no timeout problems observed.

Q3: Zabbix MySQL monitoring custom development?

No custom development; DBMP calls Zabbix APIs and adds monitoring templates.

Q4: How are Zabbix templates categorized?

Templates are grouped by MySQL parameters; classification is straightforward.

Q5: Is MySQL memory configuration optimal and auto‑adjusted?

Memory settings are tuned manually based on Zabbix performance graphs; no automatic adjustment.

Q6: Can one physical machine run multiple MySQL instances?

Current version runs one instance per server, but the platform can support multiple instances if resources allow.

Q7: Ensuring master‑slave consistency; binlog format?

Consistency is verified with Percona Toolkit tools; binlog is in ROW format.

Q8: Distributed read/write separation and Keepalived brain‑split prevention?

Read/write separation uses separate write and read domain names; Redis caches hot data. Largest instance holds over 400 GB. VIP switching is handled by hardware load balancers; scripts on Keepalived and server side prevent split‑brain scenarios.