Moving a database is a dump and an import. What makes it go wrong is what a dump does not contain, and finding that out after the site is already pointed at the new server.

Dump

mysqldump --single-transaction --quick --routines --triggers --events \\n          --default-character-set=utf8mb4 shop | gzip > shop.sql.gz

Transfer

scp shop.sql.gz newserver:/tmp/\n# or stream it directly, with no file in between\nmysqldump --single-transaction shop | gzip | ssh newserver 'gunzip | mysql shop'

Create the target properly, before importing

mysql -e "CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"\nzcat /tmp/shop.sql.gz | mysql shop
Create the database with the right character set first. Importing into a latin1 database gives you a schema that looks right and silently mangles Arabic and emoji - see character set and collation.

The three things the dump does not carry

  • Users and grants. They live in the mysql schema. Recreate them - see users and grants.
  • Server configuration. Buffer pool, max_connections, timeouts. Copy the relevant lines across.
  • Scheduled events, unless you passed --events, and the event scheduler must also be enabled on the new server.
CREATE USER 'shop'@'localhost' IDENTIFIED BY '...';\nGRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop'@'localhost';\nSET GLOBAL event_scheduler = ON;

Verify before you switch

# same table count, same row counts on the tables that matter\nmysql -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='shop'"\nmysql shop -e "SELECT COUNT(*) FROM orders; SELECT COUNT(*) FROM customers"
Compare row counts against the old server before pointing the application at the new one. An import that fails halfway still exits without complaint on some paths, and half a database looks exactly like a working one until somebody looks for last month.