基于gtid复制主要操作记录

基于gtid复制主要操作记录

一、安装系统依赖包

　　在主从上都要安装该依赖包。

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

二、安装xtrabackup工具

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.7-Linux-x86_64.libgcrypt145.tar.gz 
cd /usr/local/
ln -s /opt/percona-xtrabackup-8.0.7-Linux-x86_64 xtrabackup
ln -fs  /opt/percona-xtrabackup-8.0.7-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'@'%';

四、主库全量备份

压缩备份命令：

xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --host=10.10.146.28 --user=bk_user --password=v9SimLKsIHpwzyOgVwlM 
--port=3306  --backup --compress --compress-threads=8 --use-memory=4G --slave-info --parallel=8 --target-dir=/data/bak/data/  2>>/data/bak/logs/bak.log

# 备份脚本
[root@bj-db-m1 scripts]# cat xtraback.sh 
#!/bin/bash
# MySQL 端口
Ip_Host="10.10.146.28"
Port="3306"

# 备份用户
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="3"

# 备份过程->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=3306  --backup --compress --compress-threads=8 --use-memory=4G --slave-info --parallel=8 --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=8 --use-memory=4G --slave-info --parallel=8 --target-dir=${Data_Bak}/${Time}  2>>${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=8 --remove-original --target-dir=/data/backup/  # 解压缩备份
xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --prepare --use-memory=4G --parallel=8 --target-dir=/data/backup/  # 恢复数据
xtrabackup --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --copy-back --use-memory=4G --parallel=8 --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 '195', 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 statusG;

至此完毕。