Migrasi dari MySQL ke MariaDB adalah keputusan strategis yang banyak organization ambil karena licensing dan fitur. Setelah melakukan migrasi untuk beberapa enterprise clients dengan zero downtime, saya akan berbagi playbook lengkap.

Pre-Migration Assessment

1. Compatibility Check

-- Check MySQL version dan features
SELECT VERSION();
SHOW VARIABLES LIKE '%version%';
SHOW PLUGINS;
-- Check storage engines
SELECT 
ENGINE,
COUNT(*) as table_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE;

2. Identify Incompatibilities

MySQL 8.0 features not in MariaDB 10.x:
– JSON Data Type improvements
– CTE enhancements
– Window function extensions
– Roles (limited support in older MariaDB)
– Default authentication plugin (caching_sha2_password)

3. Schema Compatibility Analysis

-- Check for incompatible data types
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE DATA_TYPE IN ('json', 'geometry')
   OR COLUMN_TYPE LIKE '%GENERATED%';
-- Check for views dengan complex queries
SELECT 
TABLE_SCHEMA,
TABLE_NAME,
VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql');

Migration Strategy

Strategy 1: In-Place Upgrade (Downtime Required)

Untuk: Small databases, acceptable downtime

# 1. Backup penuh
mysqldump -u root -p --all-databases --routines --triggers > /backup/mysql_full.sql
2. Stop MySQL
sudo systemctl stop mysql
3. Install MariaDB
sudo apt remove mysql-server mysql-client -y
sudo apt autoremove -y
sudo apt install mariadb-server mariadb-client -y
4. Start MariaDB dan restore
sudo systemctl start mariadb
mysql -u root -p < /backup/mysql_full.sql
5. Run mysql_upgrade
sudo mysql_upgrade -u root -p
6. Restart
sudo systemctl restart mariadb

Strategy 2: Online Migration dengan Replication (Zero Downtime) ✓ Recommended

Setup:
– MySQL 8.0 Master (current)
– MariaDB 10.x Slave (target)
– Application switches to MariaDB setelah sync

1. Prepare MySQL Master

-- Enable binlog dengan format compatible
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'FULL';
-- Buat replication user
CREATE USER 'repl_mariadb'@'%' IDENTIFIED WITH mysql_native_password BY 'ReplPass123!';
GRANT REPLICATION SLAVE ON 
. TO 'repl_mariadb'@'%';
GRANT BINLOG MONITOR ON 
. TO 'repl_mariadb'@'%';
FLUSH PRIVILEGES;

2. Backup dari MySQL

# Backup dengan master data position
mysqldump -u root -p \
    --single-transaction \
    --master-data=2 \
    --all-databases \
    --routines \
    --triggers \
    --events \
    --set-gtid-purged=OFF \
    > /backup/mysql_for_mariadb.sql
Atau gunakan mydumper untuk speed (parallel)
mydumper -u root -p -t 4 -o /backup/mydumper_output

3. Setup MariaDB Server

Install MariaDB:

# Install MariaDB
sudo apt update
sudo apt install mariadb-server mariadb-client -y
Start service
sudo systemctl start mariadb
sudo systemctl enable mariadb
Secure installation
sudo mysql_secure_installation

Configure my.cnf:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
server-id = 2
log_bin = mariadb-bin
binlog_format = ROW
log_slave_updates = 1
read_only = 1
Compatibility dengan MySQL 8.0
innodb_strict_mode = ON
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Performance
innodb_buffer_pool_size = 4G

4. Restore Backup ke MariaDB

# Restore dump
mysql -u root -p < /backup/mysql_for_mariadb.sql
Atau dengan myloader (jika pakai mydumper)
myloader -u root -p -d /backup/mydumper_output

5. Setup Replication

Get master position dari backup:

grep "CHANGE MASTER" /backup/mysql_for_mariadb.sql
# atau
grep "MASTER_LOG_FILE" /backup/mysql_for_mariadb.sql

Configure replication:

-- Di MariaDB
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO
MASTER_HOST = 'mysql-master-ip',
MASTER_PORT = 3306,
MASTER_USER = 'repl_mariadb',
MASTER_PASSWORD = 'ReplPass123!',
MASTER_LOG_FILE = 'mysql-bin.000001',  -- dari backup
MASTER_LOG_POS = 1234,  -- dari backup
MASTER_CONNECT_RETRY = 10,
MASTER_RETRY_COUNT = 86400,
MASTER_HEARTBEAT_PERIOD = 5;
START SLAVE;
-- Monitor replication
SHOW SLAVE STATUS\G

6. Verify Data Consistency

# Install percona-toolkit
sudo apt install percona-toolkit
Check data consistency
pt-table-checksum \
--user=root \
--password \
--host=mysql-master \
--replicate=percona.checksums \
--databases=mydb \
--chunk-size=1000
Check results
mysql -u root -p -e "SELECT * FROM percona.checksums WHERE this_crc != master_crc;"

7. Cutover Plan

Before cutover:

-- Set application ke read-only mode (jika possible)
-- Atau reduce write traffic

Cutover steps:

-- 1. Stop writes ke MySQL
-- 2. Wait untuk replication catch up
SHOW SLAVE STATUS\G
-- Pastikan: Seconds_Behind_Master = 0
-- 3. Stop replication
STOP SLAVE;
-- 4. Reset read-only
SET GLOBAL read_only = 0;
-- 5. Point application ke MariaDB

Post-Migration Tasks

1. Verify Application Functionality

-- Test critical queries
EXPLAIN SELECT * FROM critical_table WHERE indexed_col = 'value';
-- Test transactions
START TRANSACTION;
INSERT INTO test_table VALUES (...);
UPDATE another_table SET ...;
COMMIT;
-- Test stored procedures
CALL critical_procedure();

2. Performance Tuning

-- Analyze tables
ANALYZE TABLE table1, table2, table3;
-- Optimize queries yang berubah performanya
EXPLAIN ANALYZE SELECT ... FROM ...;

3. Monitoring Setup

-- Enable Performance Schema
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
-- Setup slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Handling Incompatibilities

1. JSON Columns

MySQL:

CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON,
    INDEX idx_price ((attributes->>'$.price'))
);

MariaDB equivalent:

CREATE TABLE products (
    id INT PRIMARY KEY,
    attributes JSON CHECK (JSON_VALID(attributes)),
    INDEX idx_price ((JSON_VALUE(attributes, '$.price')))
);

2. Generated Columns

MySQL:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    total DECIMAL(10,2),
    tax_rate DECIMAL(4,2),
    tax_amount DECIMAL(10,2) AS (total * tax_rate) STORED
);

MariaDB:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    total DECIMAL(10,2),
    tax_rate DECIMAL(4,2),
    tax_amount DECIMAL(10,2) AS (total * tax_rate) PERSISTENT
);

3. Authentication Plugins

-- Convert users dari caching_sha2_password ke mysql_native_password
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'same_password';
-- Atau setup plugin di MariaDB
INSTALL SONAME 'auth_sha256_password';

Rollback Plan

Jika terjadi issues:

-- 1. Enable read-only di MariaDB
SET GLOBAL read_only = 1;
-- 2. Re-enable MySQL master
-- Un-comment replication setup di MySQL
-- 3. Point application back ke MySQL
-- 4. Continue investigation di MariaDB

Automated Migration Script

#!/bin/bash
# mysql_to_mariadb_migration.sh
MYSQL_HOST="mysql-master"
MYSQL_USER="root"
MYSQL_PASS="password"
MARIADB_HOST="mariadb-slave"
MARIADB_USER="root"
MARIADB_PASS="password"
echo "=== Starting MySQL to MariaDB Migration ==="
1. Check compatibility
echo "Checking compatibility..."
mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASS -e "
SELECT 'MySQL Version: ' || VERSION();
SHOW VARIABLES LIKE 'binlog_format';
"
2. Create backup
echo "Creating backup..."
mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL
PASS \
--single-transaction \
--master-data=2 \
--all-databases > /backup/migration$(date +%Y%m%d).sql
3. Setup replication
echo "Setting up replication..."
... (replication setup commands)
echo "=== Migration Setup Complete ==="
echo "Monitor replication with: SHOW SLAVE STATUS\G"

Best Practices

1. Test di staging dulu: Complete migration test dengan production-like data
2. Plan for rollback: Always have way back
3. Verify data integrity: pt-table-checksum adalah must
4. Monitor performance: Queries bisa berbeda execution plans
5. Document everything: Untuk future reference
6. Communication: Inform semua stakeholders tentang migration

Kesimpulan

Migrasi MySQL ke MariaDB adalah proses yang manageable dengan planning yang tepat:
– Online migration dengan replication memungkinkan zero downtime
– Compatibility issues bisa di-handle dengan preparation
– Data integrity verification adalah critical
– Rollback plan harus siap

Dengan approach ini, Anda bisa migrate dengan confidence.