How to Configure MariaDB Schema Migration

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mariadb schema migration by tracking change scripts in git. A runner tool then checks a log table and runs new DDL statements. Teams often use a CI pipeline or a local runner for this task. Many teams grant the migration user broad admin rights to avoid failed runs. Those wide permissions let one bad script drop tables or read private rows. How a mariadb schema migration works in practice A migration setup needs three main parts to work well. You need a source folder of ordered s

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up a mariadb schema migration by tracking change scripts in git. A runner tool then checks a log table and runs new DDL statements. Teams often use a CI pipeline or a local runner for this task. Many teams grant the migration user broad admin rights to avoid failed runs. Those wide permissions let one bad script drop tables or read private rows.

How a mariadb schema migration works in practice

A migration setup needs three main parts to work well. You need a source folder of ordered scripts, a runner, and a database role. Each script holds structural changes like adding columns or building new indexes. The runner logs in to MariaDB and reads a dedicated tracking table. This table lists every change script that ran in the past.

The runner compares the folder scripts against the tracking table rows. It runs only the new scripts in strict order by version number. When a script finishes, the runner writes a new row to the tracking table. It also stores a hash of the script file in that row. On the next run, the tool checks those hashes to spot altered files. If an old file changed, the runner stops and alerts the team.

To run these scripts, the database role needs schema change rights. You must grant rights to create, alter, and drop tables or indexes. You should scope these rights to the single target database schema. Separate this migration role from the normal app role that reads and writes data. The daily app role should never hold rights to alter table structures.

Planning for locks and online schema changes

MariaDB handles table changes in a few different ways. Some changes update metadata fast without moving any table rows. Other changes must rebuild the whole table and copy every row. A full table rebuild can lock writes and stall live app traffic. Long locks cause app timeouts when a table holds millions of rows.

Engineers must test how each DDL statement behaves on staging data first. Native MariaDB settings let you ask for lock-free execution on supported alters. If an alter requires a heavy lock, the database rejects the command fast. This check prevents a surprise outage during peak business hours. For massive tables, teams often use external online schema change tools. These tools copy data in small chunks and swap tables at the end.

End-to-End Encryption + API Schema Validation: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native database roles fall short

Native MariaDB roles solve the basic problem of who can log in. They also separate schema change users from daily app users. Still, database roles are static and apply to whole command types. If a role can alter a table, it can alter any column. If a role can drop a staging table, it can drop a live one.

Native permissions decide who logs in, not what individual statements do in flight. A shared CI migration key often stays active day and night. When an engineer needs to fix a broken migration, they often log in directly. They use the same high-power role to run manual fixes or read data. During that session, nothing stops a query from dumping emails or card numbers. Static database grants also cannot require a peer check before a drop runs.

Inspect every MySQL query and response in flight

Static grants and network rules block bad logins. They cannot pause a risky query for review or mask emails in a result set. By design, hoop.dev sits inline in the MySQL protocol as a sidecar for your MySQL. For every plain query, prepared statement, or cursor an agent or human runs in production, hoop.dev decides to pass, mask, block, or hold it for review.

First, hoop.dev guardrails block banned actions before MySQL runs them, and an AI Analyzer you configure can score intent or escalate for approval. Next, hoop.dev data masking rewrites sensitive string columns in results in memory, and every verdict goes to the audit trail. Find the open-source binary on GitHub.

Frequently asked questions

How do you roll back a failed MariaDB schema migration?

Most DDL statements in MariaDB trigger an implicit commit and cannot roll back inside a transaction. You must write a separate undo script that reverses the structural change and run it forward.

Why did my migration fail with a checksum mismatch error?

A checksum error means someone edited a migration script after the runner already applied it. Restore the original file to match the tracking table hash, then put your new changes in a fresh script.

Should the app run schema migrations automatically on startup?

Running migrations on app startup works for small projects, but it fails when multiple app nodes boot at once. Run schema changes as a separate step before deploying new app code to avoid race conditions.

The query runs. The emails stay hidden.