Import and Export Databases Guide

Introduction

Learn how to backup (export) and restore (import) MySQL databases using phpMyAdmin. Essential skills for data migration, backup strategies, and disaster recovery.

Exporting Databases

Quick Export (Simple Backup)

Steps:

1. Open phpMyAdmin
2. Select the database from left sidebar
3. Click “Export” tab in main panel
4. Select “Quick” export method
5. Format: SQL (recommended for most cases)
6. Click “Go”
7. File downloads automatically

Default Settings:

• Format: SQL
• Includes: All tables
• Includes: Structure and data
• File: database_name.sql

Custom Export (Advanced Options)

Steps:

1. Select database
2. Click “Export” tab
3. Select “Custom” export method
4. Configure options (see below)
5. Click “Go”

Export Options:

Tables to Export

• Select All: Export all tables
• Select Specific: Choose individual tables
• Unselect All: Deselect all then choose

Output Options

• View output as text: Display in browser
• Save output to file: Download file (recommended)
• Compression:
  • None
  • zipped (recommended for large databases)
  • gzipped

Format Options

• SQL: Standard (most compatible)
• CSV: For spreadsheets
• JSON: For modern applications
• XML: For data exchange
• PDF: For documentation
• Other: Various specialized formats

SQL Options (when format is SQL)

Structure:

• Add DROP TABLE / DATABASE
• Add CREATE TABLE / DATABASE
• Add IF NOT EXISTS
• Add AUTO_INCREMENT value
• Enclose export in transaction
• Disable foreign key checks

Data:

• Complete inserts (includes column names)
• Extended inserts (multiple values in one INSERT)
• Max length of created query
• Use hexadecimal for BLOB

Exporting Specific Tables

Steps:

1. Select database
2. Click “Export” tab
3. Select “Custom” method
4. Under “Tables”, select specific tables
5. Configure other options
6. Click “Go”

Exporting Only Structure

Use Case: Create empty database with same structure

Steps:

1. Select database
2. Export → Custom
3. Under “Dump table”:
   • Check “structure”
   • Uncheck “data”
4. Click “Go”

Exporting Only Data

Use Case: Backup data without structure

Steps:

1. Select database
2. Export → Custom
3. Under “Dump table”:
   • Uncheck “structure”
   • Check “data”
4. Click “Go”

Scheduled Exports (Advanced)

For automatic backups, use command-line tools:

# WAMP
cd C:\wamp64\bin\mysql\mysql[version]\bin
mysqldump -u root -p database_name > backup.sql

# XAMPP Windows
cd C:\xampp\mysql\bin
mysqldump -u root -p database_name > backup.sql

# XAMPP macOS/Linux
/opt/lampp/bin/mysqldump -u root -p database_name > backup.sql

Export Best Practices

1. Regular Backups

- Daily: For production databases
- Weekly: For development databases
- Before major changes: Always

2. File Naming Convention

database_name_YYYYMMDD_HHMMSS.sql
Example: mystore_20241115_143000.sql

3. Storage

• Keep multiple backup versions
• Store in different locations
• Test restore periodically

4. Compression

• Use for large databases
• Saves storage space
• Faster transfer

Importing Databases

Quick Import

Steps:

1. Open phpMyAdmin
2. Select existing database OR create new database
3. Click “Import” tab
4. Click “Choose File”
5. Select .sql file
6. Leave settings as default
7. Click “Go”

Import with Custom Settings

Steps:

1. Select database
2. Click “Import” tab
3. Choose file
4. Configure format and options
5. Click “Go”

Import Options:

File Format

• Automatic (recommended)
• SQL
• CSV
• Open Document Spreadsheet
• Other formats

Partial Import

• Allows file uploads up to: (PHP limit shown)
• Number of queries to skip: Resume interrupted imports
• For large files, may need to adjust PHP settings

SQL Options

• SQL compatibility mode
• Do not use AUTO_INCREMENT value
• Ignore INSERT errors
• Partial import: number of queries to skip

Importing Large Databases

Method 1: Split Large SQL Files

Using Terminal:

# Split file into 10MB chunks
split -b 10m large_database.sql chunk_

# Import each chunk
mysql -u root -p database_name < chunk_aa
mysql -u root -p database_name < chunk_ab
# etc.

Method 2: Increase PHP Limits

Edit php.ini:

upload_max_filesize = 256M
post_max_size = 256M
max_execution_time = 600
max_input_time = 600
memory_limit = 512M

Location:

• WAMP: C:\wamp64\bin\php\php[version]\php.ini
• XAMPP: C:\xampp\php\php.ini

After editing:

1. Save file
2. Restart Apache
3. Retry import

Method 3: Use MySQL Command Line

Windows (WAMP):

cd C:\wamp64\bin\mysql\mysql[version]\bin
mysql -u root -p database_name < C:\path\to\backup.sql

Windows (XAMPP):

cd C:\xampp\mysql\bin
mysql -u root -p database_name < C:\path\to\backup.sql

macOS/Linux:

mysql -u root -p database_name < /path/to/backup.sql

Method 4: BigDump Tool

For very large databases, consider using BigDump PHP script:

• Handles files up to several GB
• Resumes interrupted imports
• No server configuration needed

Creating Database Before Import

Option 1: phpMyAdmin

1. Click “Databases” tab
2. Enter new database name
3. Select collation
4. Click “Create”
5. Then proceed with import

Option 2: Include in SQL File

CREATE DATABASE IF NOT EXISTS database_name;
USE database_name;
-- rest of SQL statements

Importing CSV Files

Steps:

1. Create table with matching structure first
2. Select the table
3. Click “Import” tab
4. Choose CSV file
5. Configure CSV options:
   • Format: CSV
   • Column names in first row
   • Field delimiter (usually comma)
   • Field enclosure (usually quote)
   • Line terminator
6. Click “Go”

CSV Import Options:

• Columns separated with: , or ; or tab
• Columns enclosed with: " or '
• Columns escaped with: \
• Lines terminated with: auto or \n or \r\n
• Column names: Check if first row has column names

Common Import/Export Scenarios

Scenario 1: Migrate Database to New Server

Export from old server:

1. phpMyAdmin → Select database
2. Export → Custom
3. Check “Add DROP TABLE”
4. Check “Add CREATE TABLE”
5. Download .sql file

Import to new server:

1. phpMyAdmin → Create new database
2. Import → Choose file
3. Upload and import

Scenario 2: Backup Before Major Changes

-- Quick backup
-- Export → Quick → Go
-- Make changes
-- If problems occur, import backup

Scenario 3: Copy Database Within Same Server

Method 1: Export/Import

1. Export original database
2. Create new database
3. Import into new database

Method 2: Operations Tab

1. Select database
2. Operations → Copy database
3. Enter new name
4. Click “Go”

Scenario 4: Clone Database Structure

Export structure only:

1. Export → Custom
2. Structure only (no data)
3. Download

Import to new database:

1. Create new database
2. Import structure file

Scenario 5: Transfer Data Only

Export data only:

1. Export → Custom
2. Data only (no structure)
3. Download

Import:

1. Tables must already exist
2. Import data file

Troubleshooting

Error: File Too Large

Solution:

• Increase PHP limits in php.ini
• Use command line import
• Split file into smaller parts
• Use BigDump tool

Error: Maximum Execution Time Exceeded

Solution:

• Increase max_execution_time in php.ini
• Use command line import
• Import in smaller batches

Error: SQL Syntax Error

Solution:

• Check MySQL version compatibility
• Try SQL compatibility mode
• Review the error line in SQL file
• Ensure correct character encoding

Error: Duplicate Entry

Solution:

• Enable “Add DROP TABLE” on export
• Check for existing data before import
• Use “Ignore INSERT errors” option
• Clear target database first

Error: Foreign Key Constraint

Solution:

• Enable “Disable foreign key checks” on export
• Import in correct order (parent tables first)
• Temporarily disable constraints:

  SET FOREIGN_KEY_CHECKS=0;
  -- import data
  SET FOREIGN_KEY_CHECKS=1;
  

Error: Character Encoding Issues

Solution:

• Ensure database collation matches
• Set proper character set on export:

  SET NAMES utf8mb4;
  

• Verify file encoding (should be UTF-8)

Partial Import Completed

Solution:

• Note the last successful query number
• Use “Number of queries to skip” option
• Resume from that point

Command Line Import/Export

Export via Command Line

Full database:

mysqldump -u username -p database_name > backup.sql

With compression:

mysqldump -u username -p database_name | gzip > backup.sql.gz

Specific tables:

mysqldump -u username -p database_name table1 table2 > backup.sql

Structure only:

mysqldump -u username -p --no-data database_name > structure.sql

Data only:

mysqldump -u username -p --no-create-info database_name > data.sql

All databases:

mysqldump -u username -p --all-databases > all_databases.sql

Import via Command Line

Basic import:

mysql -u username -p database_name < backup.sql

From compressed file:

gunzip < backup.sql.gz | mysql -u username -p database_name

Create database first:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS database_name;"
mysql -u root -p database_name < backup.sql

With progress indicator:

pv backup.sql | mysql -u username -p database_name

Automated Backups

Windows Task Scheduler

Create batch file backup.bat:

@echo off
set TIMESTAMP=%date:~-4%%date:~3,2%%date:~0,2%
cd C:\xampp\mysql\bin
mysqldump -u root -pYourPassword database_name > C:\backups\db_%TIMESTAMP%.sql

Schedule with Task Scheduler:

1. Open Task Scheduler
2. Create Basic Task
3. Set trigger (daily, weekly)
4. Action: Start a program
5. Program: path to backup.bat

Linux Cron Job

Create script backup.sh:

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
mysqldump -u root -pYourPassword database_name > /backups/db_$TIMESTAMP.sql
# Keep only last 7 days
find /backups -name "db_*.sql" -mtime +7 -delete

Add to crontab:

chmod +x backup.sh
crontab -e
# Add line:
0 2 * * * /path/to/backup.sh

Best Practices

Export Best Practices

1. Regular Schedule: Daily for production
2. Test Exports: Verify files are valid
3. Multiple Locations: Store backups in different places
4. Compression: Use for large databases
5. Documentation: Document backup procedures

Import Best Practices

1. Test First: Import to test database first
2. Backup Before: Always backup before import
3. Check Compatibility: Verify MySQL versions match
4. Monitor Progress: Watch for errors
5. Verify After: Check data integrity after import

File Management

1. Naming: Use consistent naming convention
2. Organization: Separate by date/version
3. Cleanup: Remove old backups periodically
4. Documentation: Keep import/export logs

Next Steps

• User Management
• Backup Strategies
• Database Security

Additional Resources

• phpMyAdmin Import/Export Documentation
• MySQL mysqldump Documentation
• MySQL Import Documentation