xtrabackup搭建从库(开启了GTID)

select concat('tableexclude ',TABLE_SCHEMA,'.',TABLE_NAME,';') from information_schema.tables where TABLE_SCHEMA='fsscdb' and ENGINE<>'InnoDB';


-- 1.	备份
xtrabackup --defaults-file=/mydata/ogladb/my.cnf --login-path=ogladb --backup --tmpdir=/mydata/backup/ogladb/tmp --parallel=4 --compress --compress-threads=4 --target-dir=/backup/mysql/ogladb/2022_02_24_213639

-- 2、创建同步用户
mysql> create user repl identified with mysql_native_password by 'xxooxxoooo';
mysql> grant replication slave on *.* to repl;
mysql> flush privileges;

-- 3.	解压备份文件
xtrabackup --decompress --parallel=4 --remove-original --target-dir=/backup/mysql/ogladb/2022_02_24_213639

-- 3.	应用日志
xtrabackup --prepare --use-memory=512M --target-dir=/backup/mysql/ogladb/2022_02_24_213639

-- 4.	拷贝回文件到数据目录（data目录需为空）
一、	创建数据库实例运行所需的目录
mkdir data logs trace tmp

二、	恢复数据库
xtrabackup  --defaults-file=/mydata/ogladb/my.cnf --move-back --parallel=4 --target-dir=/backup/mysql/ogladb/2022_02_24_213639

三、	启库验证
nohup mysqld_safe --defaults-file=/mydata/ogladb/my.cnf > /mydata/ogladb/nohup_mysqld_safe.out 2>&1 &

mysql_config_editor set --login-path=ogladb -hlocalhost -uroot -p --socket=/mydata/backup/ogladb/mysql.sock


-- 5.	增量应用日志
一、	根据备份日志确认备份终止时的binlog文件和position
cd data
cat xtrabackup_info|grep binlog_pos

reset master;
reset slave;

set global gtid_purged='6d8707ae-c270-11eb-b1b1-b496919360a6:1-115043557';

change master to master_host='168.168.168.17',master_port=3307,master_user='repl',master_password='xxooxxoooo',master_auto_position=1;

复制