基于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 status\G;
至此完毕。
