目录
MySQL备份是基于对MySQL的日志进行备份,且恢复也是通过日志进行数据恢复。
物理备份:直接对数据库的数据文件或日志文件进行备份
逻辑备份:对数据库的库或表对象进行备份
完全备份:每次备份都备份完整的数据库
差异备份:只备份上一次完全备份后的更新数据
增量备份:每次备份只备份上一次完全备份或增量备份后的更新数据
方法一:物理冷备
#首先关闭mysql服务
systemctl stop mysqld.service
#其次将mysql安装目录下的data目录使用tar命令进行打包,并备份到其他磁盘
tar zcvf all.sql.tar.gz /usr/local/mysql/data -C /data/
方法二:逻辑热备(mysql服务可以不用关闭)
mysqldump -uroot -p123123 --all-databases > /data/all.sql
还可以使用逻辑热备,备份一个数据库(表)或多个数据库(表)
备份一个或多个数据表
格式:
mysqldump -uXXX -p[XXX] 库名 表1 表2 ... > XXX.sql
备份一个或多个数据库
mysqldump -uXXX -p[XXX] --databases 库1 [库2 ...] > XXX.sql
增量备份是基于上一次完全备份的基础上或上一次增量备份的基础之上进行备份的操作,所以,增量备份是通过二进制日志文件进行备份的。
在做增量备份之前,需要在/etc/my.cnf文件中添加一个语句,开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1
重启mysql服务,在mysql安装目录下的data目录下会出现mysql-bin.000001二进制日志文件
登录数据库,并创建库和表,然后刷新二进制日志文件
mysql -uroot -p123123
create database class;use class;
create table course(name varchar(10),class char(10),sno int,chines int, math int, english int,lz int);
insert into course values('张三','一班',20170822,110,105,92,235);
insert into course values('李四','一班',20170820,95,115,110,260);
insert into course values('王五','一班',20170818,95,103,108,270);
insert into course values('赵六','一班',20170816,100,109,112,265);
create database test;
use test;
create table location (Region char(20),Store_Name char(20));
insert into location values('East','Boston');
insert into location values('East','New York');
insert into location values('West','Los Angeles');
insert into location values('West','Houston');
create table store_info (Store_Name char(20),Sales int(10),Date char(10));
insert into store_info values('Los Angeles','1500','2020-12-05');
insert into store_info values('Houston','250','2020-12-07');
insert into store_info values('Los Angeles','300','2020-12-08');
insert into store_info values('Boston','700','2020-12-08');quit #退出数据库
mysqladmin -uroot -p123123 flush-logs #刷新二进制日志文件
将之前的二进制日志文件进行备份,实现对之前数据库的完全备份
cp mysql-bin.000001 /data/mysq-bin-$(date -d "-1 day" +%Y%m%d).sql
继续对mysql中的class库中的course表插入数据库
insert into course values ('李宁','二班',20170824,92,98,105,235);
insert into course values ('李宁','二班',20170824,92,98,105,235);
刷新数据库的二进制日志文件,并进行增量备份
mysqladmin -uroot -p123123 flush-logs #刷新二进制日志文件
cp mysql-bin.000002 /data/mysq-bin-$(date +%Y%m%d).sql
在插入数据,再次做增量备份
insert into course values('付杰','二班',20170828,115,118,116,268);
insert into course values('郭尚','二班',20170830,111,99,80,259);
mysqladmin -uroot -p123123 flush-logs
cp mysql-bin.000003 /data/mysql-bin-$(date -d "1 day" +%Y%m%d).sql
方法一:对于物理冷备的方式的完全恢复
#首先停掉mysql服务,模拟数据丢失,删除掉所有mysql安装目录下data目录里的内容
systemctl stop mysqld.service
rm -rf /usr/locat/mysql/data
#然后将物理冷备的压缩包解压,并保留权限的复制到mysql安装目录下data目录里
tar xf /data/all.sql.tar.gz
方法二:针对逻辑热备的完全恢复
先删除数据库class和test数据库
drop database class;
drop database test;
逻辑完全恢复数据库class和test
mysql -uroot -p123123 < /data/class_test.sql
1、删除数据库class和test,模拟数据丢失
drop database class;
drop database test;
2、 进行完全备份的恢复,恢复第一次完成备份的数据
mysqlbinlog --no-defaults /data/mysq-bin-20230918.sql | mysql -uroot -p123123
mysql -u root -p123123
show databases;
select * from class.course;
3、对第一次增量备份进行恢复(可以选择性恢复,如仅恢复第一次增量备份的)
首先看一下二进制文件中的内容
1)基于位置进行恢复
仅恢复“李宁”的数据
mysqlbinlog --no-defaults --stop-position='475' /data/mysql-bin-20230920.sql |mysql -uroot -p123123
#根据上图可以看到李宁的数据提交在475位置之前,所以将位置结束在475,就可以仅恢复李宁的数据
2)基于时间恢复
仅恢复“陈铭”的数据