Fundraising/techops/procedures/services-mariadb mariabackup

From Wikitech

Backup and restore steps for mariadb using mariabackup

There are scripts in place to backup databases using mariabackup. This doc will walk through the steps as if the script were not in place to give an idea of what steps are needed. As the scripting improves, this document will be replaced by information related to the script and options.

This document currently covers full environment backups and restores. It does not address incremental or paritial (specific db or table) backups/restores.

Backups

Within the dump_database script, when doing a backup with mariabackup, we execute a command in this form: 

my $command = "$conf->{'BACKUP-PROGRAM'} --user=root --backup " .
    "--stream=xbstream --slave-info " . $exclude .
    " | $compression " . $gpg . " > $base_file.gz.tmp";

To break this down, we are running the mariabackup command in a streaming mode and passing it through to a compression program and optionally an encryption program. In practice, this command would look something like: 

mariabackup --user=root --backup --stream=xbstream --slave-info \
    | pigz -p 4 | gpg -es -r KEYIDS > mariabackup.host.date.gz.tmp

We write to a tmp file to start out with to keep the partial backup excluded from data syncs that may happen during the backup run. After the backup has completed, the file will be renamed to .gz or .gz.gpg depending on if encryption was specified.

The base config will back up all databases running on the host. This allows for easier restores or bringing up an entirely new replica. There is the option to exclude databases but that tends to be the exception as compared to the rule.

Backup file

At this point, we have a backup file. Within that file lives a copy of all of the databases and tables in the mariadb instance. Additionally it contains the ibdata files, replication postition and information, a backup my.cnf file user for firing up and preparing the backup for restore, and some internal xtra/mariabackup files. This backup file could not be directly extracted and used, but it is the complete package needed at it's smallest size and easier for shuttling around.

Restores

In order to restore a backup, we first need to extract the backup, prepare it, and then copy it back into place.

In general we would do this in a safe location for the full extraction and processing. Something akin to /srv/restore/YYYY-MM-DD. All commands from here on out assume that the backup has been copied to that directory.

Extracting the backup

Since we use the streaming backup option, we can't directly decrypt/unzip the backup and expect it to work. We need to pass it thorough an mbstream util to handle that. In this example, we will assume the backup is just compressed and not encrypted. If it were, we would append a gpg decryption of the file that we would then pipe to the rest of the commands. 

pigz -dc -p4 mariabackup.host.date.gz | mbstream -x

We now have the uncompressed, unprepared backup and all of the accessory files.

Prepare the backup

The backup does not come ready for an immediate restore. This is due to how the mariabackup process works. The backup process will back up the data files one at a time and thus are not point in time consistent. However, it does this while keeping track of any new updates that come in across the bin log. Those binlog updates are saved in a different file and need to be applied.

To prepare the backup for restoration in place, we would run the following command: 

mariabackup --prepare --target-dir=/srv/restore/YYYY-MM-DD

This will apply the bin log updates and prepare all the databases backed up. It specifies the restore directory as that is where the files that need preparation are. If you put the final destination, aka /srv/sqldata, in as the target-dir, the command will fail as there won't be a valid backup there.

Copying the backup into place

Now that the backup is complete and consistent, we will copy the files into place. This will move the files to the directory we specify, so we need to ensure a few things first.

  • Ensure any mariadb server processes are fully stopped
  • Ensure the data dir (/srv/sqldata in our case) is empty

Then we are ready to copy the files back into place. This is done using the command: 

mariabackup --copy-back --target-dir=/srv/restore/YYYY-MM-DD

It does seem odd that the target dir isn't where the backup is restored to, but that's how the options are specified.

Updating permissions

This process will copy the files back into place, but it does so as the user running the process. So we need to update the permissions to match the mariadb user. 

chown -R mysql:mysql /srv/sqldata
chmod 750 /srv/sqldata

Useful Mariabackup Docs

To look at / investigate

Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=Fundraising/techops/procedures/services-mariadb_mariabackup&oldid=2093465"