Migrating a MySQL or MariaDB database to ElectraDB

Before you start

Moving a database from a MySQL-compatible server, MariaDB included, to an ElectraDB MariaDB database takes three steps: dump the database with the tools you already use, load the dump into ElectraDB, and switch your application's connection string. You do it once as a rehearsal, and once more for real while writes are paused. This guide works for servers you run yourself and for managed services that let you run mysqldump or mariadb-dump against them.

Size

On the source, this query gives the size of a database's tables and indexes (here one called shop):

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) AS gb
FROM information_schema.tables
WHERE table_schema = 'shop';

Pick a plan with clear room above that (see pricing). When a database reaches its plan's disk it becomes read-only (see When your database is full), and the server also keeps the binary log of recent changes on that disk, so a large import briefly takes more space than the data alone.

Schemas and compatibility

• One database per ElectraDB database. Each ElectraDB database has one schema for your data, app, and one user, u_<id>, with all privileges on it. If your server holds several databases, move each into its own ElectraDB database, or merge them if your application allows.
• Compatibility. MariaDB runs most MySQL applications unchanged, but not every MySQL feature behaves the same: for example, a JSON column is stored as text with a validity check, and a few functions and options differ. Run your application's tests against the rehearsal copy before you plan the cut-over.

Then create the database: choose New database, then MariaDB, a region and a plan. When it is live, copy the connection string and the password from its page. The connection string has this form:

mysql://u_<id>@<id>.db.electradb.com:<port>/app?ssl-mode=VERIFY_IDENTITY

Dump the source database

Dump one database, by its name and without --databases, so the file holds no CREATE DATABASE or USE statement and loads into app. With the MySQL tools:

mysqldump --host=<source host> --port=3306 --user=<source user> --password \
  --single-transaction --routines --triggers --hex-blob \
  --default-character-set=utf8mb4 --set-gtid-purged=OFF \
  shop > dump.sql

From MariaDB, the same with its own client:

mariadb-dump --host=<source host> --port=3306 --user=<source user> --password \
  --single-transaction --routines --triggers --hex-blob \
  --default-character-set=utf8mb4 \
  shop > dump.sql

• --single-transaction takes a consistent copy of InnoDB tables without locking them, so the source keeps serving while you dump.
• --routines and --triggers include stored procedures, functions and triggers.
• --hex-blob writes binary columns as hexadecimal, so no byte is changed by a character set on the way.
• --set-gtid-purged=OFF matters on servers other than MariaDB that have GTIDs turned on. Without it the dump starts by changing global replication settings, which needs privileges no managed database gives you.
• If mysqldump 8.0 or later stops with an error about the PROCESS privilege, add --no-tablespaces.

Prepare the dump

Views, triggers and routines in a dump name the user who defined them, for example DEFINER=`admin`@`%`. Creating an object for another user needs privileges your ElectraDB user does not have, so remove those clauses. The object is then defined by the user who loads it:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' dump.sql > dump-clean.sql

Tables created on servers other than MariaDB often use the collation utf8mb4_0900_ai_ci. If the import rejects a collation, replace it with MariaDB's closest equivalent, utf8mb4_uca1400_ai_ci, and load again:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_uca1400_ai_ci/g' dump-clean.sql

A dump of a large database is a large text file. Run the sed commands on a machine with disk to spare, or pipe the dump through them on its way to the import.

Load it into ElectraDB

Load the file with the mariadb client, which checks the certificate and the server's name against the certificate authorities your system already trusts:

mariadb --host=<id>.db.electradb.com --port=<port> --user=u_<id> --password \
  --ssl --ssl-verify-server-cert --max-allowed-packet=64M \
  app < dump-clean.sql

Or with the mysql client from the MySQL tools, version 8 or later, which needs your system's certificate authorities named (/etc/ssl/certs/ca-certificates.crt on Debian and Ubuntu, /etc/pki/tls/certs/ca-bundle.crt on Fedora and RHEL, /etc/ssl/cert.pem on macOS):

mysql --host=<id>.db.electradb.com --port=<port> --user=u_<id> --password \
  --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
  --max-allowed-packet=64M app < dump-clean.sql

The server accepts packets up to 64 MB, which is enough for the multi-row inserts a dump writes unless a single row is larger than that. Run the import from a machine close to the database's region: it sends every row over the network, and a long distance makes each batch wait.

When the import finishes, compare the two copies. Count the rows of your most important tables on both sides, and list the tables and routines in app:

SELECT COUNT(*) FROM orders;
SHOW TABLES;
SHOW PROCEDURE STATUS WHERE Db = 'app';
SHOW FUNCTION STATUS WHERE Db = 'app';

Then point a staging copy of your application at the new database and run your tests. Note how long the dump and the load took: that is roughly how long writes will be paused at the cut-over.

Switch your application over

We do not offer replication into an ElectraDB database from a server outside it: your user cannot configure the server as a replica of yours. So the last copy is taken while nothing writes to the source:

1. Rehearse the whole move as above, and fix whatever the import or your tests found.
2. Freeze writes on the source: put the application in maintenance mode, stop its workers and scheduled jobs, or set the source server read-only if you run it yourself (SET GLOBAL read_only = ON; accounts with administrative privileges can still write, so stop anything that connects as one).
3. Take the final dump and prepare it with the same commands.
4. Load it. The dump starts each table with DROP TABLE IF EXISTS, so it replaces the rehearsal's tables; tables you removed from the source since the rehearsal stay behind, so drop those yourself. Or load into a new, empty database and delete the rehearsal one.
5. Check the row counts again, then switch the connection string in your application to the ElectraDB one and bring it back up.

Keep the source as it is for a few days, read-only, so you can compare or go back if something turns up.

After the move

• Set a connect timeout of 5 to 10 seconds in your driver or pool, as the quickstart shows for each client. A failed server stops answering rather than refusing, and on high-availability plans writes come back on the replica after about 10 to 12 seconds.
• The imported data is backed up like any other write: the binary log is archived every 30 seconds and a full backup runs daily, so you can restore to any second of the last 7 days into a new database.
• Limit who can connect with the IP allowlist, and, if you want, require a client certificate as well as the password.
• On high-availability plans, choose the replication mode: semi-synchronous (the default) loses no acknowledged commit in a failover, asynchronous commits faster.

To move a PostgreSQL database instead, see Migrating PostgreSQL from Heroku, Supabase or Neon. For what the MariaDB databases include, see the announcement.