# Complete Guide to Migrating MySQL Database in XAMPP

This guide provides comprehensive instructions for migrating a MySQL database between XAMPP installations, covering export, import, configuration updates, verification, and troubleshooting.

---

## Table of Contents

1. [Pre-Migration Backup](#1-pre-migration-backup)
2. [Exporting the Database](#2-exporting-the-database)
3. [Creating the New Database](#3-creating-the-new-database)
4. [Importing the Data](#4-importing-the-data)
5. [Updating Configuration Files](#5-updating-configuration-files)
6. [Verifying Data Integrity](#6-verifying-data-integrity)
7. [Testing the Migrated Database](#7-testing-the-migrated-database)
8. [Troubleshooting Common Issues](#8-troubleshooting-common-issues)

---

## 1. Pre-Migration Backup

### Why Backup is Essential

Before any migration, creating a complete backup ensures you can revert to the original state if anything goes wrong.

### Creating a Complete Backup

#### Method A: Using phpMyAdmin

1. Open your browser and navigate to `http://localhost/phpmyadmin`
2. Select the database you want to migrate from the left panel
3. Click the **Export** tab in the top navigation
4. Choose **Quick** export method for simple databases
5. Select **SQL** as the format
6. Click **Go** to download the `.sql` file

#### Method B: Using Command Line

```bash
# Navigate to XAMPP MySQL bin directory
cd C:\xampp\mysql\bin

# Export database to SQL file
mysqldump -u root -p your_database_name > backup_database.sql

# For larger databases, compress the output
mysqldump -u root -p your_database_name | gzip > backup_database.sql.gz
```

#### Method C: Backup All Databases

```bash
# Backup all databases at once
mysqldump -u root -p --all-databases > all_databases_backup.sql
```

### Verify the Backup

```bash
# Check file size
dir backup_database.sql

# Verify SQL file is valid (check first few lines)
type backup_database.sql | findstr /C:"CREATE TABLE" /C:"INSERT INTO"
```

---

## 2. Exporting the Database

### Option A: Export via phpMyAdmin

#### Step-by-Step Instructions

1. **Access phpMyAdmin**: Open `http://localhost/phpmyadmin`

2. **Select Database**: Click on the database name in the left sidebar

3. **Navigate to Export**: Click the **Export** tab

4. **Choose Export Method**:
   - **Quick**: Best for smaller databases (< 50MB)
   - **Custom**: Provides more options for larger databases

5. **Select Format**: Choose **SQL** (default)

6. **Configure Options** (Custom Method):
   - Check **Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER**
   - Check **Add CREATE DATABASE / PROCEDURE**
   - Select appropriate **Character Set** (usually utf8mb4)
   - Set **Maximal length of created query** to 50000 or higher

7. **Download**: Click **Go** to download the `.sql` file

#### Export Options Explained

| Option | Purpose | Recommended Setting |
|--------|---------|---------------------|
| DROP TABLE | Removes existing tables before creating | Checked (safe for fresh import) |
| CREATE DATABASE | Includes database creation syntax | Checked |
| Extended Inserts | Combines multiple INSERT statements | Checked (faster import) |
| Delayed Inserts | Uses INSERT DELAYED | Unchecked (default) |
| Foreign Key Checks | Disables during import | Checked |

### Option B: Export via Command Line

```bash
# Basic export
mysqldump -u root -p database_name > export_file.sql

# Export with drop table statements
mysqldump -u root -p --add-drop-table database_name > export_file.sql

# Export specific tables
mysqldump -u root -p database_name table1 table2 table3 > specific_tables.sql

# Export without data (structure only)
mysqldump -u root -p --no-data database_name > structure_only.sql

# Export with complete options
mysqldump -u root -p \
  --add-drop-table \
  --add-drop-database \
  --create-options \
  --extended-insert \
  --lock-tables \
  --database your_database > complete_export.sql
```

### Export Large Databases

For databases larger than 50MB:

```bash
# Split export into smaller chunks
mysqldump -u root -p database_name | split -b 50m - database_part_

# Compressed export
mysqldump -u root -p database_name | gzip > database.sql.gz

# Export with progress (using pv if available)
mysqldump -u root -p database_name | pv -p -t -e -b > database.sql
```

---

## 3. Creating the New Database

### Option A: Create via phpMyAdmin

1. **Access phpMyAdmin**: Navigate to `http://localhost/phpmyadmin`

2. **Create New Database**:
   - Click **New** in the left sidebar
   - Enter database name
   - Select collation (usually `utf8mb4_general_ci` or `utf8mb4_unicode_ci`)
   - Click **Create**

3. **Verify Creation**: The new database will appear in the left panel

### Option B: Create via Command Line

```bash
# Navigate to MySQL bin
cd C:\xampp\mysql\bin

# Login to MySQL
mysql -u root -p

# Create database with specific charset
CREATE DATABASE new_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# Show databases to confirm
SHOW DATABASES;

# Select the database
USE new_database_name;

# Exit MySQL
EXIT;
```

### Database Collation Recommendations

| Character Set | Collation | Use Case |
|---------------|-----------|----------|
4 | utf8| utf8mbmb4_unicode_ci | Recommended for most applications |
| utf8mb4 | utf8mb4_general_ci | Faster, slightly less accurate |
| latin1 | latin1_swedish_ci | Legacy applications |
| utf8 | utf8_general_ci | Older applications |

### Create Database with Options

```sql
-- Create database with full options
CREATE DATABASE new_database_name
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  ENGINE=InnoDB;

-- Grant privileges to user (if not using root)
GRANT ALL PRIVILEGES ON new_database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
```

---

## 4. Importing the Data

### Option A: Import via phpMyAdmin

1. **Select Database**: Click on the newly created database in the left sidebar

2. **Navigate to Import**: Click the **Import** tab

3. **Configure Import**:
   - Click **Choose File** and select your `.sql` file
   - Check **Partial import** if applicable
   - Select **SQL** as format
   - Configure **Character set** (usually utf8mb4)

4. **Set Import Options**:
   - **Number of rows to skip**: 0 (usually)
   - **Format-specific options**: Leave as default

5. **Execute Import**: Click **Go** at the bottom

6. **Wait for Completion**: Large imports may take several minutes

### Option B: Import via Command Line

```bash
# Navigate to MySQL bin
cd C:\xampp\mysql\bin

# Login and import
mysql -u root -p new_database_name < import_file.sql

# Import with specific charset
mysql -u root -p --default-character-set=utf8mb4 new_database_name < import_file.sql

# Import compressed file directly
gunzip < import_file.sql.gz | mysql -u root -p new_database_name
```

### Import Large Files

For files larger than 50MB:

```bash
# Increase MySQL max_allowed_packet
# Edit my.ini and set: max_allowed_packet=256M

# Import in chunks using source command
mysql -u root -p
USE new_database_name;
SOURCE large_file_part1.sql;
SOURCE large_file_part2.sql;
```

### Import Options Explained

| Parameter | Description |
|-----------|-------------|
| --force | Continue even if SQL error occurs |
| --ignore | Ignore duplicate key errors |
| --single-transaction | Use transaction for InnoDB tables |
| --quick | Don't buffer entire file in memory |

---

## 5. Updating Configuration Files

### Common Configuration Files to Update

#### PHP Configuration

Locate and update database connection in your PHP files:

**config/database.php** (Common location):
```php
<?php
// Database Configuration
define('DB_HOST', 'localhost');        // Usually localhost
define('DB_NAME', 'new_database_name'); // CHANGE THIS
define('DB_USER', 'root');             // Your MySQL username
define('DB_PASS', '');                 // Your MySQL password

// Connection attempt
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Set charset
mysqli_set_charset($conn, "utf8mb4");
?>
```

#### Application Config Files

Common locations:
- `/config/database.php`
- `/application/config/database.php`
- `/includes/db.php`
- `/wp-config.php` (WordPress)
- `/laravel/.env` (Laravel)

#### XAMPP MySQL Configuration

If needed, edit `C:\xampp\mysql\bin\my.ini`:

```ini
[mysqld]
# Increase for large databases
max_allowed_packet=256M
max_connections=200

# Character set
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
```

### Update Connection Strings

#### MySQLi Connection
```php
$host = 'localhost';
$db   = 'new_database_name';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

$pdo = new PDO($dsn, $user, $pass, $options);
```

#### PDO Connection
```php
$conn = new mysqli("localhost", "root", "", "new_database_name");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$conn->set_charset("utf8mb4");
```

---

## 6. Verifying Data Integrity

### Check Table Count

```sql
-- Count total tables
SELECT COUNT(*) AS total_tables 
FROM information_schema.tables 
WHERE table_schema = 'new_database_name';

-- List all tables
SHOW TABLES;
```

### Verify Row Counts

```sql
-- Count rows in each table
SELECT 
    table_name,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'new_database_name'
ORDER BY table_rows DESC;
```

### Compare with Original

```sql
-- Compare key tables
-- Run on both source and destination
SELECT COUNT(*) FROM your_table_name;
SELECT COUNT(*) FROM another_table;

-- Check for specific data
SELECT * FROM your_table_name LIMIT 10;
```

### Verify Foreign Keys

```sql
-- Check foreign key constraints
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'new_database_name';
```

### Check Data Integrity

```sql
-- Check for NULL values in critical columns
SELECT 'Table has NULL values' AS check_result
FROM your_table
WHERE critical_column IS NULL
LIMIT 1;

-- Verify unique constraints
SELECT column_name, COUNT(*) as count
FROM your_table
GROUP BY column_name
HAVING count > 1;
```

---

## 7. Testing the Migrated Database

### Create Test Script

```php
<?php
// test_migration.php

$host = 'localhost';
$db   = 'new_database_name';
$user = 'root';
$pass = '';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    echo "✓ Database connection successful\n";
    
    // Test 1: Count tables
    $stmt = $pdo->query("SHOW TABLES");
    $tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    echo "✓ Found " . count($tables) . " tables\n";
    
    // Test 2: Check each table
    foreach ($tables as $table) {
        $stmt = $pdo->query("SELECT COUNT(*) FROM `$table`");
        $count = $stmt->fetchColumn();
        echo "  - $table: $count rows\n";
    }
    
    // Test 3: Check for errors
    $stmt = $pdo->query("SHOW ENGINE INNODB STATUS");
    echo "✓ InnoDB status OK\n";
    
    echo "\n✓ All tests passed!\n";
    
} catch (PDOException $e) {
    echo "✗ Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>
```

### Run Application Tests

1. **Basic Functionality Test**:
   - Access your application
   - Login if required
   - Navigate to key pages

2. **CRUD Operations Test**:
   - Create new records
   - Read existing data
   - Update records
   - Delete records

3. **Search Functionality**:
   - Test search with special characters
   - Test search with Unicode characters

### Performance Testing

```sql
-- Check query performance
EXPLAIN SELECT * FROM your_table WHERE column = 'value';

-- Check index usage
SHOW INDEX FROM your_table;

-- Analyze tables
ANALYZE TABLE your_table;
```

---

## 8. Troubleshooting Common Issues

### Issue 1: Character Encoding Problems

#### Symptoms
- Special characters display as � or garbled text
- Accented characters appear incorrectly
- Chinese/Japanese/Arabic text not displaying

#### Solutions

**Solution A: Convert charset during import**
```bash
# Import with explicit charset
mysql -u root -p --default-character-set=utf8mb4 new_database < file.sql
```

**Solution B: Convert existing data**
```sql
-- Convert to utf8mb4
ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert each table
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Convert specific columns
ALTER TABLE table_name MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4;
```

**Solution C: Fix PHP connection**
```php
// Force UTF-8 charset
mysqli_set_charset($conn, "utf8mb4");
// Or for PDO
$dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4";
```

### Issue 2: Large Database File Handling

#### Symptoms
- Import fails or times out
- "Packet size too large" error
- Memory limit exceeded

#### Solutions

**Solution A: Increase MySQL limits**

Edit `C:\xampp\mysql\my.ini`:
```ini
[mysqld]
max_allowed_packet=256M
net_buffer_length=100M
```

Edit `C:\xampp\php\php.ini`:
```ini
memory_limit=512M
upload_max_filesize=256M
post_max_size=256M
max_execution_time=300
```

**Solution B: Use command line import**
```bash
# Import from command line (no size limits)
mysql -u root -p new_database < large_file.sql
```

**Solution C: Split large file**
```bash
# Split SQL file
split -l 10000 large_file.sql part_

# Import each part
for file in part_*; do
    mysql -u root -p new_database < $file
done
```

### Issue 3: Permission Errors

#### Symptoms
- "Access denied for user"
- "Can't create database"
- "Table 'db.table' doesn't exist"

#### Solutions

**Solution A: Grant proper permissions**
```sql
-- Login as root
mysql -u root -p

-- Grant all permissions
GRANT ALL PRIVILEGES ON new_database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
```

**Solution B: Fix file permissions (Windows)**
```
1. Right-click the XAMPP folder
2. Properties → Security
3. Give Full Control to your user
```

**Solution C: Check MySQL user permissions**
```sql
-- Show current user permissions
SHOW GRANTS FOR 'username'@'localhost';
```

### Issue 4: Foreign Key Constraint Errors

#### Symptoms
- "Cannot add or update child row"
- "Foreign key constraint fails"
- "Reference table doesn't exist"

#### Solutions

**Solution A: Disable foreign checks during import**
```sql
SET FOREIGN_KEY_CHECKS=0;

-- Import your data here

SET FOREIGN_KEY_CHECKS=1;
```

**Solution B: Fix import order**
- Import parent tables first
- Import child tables after

**Solution C: Check existing constraints**
```sql
-- Find foreign key issues
SELECT 
    kcu.TABLE_NAME,
    kcu.COLUMN_NAME,
    kcu.CONSTRAINT_NAME,
    kcu.REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE kcu
WHERE kcu.TABLE_SCHEMA = 'database_name'
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL;
```

### Issue 5: Import Hangs or Freezes

#### Symptoms
- Import process never completes
- Progress bar stuck at certain percentage

#### Solutions

**Solution A: Use InnoDB instead of MyISAM**
```sql
-- Convert tables to InnoDB
ALTER TABLE table_name ENGINE=InnoDB;
```

**Solution B: Use single transaction**
```bash
mysqldump -u root -p --single-transaction database_name > export.sql
```

**Solution C: Increase timeout values**
```ini
[mysqld]
wait_timeout = 600
interactive_timeout = 600
```

---

## Quick Reference Checklist

### Before Migration
- [ ] Create full database backup
- [ ] Verify backup file is valid
- [ ] Document source database name and credentials
- [ ] Check available disk space on destination
- [ ] Note any special configurations

### During Migration
- [ ] Export database from source
- [ ] Create new database with proper charset
- [ ] Import data into new database
- [ ] Update configuration files
- [ ] Test database connection

### After Migration
- [ ] Verify table count matches
- [ ] Check row counts for key tables
- [ ] Test application functionality
- [ ] Verify special characters display correctly
- [ ] Check foreign key relationships
- [ ] Test search functionality

---

## Additional Resources

### Useful MySQL Commands

```bash
# Check MySQL version
mysql -u root -p -e "SELECT VERSION();"

# List all databases
mysql -u root -p -e "SHOW DATABASES;"

# Check table sizes
mysql -u root -p -e "SELECT table_name, ROUND(data_length/1024/1024, 2) 'Size (MB)' FROM information_schema.tables WHERE table_schema = 'database_name';"

# Optimize tables after import
mysql -u root -p -e "OPTIMIZE TABLE table_name;" database_name
```

### XAMPP Control Panel

| Action | Location |
|--------|----------|
| Start MySQL | XAMPP Control Panel → MySQL → Start |
| Access phpMyAdmin | http://localhost/phpmyadmin |
| Access MySQL via CLI | C:\xampp\mysql\bin\mysql.exe |
| Configuration files | C:\xampp\mysql\bin\my.ini |

---

*Last Updated: February 2026*
*Compatible with XAMPP 8.x and MySQL 8.0*
