MySQL5.7安装及备份

tar -xvf mysql-server_5.7.42-1ubuntu18.04_amd64.deb-bundle.tar
rm -f ./*test*
apt install libtinfo5 libmecab2
dpkg -i mysql-*.deb

vim /etc/mysql/mysql.conf.d/mysqld.cnf
-------------------------------------------------------
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log #开启binglog
max_binlog_size = 100M
binlog_format = mixed
bind-address = 0.0.0.0 #绑定地址
-------------------------------------------------------

systemctl enable mysql
systemctl start mysql

unzip qpress-20230507.zip
cd qpress-20230507
make
sudo make install

ll /usr/local/percona-xtrabackup/bin

drwxrwxr-x 2 root root 4096 12月 2 2022 ./
drwxrwxr-x 6 root root 4096 12月 2 2022 ../
lrwxrwxrwx 1 root root 10 12月 2 2022 innobackupex - >xtrabackup*
-rwxr-xr-x 1 root root 10185463 12月 2 2022 xbcloud*
-rwxr-xr-x 1 root root 3020 12月 2 2022 xbcloud_osenv*
-rwxr-xr-x 1 root root 5299580 12月 2 2022 xbcrypt*
-rwxr-xr-x 1 root root 5370640 12月 2 2022 xbstream*
-rwxr-xr-x 1 root root 202001891 12月 2 2022 xtrabackup*

#MySQL地址
/opt/homebrew/opt/mysql/bin/mysqld \
  --basedir=/opt/homebrew/opt/mysql \
  --datadir=/opt/homebrew/var/mysql \
  --plugin-dir=/opt/homebrew/opt/mysql/lib/plugin \
  --log-error=192.168.1.4.err \
  --pid-file=192.168.1.4.pid

# 先全量备份
xtrabackup --user=root --password=12345678 --backup --target-dir=./base --no-server-version-check
# 再增量备份
xtrabackup --user=root --password=12345678 --backup --target-dir=./base-incr1 --incremental-basedir=./base --no-server-version-check
xtrabackup --user=root --password=12345678 --backup --target-dir=./base-incr2 --incremental-basedir=./base-incr1 --no-server-version-check

# --apply-log-only参数只需要加在'非最后一个备份'之前
# prepare时,中间的增量备份不可以跳过,要按照顺序执行
xtrabackup --prepare --apply-log-only --target-dir=./base
xtrabackup --prepare --apply-log-only --target-dir=./base --incremental-dir=./base-incr1
xtrabackup --prepare --target-dir=./base --incremental-dir=./base-incr2

# 停机
brew services stop mysql
# 删除mysql的datadir
rm -rf /opt/homebrew/var/mysql
# 备份恢复
xtrabackup --copy-back --target-dir=./base --datadir=/opt/homebrew/var/mysql
# 重新启动
brew services start mysql

#!/bin/bash

## 参数
MYSQL_USER=root
MYSQL_PWD=12345678
BACKUP_DIR=/Users/songchengzhong/Projects/mysql_backup/$(date +\%Y-\%m-\%d)
DATA_DIR=/opt/homebrew/var/mysql #暂时没有用到

set -e # causes the shell to exit immediately if a command returns a non-zero status
set -u # causes the shell to report an error if you try to use an undeclared variable

usage() {
  echo "usage: $(basename $0) [option]"
  echo "option=full: Perform Full Backup"
  echo "option=incremental: Perform Incremental Backup"
  echo "option=restore: Start to Restore! Be Careful!! "
  echo "option=help: show this help"
}

full_backup() {

  if [ ! -d $BACKUP_DIR ]; then
    mkdir $BACKUP_DIR
  fi

  rm -rf $BACKUP_DIR/*
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Cleanup the backup folder is done! Starting backup" >>$BACKUP_DIR/xtrabackup.log

  xtrabackup --backup -u$MYSQL_USER -p$MYSQL_PWD --history --compress --parallel=4 --compress-threads=4 --target-dir=$BACKUP_DIR/FULL --no-server-version-check
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Backup Done!" >>$BACKUP_DIR/xtrabackup.log
}

incremental_backup() {
  if [ ! -d $BACKUP_DIR/FULL ]; then
    echo "ERROR: Unable to find the FULL Backup. aborting....."
    exit -1
  fi

  if [ ! -f $BACKUP_DIR/last_incremental_number ]; then
    NUMBER=1
  else
    NUMBER=$(($(cat $BACKUP_DIR/last_incremental_number) + 1))
  fi

  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Starting Incremental backup $NUMBER" >>$BACKUP_DIR/xtrabackup.log
  if [ $NUMBER -eq 1 ]; then
    xtrabackup --backup -u$MYSQL_USER -p$MYSQL_PWD --history --parallel=4 --compress-threads=4 --target-dir=$BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/FULL --no-server-version-check
  else
    xtrabackup --backup -u$MYSQL_USER -p$MYSQL_PWD --history --parallel=4 --compress-threads=4 --target-dir=$BACKUP_DIR/inc$NUMBER --incremental-basedir=$BACKUP_DIR/inc$(($NUMBER - 1)) --no-server-version-check
  fi

  echo $NUMBER >$BACKUP_DIR/last_incremental_number
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Incremental Backup:$NUMBER done!" >>$BACKUP_DIR/xtrabackup.log
}

restore() {
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing the FULL backup" >>$BACKUP_DIR/xtrabackup-restore.log
  xtrabackup --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR/FULL
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing Done !!!" >>$BACKUP_DIR/xtrabackup-restore.log

  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Prepareing FULL Backup ..." >>$BACKUP_DIR/xtrabackup-restore.log
  xtrabackup --prepare --apply-log-only --target-dir=$BACKUP_DIR/FULL
  echo $(date '+%Y-%m-%d %H:%M:%S:%s')": FULL Backup Preparation Done!!!" >>$BACKUP_DIR/xtrabackup-restore.log

  P=1
  while [ -d $BACKUP_DIR/inc$P ] && [ -d $BACKUP_DIR/inc$(($P + 1)) ]; do
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing incremental:$P" >>$BACKUP_DIR/xtrabackup-restore.log
    xtrabackup --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR/inc$P
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing incremental:$P Done !!!" >>$BACKUP_DIR/xtrabackup-restore.log

    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Prepareing incremental:$P" >>$BACKUP_DIR/xtrabackup-restore.log
    xtrabackup --prepare --apply-log-only --target-dir=$BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": incremental:$P Preparation Done!!!" >>$BACKUP_DIR/xtrabackup-restore.log
    P=$(($P + 1))
  done

  if [ -d $BACKUP_DIR/inc$P ]; then
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing the last incremental:$P" >>$BACKUP_DIR/xtrabackup-restore.log
    xtrabackup --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR/inc$P
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Decompressing the last incremental:$P Done !!!" >>$BACKUP_DIR/xtrabackup-restore.log

    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Prepareing the last incremental:$P" >>$BACKUP_DIR/xtrabackup-restore.log
    xtrabackup --prepare --target-dir=$BACKUP_DIR/FULL --incremental-dir=$BACKUP_DIR/inc$P
    echo $(date '+%Y-%m-%d %H:%M:%S:%s')": Last incremental:$P Preparation Done!!!" >>$BACKUP_DIR/xtrabackup-restore.log
  fi
}

if [ $# -eq 0 ]; then
  usage
  exit 1
fi

case $1 in
"full")
  full_backup
  ;;
"incremental")
  incremental_backup
  ;;
"restore")
  restore
  ;;
"help")
  usage
  break
  ;;
*) echo "invalid option" ;;
esac