MySQL Change Database Encoding

A robust Ruby tool for changing MySQL database encoding and collation with minimal disruption to production systems. Supports online schema changes to keep your database operational during migrations.

Features

• Safe Encoding Migration: Change database character sets without data loss
• Collation Updates: Modify collation settings across entire databases
• Online Schema Change: Integrate with pt-online-schema-change for zero-downtime migrations
• Configurable Fallback: Automatic fallback to direct ALTER TABLE when online tools unavailable
• Table-by-Table Processing: Granular control over migration process
• Production-Ready: Designed for live database migrations with minimal disruption

Installation

Prerequisites

• Ruby 3.0 or higher
• MySQL database
• (Optional) pt-online-schema-change for online migrations

Setup

git clone https://github.com/durableprogramming/mysql_change_database_encoding.git
cd mysql_change_database_encoding
bundle install

Usage

Basic Migration

Change database encoding and collation:

MYSQL_PASSWORD=your_password MYSQL_DATABASE=your_database \
ruby mysql_change_database_encoding.rb \
--collation utf8mb4_unicode_ci --encoding utf8mb4

Online Schema Change

Use pt-online-schema-change for zero-downtime migration:

MYSQL_PASSWORD=secure_password MYSQL_DATABASE=legacy_database \
ruby mysql_change_database_encoding.rb \
--collation utf8mb4_unicode_ci --encoding utf8mb4 --osc

Environment Variables

• MYSQL_PASSWORD: Database password (required)
• MYSQL_DATABASE: Target database name (required)
• MYSQL_HOST: Database host (default: localhost)
• MYSQL_USER: Database user (default: root)
• MYSQL_PORT: Database port (default: 3306)

Command Options

• --encoding: Target character set (e.g., utf8mb4, utf8, latin1)
• --collation: Target collation (e.g., utf8mb4_unicode_ci, utf8mb4_general_ci)
• --osc: Enable online schema change via pt-online-schema-change
• --dry-run: Preview changes without executing

Common Use Cases

Migrate to UTF8MB4

The most common migration path - moving from utf8 to utf8mb4 for full Unicode support:

MYSQL_PASSWORD=pwd MYSQL_DATABASE=myapp \
ruby mysql_change_database_encoding.rb \
--encoding utf8mb4 --collation utf8mb4_unicode_ci --osc

Update Collation Only

Change collation without modifying encoding:

MYSQL_PASSWORD=pwd MYSQL_DATABASE=myapp \
ruby mysql_change_database_encoding.rb \
--collation utf8mb4_unicode_ci

Legacy Database Migration

Migrate old latin1 databases to modern encoding:

MYSQL_PASSWORD=pwd MYSQL_DATABASE=legacy_app \
ruby mysql_change_database_encoding.rb \
--encoding utf8mb4 --collation utf8mb4_unicode_ci --osc

Why Use This Tool?

Problems It Solves

• Emoji Support: Migrate to utf8mb4 to support emoji and other 4-byte Unicode characters
• Character Corruption: Fix encoding mismatches causing garbled text
• Modern Standards: Align with current best practices for character encoding
• Production Safety: Change encodings without taking database offline

Benefits

• Zero Downtime: Use online schema change for production migrations
• Automated: Processes all tables systematically
• Safe: Handles edge cases and provides rollback-friendly approach
• Configurable: Flexible options for different migration scenarios

Online Schema Change Integration

When using --osc, the tool leverages pt-online-schema-change to:

1. Create a new table with desired encoding/collation
2. Copy data in chunks while tracking changes
3. Swap tables atomically
4. Minimize locking and downtime

This is crucial for production databases that cannot afford maintenance windows.

Best Practices

Before Migration

1. Backup your database: Always create a full backup before encoding changes
2. Test in staging: Run the migration on a staging database first
3. Check disk space: Online schema changes require additional space
4. Monitor replication: Be aware of replication lag during migration

During Migration

1. Use —dry-run first: Preview changes before execution
2. Monitor performance: Watch for increased load during migration
3. Start with small tables: Test on non-critical tables first

After Migration

1. Verify data integrity: Check that all data migrated correctly
2. Update application: Ensure application uses new encoding in connections
3. Update backups: Create new backup with updated encoding

Requirements

• Ruby 3.0+
• MySQL 5.7+ or MariaDB 10.2+
• pt-online-schema-change (optional, for online migrations)

Contributing

Contributions are welcome! Please read our contributing guidelines before submitting pull requests.

1. Fork the repository
2. Create a feature branch (git checkout -b feature/my-feature)
3. Make your changes
4. Test thoroughly on a test database
5. Commit your changes
6. Push to your fork
7. Open a pull request

Commercial Support

Commercial support is available from Durable Programming, LLC at durableprogramming.com. We offer:

• Migration planning and consulting
• Custom migration scripts for complex scenarios
• Production migration assistance
• Post-migration optimization

Contact us at [email protected].

License

GNU General Public License v3.0 - see LICENSE for details.

Copyright (c) 2018-2025 Durable Programming, LLC. All rights reserved.

Support

For bugs, feature requests, or questions, please open an issue on GitHub.

Acknowledgments

• pt-online-schema-change from Percona Toolkit
• The Ruby and MySQL communities

GitHub

⭐ 10 stars | 📅 Created March 2018 | 🔄 Updated 2025

View on GitHub