SMS: Schema Migration Solution for MySQL with Tungsten Clustering

Introduction

SMS is a schema migration solution designed for MySQL databases integrated with Tungsten Clustering. This cutting-edge tool offers a testable, scalable, and flexible approach to schema migration, ensuring seamless integration with multiple channels for real-time progress status updates and delivering various operational benefits .

Why choose SMS for schema migration amidst various open-source tools available? In a pod-based architecture where multiple customers share db server resources, performance and latency should be primary considerations during schema migration, whether for individual customers or multiple customers simultaneously.

• Percona’s pt-online-schema-change is a widely used tool for online schema changes in MySQL databases, minimising downtime and interruptions. However, schema migrations through the primary database may introduce latency due to triggers capturing changes during the alteration process, especially with heavy write operations or frequently used tables.
• Gh-ost is a trigger-less tool used for online schema migration in MySQL databases. Currently requires MySQL versions 5.7 and greater. It creates a ghost table similar to the original one and migrates the empty ghost table gradually by copying data from the original table. However, this approach doubles the storage space, making it inefficient for pod-based architectures where shared resources are a concern and Foreign key constraints are not supported.

Challenges

DBAs manually migrate schema per customer, rolling from secondary nodes to primary. Full control, no performance degradation or downtime on primary, However, this process is time-consuming, resource-intensive, and prone to human errors. Continuous monitoring is required to avoid log expiration.

Solution

Designed and developed SMS (Schema Migration Solution) for MySQL with Tungsten Clustering using Python and Ansible. This solution allows developers and DBAs to provide inputs solely in JSON or Ansible Tower templates for schema migration and then execute the workflow. The framework takes care of all the migration steps , automating the process to minimize manual intervention.

Furthermore, the SMS framework seamlessly integrates with Slack and Email to provide real-time progress tracking during schema migration. Stakeholders receive notifications and updates, ensuring that they can monitor the migration’s status and address any potential issues promptly. This transparent communication fosters effective collaboration within the team.

Consideration

To ensure uninterrupted service for customers during primary switches in the backend, implementing connection caching at the proxy or connector side is crucial. Connection caching facilitates seamless failover during critical database transitions.

Requirements and External dependency:

• Ansible >= 2.9
• Python >=2.7
• Vault to store db credentials .
• Git for Schema migration script

The input variables for SMS are as follows: 
 
Cluster/Pod name
Database name 
Schema/Table Name 
Path for the Alter script
binlog_expire_logs_seconds/thl_log_retention

Github Codebase: https://github.com/vinayjaiswal1990/sms/blob/main/schema_migration.yml

Workflow Diagram:

Key Features :

• Real time Notification.
• Scalable with Ansible Workflow Engine.
• Easy Integration for Migration Scripts.
• No downtime and Zero Latency on Primary.
• The solution uses an immutable state approach, which means that the delivery process is predictable, consistent, and repeatable.
• Mysql Version Independence and Environment Compatibility.
• Integration with Continuous Deployment (CD).
• Audit historical logs of schema migration activity for compliance.

Limitations:

• Lack of Support for Standalone MySQL Nodes (Primary).
• Suboptimal for Small Schema Sizes — SMS may not be the most suitable option for small schema sizes or databases with minimal changes. In such cases, the migration process through SMS might trigger unnecessary primary switches, which could lead to inefficiencies and unnecessary overhead.

Conclusion:

SMS offers valuable features and benefits for schema migration in MySQL databases with Tungsten Clustering, it also has some limitations that should be taken into consideration. Organizations with standalone primary nodes or smaller schema sizes might find other schema migration tools more suitable for their specific needs.

Before selecting a schema migration solution, evaluating the database environment’s characteristics and requirements is essential. Each organization’s unique needs should be considered to determine the most appropriate and efficient schema migration approach.

By carefully assessing the pros, cons, and considerations, organizations can make informed decisions to achieve successful and smooth schema migrations for their MySQL databases.

Thanks for reading :)