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

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

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

# 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

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

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

# 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

# Restore database
mysql < mysql57_backup.sql

# Atau dengan myloader
myloader -t 4 -d /backup/mysql57/

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

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

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

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

-- Check current SQL mode
SELECT @@sql_mode;

-- Update jika perlu
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

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

# 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