Backing up MySQL and MariaDB databases and keeping them available

What happens if the database server fails? And can yesterday's data really be brought back? We run MySQL and MariaDB with backups whose restore we test, with replication and, where it fits, with a Galera cluster. Slow queries and pending updates are part of it.

Book an intro call Databases at a glance

MySQL and MariaDB since 2012, also in Galera clusters
Full-stack and DevOps experience since 2012
Reply within one working day

When a look at the database pays off

The database is often the part of a system that nobody touches as long as it runs. Typical reasons to change that:

Nobody knows whether the last backup can actually be restored.
The database runs on a single server. If it fails, the application stops.
Pages and reports get slower as more data comes in.
The version is old, and nobody dares to start the update.
Whoever set up the replication has left the company.

This page belongs to the service Linux operations and infrastructure. Which databases our engineers know and how we migrate between them is described in the overview of databases.

A database cylinder joined by a line to a dial gauge; the needle points into the orange part of the scale

Backups that can be restored

A backup is only worth something once it can be restored. We set up the backups to suit your database and test the restore regularly:

full backups during operation, without stopping the application
binary logs, which, depending on the setup, can also bring back a state shortly before an error
copies outside the database server and outside the production zone
a restore test on a separate system, with the time it takes measured
a notification when a backup fails

You receive the result of every test in writing. That way you know how old the data would be at most after a failure, and how long the restore takes.

On the left a database drawn solid, on the right its dashed copy; an arrow at the top leads to the copy, an orange curved arrow at the bottom leads back to the original

Backup, replication or cluster: what protects against what?

Question	Tested backup	Replication	Galera cluster
Protects against	data loss through errors, deletion, defects or attacks, back to the state of the last backup	the failure of the main server: a replica takes over after the switchover	the failure of a node: the remaining nodes keep working
Does not protect against	the downtime until the restore is complete	deleted data, because the deletion is replicated too. With asynchronous replication, the latest changes may be missing	deleted data and faulty updates, because they reach all nodes
Fits when	always, as the basis of every solution	a short interruption during the switchover is acceptable, or read access is to be spread across servers	the application has to keep running when a server fails, and three servers with a stable network are available

Galera cluster: when it fits and what it does not replace

A Galera cluster connects several MySQL or MariaDB servers. Every change is transferred to all nodes when it is saved, and every node can answer queries. If one server fails, the others keep working. Our engineers set up and run such clusters.

A cluster does come with conditions: at least three nodes or two with an arbitrator, a stable network and InnoDB tables with a primary key. Large transactions and many simultaneous writes to the same records slow it down. That is why replication with a planned switchover is often enough. We clarify this before we build anything.

And a cluster does not replace a tested backup. It protects against the failure of a server, not against deleted data. That is why we always plan the backup as well.

Three linked databases of a cluster all carry the same cross, a deletion is copied everywhere; the separate backup on the right is outlined in orange and carries no cross

Slow queries and pending updates

When the application gets slower, we look for the cause before anyone buys a bigger server. We analyse the slowest queries, read their execution plans, add missing indexes and adjust the configuration. If the cause lies in the code, for example a great many small queries per page, we fix it there. Our engineers know ColdFusion, Lucee and PHP for that too.

We apply new major versions of MySQL and MariaDB to a copy first and test the application against it. Where possible, the new server runs alongside the old one through replication. The switchover takes place in a time window agreed with you, and the old state remains as a way back.

A database cylinder, next to it a stopwatch and five horizontal bars of different lengths like query timings; the longest bar is orange

How we proceed

Database check

We review backups, replication, configuration and the slowest queries. We restore the last backup as a test on a separate system. You receive the findings in writing.
Plan

The findings lead to a recommendation with the effort involved: better backups, replication or a cluster, depending on the outage your application can tolerate. You decide what is implemented.
Implementation

We first build the solution alongside production and test it with a copy of the data. The switchover takes place in an agreed time window, with a way back.
Operation

If you wish, we then look after the database within Linux operations: backups and replication monitored, regular restore tests, updates and a runbook for your team.

The first step: a database check with a restore test

Database check: MySQL and MariaDB

For companies whose application depends on a MySQL or MariaDB database and who want to know what happens in a failure and whether yesterday's data can really be restored.

Backups: what is backed up, how often and where the copies are kept
A restore test on a separate system, with the time it takes measured
Replication and resilience: what exists and what is missing
The slowest queries and missing indexes
Version, support status and configuration
Recommendation: backup, replication or cluster, depending on your needs

A cluster such as MariaDB Galera protects against the failure of a server, not against deleted data. That takes tested backups, which is why we start with the restore test. We agree with you on the system it runs on. We change nothing on your production database.

What the result contains

A written report with the result of the restore test, the risks in order of priority and a recommendation with the effort involved. The report is yours. If you wish, we then set up the solution, including a Galera cluster.

from €1,900 excl. VAT

Typically 2 working days

Request a database check

From our projects

SQL indexes in a shop database: from full scans to microseconds

A shop database without indexes worked as long as the tables were small. At 500 000 orders and 1.75 million order items every filter and every join came down to a full scan. We measured exactly what the missing indexes cost, added the right ones, and documented the traps that make an index useless even though it exists.

Questions about backups, replication and clusters

How do you check whether our data can be restored?

We restore the latest backup on a separate system agreed with you, not on the production server. We measure how long the restore takes. Then we check whether the database is complete: tables, record counts, samples of important data and, where possible, starting the application against the copy.

We record the result in writing, even if something is missing. In ongoing operations we repeat the test regularly, so that a backup cannot become unusable unnoticed.

Does a Galera cluster replace data backups?

No. A cluster protects against the failure of a server, not against deleted data. Every change reaches all nodes, including an accidental deletion or a faulty update. The data is then missing on all servers at once.

That takes tested backups outside the cluster. Depending on the setup, binary logs can also bring back a state shortly before the error. Only a restore test shows whether that works, which is why our database check starts with one.

When is a Galera cluster worth it?

When the application has to keep running even if a database server fails, and an interruption of a few minutes is already too much. For that, Galera needs at least three nodes or two nodes with an arbitrator. It also needs a stable network with low latency and InnoDB tables with a primary key.

With very large transactions or many simultaneous writes to the same records, a cluster reaches its limits. A simpler solution then often fits better: replication with a planned switchover, or a fast, tested restore. What suits your application is set out in the recommendation of the database check.

Our application is getting slower. Is the database to blame?

Often, but not always. We first look at the slowest queries, their execution plans and the indexes. Then come the configuration, such as the memory for data and indexes, and locks between simultaneous accesses.

The cause often lies in the application: a page sends a great many small queries, or a report loads whole tables. Because our engineers also know ColdFusion, Lucee and PHP, we fix it where it originates. A bigger server is then often unnecessary.

Do you cover weekend duty from time to time?

Yes. Support and emergencies are covered around the clock (24/7), weekends included. For other enquiries we are available Monday to Friday, 9:00 to 17:00 Central European Time, and we reply within one working day.

In 20 minutes we work out which first step fits your system

You tell us the goal and the current state, and we say openly whether and how we can help. Reply within one working day, no obligation.

Book an intro call How we work

Phone (German, Polish, English) +48 501 250 220

Email Email

Reachable: Monday to Friday, 9:00 to 17:00 Central European Time; support and emergencies around the clock (24/7)