Last active
February 24, 2024 15:50
-
-
Save lasekmiroslaw/86453e6d46fbf7927005595c673c76e1 to your computer and use it in GitHub Desktop.
backup mysql percona-xtrabackup xtrabackup
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
[xtrabackup] | |
user=bkpuser | |
password='MU3T3mKu2S6PZn#w' | |
datadir=/var/lib/mysql/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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