Skip to content

Instantly share code, notes, and snippets.

@lasekmiroslaw
Last active February 24, 2024 15:50
Show Gist options
  • Save lasekmiroslaw/86453e6d46fbf7927005595c673c76e1 to your computer and use it in GitHub Desktop.
Save lasekmiroslaw/86453e6d46fbf7927005595c673c76e1 to your computer and use it in GitHub Desktop.
backup mysql percona-xtrabackup xtrabackup
#!/bin/bash
export LC_ALL=C
days_of_backups=2 # Must be less than 7
backup_owner="root"
parent_dir="/backups/mysql"
defaults_file="/etc/my.cnf.d/backup.cnf"
todays_dir="${parent_dir}/$(date +%a)"
log_file="${todays_dir}/backup-progress.log"
now="$(date +%m-%d-%Y_%H-%M-%S)"
processors="2"
# Use this to echo to standard error
error () {
printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
exit 1
}
trap 'error "An unexpected error occurred."' ERR
sanity_check () {
# Check user running the script
if [ "$(id --user --name)" != "$backup_owner" ]; then
error "Script can only be run as the \"$backup_owner\" user"
fi
}
set_options () {
# List the xtrabackup arguments
xtrabackup_args=(
"--defaults-file=${defaults_file}"
"--backup"
"--extra-lsndir=${todays_dir}"
"--compress"
"--stream=xbstream"
"--parallel=${processors}"
"--compress-threads=${processors}"
)
backup_type="full"
# Add option to read LSN (log sequence number) if a full backup has been
# taken today.
if grep -q -s "to_lsn" "${todays_dir}/xtrabackup_checkpoints"; then
backup_type="incremental"
lsn=$(awk '/to_lsn/ {print $3;}' "${todays_dir}/xtrabackup_checkpoints")
xtrabackup_args+=( "--incremental-lsn=${lsn}" )
fi
}
rotate_old () {
# Remove the oldest backup in rotation
day_dir_to_remove="${parent_dir}/$(date --date="${days_of_backups} days ago" +%a)"
if [ -d "${day_dir_to_remove}" ]; then
rm -rf "${day_dir_to_remove}"
fi
}
take_backup () {
# Make sure today's backup directory is available and take the actual backup
mkdir -p "${todays_dir}"
find "${todays_dir}" -type f -name "*.incomplete" -delete
xtrabackup "${xtrabackup_args[@]}" --target-dir="${todays_dir}" > "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" 2> "${log_file}"
mv "${todays_dir}/${backup_type}-${now}.xbstream.incomplete" "${todays_dir}/${backup_type}-${now}.xbstream"
}
sanity_check && set_options && rotate_old && take_backup
# Check success and print message
if tail -1 "${log_file}" | grep -q "completed OK"; then
printf "Backup successful!\n"
printf "Backup created at %s/%s-%s.xbstream\n" "${todays_dir}" "${backup_type}" "${now}"
else
error "Backup failure! Check ${log_file} for more information"
fi
[xtrabackup]
user=bkpuser
password='MU3T3mKu2S6PZn#w'
datadir=/var/lib/mysql/
#!/bin/bash
export LC_ALL=C
backup_owner="root"
log_file="extract-progress.log"
number_of_args="${#}"
processors="$(nproc --all)"
# Use this to echo to standard error
error () {
printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
exit 1
}
trap 'error "An unexpected error occurred. Try checking the \"${log_file}\" file for more information."' ERR
sanity_check () {
# Check user running the script
if [ "${USER}" != "${backup_owner}" ]; then
error "Script can only be run as the \"${backup_owner}\" user"
fi
# Check whether the qpress binary is installed
if ! command -v qpress >/dev/null 2>&1; then
error "Could not find the \"qpress\" command. Please install it and try again."
fi
# Check whether any arguments were passed
if [ "${number_of_args}" -lt 1 ]; then
error "Script requires at least one \".xbstream\" file as an argument."
fi
}
do_extraction () {
for file in "${@}"; do
base_filename="$(basename "${file%.xbstream}")"
restore_dir="./restore/${base_filename}"
printf "\n\nExtracting file %s\n\n" "${file}"
# Extract the directory structure from the backup file
mkdir --verbose -p "${restore_dir}"
xbstream -x -C "${restore_dir}" < "${file}"
xtrabackup_args=(
"--parallel=${processors}"
"--decompress"
)
xtrabackup "${xtrabackup_args[@]}" --target-dir="${restore_dir}"
find "${restore_dir}" -name "*.xbcrypt" -exec rm {} \;
find "${restore_dir}" -name "*.qp" -exec rm {} \;
printf "\n\nFinished work on %s\n\n" "${file}"
done > "${log_file}" 2>&1
}
sanity_check && do_extraction "$@"
ok_count="$(grep -c 'completed OK' "${log_file}")"
# Check the number of reported completions. For each file, there is an
# informational "completed OK". If the processing was successful, an
# additional "completed OK" is printed. Together, this means there should be 2
# notices per backup file if the process was successful.
if (( $ok_count != $# )); then
error "It looks like something went wrong. Please check the \"${log_file}\" file for additional information"
else
printf "Extraction complete! Backup directories have been extracted to the \"restore\" directory.\n"
fi
sudo curl -o backup-mysql.sh https://gist.githubusercontent.com/lasekmiroslaw/86453e6d46fbf7927005595c673c76e1/raw/169f81fbc4ff708072a67c995cb3cd0e3604c60a/backup-mysql.sh;sudo chmod +x backup-mysql.sh;sudo mv backup-mysql.sh /usr/bin/backup-mysql.sh;
sudo curl -o extract-mysql.sh https://gist.githubusercontent.com/lasekmiroslaw/86453e6d46fbf7927005595c673c76e1/raw/169f81fbc4ff708072a67c995cb3cd0e3604c60a/extract-mysql.sh;sudo chmod +x extract-mysql.sh;sudo mv extract-mysql.sh /usr/bin/extract-mysql.sh;
sudo curl -o prepare-mysql.sh https://gist.githubusercontent.com/lasekmiroslaw/86453e6d46fbf7927005595c673c76e1/raw/169f81fbc4ff708072a67c995cb3cd0e3604c60a/prepare-mysql.sh;sudo chmod +x prepare-mysql.sh;sudo mv prepare-mysql.sh /usr/bin/prepare-mysql.sh;
sudo curl -o backup.cnf https://gist.githubusercontent.com/lasekmiroslaw/86453e6d46fbf7927005595c673c76e1/raw/b75bb2544ca9f22b79b0517ba33565bfa20b21d3/backup.cnf; sudo mv backup.cnf /etc/my.cnf.d/backup.cnf
https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-backups-with-percona-xtrabackup-on-ubuntu-16-04
https://www.percona.com/doc/percona-xtrabackup/8.0/index.html
https://thedataguy.in/automation-script-for-percona-xtrabackup-full-incremental/
Install
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
sudo yum install percona-xtrabackup-80
#compress/decompress backups
sudo yum install qpress
User
CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'MU3T3mKu2S6PZn#w';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;
Config
sudo touch /etc/my.cnf.d/backup.conf
Full cycle
https://www.percona.com/doc/percona-xtrabackup/8.0/backup_scenarios/full_backup.html
Backup
sudo xtrabackup --backup --compress --target-dir=/backups/mysql/
# Restore backup add --force-non-empty-directories if /var/lib mysql is not empty
sudo xtrabackup --decompress --target-dir=/backups/mysql/
sudo xtrabackup --copy-back --target-dir=/backups/mysql/
sudo chown -R mysql:mysql /var/lib/mysql
## restore backup with script
sudo extract-mysql.sh *.xbstream
sudo service mysqld stop
sudo prepare-mysql.sh ## in backup directory
## make backup
sudo backup-mysql.sh
## restore backup with script
sudo extract-mysql.sh *.xbstream ## in backup directory
sudo service mysqld stop
sudo prepare-mysql.sh ## in backup directory
#!/bin/bash
export LC_ALL=C
shopt -s nullglob
incremental_dirs=( ./incremental-*/ )
full_dirs=( ./full-*/ )
shopt -u nullglob
backup_owner="root"
log_file="prepare-progress.log"
full_backup_dir="${full_dirs[0]}"
# Use this to echo to standard error
error() {
printf "%s: %s\n" "$(basename "${BASH_SOURCE}")" "${1}" >&2
exit 1
}
trap 'error "An unexpected error occurred. Try checking the \"${log_file}\" file for more information."' ERR
sanity_check () {
# Check user running the script
if [ "${USER}" != "${backup_owner}" ]; then
error "Script can only be run as the \"${backup_owner}\" user."
fi
# Check whether a single full backup directory are available
if (( ${#full_dirs[@]} != 1 )); then
error "Exactly one full backup directory is required."
fi
}
do_backup () {
# Apply the logs to each of the backups
printf "Initial prep of full backup %s\n" "${full_backup_dir}"
xtrabackup --prepare --apply-log-only --target-dir="${full_backup_dir}"
for increment in "${incremental_dirs[@]}"; do
printf "Applying incremental backup %s to %s\n" "${increment}" "${full_backup_dir}"
xtrabackup --prepare --apply-log-only --incremental-dir="${increment}" --target-dir="${full_backup_dir}"
done
printf "Applying final logs to full backup %s\n" "${full_backup_dir}"
xtrabackup --prepare --target-dir="${full_backup_dir}"
}
sanity_check && do_backup > "${log_file}" 2>&1
# Check the number of reported completions. Each time a backup is processed,
# an informational "completed OK" and a real version is printed. At the end of
# the process, a final full apply is performed, generating another 2 messages.
ok_count="$(grep -c 'completed OK' "${log_file}")"
if (( ${ok_count} == ${#full_dirs[@]} + ${#incremental_dirs[@]} + 1 )); then
cat << EOF
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 mysqld
sudo mv /var/lib/mysql/ /tmp/
Then, recreate the data directory and copy the backup files:
sudo mkdir /var/lib/mysql
sudo xtrabackup --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 mysqld
If does not work
sudo mkdir /var/lib/mysql-files
sudo chown mysql /var/lib/mysql-files
EOF
else
error "It looks like something went wrong. Check the \"${log_file}\" file for more information."
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment