Planning Your Data Protection

SHIELD can perform MySQL backups in a few different ways, depending on your network throughput, access control, and personal preferences.

Local Agent

The simplest method is to run the SHIELD agent on the same box as the mysqld process, and use the local MySQL socket to connect to the database server.

If you want to protect more than one database host, you can install the agent on each. They will all then register with the SHIELD instance, with different identities, and you can configure backup jobs for each of them.

Remote Agent

You can also run the SHIELD agent somewhere else, and configure your backup jobs to connect to the MySQL host(s) over TCP, using either their public IPs, or (if you have configured them) internal Linode IPs.

This can be useful if you'd rather not load additional software on your database host(s). You can also reuse the external agent to protect multiple MySQL instances, since your backup jobs will specify the IP address of each.

Setting up a Dedicated MySQL DB User (optional)

While you can get by using either the superuser account (especially over the local UNIX domain socket) or the same account you use from your applications, you may want to consider setting up a specific, less privileged database user for performing backups via SHIELD.

To create such a user, you can use the following SQL commands, from the mysql terminal shell:

root@localhost:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER backups@localhost IDENTIFIED BY 'its-a-sekrit';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT LOCK TABLES, DROP, CREATE, SELECT, INSERT ON *.* TO backups@localhost;
Query OK, 0 rows affected (0.00 sec)

The above privileges are representative of normal use cases. Your mileage may vary, and you may need to GRANT additional privileges to suit your particular configuration.

System Prerequisites

SHIELD uses native database tooling to perform backup and restore operations against MySQL and MariaDB servers. For this to work, you will need to install the following tools, matched to the version of MySQL or MariaDB you are running:

  1. mysqldump for dumping structure and data from one or more databases.
  2. mysql for replaying the dumped SQL commands to the server, upon restore.

Configuring Backup Jobs

With MySQL / MariaDB, you can constrain how much or how little of the full set of databases you protect. For most use cases, you'll want to backup all of the databases, but you can limit your export to a single database.

It's entirely up to you.


Three parameters govern the authentication to your MySQL server: MySQL Host, MySQL Username, and MySQL Password.

You are encouraged to set up a dedicated user to perform backup / restore tasks as. It helps with access control, compliance with least-privilege policies, and auditing.

Backing up All Databases

The default configuration of the MariaDB / MySQL backup plugin is to protect all databases found, and include all of the tables, their structure, and data, in each snapshot.

You dont' have to do anything — just make sure to leave the Database to Backup parameter blank (its default value)!

Backing up a Single Database

If you only want to back up a single database, specify the name of that database in the Database to Backup parameter when you configure your data system. This will cause the MySQL / MariaDB plugin to only emit CREATE TABLE and INSERT INTO statements for that particular database.

Note: this parameter has no bearing on restores; SHIELD will restore all databases found in the snapshot archive, regardless of what you set for Database to Backup.

Restoring MySQL from a Snapshot

MySQL / MariaDB SHIELD snapshots consist of plain SQL statements for recreating and repopulating databases and tables. These are replayed during restore operations by the mysql utility.

It is worth noting that mysql will run whatever SQL statements are in the snapshot! If you run a full backup, including all databases, and then reconfigure the data system to specify a single database, the archive created before the reconfiguration will still have all databases — more importantly, it will have all of the accompanying DROP TABLE statements.

Performance Notes

Extracting all of the data out of busy MySQL / MariaDB server can severly impact responsiveness, availability, and fitness of the database host if you are not careful. Most of the point of having a relational database is to provide accurate and fast sub-selection of the whole data set, and MySQL / MariaDB is tuned to handle this workload.

If running backups against your primary database server proves to be too detrimental, you may want to spin up a read replica, and run your backup operations against that. SHIELD has specific support for this approach.

With a read replica, one host acts as the primary database server; it processes writes to the database (INSERT, UPDATE, and DELETE queries). For every change to the underlying data, the primary database writes a record to its binlog, which the read replica reads constantly. When new binlog entries appear, the replica makes the appropriate changes in a copy of the data set.

In a data protection setting, this allows you to point SHIELD at the read replica for backup operations, and the primary database server for restores. If the read replica experiences slowdowns due to the load of performing a snapshot, database clients won't be impacted. Instead, replication lags a little, and when the backup job finishes, the replica will catch back up.

To enable this, you'll need to ensure that the same user credentials work against both your primary database server and your read replica, and then set the MySQL Read Replica parameter to the hostname / IP, and TCP port of the read replica.

Configuration Reference

This section details all available configuration parameters for the MySQL / MariaDB data protection plugin.

MySQL Host
The hostname or IP address of your MySQL server.
MySQL Username
Username to authenticate to MySQL as.
MySQL Password
Password to authenticate to MySQL as.
Database to Backup
Limit scope of the backup to include only this database. By default, all databases will be backed up.
Additional mysqldump options
You can tune mysqldump (which performs the backup) by specifying additional options and command-line arguments.
MySQL Read Replica
An optional MySQL replica (possibly readonly) to use for backups, instead of the canonical host. Restore operations will still be conducted against the real database host.