-- Check single table
CHECK TABLE users;

-- Check multiple tables
CHECK TABLE orders, order_items, products;

-- Check all tables dalam database
CHECK TABLE db1.users, db1.orders, db1.products;

-- Quick check (tidak scan rows untuk errors)
CHECK TABLE users QUICK;

-- Fast check (hanya check incorrectly closed tables)
CHECK TABLE users FAST;

-- Medium check (check dan verify deleted links)
CHECK TABLE users MEDIUM;

-- Extended check (full row scan untuk errors)
CHECK TABLE users EXTENDED;

-- Changed check (hanya check sejak last check)
CHECK TABLE users CHANGED;

CHECK TABLE users;

-- Possible statuses:
-- OK: Table is fine
-- Table is already up to date: No check needed
-- Corrupt: Table needs repair

-- Optimize single table
OPTIMIZE TABLE orders;

-- Optimize multiple tables
OPTIMIZE TABLE orders, order_items, inventory;

-- Optimize dengan ONLINE (MySQL 5.7+ InnoDB)
OPTIMIZE TABLE orders;
-- Secara otomatis menggunakan online DDL untuk InnoDB

-- Check process list selama optimize
SHOW PROCESSLIST;

-- Look untuk: State = 'optimizing'

-- Basic repair
REPAIR TABLE corrupted_table;

-- Quick repair (hanya fix index file)
REPAIR TABLE corrupted_table QUICK;

-- Extended repair (full row-by-row repair)
REPAIR TABLE corrupted_table EXTENDED;

-- Use FRM file untuk repair
REPAIR TABLE corrupted_table USE_FRM;

-- Option 1: Restore dari backup
-- Option 2: Dump dan recreate

-- Export data
mysqldump --no-create-info db_name corrupted_table > table_backup.sql

-- Drop dan recreate
DROP TABLE corrupted_table;
-- Recreate dengan CREATE TABLE

-- Import
mysql db_name < table_backup.sql

#!/bin/bash
# weekly_check.sh

DB_NAME="production"
LOG_FILE="/var/log/mysql_maintenance.log"
EMAIL="dba@example.com"

echo "[$(date)] Starting weekly table check..." >> $LOG_FILE

# Check all tables
mysql -e "
SELECT CONCAT('CHECK TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '${DB_NAME}'
AND ENGINE IN ('InnoDB', 'MyISAM', 'Aria');
" | while read table; do
    result=$(mysql -e "$table" 2>&1)
    if echo "$result" | grep -q "Corrupt"; then
        echo "ALERT: $table is corrupt" >> $LOG_FILE
        echo "$result" | mail -s "[CRITICAL] Table Corruption Detected" $EMAIL
    fi
done

echo "[$(date)] Weekly check completed" >> $LOG_FILE

#!/bin/bash
# monthly_optimize.sh

DB_NAME="production"
LOG_FILE="/var/log/mysql_optimization.log"

# Get tables larger than 100MB yang belum dioptimize dalam 30 hari
tables=$(mysql -e "
SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME)
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '${DB_NAME}'
AND DATA_LENGTH > 104857600  -- > 100MB
AND ENGINE = 'InnoDB'
ORDER BY DATA_LENGTH DESC;
")

for table in $tables; do
    echo "[$(date)] Optimizing $table..." >> $LOG_FILE
    mysql -e "OPTIMIZE TABLE $table;" >> $LOG_FILE 2>&1
    echo "[$(date)] Optimization of $table completed" >> $LOG_FILE
done

-- 1. Backup
mysqldump --single-transaction db_name > backup.sql

-- 2. Check current status
CHECK TABLE table_name;
SHOW TABLE STATUS LIKE 'table_name';

-- 3. Monitor during
SHOW PROCESSLIST;

-- 1. Verify
CHECK TABLE table_name;

-- 2. Update statistics
ANALYZE TABLE table_name;

-- 3. Verify performance
EXPLAIN SELECT * FROM table_name WHERE ...;

-- Check table statistics
SHOW TABLE STATUS FROM db_name;

-- Detailed check
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100 AS fragmentation_pct,
    CREATE_TIME,
    UPDATE_TIME,
    CHECK_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
ORDER BY fragmentation_pct DESC;

-- Tables dengan high fragmentation
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    ENGINE,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb,
    ROUND(DATA_FREE / DATA_LENGTH * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE DATA_FREE > 10485760  -- > 10MB free space
  AND DATA_LENGTH > 0
  AND ENGINE = 'InnoDB'
ORDER BY frag_pct DESC;