| #!/bin/bash | |
| # Install xtrabackup package. | |
| # https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/apt_repo.html | |
| # On production server must be installed in the slave. | |
| wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb | |
| sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb | |
| sudo apt-get update | |
| sudo apt-get install percona-xtrabackup-24 |
| #!/bin/bash | |
| # Setup MySQL and Permissions. | |
| # https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-backups-with-percona-xtrabackup-on-ubuntu-16-04 | |
| echo "Enter Host: " | |
| read mysql_host | |
| echo "Enter MySQL Password: " | |
| read mysql_password | |
| mysql -e "DROP USER IF EXISTS 'backup'@'${mysql_host}'" | |
| mysql -e "CREATE USER 'backup'@'${mysql_host}' IDENTIFIED BY '${mysql_password}'" | |
| mysql -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE, PROCESS, SUPER, CREATE, INSERT, SELECT ON *.* TO 'backup'@'${mysql_host}'" | |
| mysql -e "FLUSH PRIVILEGES" | |
| # Add backup user to mysql group | |
| sudo usermod -aG mysql backup | |
| # Add sudo user to backup group | |
| sudo usermod -aG backup ${USER} | |
| # Give the mysql group access to the MySQL data directories | |
| sudo find /var/lib/mysql -type d -exec chmod 750 {} \; | |
| sudo touch /etc/mysql/backup.cnf | |
| sudo echo "[client]" > /etc/mysql/backup.cnf | |
| sudo echo "user=backup" >> /etc/mysql/backup.cnf | |
| sudo echo "password=${mysql_password}" >> /etc/mysql/backup.cnf | |
| sudo chown backup /etc/mysql/backup.cnf | |
| sudo chmod 600 /etc/mysql/backup.cnf | |
| sudo mkdir -p /backups/mysql | |
| sudo chown backup:mysql /backups/mysql | |
| # Create an encrpytion key use to encrypt backup files. | |
| if ! [ -f /backups/mysql/encryption_key ]; then | |
| echo -n "$(openssl rand -base64 24)" | sudo tee /backups/mysql/encryption_key && echo -n | |
| fi | |
| echo "Please logout and log back in order for the permissions to take effect." | |
| echo "Log-out now? (y)" | |
| read logoutans | |
| if [ $logoutans == "y" ] || [ $logoutans == "Y" ]; then | |
| # Re-evaluate the groups available to our sudo user, either log out and log back in, or type | |
| exec su - ${USER} | |
| fi |
| #!/bin/bash | |
| # Create Backup, Extract and Prepare Scripts. | |
| # https://www.digitalocean.com/community/tutorials/how-to-configure-mysql-backups-with-percona-xtrabackup-on-ubuntu-16-04 | |
| script_path="$( cd "$(dirname "$0")" ; pwd -P )" | |
| chmod +x ${script_path}/scripts/{backup,extract,prepare}-mysql.sh | |
| sudo cp ${script_path}/scripts/{backup,extract,prepare}-mysql.sh /usr/local/bin |
backup-mysql.sh
Create full and incremental backups.
extract-mysql.sh
Extract the xtrabackup to be used in restoration.
prepare-mysql.sh
Prepare the extracted xtrabackup that will be use in restoration.
For more information, please visit this blog.
| #!/bin/bash | |
| # https://raw.githubusercontent.com/do-community/ubuntu-1604-mysql-backup/master/backup-mysql.sh | |
| export LC_ALL=C | |
| days_of_backups=3 # Must be less than 7 | |
| backup_owner="backup" | |
| parent_dir="/backups/mysql" | |
| defaults_file="/etc/mysql/backup.cnf" | |
| todays_dir="${parent_dir}/$(date +%a)" | |
| log_file="${todays_dir}/backup-progress.log" | |
| encryption_key_file="${parent_dir}/encryption_key" | |
| now="$(date +%m-%d-%Y_%H-%M-%S)" | |
| 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."' 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 encryption key file is available | |
| if [ ! -r "${encryption_key_file}" ]; then | |
| error "Cannot read encryption key at ${encryption_key_file}" | |
| fi | |
| } | |
| set_options () { | |
| # List the xtrabackup arguments | |
| xtrabackup_args=( | |
| "--defaults-file=${defaults_file}" | |
| "--backup" | |
| "--extra-lsndir=${todays_dir}" | |
| "--compress" | |
| "--stream=xbstream" | |
| "--encrypt=AES256" | |
| "--encrypt-key-file=${encryption_key_file}" | |
| "--parallel=${processors}" | |
| "--compress-threads=${processors}" | |
| "--encrypt-threads=${processors}" | |
| "--slave-info" | |
| #"--databases=simmfins.accounts" | |
| ) | |
| 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 |
| mkdir -p /tmp/xtrabackup | |
| cd /tmp/xtrabackup |
| #!/bin/bash | |
| # https://raw.githubusercontent.com/do-community/ubuntu-1604-mysql-backup/master/extract-mysql.sh | |
| export LC_ALL=C | |
| backup_owner="backup" | |
| encryption_key_file="/backups/mysql/encryption_key" | |
| encryption_key=`cat ${encryption_key_file}` | |
| 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 | |
| # Check whether the encryption key file is available | |
| if [ ! -r "${encryption_key_file}" ]; then | |
| error "Cannot read encryption key at ${encryption_key_file}" | |
| 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}" | |
| "--decrypt=AES256" | |
| "--encrypt-key=${encryption_key}" | |
| "--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 |
| #!/bin/bash | |
| # https://raw.githubusercontent.com/do-community/ubuntu-1604-mysql-backup/master/prepare-mysql.sh | |
| export LC_ALL=C | |
| shopt -s nullglob | |
| incremental_dirs=( ./incremental-*/ ) | |
| full_dirs=( ./full-*/ ) | |
| shopt -u nullglob | |
| backup_owner="backup" | |
| 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 mysql | |
| 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 mysql | |
| EOF | |
| else | |
| error "It looks like something went wrong. Check the \"${log_file}\" file for more information." | |
| fi |