mysql 24day 数据库备份 完整备份增量备份 差异备份 压缩备份

备份核心思想

大部分的数据使用 硬备份
小部分数据使用log_bin 进行恢复

逻辑备份（mysqldump）

本质 是用sql 语句进行备份
是使用sql 语言的日志进行备份的
可以备份本地 数据 也可以备份 远程的数据
消息队列

备份语法

mysqldump -u{用户名}  -p{密码}  -h{端口号} -P{端口} -S{套接字文件}
案例
mysqldump -uroot -p 123 --all-databases > alldb.sql

创建表格

# 创建表格
create table king.t1(`name` varchar(40),`time` varchar(40));
# 插入数据
insert into king.t1 values("xiaowang","1day");
insert into king.t1 values("xiaoyue","1day");
# 查看表格
select * from king.t1;

本地备份

客户机可以使用mysqldump 进行远程备份数据库

	#备份到本地 备份全库
mysqldump -uroot -p 123 --all-databases > alldb.sql
mysqldump -uroot -p 123 -A > alldb.sql
	#备份压缩
mysqldump -uroot -p 123 --all-databases | gzip > alldb.sql.gz

	# 远程备份 (在那台客户机执行就会备份到哪里)
mysqldump -remote -p123 -10.12.155.100 -A

	# 库级备份
mysqldump -pcroot@123 -B shool> shoool_mysql;
mysqldump -p{密码} -B {数据库} > {备份文件}
mysqldump -pcroot@123 -B  shool table2> shoool_mysql2;

-B 建库参数
备份一个表格  不加-B  没有建库语句   需要手动建库
加上-B  之后 ，

	# 备份 表
mysqldump -pqianfeng@passwd school tbale_name >chool_table_name.sql
mysqldump -p{密码}  {库名} {表1} {表2} > /backup/table_bk.sql

	# 备份 user db 表格

mysqldump -pqianfeng@passwd school tbale_name tbale2_name  >chool_table_name.sql

	# -d  只备份 表的结构
mysqldump -p{密码}  {数据库name} {表名} -d > {结构}.sql

	# 只要数据 不要表结构（有安全限制的）
		show variables like "secure%"; 
		查看安全文件的导出位置 /var/lib/mysql-files
			#更改数据库导出安全目录
			#修改方法 
			mkdir /data
			chown mysql.mysql /data
			vim /etc/my.cnf
			secure_file_priv=/data/
			
		#导出数据

		mysql> select * from {表} into outfile '{文件，路径}';
		mysql> select * from mysql.user into outfile '/var/lib/mysql-files/user.xlsx';
	#导入数据
		mysql> load date infile '{文件，路径}' into table user;
		mysql> load data infile '/var/lib/mysql-files/user.xlsx' into table user;

本地恢复

# 删除库

drop database school;

# 恢复一个库
## 一、数据库中恢复
mysql> source {备份文件}
mysql> source /back/alldb.sql

## 二、命令行里导入\
mysql -uroot -p{密码} < {备份文件}

## 三、使用管道
cat {备份文件} | mysql -uroot -p{密码} 




# 恢复一张表
	## 删除一张表
		drop table {表名}

	## 恢复 1、
		mysql -{密码} {库名} < {备份文件}

	##  2、数据内恢复
		mysql> cource {备份文件}

	##  3、管道恢复
		cat {备份文件} | mysql -uroot -p{密码} 


# 恢复表结构（其余同上）
 	mysqldump -p{密码} {库名} {表名字} -d > {表结构备份文件}

二进制文件恢复数据

可以用来恢复完整备份的数据 到最后一次更新的数据

数据库每天备份 备份之后增加的 数据  用binlog 进行恢复


# 开启二进制 文件
vim /etc/my.cnf
	#在文件 /etc/my.cnf 写入下边内容
	server_id=1
	# 二进制文件存放位置  mysql-bin开头的
	log_bin=/data/mysql-bin
	# 生成文件==>   /date/mysql-bin.00001 

    #配置好 之后 需要重启mysql 服务器
    systemctl restart mysqld
    
    
# 插入新的数据
插入第二天

insert into king.t1 values ("king","2day");
# 删除表格
drop table king.t1;
drop database king;

# 先恢复备份
mysql -uroot -p"{密码}" < {备份文件}


# 查看mysql二进制文件内容
mysqlbinlog {binlog 二进制文件}

# 查看详细信息  查找到错误语句 
mysqlbinlog {binlog 二进制文件} --base64-output=decode-row -vv
mysqlbinlog /data/mysql-bin.00001 --base64-output=decode-rows -vv


# 结合之前备份内容  根据这些字符找到需要恢复的内容
BEGIN
at 293
COMMIT(commit) 提交的意思
at 435


# 根据 binlog 进行恢复 
mysqlbinlog --start-position 293 --stop-position 435 {binlog备份文件} | mysql -uroot -p"{密码}"


# 刷新日志文件
flush log;

select * from king.t1;

硬件备份

数据备基础理论知识

• 三种备份方式

  • 完全备份（就是把数据库中的内容全部备份 ）
  • 增量备份（每次之备份这次所增加的数据）
  • 差异备份（都次都会备份第一次更新的内容和 之后更新的内容 ，恢复的时候，只需要把两次备份进行恢复）

环境

• centos 9
• mysql 8.0

xtrabackup 源码包

(sad)[as]

rz  //上传 rpm包
#安装源码包
yum -y install  percona-release-latest.noarch.rpm
#安装xtrabackup
yum -y install percona-xtrabackup-80

给数据库插入数据

create table bba.t1(id int,name varchar(40)); 
insert into bba.t1 values (1,'king'),(2'kaka'),(3'wawa');
select * from bba.t1;

如果安装成功 使用一下命令也是可以备份成功的

#xtrabackup备份命令解析
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/  -uroot -pQianFeng@123 -H localhost -P 3306  --no-server-version-check



--defaults-file=/etc/my.cnf
	备份目标 主配置文件  相关参数
--backup
	这条指令是用来备份的
 --target-dir=/data/backup/base/ 
	备份好以后存储的位置
 -uroot -pQianFeng@123
	备份的账号密码
-H localhost -P 3306
	主机和端口号
--no-server-version-check
	不用检查数据库版本直接备份
	
#创建备份文件
mkdir  -p   /data/backup/base
#执行
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/  -uroot -p@King1314 -H localhost -P 3306  --no-server-version-check
#回复ok 即表示成功
2023-11-22T19:47:08.100035+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

重点 重点 重点

通常需要修改的数

• 基础数据目录
• 增量备份目录
• 个人数据库 的用户 密码 主机 端口号

删库

systemctl   stop  mysqld
rm -rf  /var/lib/mysql/*

#准备备份文件
xtrabackup --prepare --target-dir=/data/backup/base
#开始恢复
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/
#授权 
chown -R mysql.mysql /var/lib/mysql

#回复ok 即表示成功  ((((到这里  全部显示ok 的话就说明已经全部备份好了))))

重启MySQL 服务

systemctl restart mysqld
mysql -uroot -p'@King1314'

到现在就完成了一个数据库 备份的全部过程

---

增量备份

1.先插入 更新数据

mysql -uroot -p'@King1314'

#先进行完整备份 完全备份
mkdir -p /data/backup/base/	
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/  -uroot -p@King1314 -H localhost -P 3306  --no-server-version-check	

2.增加新的数据

show databases;
create database new1;
create table new1.t1(id int,uname varchar(20));
insert into new1.t1 values (1,'liubang'),(2,'liuche'),(3,'huoqubing'),(4,'weiqing'),(5,'liuxiu');
select * from new1.t1;

3.增量备份

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/  --incremental-basedir=/data/backup/base/ -u root -p@King1314 -H localhost -P 3306 --no-server-version-check

xtrabackup --defaults-file=/etc/my.cnf \
--backup \
--target-dir=/data/backup/inc1/  \
--incremental-basedir=/data/backup/base/ \
-u root -pQianFeng@123 -H localhost -P 3306 \
--no-server-version-check

#命令详解
xtrabackup
	备份命令
--defaults-file=/etc/my.cnf
	备份目录 主配置文件 相关参数
--backup
	这条明命令是备份命令
--target-dir=/data/backup/inc1/ 
	备份好 以后的的储存位置
--incremental-basedir=/data/backup/base/
	第一次备份目录
-u root -pQianFeng@123 -H localhost -P 3306
	数据信息  数据库账号 密码  主机名称 端口号
--no-server-version-check
	不检测版本号
#实例
	#增量备份
systemctl stop mysqld
mkdir -p /data/backup/inc1/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/  --incremental-basedir=/data/backup/base/ -u root -p@King1314 -H localhost -P 3306 --no-server-version-check
#备份了 new 1数据

4.第二次增量备份

mysql -uroot -p'@King1314'

show databases;
create database new3;
create table new3.t1(id int,uname varchar(20));
insert into new3.t1 values (1,'liubang'),(2,'liuche'),(3,'huoqubing'),(4,'weiqing'),(5,'liuxiu');
select * from new3.t1;

备份

 xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/  --incremental-basedir=/data/backup/inc1/ -u root  -pQianFeng@123 -H localhost -P 3306 --no-server-version-check
 
 ##上一次增量备份的目录
 --target-dir=/data/backup/inc2/
 --incremental-basedir=/data/backup/inc1
 #实例
  mkdir -p /data/backup/inc2
  xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/  --incremental-basedir=/data/backup/inc1/ -u root  -p@King1314 -H localhost -P 3306 --no-server-version-check
 
 #备份了new3 数据库

好的 第二次备份成功 注意这两个选项

# 每次增量备份都是需要  修改为[[上次备份的位置]]  和[[最新备份的位置]]
--target-dir=/data/backup/inc2/
--incremental-basedir=/data/backup/inc1/

恢复增量备份

先删库(这个操作相当于数据库出了问题 )

systemctl   stop  mysqld
rm -rf  /var/lib/mysql/*

先准备数据

#准备完整备份
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
#准备第一次增量 文件
 xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1/
#准备第二次增量 文件
 xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2
 
 有多少次增量 则就需要进行多少次这样的操作。
 #注意最后一次增量不要写 --apply-log-only
 #注意最后一次增量不要写 --apply-log-only
 #注意最后一次增量不要写 --apply-log-only （如果没有写 就是告诉它 我这是最后一个文件了）

#恢复数据
 xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/
 #授权
 chown -R mysql.mysql /var/lib/mysql

登录mysql 进行查看

mysql -uroot -p'@King1314'
show databases;
#have new1 new3 则说明备份成功

总结

• 完全备份的时候 需要关闭数据库服务
• 增量备份的时候则不需要关闭数据库服务
• 备份完成之后需要重启mysqld 服务
• 最后一次准备增量 一定不要写 --apply-log-only 相当于是告诉他 我这是最后一个增量备份文件了

---

---

差异备份

• 第一次是完整备份
• 之后每次都会把所完成备份之后的内容 ，再次全部备份

一、 先做一次完整备份

#创建完整备份目录
mkdir -p /data/backup/base
#对数据库进行备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/base/  -uroot -p@King1314 -H localhost -P 3306  --no-server-version-check

二 、再做 第一次差异备份

其实就和第一次增量备份的命令 方法 方式是一样的

#给数据库插入数据
mysql   -P 3306 -u root -p'@King1314' -e 'create database zengliang_01;' 
mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 
mysql   -P 3306 -u root -p'@King1314' -e 'create table zengliang_01.t1 (id int,name varchar(40));' 
mysql   -P 3306 -u root -p'@King1314' -e 'show tables from zengliang_01;'
mysql   -P 3306 -u root -p'@King1314' -e 'insert into zengliang_01.t1 values (1,"yingzheng"),(2,"gaojianli"),(3,"baiqi"),(4,"shangyang");'
mysql   -P 3306 -u root -p'@King1314' -e 'insert into zengliang_01.t1 values (99,"hanwu");' 
mysql   -P 3306 -u root -p'@King1314' -e 'select * from zengliang_01.t1;' 
#准备增量备份
mkdir -p /data/backup/inc1/
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc1/  --incremental-basedir=/data/backup/base/ -u root -p@King1314 -H localhost -P 3306 --no-server-version-check
# 查看当前数据库内容  可以看到我们对新增的数据库 zengliang_01  进行了备份
mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 

完成备份 没有报错

三、再做第二次差异备份

• 第二次插入数据

mysql   -P 3306 -u root -p'@King1314' -e 'create database zengliang_02;' 
mysql   -P 3306 -u root -p'@King1314' -e 'show databases;'
mysql   -P 3306 -u root -p'@King1314' -e 'create table zengliang_02.t1 (id int,name varchar(40));' 
mysql   -P 3306 -u root -p'@King1314' -e 'show tables from zengliang_02;'
mysql   -P 3306 -u root -p'@King1314' -e 'insert into zengliang_02.t1 values (1,"liubang"),(2,"xiaohe"),(3,"zhangliang"),(4,"hanxin");'
mysql   -P 3306 -u root -p'@King1314' -e 'insert into zengliang_02.t1 values (99,"hanwu");' 
mysql   -P 3306 -u root -p'@King1314' -e 'select * from zengliang_02.t1;' 
# 查看当前数据库内容  可以看到我们对新增的数据库 zengliang_01，zengliang_01  进行了备份
mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 

• 差异备份

#先查看/data/backup 目录下的所有文件
ll /data/backup
#准备备份文件
mkdir -p /data/backup/inc2/
#和增量备份的区别在于 还是参照【完整备份的文件】进行备份 --incremental-basedir=/data/backup/base/ 
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/inc2/  --incremental-basedir=/data/backup/base/ -u root -p@King1314 -H localhost -P 3306 --no-server-version-check


#最好加一个步骤(如果你做到这里你肯定会回来感谢这个步骤的  真的)
cp -r /data/backup /data/backup_bak

四、准备备份文件

• 准备完整备份的文件
• 准备最后一次差异备份的文件

#准备完整备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/
#准备最后一次差异备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc2
#因为是最后一次 所以不用加 --apply-log-only

五、删库

systemctl   stop  mysqld
rm -rf  /var/lib/mysql/*
ls /var/lib/mysql/


systemctl   status  mysqld

六、对数据进行恢复，并且 把文件授权给 mysql 这个组 和用户

 #恢复
 xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/ 
 #授权
 chown -R mysql.mysql /var/lib/mysql
 
 #重启数据库
 systemctl restart mysqld
 #查看数据库内容
 mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 
 mysql   -P 3306 -u root -p'@King1314' -e 'select * from zengliang_02.t1;' 
 

七、把数据修改为第一天的备份

#删除
systemctl stop mysqld
rm -rf  /var/lib/mysql/*

#已经准备文件  但是是第二次的  这个时候我想使用第一次的准备文件


#准备完整备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/base/

#准备第一天的差异备份的文件
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/inc1


#恢复
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/base/ 
#授权
chown -R mysql.mysql /var/lib/mysql
systemctl restart mysqld
#查看数据库内容
mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 
mysql   -P 3306 -u root -p'@King1314' -e 'select * from zengliang_02.t1;' 

---

压缩备份

查看数据库内容

mysql   -P 3306 -u root -p'@King1314' -e 'show databases;' 
mysql   -P 3306 -u root -p'@King1314' -e 'select * from zengliang_02.t1;' 

压缩备份

mkdir -p /data/backup/compressed
xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compressed/  -uroot -p@King1314 -H localhost -P 3306 --no-server-version-check

#--compress  这个选项就是压缩备份

解压

#解压
xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compressed/
	#--parallel可与--decompress选项一起使用以同时解压缩多个文件


#准备文件
xtrabackup --prepare --target-dir=/data/backup/compressed

恢复

#删库
systemctl stop mysqld
rm -rf  /var/lib/mysql/*

#恢复
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup/compressed
#授权
chown -R mysql.mysql /var/lib/mysql
systemctl restart mysqld