Moving a MySQL database from one server to another is a common task when upgrading infrastructure, changing hosting providers, or scaling your application. In this tutorial, I will safely migrate a MySQL database from one LAMP server to another using mysqldump, verify data integrity, recreate users and permissions, and test the migration before switching traffic. By the end, your database will be running on the new server with zero data loss.
Prerequisites
- Access to both the source and destination LAMP servers
- Root or sudo access on both servers
- MySQL installed on both servers
- Enough disk space on both servers for the database dump file
- SSH access to both servers
Step 1 - Check the Source Database Size
Before starting, determine how much disk space you need. Log in to MySQL on the source server:
mysql -u root -p
Check the size of your database. Replace mydb with your database name:
SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.tables WHERE table_schema = 'mydb' GROUP BY table_schema;
Note the size. Ensure both servers have at least double this amount of free disk space. Exit MySQL:
exit
Step 2 - Create a Database Backup with mysqldump
Export the database to a SQL dump file. This creates a complete backup including schema, data, and stored procedures:
mysqldump -u root -p --single-transaction --routines --triggers --databases mydb > mydb_backup.sql
Enter your MySQL root password when prompted. The flags do the following:
- --single-transaction ensures a consistent backup without locking tables for InnoDB
- --routines includes stored procedures and functions
- --triggers includes triggers
- --databases includes CREATE DATABASE statement
For multiple databases, list them separated by spaces:
mysqldump -u root -p --single-transaction --routines --triggers --databases mydb1 mydb2 > all_databases.sql
Verify the backup file was created:
ls -lh mydb_backup.sql
You should see the file with a size matching your database.
Step 3 - Compress the Backup File
For faster transfer, compress the SQL file:
gzip mydb_backup.sql
This creates mydb_backup.sql.gz. Check the compressed size:
ls -lh mydb_backup.sql.gz
SQL files compress very well, often to 10 percent of the original size.
Step 4 - Transfer the Backup to the Destination Server
Use scp to securely copy the backup to the destination server. Replace DEST_IP with the destination server IP address:
scp mydb_backup.sql.gz root@DEST_IP:/tmp/
Enter the destination server password when prompted. The file will be copied to /tmp/ on the new server.
For large databases, you can combine dump and transfer in one command to avoid storing the file locally:
mysqldump -u root -p --single-transaction --routines --triggers --databases mydb | gzip | ssh root@DEST_IP 'cat > /tmp/mydb_backup.sql.gz'
This streams the backup directly to the destination server.
Step 5 - Prepare the Destination Server
SSH into the destination server:
ssh root@DEST_IP
Verify MySQL is running:
systemctl status mysql
If MySQL is not running, start it:
sudo systemctl start mysql
Decompress the backup file:
gunzip /tmp/mydb_backup.sql.gz
This extracts the SQL file back to /tmp/mydb_backup.sql.
Step 6 - Import the Database on the Destination Server
Import the SQL dump into MySQL:
mysql -u root -p < /tmp/mydb_backup.sql
Enter your MySQL root password. This creates the database and imports all tables, data, routines, and triggers. For large databases, this may take several minutes.
Monitor the import progress by checking MySQL process list in another terminal:
mysql -u root -p -e "SHOW PROCESSLIST;"
You will see the active queries being executed during the import.
Step 7 - Verify the Database Was Imported
Log in to MySQL and check that the database exists:
mysql -u root -p
List all databases:
SHOW DATABASES;
You should see your database in the list. Select it and verify table count:
USE mydb; SHOW TABLES;
Count the rows in a key table to verify data was imported. Replace users with one of your table names:
SELECT COUNT(*) FROM users;
Compare this count with the same query on the source server to confirm all data transferred.
Exit MySQL:
exit
Step 8 - Recreate Database Users and Permissions
Database users are not included in the mysqldump. You need to recreate them on the new server. On the source server, list the users and their permissions:
mysql -u root -p -e "SELECT user, host FROM mysql.user WHERE user != 'root' AND user != 'mysql.sys' AND user != 'mysql.session';"
For each application user, show their grants. Replace appuser with your username:
mysql -u root -p -e "SHOW GRANTS FOR 'appuser'@'localhost';"
Copy the output. On the destination server, recreate the user with the same password and permissions:
mysql -u root -p
Create the user:
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'your_password'; GRANT ALL PRIVILEGES ON mydb.* TO 'appuser'@'localhost'; FLUSH PRIVILEGES;
Replace your_password with the actual password from the source server.
Exit MySQL:
exit
Step 9 - Test the Application Connection
Before switching production traffic, test that your application can connect to the new database. Update your application configuration temporarily to point to the new server IP or hostname.
For a PHP application, edit the database configuration file and change the host value:
sudo nano /var/www/html/config.php
Change the DB_HOST value to the new server IP or keep it as localhost if the app is on the same server.
Load the application in a browser and test critical functions like login, data retrieval, and writes. Check application logs for any database connection errors.
Step 10 - Verify Data Integrity with Checksums
To confirm the migration is identical, compare table checksums between servers. On both the source and destination servers, run:
mysql -u root -p mydb -e "CHECKSUM TABLE users, orders, products;"
Replace users, orders, products with your actual table names. The checksum values should match exactly on both servers. If they differ, data was lost or corrupted during transfer.
Step 11 - Plan the Cutover
To minimize downtime, plan your cutover carefully. Put the source application in maintenance mode, do a final incremental backup of any changes since the first dump, import those changes on the destination, then switch DNS or load balancer to the new server.
For a final incremental sync, dump only recent changes if your tables have timestamp columns:
mysqldump -u root -p --single-transaction --where="updated_at > '2024-01-20 12:00:00'" mydb users > incremental.sql
Transfer and import this incremental dump on the destination server before going live.
Step 12 - Clean Up
After confirming the migration is successful and the application is stable on the new server, delete the backup files to free disk space:
rm /tmp/mydb_backup.sql
On the source server:
rm mydb_backup.sql.gz
Keep one compressed backup in a secure location for disaster recovery.
Wrap-up
You have successfully migrated a MySQL database from one LAMP server to another. You used mysqldump to create a consistent backup, transferred it securely, imported it on the new server, recreated users and permissions, and verified data integrity with checksums. For production migrations, always test the process in a staging environment first, plan your cutover window to minimize downtime, and keep backups until you are certain the new server is stable. This same process works for migrating between cloud providers, upgrading MySQL versions, or consolidating databases.
