centos7 xtrabackup mysql（8）压缩 增量备份（3）

centos7 xtrabackup mysql（8）压缩 增量备份（3）

添加数据1
添加数据测试一下
测试主从是否可以

主机端
mysql -u root -p
1234aA~1

show databases ;
use company_pro;
show tables ;
insert into employee(name) value (‘20240813_1420’);

从机端 查看
mysql -u root -p
1234aA~1
show databases ;
use company_pro;
show tables ;
select * from employee ;

sudo mkdir /data
sudo mkdir -p /data/backup/
sudo chmod -R 777 /data/backup/
sudo mkdir -p /data/backup/20240813

slave端 全量备份

sudo xtrabackup --backup --slave-info --compress --compress-threads=2 --target-dir=/data/backup/20240813/full_backup --user=root --password=1234aA~1 --parallel=2

注意下面这个错误

show variables like '%gtid%' ;

The --slave-info option requires GTID enabled or --safe-slave-backup option used for a multi-threaded slave.

insert into employee(name) value (‘20240813_1501’);

slave端 增量备份1

sudo xtrabackup --backup --slave-info --compress --compress-threads=2 --target-dir=/data/backup/20240813/inc_1 --incremental-basedir=/data/backup/20240813/full_backup --user=root --password=1234aA~1 --parallel=2 

slave端 增量备份2
insert into employee(name) value (‘20240813_1507’);

从机端 查看
mysql -u root -p
1234aA~1
show databases ;
use company_pro;
show tables ;
select * from employee ;

sudo xtrabackup --backup --slave-info --compress --compress-threads=2 --target-dir=/data/backup/20240813/inc_2 --incremental-basedir=/data/backup/20240813/inc_1  --user=root --password=1234aA~1 --parallel=2 

还原

wget “http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar”
tar xvf qpress-11-linux-x64.tar
chmod 775 qpress
sudo cp qpress /usr/bin

sudo systemctl stop mysqld
sudo cp -r /opt/datadir/mysql /opt/datadir/bak_mysql

xtrabackup --decompress --parallel=2 --target-dir=/home/jack/20240813/full_backup
xtrabackup --decompress --parallel=2 --target-dir=/home/jack/20240813/inc_1
xtrabackup --decompress --parallel=2 --target-dir=/home/jack/20240813/inc_2

111

sudo systemctl stop mysqld
sudo cp -r /opt/datadir/mysql /opt/datadir/bak_mysql
sudo rm -rf /opt/datadir/mysql/*
sudo xtrabackup --prepare --apply-log-only --target-dir=/home/jack/20240813/full_backup
sudo xtrabackup --prepare --apply-log-only --target-dir=/home/jack/20240813/full_backup --incremental-dir=/home/jack/20240813/inc_1
sudo xtrabackup --prepare --target-dir=/home/jack/20240813/full_backup --incremental-dir=/home/jack/20240813/inc_2
sudo xtrabackup --copy-back  --target-dir=/home/jack/20240813/full_backup
sudo chown -R mysql.mysql /opt/datadir/mysql/*
sudo systemctl restart mysqld
mysql -u root -p
mysql -V

查看信息

是否会冲掉原来的 用户信息

会覆盖

mysql -u fakeuser -p
1234aA~1

SELECT User, Host FROM mysql.user;

CREATE USER 'fakeuser'@'%' IDENTIFIED by '1234aA~1';
GRANT ALL ON *.* TO 'fakeuser'@'%';
flush privileges;

新建数据库

show databases ;
create database fakefactory;

use fakefactory;
DROP TABLE IF EXISTS person;

drop table if exists person;
create table person(
  id int unsigned primary key auto_increment comment 'id'
  , name varchar(50) not null comment '名称'
  , create_time datetime not null default current_timestamp comment '创建时间'
  , update_time datetime not null default current_timestamp on update current_timestamp comment '更新时间'
)  ENGINE=InnoDB charset=utf8 comment '测试表';

DESCRIBE person;

insert into person(name) value ('张三');
select * from person;