MySQL 5 min readMay 29, 2025

MySQL / MariaDB Database Migration — Step-by-Step Guide

A complete procedure for migrating a MySQL/MariaDB database between two environments (e.g., CMO → FMO, or Dev → Prod) using mysqldump. Covers export, user grants extraction, import, and verification.

Overview

This procedure covers a live database migration between two separate environments. The goal is a consistent, verified copy of the source database on the target server with matching user permissions and schema.

Source (CMO): The environment you are migrating FROM
Target (FMO): The environment you are migrating TO
Tool: mysqldump — ships with MySQL/MariaDB, no extra install needed

Step 1 — Connect to Source Server

RDP or SSH to your source database server and open a terminal.

bash

# Navigate to MySQL binary directory if not in PATH
mysql -u root -p<password>

-- Verify databases
SHOW DATABASES;

-- If replication is configured: gap should be 0 before migration
SHOW SLAVE STATUSG
-- Look for: Seconds_Behind_Master: 0

💡

Always confirm the slave lag is 0 before dumping to avoid capturing a mid-replication state.

Step 2 — Record Table Count (Pre-Migration Baseline)

Note the table count — you will verify this matches after import.

sql

-- Record this output to compare after import
SHOW TABLES FROM your_database_name;

Step 3 — Export the Database (mysqldump)

Exit the MySQL prompt and run the dump from the OS. Use --single-transactionfor InnoDB to avoid locking the database during export.

bash

exit;   -- exit mysql prompt first

mysqldump -u root -p<password> \
  -qf \
  --skip-add-drop-table \
  --single-transaction \
  --routines \
  --triggers \
  --databases your_database_name \
  > /path/to/backup/db_dump_$(date +%d%b%y).sql

💡

--routines includes stored procedures and functions.--triggers includes trigger definitions.--single-transaction takes a consistent snapshot without locking (InnoDB only).

Step 4 — Export User Grants

Extract the grants for non-root users that need to exist on the target. Run these in MySQL prompt, save the output.

sql

-- List all users and their grant statements
SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';')
FROM mysql.user
WHERE user != 'root';

-- Run the SHOW GRANTS for each user you need, e.g.:
SHOW GRANTS FOR 'appuser'@'%';
SHOW GRANTS FOR 'readonlyuser'@'%';

-- Copy the output — you will run these on the target in Step 8

Step 5 — Copy the Dump File to Target

bash

# Option A: SCP from source to target
scp /path/to/backup/db_dump_*.sql user@target-server:/path/to/import/

# Option B: Shared network drive (Windows environments)
# Copy db_dump_*.sql to \\target-server\Database\your_db\

Step 6 — Connect to Target Server

RDP or SSH to the target (FMO) server.

bash

mysql -u root -p<password>

-- Verify connectivity and replication state (if applicable)
SHOW DATABASES;
SHOW SLAVE STATUSG    -- Seconds_Behind_Master should be 0

Step 7 — Create the Database on Target

sql

CREATE DATABASE your_database_name;

Step 8 — Create Users and Grant Permissions

Apply the grants you captured in Step 4.

sql

-- Create users and apply grants from Step 4 output
CREATE USER 'appuser'@'%' IDENTIFIED BY '<password>';
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database_name.* TO 'appuser'@'%';

-- Apply other grants from Step 4...

-- Always flush after user/grant changes
FLUSH PRIVILEGES;

Step 9 — Import the Database

bash

exit;   -- exit mysql prompt

# Standard import
mysql -u root -p<password> your_database_name \
  < /path/to/import/db_dump_*.sql

# If the above produces empty tables, import without specifying database:
mysql -u root -p<password> < /path/to/import/db_dump_*.sql

💡

If you used --databases in the dump command (recommended), the SQL file containsCREATE DATABASE and USE statements, so you can import without specifying the DB name.

Step 10 — Post-Migration Verification

sql

mysql -u root -p<password>

-- Verify DB exists
SHOW DATABASES;

-- Check replication (if applicable)
SHOW SLAVE STATUSG    -- Seconds_Behind_Master: 0

-- Compare table count with Step 2 baseline
SHOW TABLES FROM your_database_name;

-- Quick row count check on critical tables
SELECT COUNT(*) FROM your_database_name.your_critical_table;

💡

Table count and row counts in critical tables on the target must match the source baseline from Step 2.

Connecting with SSL (MariaDB)

When SSL is required to connect to a secured MariaDB instance:

bash

mysql \
  --ssl-ca=/path/to/certs/ca.crt \
  --ssl-cert=/path/to/certs/server.crt \
  --ssl-key=/path/to/certs/server.key \
  --host=your-db-server.domain.com \
  -u your_username -p

Quick Reference

sql

-- Check users in a specific database
SELECT user FROM mysql.db WHERE db = 'your_database_name';

-- Check all user privileges
SELECT user, host, authentication_string FROM mysql.user;

-- Show grants for a specific user
SHOW GRANTS FOR 'appuser'@'%';

All postsMySQL · MariaDB · Migration · mysqldump