MySQL数据备份与恢复

快捷查看指令 ctrl+f 进行搜索会直接定位到需要的知识点和命令讲解（如有不正确的地方欢迎各位小伙伴在评论区提意见，博主会及时修改）

MySQL数据备份与恢复

防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

备份方式

从生成备份的内容区分

物理备份

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

完整备份

每次对数据进行完整的备份，保存的是当前数据库中所有的数据，是差异备份与增量备份的基础。
优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快
缺点：占用空间大，备份速度慢

增量备份

每次备份上一次备份到现在产生的新数据
特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，
逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

差异备份

只备份跟完整备份不一样的
特点：差异备份其实就是增量备份的中特殊操作，它体现在差异备份只能以一个完整备份为基础，占用空间比增量备份大，
比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

逻辑备份

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

	物理备份	逻辑备份
备份方式	备份数据库数据	备份数据库建表、建库、插入sql语句
优点	恢复速度比较快	备份文件相对较小,只备份表中的数据与结构
缺点	备份文件相对较大(备份表空间,包含数据与索引)	恢复速度较慢(需要重建索引,存储过程等)
对业务影响	I/O负载加大	I/O负载加大
代表工具	ibbackup、xtrabackup开源，mysqlbackup	mysqldump

工作中使用需要使用哪种需要根据业务和数据量来决定，一般情况下，数据量特别巨大时会采用物理备份，数据量中小时采取逻辑备份

从恢复数据的操作区分

冷备份

工作中使用需要使用哪种需要根据业务和数据量来决定，一般情况下，数据量特别巨大时会采用物理备份，数据量中小时采取逻辑备份

温备份

恢复备份时，其他人可对数据库进行查询

热备份

恢复数据时，其他人可对数据库进行所有操作

工作中使用什么方式恢复数据是由业务决定的，当恢复的业务非常重要需要使用冷备份，当恢复的业务相对不重要选择温备份或热备份
比如：银行ATM程序恢复选择冷备份
图书馆资料管理程序选择热备份

select into备份(临时)

sql中的一个基础命令，可以完成数据备份，但是由于十分简陋，只能适用于临时的数据备份

1. 准备数据
mysql> create database back;  #创建back数据库
mysql> use back;		 #切换到back
mysql> create table t_user(id int primary key,name varchar(20));   #创建t_user表
mysql> insert into t_user
values(1,'zhangsan'),     #添加数据
(2,'lisi'),
(3,'wangwu');

2. 准备备份
#语法
	select 语句 into outfile '目标文件'
mysql> select * from t_user into outfile '/tmp/user.txt'
#将select的查询结果数据储存到/tmp/user.txt

3. 赋予权限
当报错如上时，表示msyql没有使用select into 备份权限

3.1 查看当前权限
mysql> show variables like '%secure%';

3.2 进入 /etc/my.cnf修改配置
添加  secure-file-priv=/tmp
然后重启mysql，查看信息

3.3 重新执行SQL指令,并查看备份文件

3.4 删除数据

3.5 恢复数据
 load data infile '/tmp/user.txt' into table t_user;

3.6 查询数据

物理备份工具-Xtrabackup

Xtrabackup是开源免费的支持MySQL 数据库热备份的软件，在 Xtrabackup 包中主要有 Xtrabackup 和 innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。

安装Xtrabackup

# 下载并解压
[root@localhost tmp]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar
[root@localhost tmp]# tar xvf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

# 安装
[root@localhost tmp]# yum install -y percona-xtrabackup-24-2.4.9-1.el6.x86\_64.rpm

完整备份

创建备份

创建备份目录
[root@mysql-server ~]# mkdir -p /xtrabackup/full
执行备份命令，将当前mysql所有数据进行备份
[root@mysql-server ~]# innobackupex --user=root --password='111111' -S /tmp/mysql.sock  /xtrabackup/full

--user: 数据库登陆用户名
--password: 密码
-S :数据库套接文件地址，在/etc/my.cnf的socket中获取

恢复备份

1.关闭数据库
[root@mysql-server ~]#service mysqld stop
2.删除数据库所有数据，在/etc/my.cnf中的datadir获取储存位置
  #注意一旦删除所有数据，mysql数据就理论上被损坏了，无法再启动，必须恢复数据后才能使用
[root@mysql-server ~]# rm -rf /opt/liuyh/data/mysql/*

3.重演数据，也就是在恢复数据之前先检查一下备份文件中是否有问题
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2021-11-17_00-37-48

4.恢复数据
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2021-11-17_00-37-48

5.数据恢复后，到mysql指定的数据储存位置查看是否有数据文件

6.设置权限，注意恢复后的文件需要将权限设置为mysql数据库的拥有者可执行权限
[root@mysql-server ~]#chown -R mysql:mysql /opt/liuyh/data/mysql/*

7.启动数据库，查看数据

增量备份

创建备份

1.先创建完整备份

2.修改数据库数据

3.创建增量备份
[root@mysql-server ~]# innobackupex --user=root --password=111111 -S /tmp/mysql.sock
--incremental /xtrabackup/full --incremental-basedir=/xtrabackup/full/2023-11-17_15-57-12

--incremental：指定增量备份生成位置
--incremental-basedir：指定以哪个备份为基础做增量备份，注意：所选备份应为一个完整备份或增量备份

恢复备份

1.关闭数据库

2.删除数据库所有数据，在/etc/my.cnf中的datadir获取储存位置
  #注意一旦删除所有数据，mysql数据库就理论上被损坏了，无法再启动，必须恢复数据后才能使用
[root@mysql-server ~]# rm -rf /opt/liuyh/data/mysql/*

3.重演数据，并整合数据
#重演完整备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12
#按生成顺序将第一个增量备份整合到完整备份中
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12
--incremental-dir=/xtrabackup/full/2021-11-17_16-01-25
#按生成顺序将第二个增量备份整合到完整备份中
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2021-11-17_15-57-12
--incremental-dir=/xtrabackup/full/2021-11-17_16-02-01

4.恢复数据，此时所有数据都已经保存在完整备份中，只需恢复完整备份即可
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2021-11-17_15-57-12 

5.设置权限，注意恢复后的文件需要将权限设置为mysql数据库的拥有者可执行权限
[root@mysql-server ~]#chown -R mysql:mysql /opt/liuyh/data/mysql/*

6.启动数据库，查看数据

差异备份

在上面我们已经知道差异备份其实就是增量备份的一种，它的操作与增量备份一致，区别在于生成差异备份时只能以一个完整备份为基础

逻辑备份工具-mysqldump

mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。

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

参数介绍:
	-A, --all-databases #备份所有库

	-B, --databases  #备份多个数据库

	-F, --flush-logs #备份之前刷新binlog日志

	--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，
							那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

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

	--lock-tables #备份前，锁定所有数据库表

	--single-transaction #保证数据的一致性和服务的可用性

	-f, --force #即使在一个表导出期间得到一个SQL错误，继续。


着重强调：
使用 mysqldump 备份数据库时避免锁表:
对一个正在运行的数据库进行备份请慎重，尽量不要在数据库开放服务时备份，如果一定要在服务运行期间备份，
可以选择添加 --single-transaction选项，

类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql

备份和恢复所有数据

1.创建备份目录
[root@mysql-server ~]# mkdir /tmp/mysqldumpdata

2.备份当前数据库所有数据
[root@mysql-server ~]# mysqldump -uroot -p111111 -A > /tmp/mysqldumpdata/all.sql

3.查看是否生成文件
ls /tmp/mysqldumpdata/
4.恢复所有数据
[root@localhost mysqldumpdata]# mysql -uroot -p111111 < /tmp/mysqldumpdata/all.sql

备份和恢复指定库内容

备份指定库
[root@mysql-server ~]# mysqldump -uroot -p111111 -B back > /tmp/mysqldumpdata/back.sql

-B:指定数据库，多个库之间用空格区分

恢复指定库
[root@mysql-server ~]# mysql -uroot -p111111 -B mysql </tmp/mysqldumpdata/mysql.sql

注意:恢复时只能指定一个库操作，若有多个则多次执行

备份和恢复指定表内容

备份指定表
[root@mysql-server ~]# mysqldump -uroot -p111111 -B back --tables t_user > /tmp/mysqldumpdata/back.sql

--tables：指定表

恢复指定表
[root@mysql-server ~]# mysql -uroot -p111111 -B back </tmp/mysqldumpdata/user.sql

通过binlog恢复数据

# 开启binlog日志 修改/etc/my.cnf并添加
[root@mysql-server ~]# vim /etc/my.cnf
log-bin=/opt/liuyh/log/mylog
server-id=1

# 创建目录并修改权限
[root@mysql-server ~]# mkdir /opt/liuyh/log/mylog
[root@mysql-server ~]# chown -R mysql:mysql /opt/liuyh/log/mylog

重启mysql

进入mysql

mysql> flush logs; #刷新binlog日志会截断产生新的日志文件

mysql> create table t3(id int);   #创建一个表

查看日志文件信息，找到要恢复内容的指定位置
[root@mysql-server ~]# mysqlbinlog mylog.000002

# 测试
[root@mysql-server ~]# mysql -uroot -p111111
mysql> drop tablet3;   #将这个表删除
Query OK, 0 rows affected (0.01 sec)
恢复：
[root@mysql-server sql-bin]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p111111

查看mysql,表恢复