Upgrade MySQL adalah proses yang critical dan perlu planning matang. Setelah melakukan puluhan upgrade untuk production systems, saya akan berbagi metode yang aman dan terbukti.

Pre-Upgrade Checklist

1. Compatibility Check

-- Check current version
SELECT VERSION();
-- Check for deprecated features
SELECT * FROM performance_schema.user_defined_functions;
-- Check for utf8mb4 issues
SELECT 
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CHARACTER_SET_NAME,
COLLATION_NAME
FROM information_schema.COLUMNS
WHERE DATA_TYPE = 'varchar'
AND CHARACTER_SET_NAME = 'utf8';

2. Backup Database

# Full backup sebelum upgrade
mysqldump --all-databases --routines --triggers --events > full_backup.sql
Backup specific databases
mysqldump --databases db1 db2 > dbs_backup.sql
Atau gunakan mydumper untuk parallel backup
mydumper -t 4 -o /backup/pre_upgrade/

3. Check Requirements

• MySQL 5.7 harus versi terbaru (5.7.9 atau lebih baru)
• Sufficient disk space (minimal 2x data size)
• Compatible OS
• Test di staging environment dulu

Upgrade Method 1: In-Place Upgrade (Recommended)

1. Prepare Server

# Stop application writes
# Enable read-only mode
mysql -u root -p -e "SET GLOBAL read_only = ON;"
Flush tables
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"

2. Upgrade Process

# Stop MySQL 5.7
sudo systemctl stop mysql
Backup data directory
sudo cp -r /var/lib/mysql /var/lib/mysql_backup_5.7
Install MySQL 8.0
sudo apt update
wget 
https://dev.mysql.com/get/mysql-apt-config_0.8.25-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.25-1_all.deb
sudo apt update
sudo apt install mysql-server-8.0
Start MySQL 8.0 (akan otomatis upgrade data files)
sudo systemctl start mysql
Monitor error log
tail -f /var/log/mysql/error.log

3. Post-Upgrade Tasks

-- Check version
SELECT VERSION();  -- Should show 8.0.x
-- Run mysql_upgrade
sudo mysql_upgrade -u root -p
-- Restart
sudo systemctl restart mysql
-- Verify tables
CHECK TABLE db1.table1;
-- Update statistics
ANALYZE TABLE db1.table1;

Upgrade Method 2: Logical Upgrade (Dump dan Restore)

1. Export dari 5.7

# Export dengan compatible format
mysqldump --compatible=mysql40 --default-character-set=utf8mb4 \
    --databases db1 db2 > mysql57_backup.sql
Atau dengan mydumper
mydumper -t 4 -o /backup/mysql57/

2. Install MySQL 8.0 Fresh

# Uninstall MySQL 5.7
sudo apt remove mysql-server mysql-client -y
sudo apt autoremove -y
Install MySQL 8.0
sudo apt install mysql-server-8.0 mysql-client-8.0
Secure installation
sudo mysql_secure_installation

3. Import ke 8.0

# Restore database
mysql < mysql57_backup.sql
Atau dengan myloader
myloader -t 4 -d /backup/mysql57/

Post-Upgrade Verification

1. Data Integrity Check

-- Check row counts
SELECT COUNT(*) FROM db1.table1;
-- Bandingkan dengan 5.7
-- Check sample data
SELECT * FROM db1.table1 LIMIT 10;
-- Verify critical data
SELECT SUM(amount) FROM db1.orders;

2. Performance Testing

-- Check query plans
EXPLAIN SELECT * FROM db1.table1 WHERE indexed_col = 'value';
-- Test slow queries
SELECT BENCHMARK(1000000, (SELECT * FROM db1.table1 WHERE id = 1));

3. Feature Testing

-- Test CTEs (new in 8.0)
WITH cte AS (SELECT * FROM db1.table1) SELECT * FROM cte;
-- Test Window Functions
SELECT *, ROW_NUMBER() OVER (ORDER BY id) FROM db1.table1;
-- Test JSON functions
SELECT JSON_OBJECT('key', 'value');

Handling Issues

1. Authentication Plugin Change

MySQL 8.0 default menggunakan caching_sha2_password.

-- Convert users ke mysql_native_password jika perlu
ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
-- Atau update application untuk support caching_sha2_password

2. SQL Mode Changes

-- Check current SQL mode
SELECT @@sql_mode;
-- Update jika perlu
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. Charset Conversion

-- Convert utf8 ke utf8mb4
ALTER DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE db1.table1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Rollback Plan

Jika Upgrade Gagal:

# Stop MySQL 8.0
sudo systemctl stop mysql
Restore backup data
sudo rm -rf /var/lib/mysql/

sudo cp -r /var/lib/mysql_backup_5.7/ /var/lib/mysql/
Reinstall MySQL 5.7
sudo apt remove mysql-server-8.0 -y
sudo apt install mysql-server-5.7
Start 5.7
sudo systemctl start mysql

Best Practices

1. Always backup: Sebelum dan setelah upgrade
2. Test di staging: Selalu test dulu
3. Read-only mode: Selama upgrade process
4. Monitor logs: Check error log untuk issues
5. Verify data: Compare row counts dan checksums
6. Update applications: Support new features dan auth methods

Kesimpulan

Upgrade MySQL 5.7 ke 8.0 memberikan:
– Better performance
– New features (CTEs, Window Functions, JSON)
– Improved security
– Long-term support

Dengan preparation yang tepat, upgrade bisa smooth dan aman.