Move a MySQL or PostgreSQL database to another server, verified

To move a MySQL, MariaDB or PostgreSQL database to another server, dump it on the old server, restore the dump on the new one, check that tables and row counts match, then point your application at the new host and remove the old copy. You can do this by hand with mysqldump or pg_dump, or have Fleetdock run it as a tracked backup → restore → verify job that drops the source only after the copy is verified. Neither way is zero-downtime: writes made after the dump are not carried over.

Before you start

• Same engine on the destination, at the same or a newer major version.
• Disk space on the destination for the restored data, which is larger than the gzipped dump because indexes are rebuilt.
• Users and grants. They live at the server level, not in a single-database dump. List them on the old server and create them on the new one.
• A write freeze. Measure how long a copy takes, and stop writes for at least that long during the cutover.
• Everything that connects: application config, cron jobs, reporting tools, other services' connection strings.

The manual way

MySQL and MariaDB

# 1. Dump from the old server
mysqldump -h old-db.internal -u root -p \
  --single-transaction --quick --routines --triggers --events \
  shop | gzip > shop.sql.gz

# 2. Create the database on the new server and load the dump
mysql -h new-db.internal -u root -p -e "CREATE DATABASE shop"
gunzip < shop.sql.gz | mysql -h new-db.internal -u root -p shop

On MariaDB, mariadb-dump and mariadb take the same options. --single-transaction gives a consistent snapshot of InnoDB tables without locking them; MyISAM tables are not covered by it. --routines and --events include stored procedures and scheduled events, which mysqldump leaves out by default.

PostgreSQL

# 1. Dump from the old server
pg_dump -h old-db.internal -U postgres --no-owner --no-privileges shop \
  | gzip > shop.sql.gz

# 2. Create the database on the new server and load the dump
createdb -h new-db.internal -U postgres shop
gunzip < shop.sql.gz | psql -h new-db.internal -U postgres \
  --set ON_ERROR_STOP=1 --single-transaction -d shop

ON_ERROR_STOP with --single-transaction makes a failed restore roll back completely instead of leaving half a database. With --no-owner, restored objects belong to the user that runs the restore; reassign them if your application expects its own role to own them. pg_dumpall --globals-only exports roles if you want to copy them. Use the pg_dump from the newer of the two PostgreSQL versions.

Verify by hand

Compare exact row counts of your key tables on both servers:

SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM customers;

On MySQL and MariaDB with the same version on both sides, CHECKSUM TABLE orders, customers; also compares contents. Do not rely on table_rows in information_schema: for InnoDB it is an estimate.

The Fleetdock way

Fleetdock runs the same idea as a background job: back up the source, restore into the destination, verify, and optionally drop the source. Every step is tracked in Activity.

You need both database servers connected to Fleetdock (see Manage many MySQL servers) and backup storage configured, because the copy goes through a backup (see Scheduled backups to S3 or R2).

SELECT 'orders' AS table_name, COUNT(*) AS row_count FROM orders
UNION ALL
SELECT 'customers', COUNT(*) FROM customers;

Manual or Fleetdock?

For one database you move once a year, the manual way is fine. For moving tenants between servers as they grow, the tracked job saves the checks you would otherwise script.

What this does not cover

• Moves without downtime. Set up MySQL replication or PostgreSQL logical replication from the old server to the new one, let it catch up, then switch. Fleetdock does not configure replication or failover.
• Moving a whole server. A Fleetdock move covers one database. Repeat it for each database, or move the server's data directory with your own tooling.
• Changing engines or upgrading versions in place. Fleetdock does neither.