Strategy ‐ Backup and Restore Large Database On a Production Server

For Restore: restore into a new database

Put your main application in maintenance mode or read only mode. assume that your application is using mydb1, the connection string will look something like this:

server=localhost;user=myuser;pwd=1234;database=mydb1;

Restore the backup to new database mydb_new2, then update your main application's connection to the new db:

server=localhost;user=myuser;pwd=1234;database=mydb_new2;

Resume the application for production.

Restoring to a new database is always faster than restoring to existing production database. Reasons:

• Zero risk to production data - If the restore fails partway through (corrupted backup, disk full, process crash), your original database is completely untouched. There's no "nuked the production database" scenario. You can simply drop the failed restore and try again.
• Efficient disk space usage - A fresh database writes data sequentially on disk, whereas an existing production database accumulates fragmentation over time from updates, deletes, and row movements. So restoring to a new database effectively gives you a defragmented, compacted copy of your data.
• No existing data to deal with — no need to drop/truncate tables, no foreign key checks to resolve, no index rebuilds on existing rows
• No contention — no other connections reading/writing to the database during restore
• Cleaner INSERT path — inserting into empty tables is faster than inserting into tables that already have data and indexes
• No triggers firing on existing data — if the production database has triggers, they could slow things down during restore

For Backup: use a replica

Implement a master-replica strategy. Before you want to perform a backup. Stop the replication process:

STOP REPLICA;    -- pause replication (MySQL 8.0.22+)
-- perform backup --
START REPLICA;   -- resume replication

Logical Backup

• Run the backup process targeting the replica (MySqlBackup.NET, mysqldump, etc)
• With this you can perform the backup process without the need to take down the main application.

Physical Backup

• Stop MySQL server of the replica.
• Copy the raw physical files: Copy InnoDB data files (ibdata*, .ibd files) directly.
• Since you're copying the raw files, restoring it means just copy/paste it to a new location.
• The fastest simplest method of them all.

Other Alternative Solutions

• Enable table locking during export/import to prevent interference: mb.ExportInfo.EnableLockTablesWrite = true;
• Use the LOAD DATA INFILE statement in MySQL: Faster than SQL row by row INSERTs.

---

Transferring a Database Using InnoDB Transportable Tablespaces

This method lets you physically copy raw .ibd files from one MySQL server (or backup) to another, which is significantly faster than logical SQL import for large databases.

Prerequisites

• Both source and destination must run the same MySQL major version (e.g., both 8.0.x)
• Both must use the same innodb_page_size (default 16K)
• innodb_file_per_table must be ON (default since MySQL 5.6)
• The destination must have enough disk space

Step 1: On the Source: Export the Tablespaces

-- Connect to the source MySQL server
USE mydb;

-- This flushes all tables and produces .cfg metadata files
-- The database will be READ LOCKED until you UNLOCK
FLUSH TABLES FOR EXPORT;

Don't close this session. The lock is released when the session ends.

Step 2: Copy the Files from Source

While the lock is held, copy all .ibd and .cfg files from the source database folder.

On Linux:

cp /var/lib/mysql/mydb/*.ibd /tmp/mydb_transport/
cp /var/lib/mysql/mydb/*.cfg /tmp/mydb_transport/

On Windows:

xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data\mydb\*.ibd" "C:\temp\mydb_transport\"
xcopy "C:\ProgramData\MySQL\MySQL Server 8.0\Data\mydb\*.cfg" "C:\temp\mydb_transport\"

Step 3: On the Source: Release the Lock

Go back to the session from Step 1:

UNLOCK TABLES;

The source server is now fully operational again.

Step 4: On the Destination: Create the Database and Tables

You need identical table structures on the destination. The easiest way is to get the schema from the source first:

mysqldump --no-data -u root -p mydb > mydb_schema.sql

Then on the destination:

CREATE DATABASE mydb_new;

mysql -u root -p mydb_new < mydb_schema.sql

Now mydb_new has all the tables with zero rows.

Step 5: On the Destination: Discard All Tablespaces

You need to run ALTER TABLE ... DISCARD TABLESPACE for every table. Generate the statements dynamically:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` DISCARD TABLESPACE;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb_new'
  AND TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'InnoDB';

Copy the output and execute all the statements. After this, the .ibd files for those tables are deleted — the tables exist in the dictionary but have no physical data files.

Step 6 — Copy the Files to the Destination

Copy the .ibd and .cfg files from Step 2 into the destination database folder.

On Linux:

cp /tmp/mydb_transport/*.ibd /var/lib/mysql/mydb_new/
cp /tmp/mydb_transport/*.cfg /var/lib/mysql/mydb_new/

# Critical: fix ownership
chown mysql:mysql /var/lib/mysql/mydb_new/*.ibd
chown mysql:mysql /var/lib/mysql/mydb_new/*.cfg

On Windows, just copy them into the data folder. The MySQL service account typically has access already.

Step 7: On the Destination: Import All Tablespaces

Generate and run the import statements:

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` IMPORT TABLESPACE;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb_new'
  AND TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'InnoDB';

Copy the output and execute. MySQL will read each .ibd file, validate it, and register the tablespace. The .cfg files are consumed and deleted automatically.

Step 8: Verify

USE mydb_new;

-- Check row counts
SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb_new';

-- Spot check a few tables
SELECT COUNT(*) FROM some_important_table;

Step 9: Switch Your Application

Update your connection string from mydb to mydb_new and resume operations. Same blue-green swap strategy described earlier.

---

Automating Steps 5 and 7

If you have dozens of tables, here's a quick bash one-liner to do the discard/import in bulk:

# Discard all
mysql -u root -p -N -e \
  "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` DISCARD TABLESPACE;') 
   FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA='mydb_new' AND ENGINE='InnoDB'" \
  | mysql -u root -p mydb_new

# (copy files here)

# Import all
mysql -u root -p -N -e \
  "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` IMPORT TABLESPACE;') 
   FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA='mydb_new' AND ENGINE='InnoDB'" \
  | mysql -u root -p mydb_new

Things That Can Go Wrong

• File ownership on Linux — if the copied files are owned by root instead of mysql, the import will fail. Always chown mysql:mysql.
• Schema mismatch — if the table definition on the destination differs in any way (different column order, different index, different ROW_FORMAT), the import will fail.
• Foreign keys — IMPORT TABLESPACE doesn't validate foreign key relationships. The data will import fine, but you should run consistency checks afterward if referential integrity matters.
• Partitioned tables — each partition is a separate tablespace. You need to discard/import each partition individually, which makes this approach more tedious.
• Views, routines, triggers, events — these are not stored in .ibd files. The mysqldump --no-data in Step 4 captures them as part of the schema, so they should already be on the destination.

For a 50GB database, the bottleneck is literally just the file copy speed — the SQL overhead of the DISCARD/IMPORT statements is negligible.