Backup and restore

There are basically two categories of backup formats: logical and physical. An example of logical backup is the SQL file created by mysqldump, and an example of physical backup is the files created by mariabackup. Logical backups are generally in text format, whereas physical backups are essentially copies of the data directory.

mysqldump

Backup

Example command:

mysqldump ispyb > /tmp/ispyb.sql

Restore

Example command:

mysql ispyb < /tmp/ispyb.sql

mariabackup

Backup

Example commands:

mariabackup --user=root --password=1234 --backup --no-lock --target-dir=/tmp/2019-03-15
mariabackup --defaults-extra-file=$HOME/mariabackup.cnf --backup --no-lock --target-dir=/tmp/2019-03-15

Restore

First make sure the server on which you're restoring the backup has the same version of MariaDB installed as on the prod cluster, and that the MariaDB configuration files, by default /etc/my.cnf and /etc/my.cnf.d/*, are working correctly. Then:

Create a directory for unzipping the backup file, then unzip it there:

mkdir /scratch/backup
cd /scratch/backup
tar xvfz /path/to/backup.tar.gz

Make sure your MariaDB server is shut down. Make sure /scratch/mariadb/data/ exists and is empty. Use mariabackup to restore the datadir:

mariabackup --copy-back --target-dir=/path/to/unzipped/backup/ --datadir=/scratch/mariadb/data

Now start up a MariaDB server, and then you can connect with a client as the root user, no password required. Either:

sudo systemctl mariadb start

Or to run as yourself with your own special datadir:

mysqld_safe --no-defaults --datadir=/scratch/mariadb/data &

And then log in with the client:

mysql -uroot

However, if restoring to a MariaDB server running as 'mysql' with the default datadir and SELinux in enforcing mode, you will also have to set correct permissions and security context before starting the MariaDB server:

sudo chown -R mysql:mysql /var/lib/mysql

sudo restorecon -Rv /var/lib/mysql

sudo systemctl mariadb start