DB-유

Migrating a MySQL Database to a New Server with Minimal Downtime

· mysql, migration, replication

Stop the app, dump, copy, restore, repoint: that's the naive way to move a database, and for 200 GB it means hours of outage. I never do it like that any more. I make the new server a replica of the old one, let it catch up while the app keeps running, and cut over in a minute or two.

This moves a MySQL 5.7 server to a new host running 5.7 or 8.0; MariaDB works much the same with small syntax differences. Before you start you need binary logging on the source (log_bin, plus server_id; if it's off, one restart is unavoidable), network access from the new server to the old on 3306, XtraBackup 2.4 on the source or enough time for mysqldump, and a plan for how the app finds the database (short-TTL DNS name, config file or load balancer).

Prepare the source

Confirm the settings and create a replication user:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'gtid_mode';
CREATE USER 'repl'@'10.0.7.20' IDENTIFIED BY 'repl-password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.7.20';

If log_bin is OFF, add to /etc/my.cnf and restart during a quiet moment:

[mysqld]
server_id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

Seed the new server

Install MySQL on the new host, stop it, and take a physical copy with XtraBackup streamed straight across:

# on the old server
sudo xtrabackup --backup --stream=xbstream --parallel=4 --target-dir=/tmp \
  | ssh newdb "xbstream -x -C /var/lib/mysql-seed"
# on the new server
sudo xtrabackup --prepare --target-dir=/var/lib/mysql-seed
sudo systemctl stop mysqld
sudo rm -rf /var/lib/mysql/*
sudo xtrabackup --move-back --target-dir=/var/lib/mysql-seed
sudo chown -R mysql:mysql /var/lib/mysql
cat /var/lib/mysql/xtrabackup_binlog_info

The last command prints the binlog file and position (and GTID set, if enabled) the copy corresponds to. Keep it.

If you cannot use XtraBackup, a dump with the position embedded works too, just slower:

mysqldump --single-transaction --master-data=2 --routines --triggers --events --all-databases | gzip > /tmp/seed.sql.gz

--master-data=2 writes the CHANGE MASTER TO coordinates as a comment near the top of the file.

Start replication

Give the new server a different server_id in its my.cnf, plus read_only = ON so nobody writes to it before cutover, and start it. I always set read_only at this point because someone will otherwise "just test something" on the new box. Then point it at the old server using the coordinates from the seed step:

CHANGE MASTER TO
  MASTER_HOST = '10.0.7.10',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'repl-password',
  MASTER_LOG_FILE = 'mysql-bin.000318',
  MASTER_LOG_POS = 58211944;
START SLAVE;
SHOW SLAVE STATUS\G

With GTID mode on both sides, use MASTER_AUTO_POSITION = 1 instead of file and position. Watch Seconds_Behind_Master fall to 0. If the target is 8.0 and the source 5.7, run mysql_upgrade on the seeded copy before starting replication; replication from an older to a newer major version is supported, the other direction is not.

Rehearse

While the replica is in sync, test the application against it in a staging setup, run your slow query report on the new hardware, and, importantly, confirm the application users exist on the new host (they do, because the mysql schema was copied). Also set up the new server's own binary logging now so it can have replicas of its own later.

Cut over

The only part with downtime. Keep a stopwatch running:

  1. Put the application into maintenance mode or stop it.
  2. On the old server, block writes and confirm nothing is running:
SET GLOBAL read_only = ON;
SHOW PROCESSLIST;
SHOW MASTER STATUS;
  1. On the new server, wait until the replica has applied everything, then promote it:
SELECT MASTER_POS_WAIT('mysql-bin.000319', 1044201);   -- values from SHOW MASTER STATUS on the old server
SHOW SLAVE STATUS\G                                       -- Seconds_Behind_Master: 0, Exec_Master_Log_Pos matches
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
  1. Change DNS or the application configuration to the new host, and start the application.

Optionally, before step 4, set up replication in the reverse direction (new to old) so you can fail back if something is wrong in the first hour.

Right after cutover

SHOW PROCESSLIST;                                 -- application connections arriving on the new host
SHOW GLOBAL STATUS LIKE 'Com_insert';             -- increasing
SELECT MAX(id), MAX(created_at) FROM shop.orders; -- matches what the old server had

And on the old server, SHOW PROCESSLIST should show nothing but your own session. Leave it running read-only for a few days before decommissioning.

Things that go wrong

  • Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' – The binlog referenced by the seed has been purged. Raise expire_logs_days on the source and re-seed.
  • Last_SQL_Errno: 1062 on the replica during catch-up – The seed and the coordinates do not match (for example the position came from a different backup). Re-seed; do not skip errors on a migration target.
  • Application connects to the new server but gets Access denied for user 'shopapp'@'10.0.1.23' – The application host list changed or the new server has skip_name_resolve set differently. Compare SHOW GRANTS on both.
  • Writes fail with ERROR 1290 (HY000): The MySQL server is running with the --read-only option after cutover – You forgot to clear read_only on the new server, or it is set in my.cnf and will come back on restart. Remove it from the config too.

A boring migration is a good one.