Migrate database between servers

Migrate database between servers

What is database migration?

Migrating a database means transferring all data, structure, and configurations from a source server to a destination server. This is common when changing hosting, updating servers, or needing to move data to a different environment.

Migration methods

There are several methods to migrate MySQL/MariaDB databases:

• mysqldump: Exports to SQL and then imports (most common)
• Replication: Synchronizes data in real time
• Direct file copy: Copies physical database files
• GUI tools: phpMyAdmin, MySQL Workbench, etc.

Method 1: Using mysqldump (Recommended)

Step 1: Export database on source server

# Export complete database
mysqldump -u user -p database_name > backup.sql

# Export with UTF-8 charset
mysqldump -u user -p --default-character-set=utf8mb4 database_name > backup.sql

# Export all databases
mysqldump -u user -p --all-databases > all_databases.sql

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

# Export only data (no structure)
mysqldump -u user -p --no-create-info database_name > data.sql

Step 2: Transfer file to destination server

Using SCP:

scp backup.sql user@destination_server:/path/destination/

Using FTP/SFTP:

Upload the file using FileZilla, WinSCP or any FTP client.

Using rsync:

rsync -avz backup.sql user@destination_server:/path/destination/

Step 3: Create database on destination server

mysql -u root -p
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
EXIT;

Step 4: Import database on destination server

mysql -u user -p database_name < backup.sql

Or from within MySQL:

mysql -u user -p
USE database_name;
SOURCE /full/path/backup.sql;

Method 2: Direct migration between servers

If both servers have access to each other, you can do the migration directly:

# On destination server
mysqldump -h source_server -u user -p database_name | mysql -u user -p database_name

Or using an SSH tunnel:

# Create SSH tunnel
ssh -L 3307:localhost:3306 user@source_server

# In another terminal, migrate through tunnel
mysqldump -h 127.0.0.1 -P 3307 -u user -p database_name | mysql -u user -p database_name

Method 3: Using phpMyAdmin

Export:

1. Access phpMyAdmin on source server
2. Select the database
3. Click "Export"
4. Choose "Custom" method
5. Select all tables and necessary options
6. Click "Go" and download the SQL file

Import:

1. Access phpMyAdmin on destination server
2. Create the database if it doesn't exist
3. Select the database
4. Click "Import"
5. Select the downloaded SQL file
6. Click "Go"

Verify migration

1. Verify all tables were imported:

mysql -u user -p database_name
SHOW TABLES;

2. Verify record count:

SELECT COUNT(*) FROM table_name;

3. Compare sizes:

SELECT 
    table_name AS 'Table',
    table_rows AS 'Rows',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

4. Verify users and permissions:

SELECT user, host FROM mysql.user WHERE user = 'username';
SHOW GRANTS FOR 'username'@'localhost';

Migrate users and permissions

Users are not automatically migrated with mysqldump. To migrate them:

# Export users
mysql -u root -p -e "SELECT CONCAT('CREATE USER ''', user, '''@''', host, ''' IDENTIFIED BY ''password'';') FROM mysql.user WHERE user != 'root' AND user != '';" > users.sql

# Or simpler, export user table
mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > users_permissions.sql

# Import on destination server
mysql -u root -p mysql < users_permissions.sql
FLUSH PRIVILEGES;

Migrate large databases

For very large databases, consider these options:

# 1. Compress during export
mysqldump -u user -p database_name | gzip > backup.sql.gz

# 2. Export by tables
mysql -u user -p -e "SHOW TABLES FROM database_name;" > tables.txt
for table in $(cat tables.txt); do
    mysqldump -u user -p database_name $table > ${table}.sql
done

# 3. Use mydumper (faster tool)
sudo apt install mydumper
mydumper -u user -p password -B database_name -o /path/backup/
myloader -u user -p password -d /path/backup/

Common problems and solutions

Error: "Access denied"

• Verify user has permissions on both servers
• Use root user if necessary

Error: "Table doesn't exist"

• Verify database was created correctly
• Check that SQL file was imported completely

Error: "Out of memory"

• Export by tables instead of entire database
• Increase available memory for MySQL
• Use mydumper instead of mysqldump

Strange characters after migration

• Export with --default-character-set=utf8mb4
• Import with same charset
• Check charset configuration on both servers

Migration checklist

• ✅ Make full backup of source server
• ✅ Verify available space on destination server
• ✅ Create database on destination server
• ✅ Export database from source server
• ✅ Transfer file to destination server
• ✅ Import database on destination server
• ✅ Verify all tables were imported
• ✅ Verify data is correct
• ✅ Migrate users and permissions if necessary
• ✅ Update application configuration
• ✅ Test application on new server
• ✅ Make backup of destination server

Best practices

• Make backups: Always make a full backup before migrating
• Test first: Test migration in a development environment if possible
• Verify after: Always verify migration was successful
• Document: Keep a record of steps followed
• Plan downtime: If necessary, coordinate with users
• Keep both servers: Don't delete source server until verifying everything works

Need help?

If you're having trouble migrating your database or need assistance, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host