Automating Percona's XtraBackup

$ python3 -m pip install pexpect

$ sudo mkdir /lib/xtrabackupautomator
$ sudo chmod 700 /lib/xtrabackupautomator

$ sudo mkdir -p /data/backups/mysql
$ sudo mkdir -p /data/backups/archive
$ sudo mkdir -p /data/backups/archive/archive_restore

$ sudo chmod 760 /data/backups/mysql
$ sudo chmod 700 /data/backups/archive
$ sudo chmod 700 /data/backups/archive/archive_restore
$ sudo chown -R root:root /data/backups/

$ sudo mv xtrabackupautomator.py /lib/xtrabackupautomator/.
$ sudo mv xtrabackupautomator.service /etc/systemd/system/.
$ sudo mv xtrabackupautomator.timer /etc/systemd/system/.

$ sudo systemctl daemon-reload

$ sudo systemctl enable xtrabackupautomator.timer

$ sudo systemctl start xtrabackupautomator.timer
$ sudo systemctl status xtrabackupautomator.timer

$ systemctl start xtrabackupautomator.service
$ journalctl -f -n 100 -u xtrabackupautomator

# Always verify our version of Percona's XtraBackup and MySQL match before performing a backup... these differences can make restores fail or behave oddly.
$ sudo mysql --version
$ sudo xtrabackup --version

# Clean our restore folder, just to be safe
$ rm -r /data/backups/archive/archive_restore/*

# Unzip our archived backup to an empty folder. Always verify we have enough disk space to unzip before unzipping
$ sudo tar -xvf database_backup_12_23_2022__06_25_10.tar.gz  -C /data/backups/archive/archive_restore/

# Sanity check, verify we are looking at the backup we think we are (This command checks the base folder, check the latest incremental folder we may have)
$ cd /data/backups/archive/archive_restore/data/backups/mysql/
$ sudo  cat base/xtrabackup_info | grep 'tool_version\|server_version\|start_time\|end_time\|partial\|incremental'

# Prepare the base
$ sudo xtrabackup --prepare --apply-log-only --no-server-version-check  --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base 

# Prepare each incremental folder. This must be done for each incremental folder we wish to back up to.
$ sudo xtrabackup --prepare --apply-log-only --no-server-version-check --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base  --incremental-dir=/data/backups/archive/archive_restore/data/backups/mysql/inc_0

# Repeat as necssary....
...

# Stop SQL and our backup script as we do not want it running mid restore
$ sudo systemctl stop mysql
$ sudo systemctl stop xtrabackupautomator.timer

# Wipe bad/corrupted sql data from current instance
$ sudo rm -rv /var/lib/mysql/*

# Verify our mysql data is wiped
$ sudo ls /var/lib/mysql

# I use this method to restore my base backup, there are other options but they did not work correctly in my environment
$ sudo xtrabackup --copy-back --target-dir=/data/backups/archive/archive_restore/data/backups/mysql/base

# Verify the contents are the size we expect as a sanity check and apply the correct ownership to the files
$ du -hs /var/lib/mysql/
$ sudo chown -R mysql:mysql /var/lib/mysql

# Restart mysql and xtrabackupautomator. Verify MySQL's status
$ sudo systemctl start mysql
$ sudo systemctl xtrabackupautomator.timer
$ sudo systemctl status mysql

== db ==
    -un
        [DEFAULT_VALUE: ""]
        XtraBackup user you set up during your initial configuration of Percona's XtraBackup
    
    -pw
        [DEFAULT_VALUE: ""]
        This user's password

    -host
        [DEFAULT_VALUE: "localhost"]
        The IP of your database 

    -port
        [DEFAULT_VALUE: 3306]
        The port to access database 


== folder_names ==
    -base_dir 
        [DEFAULT_VALUE: "/data/backups/"]
        The root directory for all backup related things. Holds current backup and any archived backups.
        This is the default location and is reflected in the setup as we request you create this folder.
        If you change this directory in the config this change must be reflected in the setup.

    -datadir 
        [DEFAULT_VALUE: "mysql/"]
        Folder that current backups will be saved to. This would be the folder that holds the base backup and any 
          incremental backups before they are archived
        If you change this directory in the config this change must be reflected in the setup.
        *** XtraBackupAutomator WILL ARCHIVE AND DELETE ANYTHING IN HERE. THIS SHOULD BE AN EMPTY FOLDER, NOT UTILIZED BY ANYTHING ELSE.

    -archivedir 
        [DEFAULT_VALUE: "archive/"]
        Folder that a group of backups will be archived to. 
        If you change this directory in the config this change must be reflected in the setup.
        *** XtraBackupAutomator COULD POTENTIALLY DELETE ANY NON-DIRECTORY IN HERE.

== file_names ==
    -basefolder_name
        [DEFAULT_VALUE: "base"]
        Foldername for the base backup

    -incrementalfolder_perfix
        [DEFAULT_VALUE: "inc_"]
        Folder name prefix for incremental backups. 
        Suffixed with the current number of incremental backups minus one
        e.g., 'inc_0'

    -archive_name_prefix
        [DEFAULT_VALUE: "database_backup_"]
        Prefix for the archive files. 
        Suffixed by the datetime of the archive
        e.g., 'database_backup_11_28_2022__06_25_03.tar.gz'


== archive_settings ==
    -allow_archive
        [DEFAULT_VALUE: True]
        An override to enable/disable all archive settings.
        Currently, disabling this will cause the program to do a base backup and then incremental backups forever.

    -archive_zip_format
        [DEFAULT_VALUE: "gztar"]
        The default archive file type. I like tarballs because they zip our large database into a manageable file. 
        However, tarballs can take a long time to create and require a fair amount of resources if your DB is large. 
        This setting will depend on your system and the size of your DB. I recommend playing around with this.
        Other zip options: [Shutil Man Page](https://docs.python.org/3/library/shutil.html#shutil.make_archive)

    -archived_bu_count
        [DEFAULT_VALUE: 7]
        Keep x archived backups, once this threshold is reached the oldest archive will be deleted.
        Archiving daily, this is a week of archives.

    -enforce_max_num_bu_before_archive
        [DEFAULT_VALUE: True]
        One of two ways to 'force archive' of backups. 
        This counts the # of incremental backup folders and initiates the archives once that number is reached. 
        A sample use case is that in your systemd timer file is scheduled to do 5 backups throughout the day, so setting this to 
          true and max_num_bu_before_archive_count set to 4 (because we do not count the base) would give you a 'daily archive'

    -max_num_bu_before_archive_count
        [DEFAULT_VALUE: 4]
        The max number of incremental backups to do before we archive (does not count the base). 
        Set to 0 to archive after each base

    -enforce_archive_at_time
        [DEFAULT_VALUE: False]
        One of two ways to 'force archive' of backups. 
        This will archive what ever base or incremental folders exist if a backup is happening within the 
          archive_at_utc_24_hour hour. This is intended to make it easier to schedule when your archive and base backup occur. 
        These can be resource intensive and so it is nice to do at off hours.
        *If this program is scheduled to run more than once during the 'archive_at_utc_24_hour' hour each run will cause an archive.

    -archive_at_utc_24_hour
        [DEFAULT_VALUE: 6]
        If a backup happens within this hour we will archive w/e was previously there and create a new base.
        Matching this with a time setup in your xtrabackupautomator.timer allows you to choose when your backups will 
          occur.
        No explicit consideration for daylight savings time. 
        Defaults to the hour of 1:00am EST, 6:00am UTC.


== general_settings ==
    -backup_command_timeout_seconds
        [DEFAULT_VALUE: 30]
        Give us 'backup_command_timeout_seconds' seconds for the command to respond. 
        This is not the same as saying 'a backup can only take this long'.

    -max_time_between_backups_seconds
        [DEFAULT_VALUE: 60*60*24]
        Max number of seconds between this backup and the last.
        If the last backup is older than this we will archive and create a new base. 
        This is in an attempt to prevent an incremental backup that might span days or weeks due to this service being 
          turned off or some such.
        Defaults (arbitrarily) to 24 hours

    -additional_bu_command_params
        [DEFAULT_VALUE: ["no-server-version-check"]]
        Any additional parameters that you wish to pass along to your backup commands.
        We loop this list, put a '--' before each element and append it to the end of our backup commands.
        This gets applied to the base and incremental backup commands.
        These are params that I have found useful.


== log_settings ==
    -is_enabled
        [DEFAULT_VALUE: True]
        Enables/Disables all logging type settings. This was useful in testing, so I kept it around.

    -log_child_process_to_screen
        [DEFAULT_VALUE: True]
        If this is set to true the child process's output will be dumped to screen but not actually logged anywhere

    -is_log_to_file
        [DEFAULT_VALUE: True]
        If set to True we will try to log to the 'default_log_file' in the 'default_log_path' directory

    -default_log_path
        [DEFAULT_VALUE: "/var/log/"]
        The path that we will try to place our log file ('default_log_file') 

    -default_log_file
        [DEFAULT_VALUE: "xtrabackupautomator.log"]
        The file name we will try to log to