Moving a database to a new server is usually the riskiest part of a migration: the data changes constantly, and a plain dump and restore means the application must stay offline for as long as the copy takes. In this tutorial you will migrate a MySQL 8 database between two Ubuntu 24.04 servers by restoring a consistent dump on the new server and then keeping it in sync with replication, so the actual cutover takes seconds. A second part covers PostgreSQL with pg_dump and pg_restore, including users and a way to verify the result.

Prerequisites

To follow this tutorial you need:

  • Two servers running Ubuntu 24.04 LTS: the old server (current database) and the new server, for example a CubePath VPS. Both need a non-root user with sudo privileges.
  • Network connectivity between the two servers on port 3306 (MySQL). A private network is preferable; if you use public IPs, the replication traffic must be restricted by firewall as shown below.
  • Enough free disk space on both servers for the dump file, roughly the size of the data directory.
  • The same major database version on both sides, or a newer one on the new server (for example MySQL 8.0 to 8.0 or 8.4). Replication from a newer source to an older replica is not supported.

Throughout the guide, replace old_server_ip, new_server_ip and app_db with your own values.

Step 1 - Preparing the new MySQL server

Install MySQL on the new server:

sudo apt update
sudo apt install mysql-server

Each server in a replication setup needs a unique server_id. MySQL 8 defaults to 1, which is also what the old server most likely uses, so set a different value on the new server. Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines to the [mysqld] section:

server-id = 2
relay-log = relay-bin

Restart MySQL and confirm the value:

sudo systemctl restart mysql
sudo mysql -e "SELECT @@server_id;"

+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+

Step 2 - Allowing the new server to replicate from the old one

On the old server, check that binary logging is enabled. It is on by default in MySQL 8, but older configurations sometimes disable it:

sudo mysql -e "SHOW VARIABLES LIKE 'log_bin'; SELECT @@server_id;"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+

If log_bin is OFF, remove any skip-log-bin or disable-log-bin line from /etc/mysql/mysql.conf.d/mysqld.cnf and restart MySQL. This requires a short restart of the old database, so plan it.

By default Ubuntu's MySQL listens only on 127.0.0.1. Open the same file on the old server:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change bind-address to the old server's private IP (or 0.0.0.0 if you have no private network):

bind-address = old_server_ip

Restart MySQL and allow only the new server through UFW:

sudo systemctl restart mysql
sudo ufw allow from new_server_ip to any port 3306 proto tcp

Now create a dedicated replication user on the old server. Replace your_strong_password with a strong, unique password:

sudo mysql

CREATE USER 'repl'@'new_server_ip' IDENTIFIED BY 'your_strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'new_server_ip';
EXIT;

From the new server, confirm that you can log in with that user:

mysql -h old_server_ip -u repl -p -e "SELECT 1;"

If the command hangs, a firewall is blocking port 3306. If it returns Access denied, check that the host part of the user matches the IP the new server connects from.

Step 3 - Taking a consistent dump

On the old server, dump the application database. The options matter:

  • --single-transaction takes a consistent snapshot of InnoDB tables without locking them, so the application keeps working.
  • --source-data=2 writes the binary log position of that snapshot into the dump as a comment. You need it to start replication from exactly that point.
  • --routines --triggers --events include stored procedures, triggers and scheduled events.
sudo mysqldump --single-transaction --source-data=2 \
  --routines --triggers --events \
  --databases app_db | gzip > app_db.sql.gz

To migrate several databases, list them all after --databases. Avoid --all-databases: it includes the mysql system schema, and restoring it over a fresh MySQL installation can break it. Users are recreated separately in Step 6.

Read the replication coordinates stored in the dump:

zcat app_db.sql.gz | grep -m1 -E "CHANGE (MASTER|REPLICATION SOURCE) TO"

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000014', SOURCE_LOG_POS=5873412;

Write down the file name and position. Copy the dump to the new server:

rsync -avP app_db.sql.gz your_user@new_server_ip:~/

Step 4 - Restoring the dump on the new server

On the new server, import the dump. Because it was created with --databases, it contains the CREATE DATABASE statement:

zcat ~/app_db.sql.gz | sudo mysql

For a large database this can take a while. Run it inside tmux or screen so a dropped SSH session does not interrupt it. When it finishes, check that the tables are there:

sudo mysql -e "SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'app_db';"

Step 5 - Starting replication

Point the new server at the old one, using the coordinates from Step 3. GET_SOURCE_PUBLIC_KEY = 1 is needed because MySQL 8 uses the caching_sha2_password plugin, which otherwise refuses a password login over an unencrypted connection:

sudo mysql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'old_server_ip',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'your_strong_password',
  SOURCE_LOG_FILE = 'binlog.000014',
  SOURCE_LOG_POS = 5873412,
  GET_SOURCE_PUBLIC_KEY = 1;
START REPLICA;

Check the replication status:

SHOW REPLICA STATUS\G

The important fields are:

             Replica_IO_Running: Yes
            Replica_SQL_Running: Yes
          Seconds_Behind_Source: 0
                 Last_IO_Error:
                Last_SQL_Error:

Both threads must show Yes. Seconds_Behind_Source goes down to 0 once the new server has applied everything written on the old server since the dump. From now on, every change on the old server is copied to the new one within seconds, and you can take your time preparing the application on the new infrastructure.

Step 6 - Recreating application users

The dump does not include MySQL accounts. List the application users and their grants on the old server:

sudo mysql -e "SELECT user, host FROM mysql.user WHERE user NOT LIKE 'mysql.%' AND user NOT IN ('root','debian-sys-maint','repl');"
sudo mysql -e "SHOW GRANTS FOR 'app_user'@'localhost';"

Create the same users on the new server with the same passwords, so the application configuration only needs a new host name:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'app_user_password';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';

Adjust the host part if the application runs on a different server than the database.

Step 7 - Cutting over to the new server

The cutover is the only moment with a short write interruption. Do it in this order.

  1. On the old server, block all writes. super_read_only also blocks users with administrative privileges:

    sudo mysql -e "SET GLOBAL super_read_only = ON;"

  2. Still on the old server, note the current binary log position:

    sudo mysql -e "SHOW MASTER STATUS;"

    On MySQL 8.4 and later, the command is SHOW BINARY LOG STATUS.

  3. On the new server, wait until SHOW REPLICA STATUS\G shows the same file in Relay_Source_Log_File and the same position in Exec_Source_Log_Pos. Then stop and remove the replication configuration:

    STOP REPLICA;
RESET REPLICA ALL;

  4. Make sure the new server accepts writes:

    sudo mysql -e "SELECT @@read_only, @@super_read_only;"

    Both values must be 0.

  5. Update the application's database host to new_server_ip (or the new server's private IP) and restart it.

Leave the old server in read-only mode for a few days as a fallback, then shut it down.

Step 8 - Verifying the migrated data

Compare table checksums on both servers. Run the same statement on the old server (still read-only, so the data cannot change) and on the new server right after the cutover:

sudo mysql -e "CHECKSUM TABLE app_db.users, app_db.orders;"

+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| app_db.users   | 2839410712 |
| app_db.orders  | 1180243659 |
+----------------+------------+

Identical checksums mean identical content. CHECKSUM TABLE reads the whole table, so on very large tables compare row counts with SELECT COUNT(*) first and checksum the most important tables only.

Migrating PostgreSQL

PostgreSQL has its own tools. The simplest reliable method is a dump in custom format restored with pg_restore, which requires stopping writes for the duration of the copy. Ubuntu 24.04 ships PostgreSQL 16; if the old server runs an older version, use the pg_dump from the newer version when possible, because pg_dump can read older servers but not newer ones.

On the new server, install PostgreSQL:

sudo apt install postgresql

On the old server, export roles (users and passwords) and then the database. pg_dumpall --globals-only exports roles and tablespaces, but no data:

sudo -u postgres pg_dumpall --globals-only > globals.sql
sudo -u postgres pg_dump -Fc -d app_db -f /tmp/app_db.dump

The -Fc custom format is compressed and lets pg_restore restore in parallel. Copy both files to the new server:

rsync -avP globals.sql /tmp/app_db.dump your_user@new_server_ip:~/

On the new server, restore the roles, create the database with the same owner, and restore it with four parallel jobs:

sudo -u postgres psql -f ~/globals.sql
sudo -u postgres createdb -O app_user app_db
sudo -u postgres pg_restore -d app_db -j 4 --no-owner --role=app_user ~/app_db.dump

globals.sql also tries to create the postgres role, which already exists; the role "postgres" already exists error is expected and harmless. If the files are in your home directory, make sure the postgres user can read them, or move them to /tmp first.

Verify the result by comparing table sizes and row counts on both servers:

sudo -u postgres psql -d app_db -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;"

Run ANALYZE afterwards so the query planner has fresh statistics:

sudo -u postgres psql -d app_db -c "ANALYZE;"

Troubleshooting

Replica_IO_Running: Connecting: the new server cannot reach the old one. Check bind-address, UFW rules and the replication user's host. The exact reason appears in Last_IO_Error.

Authentication requires secure connection: you omitted GET_SOURCE_PUBLIC_KEY = 1. Run STOP REPLICA, repeat CHANGE REPLICATION SOURCE TO GET_SOURCE_PUBLIC_KEY = 1; and START REPLICA.

Replica_SQL_Running: No with a duplicate key error: replication started from the wrong position, usually because the coordinates were not taken from the same dump you restored. Drop the database on the new server and repeat Steps 3 to 5.

The import is very slow: large imports are limited by disk writes. Make sure the new server has enough RAM for innodb_buffer_pool_size and run the import on a server that is not yet serving traffic.

Conclusion

You restored a consistent MySQL dump on a new server, kept it in sync with replication and switched the application over with only a few seconds of read-only time, and you know how to move a PostgreSQL database with pg_dump and pg_restore. As next steps, set up automated backups on the new server, enable TLS for remote database connections, and remove the replication user from the old server before decommissioning it.