How to back up a MariaDB database to a .sql file

A .sql dump of a MariaDB database is the simplest kind of backup there is, and Server by Zaweba makes one with Export SQL on its Databases page. It writes the dump with mariadb-dump, including routines and triggers, in a single transaction and with binary data stored as hex. The result is one text file you can keep, copy or load back in later.

Local databases are easy to forget about. They sit on your PC, they work, and then one day an experiment goes wrong or a test script empties a table. A dump taken beforehand turns that from a lost afternoon into a minute's work.

Making the export

Make sure MariaDB is running; its card on the control panel shows a status light. Open the Databases page, which lists your databases with their tables and size, choose the one you want and use Export SQL. Pick where to save the file and give it a name.

A good name says what and when, for example the site name and the date. When you have several dumps of the same database, you'll be glad you can tell them apart without opening them.

What's in the file

The dump is SQL text: statements that create each table and then insert its rows. Server asks mariadb-dump for a few things that make it a better backup.

Routines and triggers are included. These are pieces of logic stored inside the database itself. A plain export of tables alone would leave them out, and a site that depends on them would break after a restore.

The dump is made in a single transaction, so it's a consistent snapshot of one moment, even if something writes to the database while the export runs.

Binary data, such as images stored in a table, is written as hex. That keeps it safe in a text file, where raw bytes could otherwise be mangled.

Restoring from a dump

To bring a backup back, use Import SQL on the same Databases page. The import is streamed, so large files are fine. If you want to keep the current state as well, make a fresh database with New database and user and import the dump into that, then compare the two in Adminer before you decide which to keep.

It's worth doing one test restore early on. A backup you've never loaded is a backup you hope works.

When to take a dump

Before anything risky: a migration script, an upgrade of a content system, a bulk edit in Adminer, or switching a site to a newer PHP version. All of those can change data in ways you didn't intend.

Before deleting a database, too. Server asks for confirmation, uses a red button and makes Enter mean Cancel, but once you've confirmed, the data is gone. Removing a site offers to delete its database as well, so if there's any chance you'll want it, export first.

And at the end of a good day's work on a project, if the database holds content you've built up by hand.

Keeping backups safe

Server's own databases live in its data folder, alongside its configuration and logs. Keep your dumps somewhere else, such as a documents folder that your normal backup already covers, so losing one doesn't lose both.

Dumps can contain passwords, email addresses and other private details from a site. Never put one inside a site's folder, where a web server could serve it, and don't upload one to a web host by accident. Server only listens on 127.0.0.1, so locally nothing can fetch it, but files have a habit of travelling.

A dated .sql file in a safe folder is a small habit, and Export SQL in Server by Zaweba makes it a quick one.