yum -y install perl-DBI

yum -y install perl-DBD-MySQL

yum -y install perl-IO-Socket-SSL.noarch

yum -y install perl-Time-HiRes

yum -y install perl-TermReadKey

yum -y install perl-ExtUtils-MakeMaker

yum -y install perl-Digest-MD5

yum -y install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr

cd /opt/

wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/tarball/percona-xtrabackup-8.0.7-Linux-x86_64.libgcrypt145.tar.gz

tar -zxf percona-xtrabackup-8.0.-Linux-x86_64.libgcrypt145.tar.gz

cd /usr/local/

ln -s /opt/percona-xtrabackup-8.0.-Linux-x86_64 xtrabackup

ln -fs  /opt/percona-xtrabackup-8.0.-Linux-x86_64/bin/* /usr/bin/

xtrabackup -v

create user 'bk_user'@'%' identified WITH mysql_native_password by 'v9SimLKsIHpwzyOgVwlM' PASSWORD EXPIRE NEVER ;

GRANT BACKUP_ADMIN,SELECT, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'bk_user'@'%';

# 备份脚本

[root@bj-db-m1 scripts]# cat xtraback.sh

#!/bin/bash

# MySQL 端口

Ip_Host="10.10.146.28"

Port=""

# 备份用户

User_Name="bk_user"

Pass_Word="v9SimLKsIHpwzyOgVwlM"

Time=`date +%Y%m%d_%H%M%S`

# 备份路径

BaseDIR="/data/bak"

Data_Bak="/data/bak/data"

# 备份保留天数

Backup_Save_Days=""

# 备份过程->Log文件

LogFILE="${BaseDIR}/logs/${Time}_info.log"

touch ${LogFILE}

# 开始备份

Start_Time=`date +%Y%m%d_%H%M%S`

echo "Start-Time ：${StartTime}" |tee -a ${LOGFILE}

echo "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" |tee -a ${LOGFILE}

# 备份命令

# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=10.10.146.28 --user='bk_user' --password='v9SimLKsIHpwzyOgVwlM' --port=  --backup --compress --compress-threads= --use-memory=4G --slave-info --parallel= --target-dir=/data/backup/

# xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=${Ip_Host} --user=${User_Name} --password=${Pass_Word} --port=${Port}  --backup --compress --compress-threads= --use-memory=4G --slave-info --parallel= --target-dir=${Data_Bak}/${Time}  >>${LogFILE}

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=${Ip_Host} --user=${User_Name} --password=${Pass_Word} --port=${Port} --backup --compress --compress-threads=8 --use-memory=4G  --parallel=8 --target-dir=${Data_Bak}/${Time} 2>>${LogFILE} 

# 结束备份

StopTime=`date +%Y%m%d_%H%M%S`

echo "Stop-Time ：${StopTime}" |tee -a ${LOGFILE}

echo "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++" |tee -a ${LOGFILE}

# 清除N天之前的备份

cd ${BASEDIR}

#/usr/bin/find -name "*.tar.gz" -mtime +${Backup_Save_Days} -exec rm {} \;

#/usr/bin/find -name "*info.log" -mtime +${Backup_Save_Days} -exec rm {} \;

/*

wget http://www.quicklz.com/qpress-11-linux-x64.tar

tar xvf qpress-11-linux-x64.tar

cp qpress /usr/bin

# 如果无法下载，登录其官网，单独下载，再上传

*/

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --use-memory=4G --decompress --parallel= --remove-original --target-dir=/data/backup/  # 解压缩备份

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --use-memory=4G --parallel= --target-dir=/data/backup/  # 恢复数据

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --copy-back --use-memory=4G --parallel= --target-dir=/data/backup/  # 拷贝会目录所在，这里推荐使用--copy-back方式，因为我的my.cnf中的配置路径是分散的

chown -R mysql.mysql *

# 在从库上执行

rm -rf /data/mysql/mysql_3306/undolog/* /data/mysql/mysql_3306/data/* /data/mysql/mysql_3306/logs/mysql-bin*

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf  --use-memory=4G --decompress --parallel=8 --remove-original --target-dir=/data/m/20191029_155959

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --copy-back --use-memory=4G --parallel=8 --target-dir=/data/m/20191029_155959

chown -R mysql.mysql /data/mysql/mysql_3306/

# 修改server_id
# 启动数据库即可

[root@bj-db-m2 data]# cat xtrabackup_info

uuid = 3d67d0cd-fa22-11e9-aa77-525400f4342d

name =

tool_name = xtrabackup

tool_command = --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=10.10.146.28 --user=bk_user --password=... --port=3306 --backup --compress --compress-threads=8 --use-memory=4G --slave-info --parallel=8 --target-dir=/data/bak/data/20191029_155959

tool_version = 8.0.7

ibbackup_version = 8.0.7

server_version = 8.0.18

start_time = 2019-10-29 16:00:00

end_time = 2019-10-29 16:00:57

lock_time = 0

binlog_pos = filename 'mysql-bin.000018', position '', GTID of the last change '18026056-f574-11e9-9d03-525400f4342d:1-19494'

innodb_from_lsn = 0

innodb_to_lsn = 24342657453

partial = N

incremental = N

format = file

compressed = compressed

encrypted = N

reset master

SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;

SET @@SESSION.SQL_LOG_BIN= 0;

SET @@GLOBAL.GTID_PURGED='18026056-f574-11e9-9d03-525400f4342d:1-19494';

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

# 或者在从库中直接执行: SET GLOBAL gtid_purged="18026056-f574-11e9-9d03-525400f4342d:1-19494";

change master to master_host='10.10.146.28', master_port=3306, master_user='repl', master_password='replpfhOTnWffQdQL3F3', master_auto_position = 1;

start slave;

show slave status\G;