-- 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;

-- 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');

# 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

-- 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;

# 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

# 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

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

# 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

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

-- 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

# 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;"

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

-- 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

-- 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();

-- Analyze tables
ANALYZE TABLE table1, table2, table3;

-- Optimize queries yang berubah performanya
EXPLAIN ANALYZE SELECT ... FROM ...;

-- 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;

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

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

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
);

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
);

-- 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';

-- 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

#!/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"