Backup, restore and replication

RPO and RTO

RPO, the recovery point objective, is how much data the business can afford to lose in time, and a nightly dump alone gives an RPO of up to 24 hours. RTO, the recovery time objective, is how long the database may be down, and it decides whether a logical dump restores fast enough or you need a physical copy.

Logical or physical

A logical backup describes the data as statements or text that a server re-executes. A physical backup copies the data directory as the engine stored it.

Rule of thumb: logical for small and medium databases and for "bring back one table", physical when the database is too large for the RTO to survive re-executing every INSERT.

The MySQL tools on Windows

Your data sits under C:\ProgramData\MySQL\MySQL Server 8.4\Data (or 9.7), as you saw in chapter 4, and nothing there is safe to copy while the service runs.

Take a dump and restore it

1. Check that the tables are InnoDB.

   --single-transaction only guarantees consistency for InnoDB, so check the engines before you trust the dump.

   SELECT engine, COUNT(*) FROM information_schema.tables
   WHERE table_schema = 'shop' GROUP BY engine;

2. Take a consistent dump.

   Run it while nobody runs ALTER or DROP, and confirm the SOURCE_LOG_POS comment written by --source-data=2 is at the top of the file.

   # the backtick continues the line
   mysqldump -u root -p --single-transaction --routines --events --triggers `
     --source-data=2 --databases shop `
     --result-file=D:\backups\shop-2026-09-27.sql

3. Restore it into a fresh database.

   Never test on production. PowerShell reserves the < operator, so load the file from cmd.exe or with the client's source command.

   CREATE DATABASE shop_restore;
   -- inside the mysql client (forward slashes are fine):
   source D:/backups/shop-2026-09-27.sql
   -- or from cmd.exe:  mysql -u root -p shop_restore < D:\backups\shop.sql

4. Verify.

   Compare row counts and checksums with the source, then run one real query the application uses.

   SELECT COUNT(*) FROM shop_restore.orders;
   CHECKSUM TABLE shop.orders, shop_restore.orders;   -- both should match

5. Schedule it on Windows.

   There is no cron. Task Scheduler (or schtasks) runs a .ps1 nightly, and mysql_config_editor keeps the password out of the script in %APPDATA%\MySQL\.mylogin.cnf.

   # once: store credentials for a dedicated backup account
   mysql_config_editor set --login-path=backup --host=localhost --user=backup --password
   
   # backup-shop.ps1
   $stamp = Get-Date -Format 'yyyy-MM-dd'
   & "C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqldump.exe" --login-path=backup `
     --single-transaction --routines --events --triggers --source-data=2 `
     --databases shop --result-file="D:\backups\shop-$stamp.sql"
   
   # register it
   schtasks /Create /SC DAILY /ST 02:00 /TN "shop backup" /TR "powershell -NoProfile -File D:\backups\backup-shop.ps1"

6. Keep a copy off the machine.

   A backup on the same disk as the database dies with the disk. Copy dumps and binary logs to a share or cloud storage.

Point-in-time recovery with the binary log

A nightly dump restores last night, and the binary log carries you forward from there; log_bin is ON by default in 8.x and later. Say a colleague ran UPDATE orders SET status = 'cancelled' at 10:42 and forgot the WHERE: restore last night's dump into a scratch server, read SOURCE_LOG_POS from the top of the dump, and replay the binary logs from that position with mysqlbinlog, stopping one second before the damage. Send all the files through one mysql process so a transaction that spans two files stays intact. This works only while every binary log since the dump still exists, so copy them off the machine with the dumps.

REM cmd.exe: replay from the dump's position, stop just before the mistake
mysqlbinlog --start-position=157 --stop-datetime="2026-09-27 10:41:59" ^
  "C:\ProgramData\MySQL\MySQL Server 8.4\Data\binlog.000041" ^
  "C:\ProgramData\MySQL\MySQL Server 8.4\Data\binlog.000042" | mysql -u root -p

The PostgreSQL equivalents

PostgreSQL The same shapes under different names. Roles and tablespaces live outside any single database, so a complete logical backup is pg_dumpall --globals-only plus one pg_dump per database.

Replication

MySQL You replicate for read scaling, for high availability, and for an always-current offsite copy. MySQL ships binary log events from a source to a replica, asynchronous by default, so a crash can lose the last transactions that never arrived; semisynchronous replication makes COMMIT wait for one replica. Group Replication elects a new primary on its own, and through MySQL Shell's AdminAPI it becomes an InnoDB Cluster. Check lag with SHOW REPLICA STATUS\G: both threads running with Seconds_Behind_Source climbing means the replica cannot keep up, so reports there show stale numbers.

PostgreSQL A PostgreSQL replica is a standby. Streaming replication ships WAL as the primary writes it, copying the whole cluster at the same major version, and a hot standby serves read-only queries. Logical replication is the selective kind: a publication names tables, a subscription pulls their row changes, and the target may run a different major version. Either way a replica is not a backup: it replicates the DROP TABLE too, a few milliseconds later.

CHANGE REPLICATION SOURCE TO SOURCE_HOST = 'db1', SOURCE_USER = 'repl',
  SOURCE_PASSWORD = '...', SOURCE_AUTO_POSITION = 1;
START REPLICA;
SHOW REPLICA STATUS\G      -- the health check you will run most

Managed cloud databases

Both On Amazon RDS, Google Cloud SQL or Azure Database, the provider takes the backups and offers point-in-time restore and one-click replicas, which removes the scripting and none of your responsibility for retention, restore drills and lag.

Where to go deeper

• mysqldump

  Every option.

• MySQL Shell dump utilities

  dumpInstance and friends.

• Point-in-time recovery using the binary log

  The official procedure.

• MySQL replication

  GTIDs and Group Replication.

• Continuous archiving and PITR

  WAL archiving and recovery targets.