Install MySQL

curl -sSL https://dwz.cn/gfcnHqGS -o install-mysql.sh
chmod +x install-mysql.sh
./install-mysql.sh --get-version   # View software versions in the repository

# Example
./install-mysql.sh --active install --data-dir /home/hadoop/mysql --version 5.7.23 --root-pass 123abc@DEF

  --help       # View Help Information
  --data-dir   # mysql data storage directory defaults to / home/hadoop/mysql
  --version    # Specify installation version
  --root-pass  # root password
  --bin-log    # Whether or not binary logging is enabled (binary logging is only kept for 7 days) is not enabled by default

Backup MySQL with xtrabackup

• Install percona-xtrabackup

wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.15-1.el7.x86_64.rpm

• Create backup scripts

The script supports two backup modes

1. mode 1 Make full backup on Sunday and incremental backup on Sunday from Monday to Saturday
2. Make full backups on Sundays, incremental backups on Mondays to Saturdays

cat > innobackup.sh <<'!'
#!/bin/bash

function usage() {
  cat <<EOF
Usage: $0 command ...[parameters]....
    --help, -h             View Help Information
    --data-dir, -d         Data backup storage directory
    --mode, -m             Backup mode [1|2] Mandatory
                     1.Make full backups on Sundays, incremental backups on Sundays every day from Monday to Saturday
                     2.Make full backups on Sundays and incremental backups on Mondays to Saturdays every day

    $0 --mode 2 --data-dir /home/backup
EOF
}

GETOPT_ARGS=`getopt -o hd:m: -al help,data-dir:,mode: -- "$@"`
eval set -- "$GETOPT_ARGS"
while [ -n "$1" ]; do
    case "$1" in
        -h|--help)
            usage
            exit 0
            ;;
        -d|--data-dir)
            data_dir=$2
            shift 2
            ;;
        -m|--mode)
            mode=$2
            shift 2
            ;;
        --)
            shift
            ;;
        *)
            usage
            exit 1
            ;;
    esac
done

function get_cfg(){
    # The day of the week (0 for Sunday)
    week_day=$(date +%w)

    # Backup Data Storage Path
    data_dir=${data_dir:-/home/backup}

    # Whether to send mail when backup fails (Enable: true)
    mail=

    # Addressee
    mail_addr=example@qq.com

    # Get the server IP
    HOST_IF=$(ip route|grep default|cut -d' ' -f5)
    HOST_IP=$(ip a|grep "$HOST_IF$"|awk '{print $2}'|cut -d'/' -f1)

    # Mail title
    mail_title="MySQL backups-$HOST_IP"

    # Exit if backup mode is not specified
    if [ -z "$mode" ]; then
        usage
        exit 1
    fi
}

# Get full backup time (incremental backup calls only)
function get_last_weekend(){
    indate=${*:-$(date +%Y%m%d)}

    # Last week's today's date
    statday=$(date -d "$indate -1 weeks" +%Y%m%d)

    # What day was it last week?
    whichday=$(date -d $statday +%w)

    # Get last weekend
    if [[ $whichday == 0 ]]; then
        startday=$(date -d "$statday" +%Y%m%d)
    else
        # Last week time + 7 (7 days a week) - Weekly days
        startday=$(date -d "$statday + $[7 - ${whichday}] days" +%Y%m%d)
    fi

    echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') This incremental backup is based on the last backup ${startday}"
}

# Full volume backup
function full_backup(){
    if [ "$1" == "rebackup" ]; then
        # Make incremental backups every day from Monday to Saturday for the last weekend
        if [[ $mode == 1 ]]; then
            # No full backup was detected last weekend to create today's full backup to last weekend's full backup folder.
            # Full Backup Folder
            get_last_weekend
            full_date="full_${startday}"
        # Weekend incremental backup on Monday, Monday incremental backup on Tuesday, Wednesday incremental backup on Tuesday and so on
        elif [[ $mode == 2 ]]; then
            # No previous backup was detected (full Backup this time)
            # Full Backup Folder
            full_date="inc_$(date '+%Y%m%d')"
        fi
    else
        # Normal full backup folder full_20190929
        full_date="full_$(date '+%Y%m%d')"
    fi

    # Full backup path
    full_backup_dir="${data_dir}/${full_date}"

    # Full backup log
    full_backup_log="${full_backup_dir}/backup.log"

    # Move if it exists (prevent first full backup failure and subsequent backup failure)
    if [[ -d "${full_backup_dir}" ]]; then
        temp_dir="$(mktemp --tmpdir=${data_dir} -d ${full_date}_XXX)"
        mv ${full_backup_dir} $temp_dir
    fi

    # Create a backup directory
    mkdir -p ${full_backup_dir}

    # Full volume backup
    echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Start full backup."
    echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Start full backup." > ${full_backup_log}
    innobackupex --defaults-file=/etc/my.cnf --user=root --password='123abc@DEF' --no-timestamp ${full_backup_dir} &>> ${full_backup_log}

    # Verify backup results (failed mail)
    tail -n 1 ${full_backup_log} | grep 'completed OK!' &> /dev/null
    if [[ $? -eq 0 ]];then
        touch ${full_backup_dir}/backup_ok
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Successful full backup."
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Successful full backup." >> ${full_backup_log}
    else
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Full backup failed."
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Full backup failed." >> ${full_backup_log}
        if [[ "$mail" == 'true' ]]; then
            /usr/bin/python /script/send_mail.py $mail_addr $mail_title "$(cat ${full_backup_log})"
        fi
        exit 1
    fi
}

function incremental_backup(){
    # Incremental backup path
    inc_date="inc_$(date '+%Y%m%d')"
    inc_backup_dir="${data_dir}/${inc_date}"

    # Incremental backup log
    inc_backup_log="${inc_backup_dir}/backup.log"

    # If a backup exists in the current directory
    if [[ -f "${inc_backup_dir}/xtrabackup_logfile" ]]; then
        temp_dir="$(mktemp --tmpdir=${data_dir} -d  ${inc_date}_XXX)"
        mv ${inc_backup_dir} $temp_dir
    fi

    # Create a backup directory
    mkdir -p ${inc_backup_dir}

    # Check that the last backup was normal
    if [[ -f "${incremental_basedir}/backup_ok" ]];then
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') The last backup was in good condition."
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') The last backup was in good condition." >> ${inc_backup_log}
    else
        echo "[Warning] $(date '+%Y-%m-%d %H:%M:%S') The last backup was not ready for full backup."
        rm -rf ${inc_backup_dir}
        full_backup rebackup
        if [[ $mode == 1 ]]; then
            echo "[Warning] $(date '+%Y-%m-%d %H:%M:%S') Incremental Backup Not Built(Full backup created last weekend does not exist and full backup has been recreated)"
        elif [[ $mode == 2 ]]; then
            echo "[Warning] $(date '+%Y-%m-%d %H:%M:%S') Full backup was created this time.(Last backup failed or did not exist)"
        fi
        exit 0
    fi

    # Incremental backup
    echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Start incremental backup."
    echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Start incremental backup." >> ${inc_backup_log}
    /usr/bin/innobackupex --user=root --password='123abc@DEF' --no-timestamp --incremental --incremental-basedir=${incremental_basedir} ${inc_backup_dir} &>> ${inc_backup_log}

    # Verify backup results (failed mail)
    tail -n 1 ${inc_backup_log} | grep 'completed OK!' &> /dev/null
    if [[ $? -eq 0 ]];then
        touch ${inc_backup_dir}/backup_ok
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental Backup Successful."
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental Backup Successful." >> ${inc_backup_log}
    else
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental backup failed."
        echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental backup failed." >> ${inc_backup_log}
        if [[ "$mail" == 'true' ]]; then
            /usr/bin/python /script/send_mail.py $mail_addr $mail_title "$(cat ${inc_backup_log})"
        fi
        exit 1
    fi
}

# Clean up backup
function clean_backup(){
    find $data_dir -mtime +$[4 * 7] -type d | xargs rm -rf
}

# If it's full backup at weekend, otherwise incremental backup
function mode_1(){
  case $week_day in
    0)
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Full volume backup."
      full_backup
      sleep 3
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Clean up historical backups."
      clean_backup
      ;;
    [1-6])
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental backup."

      # Get the incremental backup directory for last weekend
      get_last_weekend

      # Each incremental backup is based on last weekend's full backup
      incremental_basedir=${data_dir}/full_${startday}

      # Start incremental backup
      incremental_backup
      ;;
  esac
}

function mode_2(){
  case $week_day in
    0)
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Full volume backup."
      full_backup
      sleep 3
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Clean up historical backups."
      clean_backup
      ;;
    1)
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental backup."

      # Get the incremental backup directory for last weekend
      get_last_weekend

      # This incremental backup is based on last weekend's full backup
      incremental_basedir=${data_dir}/full_${startday}

      # Start incremental backup
      incremental_backup
      ;;
    [2-6])
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') Incremental backup."

      # This incremental backup is based on the last incremental backup directory
      echo "[INFO] $(date '+%Y-%m-%d %H:%M:%S') This incremental backup is based on the last backup inc_$(date -d "-1 day" '+%Y%m%d')."
      incremental_basedir=${data_dir}/inc_$(date -d "-1 day" '+%Y%m%d')

      # Start incremental backup
      incremental_backup
      ;;
  esac
}

if [[ "$mode" == '1' ]]; then
    get_cfg
    mode_1
elif [[ "$mode" == '2' ]]; then
    get_cfg
    mode_2
fi
!

• Mail alarm

1. Set mail = to mail=true in the script innobackup.sh function get_cfg if you need mail alerts
2. Modify mail recipient and mail header configuration in get_cfg
3. Modify account information in send_mail.py
4. Local Test Send Mail. / send.py Your Mail Title Content

cat > send_mail.py <<'!'
#!/usr/bin/env python
# _*_ coding:utf-8 _*_
# Usage: ./send.py example@qq.com title content

import smtplib
from email.mime.text import MIMEText
import sys

# configure your own parameters here
#The smtp address of the following mail address
mail_host = 'smtp.qq.com'

#The mailbox used to send mail is displayed on the sender's head (otherwise your mail will be considered spam)
mail_user = 'examp@qq.com'

# Client Authorization Code
mail_auth = '********yIskw5WOkJ*******'

# Name displayed by sender
send_name = 'examp@qq.com'

# Name displayed by the recipient
recv_name = 'examp@qq.com'

def excute(to, title, content):
    msg = MIMEText(content, 'plain', 'utf-8')
    msg['From'] = send_name
    msg['To'] = recv_name
    msg['Subject'] = title
    server = smtplib.SMTP(mail_host, 25)
    server.login(mail_user,mail_auth)
    server.sendmail(mail_user,to,msg.as_string())
    server.quit()

if __name__ == '__main__':
    excute(sys.argv[1], sys.argv[2], sys.argv[3])
!

• Usage method

mkdir /script
chmod +x send_mail.py innobackup.sh
mv send_mail.py innobackup.sh /script

# Example
/script/innobackup.sh --mode 2 --data-dir /home/backup

  --help      # View Help Information
  --mode      # Backup mode 1. Make full backup every Sunday from Monday to Saturday, do incremental backup every day from Sunday 2. Make full backup every Sunday from Monday to Saturday, do incremental backup every day from Monday to Saturday.
  --data-dir  # Backup storage directory

Test backup restore

• Clean up the database initialization database settings password

systemctl stop mysqld
rm -rf /home/hadoop/mysql/*
systemctl start mysqld

new_pass=123abc@DEF
old_pass=$(grep -rn 'temporary password' /var/log/mysqld.log | tail -n1 | awk '{print $NF}')
/home/software/mysql_secure_installation.exp $old_pass $new_pass

• Create test database (modification time 2019-09-1-2019-09-1, create a database a201901-16 and backup after each modification)

rm -rf /home/backup
export MYSQL_PWD=123abc@DEF
for i in {1..16};do
    date -s "2019-09-$i 1:00"
    mysql -uroot -e "create database a$(date '+%Y%m%d');"
    /script/innobackup.sh --mode 1 --data-dir /home/backup
done

• View checkpoints

more -u backup/full_20190901/xtrabackup_checkpoints  backup/inc_20190902/xtrabackup_checkpoints backup/inc_20190903/xtrabackup_checkpoints

• Restore 20190904 Backup

systemctl stop mysqld
rm -rf /home/hadoop/mysql
rm -rf /home/backup/full_20190901.bak
cp -a /home/backup/full_20190901 /home/backup/full_20190901.bak
innobackupex --apply-log --redo-only /home/backup/full_20190901.bak
innobackupex --apply-log --redo-only /home/backup/full_20190901.bak --incremental-dir=/home/backup/inc_20190904
innobackupex --copy-back /home/backup/full_20190901.bak
chown -R mysql:mysql /home/hadoop/mysql
systemctl start mysqld
mysql -e 'show databases;'

• Clean up the database initialization database settings password

systemctl stop mysqld
rm -rf /home/hadoop/mysql/*
systemctl start mysqld

new_pass=123abc@DEF
old_pass=$(grep -rn 'temporary password' /var/log/mysqld.log | tail -n1 | awk '{print $NF}')
/home/software/mysql_secure_installation.exp $old_pass $new_pass

• Create test database (modification time 2019-09-1-2019-09-1, create a database a201901-16 and backup after each modification)

rm -rf /home/backup
export MYSQL_PWD=123abc@DEF
for i in {1..16};do
    date -s "2019-09-$i 1:00"
    mysql -uroot -e "create database a$(date '+%Y%m%d');"
    /script/innobackup.sh --mode 2 --data-dir /home/backup
done

• View checkpoints

more -u backup/full_20190901/xtrabackup_checkpoints  backup/inc_20190902/xtrabackup_checkpoints backup/inc_20190903/xtrabackup_checkpoints

• Restore 20190906 Backup

systemctl stop mysqld
rm -rf /home/hadoop/mysql
rm -rf /tmp/backup
cp -a /home/backup /tmp/backup
innobackupex --apply-log --redo-only /tmp/backup/full_20190901
innobackupex --apply-log --redo-only /tmp/backup/full_20190901 --incremental-dir=/tmp/backup/inc_20190902
innobackupex --apply-log --redo-only /tmp/backup/full_20190901 --incremental-dir=/tmp/backup/inc_20190903
innobackupex --apply-log --redo-only /tmp/backup/full_20190901 --incremental-dir=/tmp/backup/inc_20190904
innobackupex --apply-log --redo-only /tmp/backup/full_20190901 --incremental-dir=/tmp/backup/inc_20190905
innobackupex --apply-log --redo-only /tmp/backup/full_20190901 --incremental-dir=/tmp/backup/inc_20190906
innobackupex --copy-back /tmp/backup/full_20190901
chown -R mysql:mysql /home/hadoop/mysql
systemctl start mysqld
mysql -e 'show databases;'

Create Planned Tasks

crontab -l > /tmp/crontab.tmp
echo "10 1 * * * /script/innobackup.sh --mode 2 --data-dir /home/backup" >> /tmp/crontab.tmp
cat /tmp/crontab.tmp | uniq > /tmp/crontab
crontab /tmp/crontab
rm -f /tmp/crontab.tmp /tmp/crontab

code