Linux之MySQL的数据备份，Linux下MySQL数据备份攻略

摘要：，，Linux系统下的MySQL数据库备份是确保数据安全的重要环节。备份过程涉及对数据库内容的复制和存储，以防数据丢失或损坏。管理员使用专门的工具或命令，如mysqldump，进行数据库备份。备份过程包括选择备份类型、安排备份时间以及选择备份存储位置。备份的重要性在于能够迅速恢复数据，减少系统故障带来的损失。定期的数据备份是保障MySQL数据库安全的关键措施。

目录

一、逻辑备份

 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密码   
第二种方法：
 
[root@localhost ~]# cat dbname.sql | mysql -p密码
 
第三种方法：
 
mysql> source /opt/backup/dbname.sql; 
 
2.恢复数据表
 第一种方法：
 
[root@localhost ~]# mysql -p密码 dbname   
第二种方法：
 
[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、差异备份
 如果后面星期三星期四还有数据，可以基于星期一的全量备份做差异备份，相当于把数个增量备份合并在一起，恢复时还是基于全量备份依次重演，然后回滚即可，操作步骤同增量备份一致