How to restore a MariaDB database on a new server

To restore a MariaDB database on a new server, dump it with mariadb-dump --single-transaction --routines --triggers --events --hex-blob, create its users on the new server before the import, create an empty database with the same character set, import the dump as root, and only then apply the users' grants. A one-database dump holds the tables, data, views, stored procedures and functions, triggers and events, but not the users who log in or their grants. Without the users, the import still finishes without an error; the app then fails on its first write, because every trigger and procedure runs as a user that doesn't exist.

This tutorial moves one application database: a rehearsal first, with the old server still live, then a final import at the cutover that replaces the rehearsal copy, then checks that every table matches. For the rest of a server move (files, DNS, HTTPS), follow Move to a larger server. To check a backup rather than move a database, see Test a backup restore.

What we tested: on 7 November 2026, every command here ran against MariaDB 10.11.19 → 10.11.19 (the series Ubuntu 24.04 and Debian 12 ship) and 10.11.19 → 11.4.13, in throwaway Docker containers from MariaDB's official images. The test database had InnoDB tables with a foreign key and a binary column, a view, a stored procedure, a stored function, a trigger and an event, all with the app's user as their definer, plus a second user with grants on one view and one function. We ran the rehearsal, then inserted, updated and deleted rows and added a table on the old server, wrote a test row on the new one, ran the final import over the populated copy and compared every table. On a real server you run them through sudo and systemctl, as written below. What we didn't test is listed at the end.

The examples use these names. Replace them with yours:

Before you start: versions, tables and what a dump leaves out#

Versions. The simplest move is to the same series: Ubuntu 24.04 and Debian 12 both ship MariaDB 10.11. Check both sides:

sudo mariadb -e "SELECT VERSION();"

Moving to a newer long-term series (11.4, 11.8) with a dump is the supported upgrade direction; our 10.11 → 11.4 test imported and checked out the same. A dump into an older series isn't something MariaDB supports. Ours happened to import into 10.11 from 11.4, but a real app's schema can use features or collations the older server lacks, so don't plan on it.

Storage engines. --single-transaction gives a consistent copy of InnoDB tables while the app keeps writing. It doesn't for other engines. Check:

sudo mariadb -e "SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_TYPE = 'BASE TABLE';"

If every table says InnoDB, the rehearsal dump can run while the app is live. If any says MyISAM or Aria, its rehearsal copy may be slightly inconsistent; the final dump is taken with writes stopped, so it is consistent either way.

What a one-database dump includes, and what it doesn't:

List the users the database needs#

Two kinds of user matter: those with grants on the database, and those named as DEFINER. On the old server:

sudo mariadb -e "SELECT User, Host, 'database' AS level FROM mysql.db WHERE Db = 'shopdb' UNION SELECT User, Host, CONCAT('table ', Table_name) FROM mysql.tables_priv WHERE Db = 'shopdb' UNION SELECT User, Host, CONCAT('routine ', Routine_name) FROM mysql.procs_priv WHERE Db = 'shopdb';"

User	Host	level
shopapp	localhost	database
reporter	localhost	table order_totals
reporter	localhost	routine customer_spent

sudo mariadb -e "SELECT DEFINER, 'routine' AS used_by FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'shopdb' UNION SELECT DEFINER, 'trigger' FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'shopdb' UNION SELECT DEFINER, 'event' FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'shopdb' UNION SELECT DEFINER, 'view' FROM information_schema.VIEWS WHERE TABLE_SCHEMA = 'shopdb';"

DEFINER	used_by
shopapp@localhost	routine
shopapp@localhost	trigger
shopapp@localhost	event
shopapp@localhost	view

A user with global rights (ON *.*, such as a backup user) appears in neither list; add it if it should move too. Then write the users and their grants into two files. They're separate because grants on a table or routine fail until that table or routine exists:

sudo mariadb -N -e "SHOW CREATE USER 'shopapp'@'localhost'; SHOW CREATE USER 'reporter'@'localhost';" | sed 's/$/;/' > /root/users.sql
sudo mariadb -N -e "SHOW GRANTS FOR 'shopapp'@'localhost'; SHOW GRANTS FOR 'reporter'@'localhost';" | sed 's/$/;/' > /root/grants.sql
cat /root/users.sql /root/grants.sql

CREATE USER `shopapp`@`localhost` IDENTIFIED BY PASSWORD '*6C18924D3B2D029A4D4BAFB930173A9F342A451B';
CREATE USER `reporter`@`localhost` IDENTIFIED BY PASSWORD '*8FEF0E7F0AF51AEDAEEB8B7665FE819953070EBC';
GRANT USAGE ON *.* TO `shopapp`@`localhost` IDENTIFIED BY PASSWORD '*6C18924D3B2D029A4D4BAFB930173A9F342A451B';
GRANT ALL PRIVILEGES ON `shopdb`.* TO `shopapp`@`localhost`;
GRANT USAGE ON *.* TO `reporter`@`localhost` IDENTIFIED BY PASSWORD '*8FEF0E7F0AF51AEDAEEB8B7665FE819953070EBC';
GRANT SELECT ON `shopdb`.`order_totals` TO `reporter`@`localhost`;
GRANT EXECUTE ON FUNCTION `shopdb`.`customer_spent` TO `reporter`@`localhost`;

The password hashes come across as they are, so each user keeps its password. The files hold those hashes: keep them in /root and delete them when you're done. mariadb-dump --system=users also exists, but it writes every account on the server, root included, which you don't want on the new one.

Note two more things from the old server while you're there:

sudo mariadb -e "SHOW CREATE DATABASE shopdb\G" -e "SELECT @@event_scheduler;"

The first gives the character set and collation (utf8mb4 and utf8mb4_unicode_ci here). The second says whether events run (ON) or not (OFF, the default).

Dump the database#

On the old server:

sudo mariadb-dump --single-transaction --routines --triggers --events --hex-blob shopdb > /root/shopdb.sql

• --single-transaction: a consistent snapshot of InnoDB tables without locking the app out.
• --routines and --events: procedures, functions and events. They are left out without these flags. Triggers are included by default; --triggers just says so.
• --hex-blob: binary columns written as hex (0x89504E47...), so nothing in the file depends on character-set conversion.

The dump ends with a -- Dump completed on ... line. If tail -1 /root/shopdb.sql doesn't show it, the dump stopped early.

Send the files to the new server#

From the old server (as root, with an SSH key the new server accepts; Move to a larger server sets one up):

rsync -a /root/shopdb.sql /root/users.sql /root/grants.sql root@203.0.113.50:/root/

Compare sha256sum /root/shopdb.sql on both servers.

Rehearse the import#

On the new server, in this order: users, an empty database, the dump, then grants:

sudo mariadb < /root/users.sql
sudo mariadb -e "CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
sudo mariadb shopdb < /root/shopdb.sql
sudo mariadb < /root/grants.sql

• Users first, because every trigger, procedure, event and view runs as its DEFINER. Import as root (sudo mariadb), since only an admin may create objects whose definer is another user.
• The same character set as SHOW CREATE DATABASE showed on the old server. Tables keep their own character sets from the dump either way; this sets the default for new ones.
• Grants last, once the view and function they name exist. Run in the wrong order, it stops with ERROR 1146 (42S02) at line 4: Table 'shopdb.order_totals' doesn't exist.

The mariadb client stops at the first error and prints it with its line number. No output means every statement ran.

If the old server had @@event_scheduler ON, the new one is probably OFF: events came across but don't run. Leave it off until the cutover, so a rehearsal copy doesn't run scheduled jobs on data you're about to replace.

Now log in as the app does and use it. The trigger and procedure only work if their definer exists:

mariadb -u shopapp -p shopdb -e "CALL place_order('carol@example.com', 3.00); SELECT order_count FROM customers WHERE email = 'carol@example.com';"
mariadb -u reporter -p shopdb -e "SELECT shopdb.customer_spent('alice@example.com');"

Point a copy of the app at the new database and click through it, as in testing before anyone else sees it. Anything written here is replaced at the final import.

Check the data#

Counting a couple of tables catches an import that stopped early. To catch a table that came across wrong, compare every table's exact row count and checksum, and the number of routines, triggers, events and views, on both servers. Save this as /root/verify-mariadb.sh on both:

#!/bin/bash
# Exact row count and checksum of every table in one database.
db=shopdb
for t in $(mariadb -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE' ORDER BY TABLE_NAME"); do
  rows=$(mariadb -N -e "SELECT COUNT(*) FROM \`$db\`.\`$t\`")
  sum=$(mariadb -N -e "CHECKSUM TABLE \`$db\`.\`$t\`" | cut -f2)
  echo "$t $rows $sum"
done
mariadb -N -e "SELECT 'routines', COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='$db'
  UNION ALL SELECT 'triggers', COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='$db'
  UNION ALL SELECT 'events', COUNT(*) FROM information_schema.EVENTS WHERE EVENT_SCHEMA='$db'
  UNION ALL SELECT 'views', COUNT(*) FROM information_schema.VIEWS WHERE TABLE_SCHEMA='$db'"

It uses COUNT(*), not the TABLE_ROWS column, which is only an estimate for InnoDB. Run it on the old server and copy the result across:

sudo bash /root/verify-mariadb.sh > /root/verify-old.txt

On the new server, run it again and compare:

sudo bash /root/verify-mariadb.sh > /root/verify-new.txt
diff /root/verify-old.txt /root/verify-new.txt && echo "no differences"

After the rehearsal, differences are expected: the old server has kept taking writes. After the final import, before the app starts, it should print no differences. Here is what a difference looks like, from our test, after a test order was placed on the new server:

3,4c3,4
< customers 5002 1633197664
< orders 20007 2473476669
---
> customers 5003 1864817119
> orders 20008 2847780952

CHECKSUM TABLE reads each table whole, so on big tables it takes a while; run it when the servers are quiet. In our test the checksums also matched between 10.11 and 11.4, but MariaDB doesn't promise that across versions or row formats: if only the checksum differs across a version change and the counts match, compare a few recent rows by hand before deciding the copy is wrong.

Do the final import#

Plan a few minutes of downtime: from when the old app stops to when the new one starts.

1. Stop the app on the new server (for example sudo systemctl stop myapp, with your own service and worker names), so nothing writes to the rehearsal copy while you replace it.

2. Stop writes on the old server. Stop the app, its workers and its cron jobs the same way. Then, as a guard for anything you missed:

   bash

   sudo mariadb -e "SET GLOBAL read_only = ON"

   Ordinary users' writes now fail with ERROR 1290 (HY000) ... The MariaDB server is running with the --read-only option so it cannot execute this statement. Admin accounts like root can still write, and mariadb-dump works as usual.

3. Dump again and send it, with the same mariadb-dump and rsync commands as before. Check the last line and the checksum.

4. On the new server, see what is still connected, then replace the database:

   bash

   sudo mariadb -e "SELECT ID, USER, HOST, DB, COMMAND, TIME FROM information_schema.PROCESSLIST WHERE DB = 'shopdb';"
   sudo mariadb -e "DROP DATABASE shopdb; CREATE DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
   sudo mariadb shopdb < /root/shopdb.sql
   sudo mariadb < /root/grants.sql

   The first command should print nothing. A session still in a transaction on one of the tables makes DROP DATABASE wait for it, by default for up to a day; stop whatever the USER and HOST columns point to first. The users stay; run grants.sql again anyway. On 10.11 the grants survived DROP DATABASE, but on 11.4 the grant on the dropped function was gone, and running the file again is harmless when they are already there.

5. Compare with verify-mariadb.sh on both servers as above. Expect no differences.

6. Turn on the event scheduler if the old server had it on: sudo mariadb -e "SET GLOBAL event_scheduler = ON", and add event_scheduler = ON under [mysqld] in /etc/mysql/mariadb.conf.d/50-server.cnf so it stays on after a restart.

7. Start the app on the new server, check it can write (place a test order, sign up a test user), and switch DNS as in Move to a larger server.

Why drop the database, rather than import over it? The dump's DROP TABLE IF EXISTS lines replace the tables that are in it, and routines and events are dropped and recreated too. Anything else stays. In our test, importing the final dump straight over the rehearsal copy finished without an error, and a table we had created on the new server after the rehearsal was still there. Dropping the database first leaves nothing from the rehearsal behind.

Keep the old database, and writes after the dump#

Leave the old server's database exactly as it is for a few days, read-only, along with the final dump. It is your way back and your reference if a number looks wrong.

Anything written to the old database after the final dump isn't on the new server. That is why writes stop first, and why read_only is there: it catches a worker or job you forgot to stop, unless that job logs in as an admin. Once the new server is live, it takes writes the old one never sees. If you go back to the old server after that, those writes stay on the new server, and nothing copies them back for you: compare the newest rows on both (for example SELECT MAX(id) FROM orders;) and move them across by hand or from the app. To make the old server writable again:

sudo mariadb -e "SET GLOBAL read_only = OFF"

Troubleshooting#

ERROR 1449 (HY000): The user specified as a definer ('shopapp'@'localhost') does not exist. The import ran before the users existed. It finished without complaint, but every trigger, procedure and view owned by that user fails when used. Run users.sql, then import again from a fresh DROP DATABASE/CREATE DATABASE, then grants.sql.

ERROR 1146 (42S02) at line 4: Table 'shopdb.order_totals' doesn't exist from grants.sql. The grants ran before the import. Run grants.sql again once the import is done.

ERROR 1045 (28000): Access denied for user 'shopapp'@'localhost' (using password: YES) on the new server. Wrong password, or the user was created for a different host: 'shopapp'@'localhost' and 'shopapp'@'%' are separate accounts. sudo mariadb -e "SELECT User, Host FROM mysql.user;" lists them; compare with the old server.

ERROR 1142 (42000): SELECT command denied to user 'reporter'@'localhost' for table .... The user has no grant on that table. If it worked on the old server, grants.sql hasn't run, or ran before the import.

What wasn't tested#

• Ubuntu's and Debian's own packages and systemctl: the test used MariaDB's official Docker images, run directly rather than through sudo and systemd. Logins went over the Unix socket, as localhost.
• MyISAM or Aria tables, MariaDB 11.8, and moving from MySQL to MariaDB or back.
• Keeping the event scheduler on through a restart with the 50-server.cnf line (we turned it on with SET GLOBAL only).
• Importing with an older client or MySQL's: dumps from current MariaDB releases start with a /*M!999999\- enable the sandbox mode */ line that some older clients don't understand.
• The rsync copy between two servers (the test copied the files between containers and compared checksums).
• Large databases: the test database was about 25,000 rows, so the timing tells you nothing about yours. Rehearse to find out how long your import takes.
• Going back to the old server after the new one has taken writes.

Next steps#

• Back up the new server from day one: restic to S3-compatible storage or borgmatic to a second server, and test that backup now and then.
• Moving PostgreSQL too? Restore a PostgreSQL database on a new server covers roles and extensions.
• MariaDB's documentation on mariadb-dump lists every option used here.