How to migrate a MySQL database between servers without data loss

List the databases and measure the actual size:

mysql -uroot -p -e "SELECT table_schema AS db, ROUND(SUM(data_length + index_length)/1024/1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema;"

Note the size of the database you will migrate — you need this to size the space on the destination and estimate dump time (~1-2 minutes per GB on fast disk).

Check which engines are in use:

mysql -uroot -p -e "SELECT table_schema, engine, COUNT(*) FROM information_schema.tables WHERE table_schema = 'mydatabase' GROUP BY table_schema, engine;"

If MyISAM appears in the result, consider converting to InnoDB before migrating. MyISAM does not support --single-transaction, forcing the dump to use a table lock that blocks writes.

Export users and permissions to a separate file:

mysql -uroot -p -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user WHERE user NOT IN ('mysql.sys','mysql.session','mysql.infoschema','root');" -s --skip-column-names | mysql -uroot -p -s --skip-column-names | sed 's/$/;/' > grants.sql

The standard mysqldump does not include users — they live in mysql.user, outside the application database. Without this file, you restore the data but application connections fail with “Access denied”.

Generate the dump with consistency flags:

mysqldump -uroot -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --hex-blob \
  --default-character-set=utf8mb4 \
  mydatabase > mydatabase_$(date +%Y%m%d_%H%M%S).sql

Each flag covers a specific case: --single-transaction ensures a consistent snapshot without locks; --routines --triggers --events include procedures, triggers and scheduled events; --master-data=2 records the binlog position as a comment (useful for later replication); --hex-blob prevents BLOB field corruption during transfer.

Compress the dump to speed up transfer:

gzip -9 mydatabase_*.sql

gzip compression typically reduces SQL dumps by 70-85%. On large databases, this turns a 40-minute transfer into 8 minutes — worth the CPU time.

Transfer via SCP from the source server:

scp mydatabase_*.sql.gz [email protected]:/tmp/

If the connection is unstable or the file exceeds 5 GB, prefer rsync --partial --progress — it resumes from where it stopped if it drops midway.

On the destination server, validate the checksum:

# On the source:
sha256sum mydatabase_*.sql.gz

# On the destination:
sha256sum /tmp/mydatabase_*.sql.gz

Compare the hashes. A difference means corruption during transfer — repeat the SCP/rsync before restoring. Restoring a corrupted dump generates random errors midway and forces you to redo everything.

Create the empty database with the correct charset:

mysql -uroot -p -e "CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

The wrong charset is a classic source of ”?????” instead of accented characters. If the source uses utf8mb4, the destination must use utf8mb4 — not utf8 (which is 3 bytes and breaks emoji).

Restore the dump with performance flags:

gunzip < /tmp/mydatabase_*.sql.gz | mysql -uroot -p \
  --max_allowed_packet=512M \
  --init-command="SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \
  mydatabase

Disabling unique_checks and foreign_key_checks during the restore speeds it up by 3-5×. The dump is internally consistent, so there is no risk. The checks return to default on the next connection.

Restore users and permissions:

mysql -uroot -p < grants.sql
mysql -uroot -p -e "FLUSH PRIVILEGES;"

FLUSH PRIVILEGES forces MySQL to reread the permissions table. Without it, some clients continue receiving “Access denied” even with the grants already inserted.

Compare row counts table by table:

# On the source:
mysql -uroot -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='mydatabase' ORDER BY table_name;" > source_counts.txt

# On the destination:
mysql -uroot -p -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='mydatabase' ORDER BY table_name;" > destination_counts.txt

diff source_counts.txt destination_counts.txt

table_rows in information_schema is an estimate for InnoDB — it can diverge slightly. For exact counts on critical tables, run SELECT COUNT(*) FROM table on both sides.

Verify auxiliary objects:

mysql -uroot -p mydatabase -e "SHOW TRIGGERS\G SHOW PROCEDURE STATUS WHERE Db='mydatabase'\G SHOW EVENTS\G"

Triggers, procedures and events only come in the dump if you used the --triggers --routines --events flags. If any are missing, reinsert them manually via a specific dump (mysqldump --no-data --no-create-info --triggers ...).