# How to restore a MariaDB database on a new server > Move one MariaDB database to a new server with its users, grants, routines and events: rehearse the import, redo it at the cutover, check every table. Source: https://rack4.io/tutorials/restore-mariadb-database · Updated: 2026-11-07 Difficulty: intermediate · Time: about 40 minutes · Tested on: Ubuntu 24.04 LTS, Debian 12 Oldstable Prerequisites: - Two servers running Ubuntu 24.04 or Debian 12 with MariaDB installed - Root or a user with sudo on both - SSH from the old server to the new one 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](https://rack4.io/help/move-to-a-larger-server). To check a backup rather than move a database, see [Test a backup restore](https://rack4.io/help/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: | What | In this tutorial | | --- | --- | | Old server, still live | `203.0.113.10` | | New server | `203.0.113.50` | | The database | `shopdb` | | The app's user, with all rights on `shopdb` | `'shopapp'@'localhost'` | | A reporting user, with rights on one view and one function | `'reporter'@'localhost'` | ## 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: ```bash 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: ```bash 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:** | In the dump | Not in the dump: you bring it across | | --- | --- | | Tables, data, indexes, foreign keys, `AUTO_INCREMENT` positions | Users and their passwords | | Views, procedures, functions (`--routines`), triggers, events (`--events`) | Grants on the database, its tables and its routines | | Each view's, routine's, trigger's and event's `DEFINER` | Whether the event scheduler is on | | | The database's character set, unless you dump with `--databases` | ## 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: ```bash 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';" ``` ```text User Host level shopapp localhost database reporter localhost table order_totals reporter localhost routine customer_spent ``` ```bash 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';" ``` ```text 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: ```bash 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 ``` ```text 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: ```bash 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: ```bash 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](https://rack4.io/help/move-to-a-larger-server) sets one up): ```bash 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: ```bash 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: ```bash 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](https://rack4.io/help/move-to-a-larger-server). 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: ```bash #!/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: ```bash sudo bash /root/verify-mariadb.sh > /root/verify-old.txt ``` On the new server, run it again and compare: ```bash 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: ```text 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](https://rack4.io/help/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: ```bash 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](https://rack4.io/tutorials/restic-backup-s3) or [borgmatic to a second server](https://rack4.io/tutorials/borgmatic-backup-setup), and [test that backup](https://rack4.io/help/test-a-backup-restore) now and then. - Moving PostgreSQL too? [Restore a PostgreSQL database on a new server](https://rack4.io/tutorials/restore-postgresql-database) covers roles and extensions. - MariaDB's documentation on [mariadb-dump](https://mariadb.com/kb/en/mariadb-dump/) lists every option used here.