MySQL is one of the most popular open-source relational database management systems. This guide walks you through installing the Countries States Cities database in MySQL.
MySQL installation typically takes 2-5 minutes and requires approximately 200MB of disk space.

​
Prerequisites

Before starting, ensure you have:
  • MySQL 5.7+ installed (MySQL 8.0+ recommended)
  • Administrative access to your MySQL server
  • At least 500MB free disk space
  • Downloaded the mysql/world.sql file from our GitHub repository

​
Method 1: Command Line Import

1

Create Database

mysql -u root -p -e "CREATE DATABASE world CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
We use utf8mb4 charset to properly handle Unicode characters including emojis and special characters.
2

Import Database

mysql -u root -p world < mysql/world.sql
The import process typically takes 2-5 minutes depending on your system performance.
3

Verify Installation

mysql -u root -p world -e "
SELECT COUNT(*) as countries FROM countries;
SELECT COUNT(*) as states FROM states;  
SELECT COUNT(*) as cities FROM cities;
"
Expected output:
| countries |
|    250    |

| states |
|  5038  |

| cities |
| 151024 |

​
Method 2: MySQL Workbench

For users who prefer a graphical interface:
1

Open MySQL Workbench

Connect to your MySQL server instance.
2

Create New Schema

  • Right-click in the Navigator panel
  • Select “Create Schema”
  • Name it “world”
  • Set charset to “utf8mb4”
  • Set collation to “utf8mb4_unicode_ci”
3

Import SQL File

  • Go to Server → Data Import
  • Select “Import from Self-Contained File”
  • Choose the mysql/world.sql file
  • Select “world” as target schema
  • Click “Start Import”

​
Advanced Configuration

​
Performance Tuning for Large Imports

-- Disable checks during import for better performance
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

-- Import the database here --
-- mysql -u root -p world < mysql/world.sql

-- Re-enable checks after import
SET foreign_key_checks = 1;
SET unique_checks = 1;
COMMIT;

​
Create Application User

1

Create Dedicated User

CREATE USER 'csc_user'@'localhost' IDENTIFIED BY 'secure_password_here';
Replace ‘secure_password_here’ with a strong, unique password.
2

Grant Permissions

-- For read-only access
GRANT SELECT ON world.* TO 'csc_user'@'localhost';

-- For read-write access (if needed)
GRANT SELECT, INSERT, UPDATE, DELETE ON world.* TO 'csc_user'@'localhost';

FLUSH PRIVILEGES;

​
Database Structure Verification

After installation, verify the database structure: 
-- Check all tables are created
SHOW TABLES;

-- Check table structures
DESCRIBE countries;
DESCRIBE states;
DESCRIBE cities;

​
Performance Optimization

​
Create Additional Indexes

-- Performance indexes for common queries
CREATE INDEX idx_countries_iso2 ON countries(iso2);
CREATE INDEX idx_countries_iso3 ON countries(iso3);
CREATE INDEX idx_countries_region ON countries(region);

CREATE INDEX idx_states_country_id ON states(country_id);
CREATE INDEX idx_states_name ON states(name);

CREATE INDEX idx_cities_country_id ON cities(country_id);
CREATE INDEX idx_cities_state_id ON cities(state_id);
CREATE INDEX idx_cities_coordinates ON cities(latitude, longitude);
CREATE INDEX idx_cities_name ON cities(name);

​
Update Table Statistics

-- Update statistics for query optimizer
ANALYZE TABLE countries, states, cities;

​
Connection Examples

<?php
$servername = "localhost";
$username = "csc_user";
$password = "your_password";
$dbname = "world";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", 
                   $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Test query
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM countries");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo "Countries in database: " . $result['count'];
    
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

​
Troubleshooting

​
Common Issues

Problem: ERROR 1045 (28000): Access denied for userSolutions:
  1. Verify username and password
  2. Check user permissions: SHOW GRANTS FOR 'csc_user'@'localhost';
  3. Ensure user can connect from your host
  4. Try connecting as root first to verify server is running
Problem: Special characters display as question marks or boxesSolutions:
  1. Ensure database uses utf8mb4 charset
  2. Set connection charset: SET NAMES utf8mb4;
  3. Check client application charset settings
  4. Verify file encoding when importing
Problem: ERROR 2006 (HY000): MySQL server has gone awaySolutions:
  1. Increase max_allowed_packet: SET GLOBAL max_allowed_packet=1073741824;
  2. Increase wait_timeout: SET GLOBAL wait_timeout=3600;
  3. Import in smaller batches
  4. Use --single-transaction flag for mysqldump imports
Problem: Queries are running slowlySolutions:
  1. Add appropriate indexes (see Performance Optimization section)
  2. Increase innodb_buffer_pool_size
  3. Update table statistics with ANALYZE TABLE
  4. Use EXPLAIN to identify slow queries
  5. Consider query optimization techniques

​
Backup and Maintenance

​
Create Automated Backup

#!/bin/bash
# backup_world_db.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/path/to/backups"
DB_NAME="world"

# Create backup
mysqldump -u root -p --single-transaction --routines --triggers \
  $DB_NAME > "$BACKUP_DIR/world_backup_$DATE.sql"

# Compress backup
gzip "$BACKUP_DIR/world_backup_$DATE.sql"

# Keep only last 7 backups
find $BACKUP_DIR -name "world_backup_*.sql.gz" -type f -mtime +7 -delete

echo "Backup completed: world_backup_$DATE.sql.gz"

​
Regular Maintenance

-- Check table integrity
CHECK TABLE countries, states, cities;

-- Optimize tables
OPTIMIZE TABLE countries, states, cities;

-- Update statistics
ANALYZE TABLE countries, states, cities;
Set up automated maintenance scripts to run during off-peak hours for optimal performance.

​
Next Steps

After successful installation:
  1. Set up regular backups using the script above
  2. Configure monitoring for database performance
  3. Implement connection pooling for production applications
  4. Review security settings and user permissions
  5. Test your application connectivity and queries

Need Help?

Join our community discussions for MySQL-specific questions and optimization tips.