# MySQL Migration Guide

This guide provides step-by-step instructions for migrating the MyGB School Management System from SQLite to MySQL database.

## Table of Contents

1. [Prerequisites](#prerequisites)
2. [Pre-Migration Checklist](#pre-migration-checklist)
3. [Migration Steps](#migration-steps)
4. [Post-Migration Verification](#post-migration-verification)
5. [Troubleshooting](#troubleshooting)
6. [Rollback Instructions](#rollback-instructions)

## Prerequisites

### System Requirements

- **PHP Version**: 7.4 or higher
- **PHP Extensions Required**:
  - `mysqli` - MySQL Improved extension
  - `pdo_sqlite` - SQLite PDO driver (for reading source database)
  - `pdo_mysql` - MySQL PDO driver (optional, for compatibility)
- **MySQL Server**: 5.7 or higher (MySQL 8.0 recommended)
- **Disk Space**: At least 2x the size of your current SQLite database
- **Command Line Access**: Required to run the migration script

### Verify PHP Extensions

Run this command to check if required extensions are installed:

```bash
php -m | findstr -i "mysqli pdo_sqlite"
```

You should see:
```
mysqli
pdo_sqlite
PDO
```

If any extensions are missing, enable them in your `php.ini` file.

### MySQL Server Setup

1. **Start MySQL Server**
   - If using XAMPP: Open XAMPP Control Panel and start MySQL
   - Verify MySQL is running on port 3306 (default)

2. **Create MySQL User** (if needed)
   ```sql
   CREATE USER 'mygb_user'@'localhost' IDENTIFIED BY 'your_password';
   GRANT ALL PRIVILEGES ON mygb_school.* TO 'mygb_user'@'localhost';
   FLUSH PRIVILEGES;
   ```

3. **Test MySQL Connection**
   - Open phpMyAdmin or MySQL Workbench
   - Try connecting with your credentials

## Pre-Migration Checklist

Before starting the migration, complete these steps:

### 1. Backup Your Data

**CRITICAL**: Always backup your data before migration!

```bash
# Manual backup (recommended)
copy data\school.db data\school_backup_manual.db

# Or use the backup script
php backup.php
```

### 2. Configure Database Settings

1. Copy the configuration template:
   ```bash
   copy config\database.php.example config\database.php
   ```

2. Edit `config/database.php` with your MySQL credentials:
   ```php
   <?php
   // Database Configuration
   define('DB_TYPE', 'sqlite'); // Keep as 'sqlite' for now
   
   // MySQL Configuration
   define('DB_HOST', 'localhost');
   define('DB_NAME', 'mygb_school');
   define('DB_USER', 'root');  // Change if using different user
   define('DB_PASS', '');      // Add password if required
   define('DB_CHARSET', 'utf8mb4');
   define('DB_PORT', 3306);
   
   // SQLite Configuration (current database)
   define('SQLITE_DB_PATH', __DIR__ . '/../data/school.db');
   ?>
   ```

3. **Important**: Keep `DB_TYPE` as `'sqlite'` until migration is complete and verified!

### 3. Test MySQL Connection

Run a quick connection test:

```bash
php -r "new mysqli('localhost', 'root', '', '', 3306) or die('Cannot connect to MySQL');"
```

If successful, you'll see no output. If it fails, you'll see an error message.

### 4. Check Disk Space

Ensure you have enough disk space:

```bash
# Check SQLite database size
dir data\school.db

# Ensure you have at least 2x this size available
```

## Migration Steps

### Step 1: Run Dry Run (Recommended)

First, run the migration in dry-run mode to check for potential issues:

```bash
php migrate_to_mysql.php --dry-run --verbose
```

This will:
- ✓ Verify all prerequisites
- ✓ Show what would be migrated
- ✓ Identify potential issues
- ✗ NOT make any changes to MySQL

Review the output carefully. If you see any errors, fix them before proceeding.

### Step 2: Run Actual Migration

Once the dry run completes successfully, run the actual migration:

```bash
php migrate_to_mysql.php --verbose
```

**What happens during migration:**

1. **Pre-flight Checks**
   - Verifies configuration file exists
   - Tests MySQL connection
   - Checks SQLite database exists
   - Validates credentials

2. **Backup Creation**
   - Creates timestamped backup of SQLite database
   - Stores in `backups/database/` directory
   - Verifies backup integrity

3. **Database Creation**
   - Creates MySQL database with utf8mb4 charset
   - Sets proper collation (utf8mb4_unicode_ci)

4. **Schema Migration**
   - Reads SQLite table structures
   - Converts to MySQL-compatible schema
   - Creates tables with proper data types
   - Creates indexes

5. **Data Migration**
   - Migrates data table by table
   - Uses batch inserts (100 records per batch)
   - Shows progress for each table
   - Uses transactions for safety

6. **Verification**
   - Compares record counts
   - Verifies sample records
   - Checks data integrity

7. **Report Generation**
   - Creates detailed migration report
   - Logs all operations
   - Provides next steps

### Step 3: Review Migration Report

After migration completes, review the report:

```
Migration Report: migration_report_YYYY-MM-DD_HH-MM-SS.txt
Log File: migration_log_YYYY-MM-DD_HH-MM-SS.txt
```

Check for:
- ✓ All tables migrated successfully
- ✓ Record counts match
- ✓ No errors reported
- ⚠ Review any warnings

### Step 4: Update Configuration

If migration was successful, update your configuration:

Edit `config/database.php`:

```php
define('DB_TYPE', 'mysql'); // Change from 'sqlite' to 'mysql'
```

### Step 5: Test Application

Test all major features:

1. **Admin Panel**
   - Login: `http://localhost/mygb/admin/`
   - Verify dashboard loads
   - Check student list
   - Try adding/editing a student

2. **Public Pages**
   - Homepage: `http://localhost/mygb/public/`
   - Check all pages load correctly
   - Verify images display

3. **Database Operations**
   - Create a test student
   - Update student information
   - Delete test student
   - Upload a file

## Post-Migration Verification

### Verification Checklist

- [ ] Admin login works
- [ ] Student list displays correctly
- [ ] Can add new students
- [ ] Can edit existing students
- [ ] Can delete students
- [ ] File uploads work
- [ ] Settings page works
- [ ] Public pages display correctly
- [ ] No PHP errors in logs
- [ ] Database queries execute properly

### Performance Check

Compare performance before and after:

```sql
-- Run in MySQL
EXPLAIN SELECT * FROM students WHERE grade = '10A';

-- Check query execution time
SELECT COUNT(*) FROM students;
```

MySQL should be faster for:
- Complex queries with JOINs
- Concurrent user access
- Large datasets (1000+ records)

### Monitor for Issues

Monitor for 24-48 hours after migration:

1. **Check Error Logs**
   ```bash
   # PHP error log
   type C:\xamp8\apache\logs\error.log | findstr /i "mysql"
   ```

2. **Check Application Logs**
   - Review `migration_log_*.txt` files
   - Check for any database errors

3. **User Feedback**
   - Ask users to report any issues
   - Test all features thoroughly

## Troubleshooting

### Common Issues and Solutions

#### Issue 1: "Cannot connect to MySQL server"

**Symptoms:**
```
❌ Cannot connect to MySQL server!
Error: Connection refused
```

**Solutions:**
1. Verify MySQL is running:
   - Open XAMPP Control Panel
   - Check if MySQL shows "Running"
   - Click "Start" if it's stopped

2. Check MySQL port:
   ```bash
   netstat -an | findstr :3306
   ```
   Should show: `TCP 0.0.0.0:3306 LISTENING`

3. Verify credentials in `config/database.php`

4. Try connecting with phpMyAdmin

#### Issue 2: "Access denied for user"

**Symptoms:**
```
❌ Cannot connect to MySQL server!
Error: Access denied for user 'root'@'localhost'
```

**Solutions:**
1. Check username and password in `config/database.php`

2. Reset MySQL root password:
   - Stop MySQL in XAMPP
   - Start MySQL with `--skip-grant-tables`
   - Reset password
   - Restart MySQL normally

3. Create a new MySQL user with proper permissions

#### Issue 3: "Record count mismatch"

**Symptoms:**
```
❌ Record count mismatch!
SQLite: 150 records
MySQL: 148 records
```

**Solutions:**
1. Check migration log for errors during data migration

2. Re-run migration:
   ```bash
   php migrate_to_mysql.php --force
   ```

3. Manually verify missing records:
   ```sql
   -- In SQLite
   SELECT COUNT(*) FROM students;
   
   -- In MySQL
   SELECT COUNT(*) FROM students;
   ```

4. If issue persists, restore from backup and retry

#### Issue 4: "Failed to create table"

**Symptoms:**
```
❌ Failed to create table students
Error: Syntax error near 'AUTOINCREMENT'
```

**Solutions:**
1. This indicates schema conversion issue

2. Check MySQL version:
   ```sql
   SELECT VERSION();
   ```
   Ensure it's 5.7 or higher

3. Review the converted schema in verbose mode:
   ```bash
   php migrate_to_mysql.php --dry-run --verbose
   ```

4. Report the issue with the specific table schema

#### Issue 5: "PHP extension not loaded"

**Symptoms:**
```
Fatal error: Call to undefined function mysqli_connect()
```

**Solutions:**
1. Enable mysqli extension in `php.ini`:
   ```ini
   extension=mysqli
   ```

2. Restart Apache

3. Verify extension is loaded:
   ```bash
   php -m | findstr mysqli
   ```

### Getting Help

If you encounter issues not covered here:

1. **Check Log Files**
   - `migration_log_*.txt` - Detailed migration log
   - `migration_report_*.txt` - Summary report
   - Apache error log

2. **Review Error Messages**
   - Note the exact error message
   - Check which step failed
   - Look for related errors in logs

3. **Gather Information**
   - PHP version: `php -v`
   - MySQL version: `mysql --version`
   - Enabled extensions: `php -m`
   - Database size: `dir data\school.db`

## Rollback Instructions

If you need to rollback to SQLite after migration:

### Immediate Rollback (Before Changing DB_TYPE)

If you haven't changed `DB_TYPE` yet, simply don't change it. Your application will continue using SQLite.

### Rollback After Switching to MySQL

If you've already changed `DB_TYPE` to 'mysql' and want to go back:

1. **Update Configuration**
   
   Edit `config/database.php`:
   ```php
   define('DB_TYPE', 'sqlite'); // Change back from 'mysql'
   ```

2. **Verify SQLite Database**
   
   Check that your SQLite database is intact:
   ```bash
   dir data\school.db
   ```

3. **Test Application**
   
   Access the application and verify it works with SQLite.

### Restore from Backup

If your SQLite database was corrupted or modified:

1. **Locate Backup**
   
   Backups are in `backups/database/`:
   ```bash
   dir backups\database\school_backup_*.db
   ```

2. **Restore Backup**
   
   ```bash
   copy backups\database\school_backup_YYYY-MM-DD_HH-MM-SS.db data\school.db
   ```

3. **Verify Restoration**
   
   ```bash
   php -r "$db = new PDO('sqlite:data/school.db'); echo 'OK';"
   ```

4. **Test Application**
   
   Access the application and verify all data is present.

### Clean Up MySQL Database (Optional)

If you want to remove the MySQL database after rollback:

```sql
DROP DATABASE IF EXISTS mygb_school;
```

**Note**: Only do this if you're certain you won't need the MySQL data.

## Best Practices

### Before Migration

1. ✓ **Backup Everything**
   - SQLite database
   - Uploaded files in `data/uploads/`
   - Configuration files

2. ✓ **Test in Development First**
   - Never migrate production directly
   - Test on a copy of your data
   - Verify all features work

3. ✓ **Schedule Maintenance Window**
   - Inform users of downtime
   - Choose low-traffic time
   - Allow 1-2 hours for migration

### During Migration

1. ✓ **Monitor Progress**
   - Watch for errors
   - Check log files
   - Don't interrupt the process

2. ✓ **Keep Backups Safe**
   - Don't delete SQLite database
   - Keep backups for 30 days
   - Store backups off-server

### After Migration

1. ✓ **Thorough Testing**
   - Test all features
   - Check data integrity
   - Monitor performance

2. ✓ **Monitor for 48 Hours**
   - Watch error logs
   - Check user reports
   - Be ready to rollback

3. ✓ **Keep SQLite Backup**
   - Don't delete for 30 days
   - Verify MySQL stability first
   - Document any issues

## Performance Optimization

After successful migration, optimize MySQL performance:

### 1. Add Indexes

```sql
-- Add indexes for frequently queried columns
CREATE INDEX idx_student_grade ON students(grade);
CREATE INDEX idx_student_name ON students(name);
CREATE INDEX idx_berita_date ON berita(date);
```

### 2. Optimize Tables

```sql
-- Run after migration
OPTIMIZE TABLE students;
OPTIMIZE TABLE admins;
OPTIMIZE TABLE settings;
```

### 3. Configure MySQL

Edit MySQL configuration (`my.ini` or `my.cnf`):

```ini
[mysqld]
# Increase buffer pool size (adjust based on available RAM)
innodb_buffer_pool_size = 256M

# Optimize for many connections
max_connections = 100

# Enable query cache (MySQL 5.7)
query_cache_type = 1
query_cache_size = 32M
```

Restart MySQL after configuration changes.

## Conclusion

Congratulations on successfully migrating to MySQL! Your application now benefits from:

- ✓ Better concurrent user support
- ✓ Improved performance for complex queries
- ✓ Production-ready database system
- ✓ Better scalability
- ✓ Advanced features (stored procedures, triggers, etc.)

For questions or issues, refer to:
- Migration log files
- This guide's troubleshooting section
- MySQL documentation: https://dev.mysql.com/doc/

---

**Document Version**: 1.0  
**Last Updated**: 2024  
**For**: MyGB School Management System
