Linux之MySQL的数据备份

一、逻辑备份

备份的是建表、建库、插入等操作所执行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 ~]# shown -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/