scriptor

Backup, extract, and prepare script for database.

How to use

Requirements

MariaDB
Mariabackup
Percona Xtrabackup
- We need this for extracting the encrypted database backup.
Mutt
- We need this for email notification.
A dedicated system and mysql user for performing the backup.

Steps

For this example, we will be using backup as our backup user

Install the first 4 requirements above.
Create a MYSQL user dedicated for performing the backup.

mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';

Grant appropriate privileges.

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO 'backup'@'localhost';

Create a system user and group named backup.

Note: If you are using Ubuntu distro, you can skip this step. On Ubuntu, a backup user and corresponding backup group is already available.

Configure the system backup user and assigning permissions.
- Type the following commands to add the backup user to the mysql group and your sudo user to the backup group:
```
sudo usermod -aG mysql backup
sudo usermod -aG backup ${USER}
```
Creating backup assets.
- Create a MySQL configuration file with the backup parameters
```
sudo nano /etc/mysql/backup.cnf
```
- Inside, start a [client] section and set the MySQL backup user and password user you defined within MySQL:
  
  [client]
  user=backup
  password=password
- Give ownership of the file to the backup user and then restrict the permissions so that no other users can access the file:
```
sudo chown backup /etc/mysql/backup.cnf
sudo chmod 600 /etc/mysql/backup.cnf
```
Create a backup root directory.

sudo mkdir -p /backups/mysql

Assign ownership of the /backups/mysql directory to the backup user and group ownership to the mysql group:

sudo chown backup:mysql /backups/mysql

Create an encryption key to secure the backup files.

printf '%s' "$(openssl rand -base64 24)" | sudo tee /backups/mysql/encryption_key && echo

Git clone this repository.

git clone https://github.com/rppf/scriptor.git

Move the scripts to /usr/local/bin and make them executable.
Then try to run the backup script.

sudo -u backup /usr/local/bin/backup-mysql.sh

Note: Don't forget to edit this line in backup-mysql.sh. Add your email to enable email notifications.

email="your@email.com"

If theres's an error, the log is located in your backup root directory.

Restore the database

Extract the backups using the extract-mysql.sh script. Go to your backup directory /backups/mysql/[ the date you want to restore ] the run the script.

sudo -u backup extract-mysql.sh *.xbstream

After extracting the backups. There should be a folder named restore, this is where your extracted backups are. These directories contains the raw backup files, but they are not yet in a state that MariaDB can use. To fix that we need to prepare the files. To do this you must inside the restore folder the run the prepare-mysql.sh.

sudo -u backup prepare-mysql.sh

After running the command above. The output should be like this:

Backup looks to be fully prepared.  Please check the "prepare-progress.log" file
to verify before continuing.

If everything looks correct, you can apply the restored files.

First, stop MySQL and move or remove the contents of the MySQL data directory:
    
        sudo systemctl stop mysql
        sudo mv /var/lib/mysql/ /tmp/
    
Then, recreate the data directory and  copy the backup files:
    
        sudo mkdir /var/lib/mysql
        sudo mariabackup --copy-back --target-dir=${PWD}/$(basename "${full_backup_dir}")
    
Afterward the files are copied, adjust the permissions and restart the service:
    
        sudo chown -R mysql:mysql /var/lib/mysql
        sudo find /var/lib/mysql -type d -exec chmod 750 {} \\;
        sudo systemctl start mysql

The output above are the last steps.