Sometimes the hardest problems in infrastructure are actually the simplest ones. Not because they are complicated, but because we forget the exact commands when we need them.

Migrating a MariaDB database from one server to another is one of those things. You’ve done it many times, but when you need it again six months later, you end up searching your own notes.

Not as dificlut or tedius restoring master slave without GTID, but sometimes I forgot how to reproduce the steps.

• Reseeding MariaDB Master–Slave Replication (Non-GTID) While the Master Stays Online

So here’s a quick and practical note for future me (and maybe useful for others too).

The Classic Method: Dump > Transfer > Restore

The safest and most common approach is:

1. Dump the database from the old server
2. Copy the dump file to the new server
3. Restore the database

Simple and reliable.

Step 1, Dump the Database

On the source server, create a dump file.

Basic command

mysqldump -u root -p dbname > dbname.sql
Code language: CSS (css)

But for production systems, it’s better to use a few additional flags.

mysqldump -u root -p \
  --single-transaction \
  --quick \
  --routines \
  --triggers \
  --events \
  dbname > dbname.sql
Code language: CSS (css)

Why these options?

• –single-transaction
  Keeps the dump consistent without locking tables (important for InnoDB).
• –quick
  Streams rows instead of loading everything into memory.
• –routines / triggers / events
  Includes stored procedures, triggers, and scheduled events.

Without these flags, some database logic might silently disappear.

Step 2, Transfer the Dump File

Copy the dump file to the new server, You can use FIlezilla, SCP, XFtp and others

Example using scp.

scp dbname.sql user@newserver:/tmp/
Code language: JavaScript (javascript)

If the database is large, compress it first.

mysqldump -u root -p dbname | gzip > dbname.sql.gz
scp dbname.sql.gz user@newserver:/tmp/
Code language: JavaScript (javascript)

Compression can reduce transfer time significantly.

Step 3, Create the Database on the New Server

Login to MariaDB on the destination server:

mysql -u root -p

Create the database:

CREATE DATABASE dbname
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Then exit the MariaDB console.

Step 4, Restore the Database

Restore from the SQL dump:

mysql -u root -p dbname < dbname.sql
Code language: CSS (css)

If you used compression:

gunzip < dbname.sql.gz | mysql -u root -p dbname

After this finishes, your database should be fully restored.

Dumping All Databases

If you need to migrate the entire server, use:

mysqldump -u root -p --all-databases > alldb.sql
Code language: CSS (css)

And restore with:

mysql -u root -p < alldb.sql
Code language: CSS (css)

Alternatives, direct Server to Server Migration

If both servers can connect via SSH, you can skip the intermediate file entirely.

mysqldump -u root -p dbname | ssh user@newserver "mysql -u root -p dbname"
Code language: JavaScript (javascript)

This streams the dump directly into the new database, It’s fast and very convenient for quick migrations.

Things I Always Double-Check

After restoring a database, I usually run a few quick checks.

List tables:

SHOW TABLES;

Check approximate row counts:

SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema='dbname';
Code language: JavaScript (javascript)

Just to make sure nothing obvious is missing.

Final Thoughts

Database migrations sound like a big operation, but most of the time they come down to a very simple workflow:

dump > copy > restore

It’s one of those small operational tasks that every sysadmin or developer does regularly, yet still forgets the exact commands.

So this post is mostly a note for my future self, because simple things deserve good documentation too.