Linux之MySQL的数据备份

发布于:2024-07-11 ⋅ 阅读:(21) ⋅ 点赞:(0)

目录

一、逻辑备份

 1、备份数据

1.全库备份

2.库级备份

3.表级备份

4.备份表结构

5.导出数据,不导出表结构

1.查看安全路径:

​2.导出数据

​3.导入数据

6.恢复数据 

1.恢复数据库 

2.恢复数据表

7.更改数据库导出安全目录

 二、根据二进制日志文件恢复数据

1、开启binlog日志功能

2、重启数据库

3、模仿数据库数据丢失

​4、查看二进制日志

5、恢复数据

第一种方法:根据position位置点

第二种方法:根据时间恢复

三、物理备份

 1、全量备份

1.建立备份文件所需存放的目录

2.开始备份 

3.恢复数据

​2、增量备份 

3、差异备份


一、逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句,适用于中小型数据库,效率相对较低。

本质:导出的是SQL语句文件

优点:不论是什么存储引擎,都可以用mysqldump备成SQL语句

缺点:速度较慢,导入时可能会出现格式不兼容的突发情况,无法做增量备份和累计增量备份。

提供三种级别的备份,表级,库级和全库级

逻辑备份: 备份的是建表、建库、插入等操作所执行SQL语句(DDL DML DCL),适用于中小型数据库,效率相对较低。

数据一致,服务可用。:如何保证数据一致,在备份的时候进行锁表会自动锁表。锁住之后在备份。

本身为客户端工具:
远程备份语法: # mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
本地备份语法: # mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

常用备份选项:

-A        --all-databases \ 备份所有库

-B        --databases bbs test mysql 备份多个数据库

--no-data        -d\ 不导出任何数据,只导出数据库表结构。  

mysqldump -u用户名 -p密码 -h主机地址 -P端口 -S套接字文件 


 1、备份数据

1.全库备份

[root@localhost ~]# mysqldump -p密码 [-A|--all-databases]  >alldb.sql


2.库级备份

[root@localhost ~]# mysqldump -p密码 [-B|--databases] dbname1 dbname2 >dbname.sql 


3.表级备份

[root@localhost ~]# mysqldump -p密码 dbname tablename1 tablename2 | gzip >db_t1_t2.sql.gz

备份并压缩:

4.备份表结构

[root@localhost ~]# mysqldump -p密码 dbname tablename -d > tablename_jiegou.sql

5.导出数据,不导出表结构

1.查看安全路径:
mysql> show variables like "secure%"; 
 
2.导出数据
mysql> select * from school.stu into outfile '/var/lib/mysql-files/stu.xlsx';

3.导入数据

把表里数据先清空,注意:不要把表结构也删了,

mysql> load data infile '/var/lib/mysql-files/stu.xlsx' into table stu;

然后看效果 

6.恢复数据 

1.恢复数据库 

第一种方法:

[root@localhost ~]# mysql -p密码  < dbname.sql

第二种方法:

[root@localhost ~]# cat dbname.sql | mysql -p密码

第三种方法:

mysql> source /opt/backup/dbname.sql; 
2.恢复数据表

 第一种方法:

[root@localhost ~]# mysql -p密码 dbname  < table.sql

第二种方法:

[root@localhost ~]# cat table.sql | mysql -p密码 dbname

第三种方法:

mysql> use dbname
mysql> source /opt/backup/table.sql;

7.更改数据库导出安全目录

查看数据库导出安全目录:

mysql> show variables like "secure%";


修改:

[root@localhost ~]# mkdir /data
[root@localhost ~]# chown mysql.mysql /data
[root@localhost ~]# vim /etc/my.cnf

在my.cnf中添加:

secure_file_priv=/data/     

   
新开一个窗口,重启数据库,检查更改后的安全目录是否生效:

[root@localhost ~]# systemctl restart mysqld

 二、根据二进制日志文件恢复数据

1、开启binlog日志功能

[root@localhost ~]# mkdir /data
[root@localhost ~]# chown mysql.mysql /data
[root@localhost ~]# vim /etc/my.cnf

添加以下两行:

2、重启数据库

[root@localhost ~]# systemctl restart mysqld

 3、模仿数据库数据丢失

 4、查看二进制日志

[root@localhost ~]# mysqlbinlog /data/mysql-bin.000001 --base64-output=decode-rows -vv

5、恢复数据

第一种方法:根据position位置点

[root@localhost ~]# mysqlbinlog --start-position 796 --stop-position 961 /data/mysql-bin.000001 | mysql -pQianfeng@123 school

 查看是否恢复:

第二种方法:根据时间恢复

 我们这次再模仿数据丢失,把school库里面的stu表给删了

还是查看二进制日志文件,找到建表跟插入数据的信息

 

 

执行恢复操作:

[root@localhost ~]# mysqlbinlog --start-datetime="2023-12-23 15:06-13" --stop-datetime="2023-12-23 15:09:41" /data/mysql-bin.000001 | mysql -pQianfeng@123

三、物理备份


物理备份: 直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。


安装xtrabackup :选择适合的版本即可

官网Software Downloads - Percona

 1、全量备份

1.建立备份文件所需存放的目录

[root@localhost ~]# mkdir -p /data/{full,intr,xebf}
[root@localhost ~]# chown -R mysql.mysql /data

full 存放全量备份
intr 存放增量备份
xebf 存放差异备份

2.开始备份 

[root@localhost xtrabackup]# innobackupex --user=root --password='Qianfeng@123' /data/full

3.恢复数据

 先停止数据库,然后清理数据目录(生产环境建议用mv

[root@localhost xtrabackup]# systemctl stop mysqld
[root@localhost xtrabackup]# rm -rf /var/lib/mysql/*

重演:

[root@localhost full]# innobackupex --user=root --password='Qiangfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/

 回滚:

[root@localhost full]# innobackupex --user=root --password="Qianfeng@123" --copy-back /data/full/2023-12-23_16-49-08/

此时数据已恢复,修改权限然后启动数据库:

[root@localhost mysql]# chown -R mysql.mysql *
[root@localhost mysql]# systemctl start mysqld

 查看数据是否恢复:

2、增量备份 

在之前备份基础上再添加一条数据做增量备份

[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --incremental /data/intr --incremental-basedir=/data/full/2023-12-23_16-49-08/

清理环境同上

依次重演:先重演第一次全量备份,再在全量备份基础上重演增量备份数据

[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/
[root@localhost full]# innobackupex --user=root --password='Qianfeng@123' --apply-log --redo-only /data/full/2023-12-23_16-49-08/ --incremental-dir=/data/intr/2023-12-23_17-10-22/

 回滚:

[root@localhost full]# innobackupex --user=root --password=Qianfeng@123 --copy-back /data/full/datetime

修改权限,启动数据库,查看数据是否恢复

3、差异备份

 如果后面星期三星期四还有数据,可以基于星期一的全量备份做差异备份,相当于把数个增量备份合并在一起,恢复时还是基于全量备份依次重演,然后回滚即可,操作步骤同增量备份一致