MySQL数据备份和恢复

一、备份方式：

1、物理备份&逻辑备份：

物理备份的备份方式：

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

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

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

2、逻辑备份：

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

 二、第一个备份——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');     #添加数据
mysql> insert into t_user values(2,'lisi');
mysql> insert into t_user values(3,'wangwu');

2、进行备份

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

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

3、赋予权限  

        1.查看当前权限         

mysql> show variables like '%secure%';

         2.进入/etc/my.cnf，修改配置

secure-file-priv=/tmp

         3.然后重启mysql，查看信息

        4.重新执行命令，并查看备份文件

         5、恢复数据

 load data infile '/tmp/user.txt' into table t_user;

三、物理备份工具-Xtrabackup

1.安装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

2.完整备份

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

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

3.恢复备份

1.关闭数据库
# 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.启动数据库，查看数据
# service mysqld start

四、增量备份

 创建备份：

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

1、常用关键字

-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

2、备份所有数据

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

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

3.查看是否生成文件

3、备份和恢复指定库内容

[root@localhost mysqldumpdata]# mysql -uroot -p111111 < /tmp/mysqldumpdata/all.sql
注意mysqldump只能恢复表级内容，不能恢复库，所以当数据库失踪时需要先手动创建数据库再执行恢复命令

4、备份和恢复指定库内容

备份指定库
[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

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

5、备份和恢复指定表内容

备份指定表
[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