Automated Database Management | Atomic Server Pilot

The Business Problem: Database Management as a Bottleneck

Every application relies on a database. However, when managing multiple servers and applications, creating, monitoring, and securely managing databases quickly becomes a costly and risky manual task. Traditional approaches require SSH connections, typing SQL commands, storing passwords in spreadsheets or files, and constant verification. Any mistake can lead to data leaks, downtime, or loss of productivity.

Atomic Server Pilot addresses this problem with an automated, centralized database management platform that reduces time from hours to seconds and eliminates human risk factors.

Architectural Approach: From Command to Workflow

Instead of providing a simple interface that executes SSH commands, we designed a system that treats the full lifecycle of a database as a single, automated workflow. The architecture is based on three layers:

• Presentation Layer: A web interface displaying all databases per server, with capabilities for creation, structure viewing, password reveal, and deletion.

• Logic Layer (Controller): A central control point that validates permissions, applies rate limiting, logs every action, and coordinates communication with the server.

• Execution Layer (SSH Service): An abstraction that executes MySQL commands on the remote server via SSH, with secure password management and error handling.

The key innovation is that the user never needs to know the MySQL root password. The system securely stores it in the service configuration and uses it automatically for all operations. Database passwords are stored encrypted and revealed only upon explicit request, with rate limiting and full logging.

Key Design Decisions and Business Impact

Security Without Compromise

Password reveal is done through a dedicated endpoint that applies rate limiting (maximum 5 requests per minute) and logs every access. This means that even if an employee gains access to the interface, they cannot bulk extract passwords without being detected. For the business, this translates to compliance with regulations (GDPR, PCI DSS) and reduced insider threat risk.

Automated Creation and Deletion

Creating a database is executed in one step: the user fills out a form, the system validates the data, checks if the database already exists, and then executes MySQL commands via SSH. If something fails, the error is logged and the user is notified immediately. Deletion follows similar logic, with commands separated for better error handling. The result: zero manual errors and faster delivery of new environments.

In-Browser Exploration Without Third-Party Tools

The ability to explore the database structure directly from the browser is based on an optimized SQL query that retrieves all tables and columns in a single pass. This eliminates the need for tools like phpMyAdmin or MySQL Workbench, reducing complexity and licensing costs. Developers can quickly view the database structure without installing anything.

Data Flow and System Interaction

The workflow for a typical action (e.g., creating a database) is as follows:

1. The user submits the form via the browser.

2. The system validates the input and checks that the user has permission for the specific server.

3. It retrieves the MySQL service configuration from the server (root password, etc.).

4. It checks if a database with the same name already exists.

5. It creates a new SSH connection and executes commands to create the database, user, and permissions.

6. It stores the record in the Pilot database with an encrypted password.

7. It logs the action in a log file.

8. It returns a success or failure message to the user.

The Pilot central database stores only metadata and encrypted passwords, while the actual database lives on the remote server. This separation ensures that even if Pilot is compromised, the data remains secure.

Strategic Conclusions

• Return on Investment (ROI): Automation reduces database management time by ~90%. A team managing 50 databases saves about 10 hours per week, translating to annual savings of tens of thousands of euros.

• Risk Reduction: Password encryption, rate limiting, and full logging minimize the risk of data leaks and facilitate compliance audits.

• Team Impact: Developers gain autonomy without needing root access, while system administrators are freed from repetitive tasks.

• Lessons Learned: Investing in a well-designed abstraction (SSH Service) and strict error handling pays off in the long run, reducing incidents and resolution time.

Atomic Server Pilot demonstrates that automating database management is not just a technical improvement but a strategic investment that enhances security, speed, and competitiveness.