dbsafe: Pre-execution Safety Analysis for MySQL DDL/DML Operations

Know exactly what your MySQL DDL/DML operations will do before executing them. dbsafe offers a robust solution for pre-execution safety analysis, eliminating uncertainties and ensuring safer database management.

✨ Highlights

• 🔍 Deep Analysis: Understand your DDL and DML operations with in-depth insight into algorithms (INSTANT/INPLACE/COPY), locking behaviors, and table rebuild detection.
• 🎯 Risk Assessment: Automatic classification of operations into categories: Safe, Caution, or Dangerous.
• 🌐 Topology Aware: Automatically detects Galera/PXC, Group Replication, and asynchronous replicas to provide tailored warnings.
• 📊 Impact Estimation: Calculate table sizes, row counts, potential replication lags, and write-set sizes.
• 🔄 Rollback Plans: Generate pre-defined reverse SQL commands and recovery options to mitigate potential errors.
• 📝 Chunked Scripts: For extensive operations, dbsafe can produce batched DELETE/UPDATE scripts.
• 🎨 Multiple Output Formats: Outputs can be generated in Text, Plain, JSON, or Markdown, making it suitable for CI/CD pipelines and documentation.
• ⚡ Read-Only: Ensures data integrity as it never modifies data in the database.
• ✅ Production-Ready: Ensures high reliability with 85-97% test coverage and integration tests against real MySQL instances.

🚀 Quick Start

1. **Set up your MySQL user with read-only permissions:

CREATE USER 'dbsafe'@'%' IDENTIFIED BY '<password>';  
GRANT SELECT ON *.* TO 'dbsafe'@'%';  
GRANT PROCESS ON *.* TO 'dbsafe'@'%';  
GRANT REPLICATION CLIENT ON *.* TO 'dbsafe'@'%';  
-- ⚠️ No write permissions ever.  
```**
2. **Initialize configuration:**  
```bash  
dbsafe config init  

3. Test the connection:

dbsafe connect  

4. Analyze your first DDL operation:

dbsafe plan "ALTER TABLE users ADD COLUMN email VARCHAR(255)"  

Receive a detailed analysis outlining the consequences and risks associated with this operation.

🔬 What It Analyzes

DDL Operations: From ADD COLUMN to DROP INDEX, dbsafe accurately detects algorithm types, rebuild warnings, and handles concurrent DDL operations seamlessly.
DML Operations: Efficiently estimate affected rows for DELETE, UPDATE, and INSERT operations, including automatic chunking recommendations for large datasets.
Topology Detection: dbsafe identifies cluster types (Galera, Group Replication, Standalone) to provide context-aware analysis and recommendations.

📄 Example of Usage

To analyze a DDL operation:

dbsafe plan "ALTER TABLE orders ADD INDEX idx_created (created_at)"  

This analysis offers a comprehensive report including recommendations and expected execution time, ensuring informed decision-making.

🎯 Output Formats

Different output formats are available for various use cases:

⚙️ Configuration

Configure through ~/.dbsafe/config.yaml, defining connection details, chunk sizes, and preferred output formats to fit different environments and requirements.

🤝 Contributing

Contributions to dbsafe are welcome! Engage in improving this tool to enhance MySQL safety practices for all users.