MySQL备份与恢复

备份的分类

1）、从物理和逻辑的角度

1、物理备份：
对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

日志类型与作用：

1)redo 重要日志：达到事务一致性（每次重启会重做）
2)undo 回滚日志：保证数据的原子性
3)errorlog 错误日志：MySQL本身启动，停止，运行期间发生的错误信息
4)slow query log 慢查询日志：记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功
记录的内容是：数据库中执行的sql语句
5)bin log 二进制日志：用于主从复制，实现主从同步
6)relay log 中继日志：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
7)general log 普通日志：记录数据库的操作明细，默认关闭，开启后会降低数据库性能

配置文件

mysql> show variables like 'general%'; 查看通用查询日志是否开启

mysql> show variables like 'log_bin%'; 查看二进制日志是否开启

mysql> show variables like '%slow%'; 查看慢查询日功能是否开启

mysql> show variables like 'long_query_time'; 查看慢查询时间设置

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld

mysql> show variables like '%slow%';

mysql> show variables like 'long_query_time';

备份的分类

1）、从物理和逻辑的角度

1、物理备份：
对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

2）、从数据库的备份策略角度

1、完全备份：每次对数据库进行完整的备份
2、差异备份：备份自从上次完全备份之后被修改过的文件
3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

备份方式比较

备份方式 完全备份 差异备份 增量备份
完全备份时的状态 表1、表2 表1、表2 表1、表2
第1次添加内容 创建表3 创建表3 创建表3
备份内容 表1、表2、表3 表3 表3
第2次添加内容 创建表4 创建表4 创建表4
备份内容表 1、表2、表3、表4 表3、表4 表4

常见的备份方法

1）、物理冷备

备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的

2）、专业备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3）、启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

4）、第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

完全备份

1）、简介

1、是对整个数据库、数据库结构和文件结构的备份
2、保存的是备份完成时刻的数据库
3、是差异备份与增量备份的基础
4、每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数据库

2）、优点

备份与恢复操作简单方便

3）、缺点

数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

实验

环境准备

mysql> create table if not exists info (id int(4) not null auto_increment,name varchar(10) not null,age char(10) not null,hobby varchar(50),primary key(id));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into info values (1,'user1',20,'running');
Query OK, 1 row affected (0.00 sec)
mysql> insert into info values (2,'user2',30,'running');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;

物理冷备份与恢复

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# yum -y install xz

[root@localhost ~]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/

[root@localhost opt]# systemctl start mysqld
[root@localhost opt]# mysql -uroot -pabc123

mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;

[root@localhost mysql]# tar Jxvf /opt/mysql_all_2021-07-12.tar.xz -C /usr/local/mysql/data/
mv /usr/local/mysql/usr/local/mysql/data/ /usr/local/mysql/
systemctl restart mysqld

[root@localhost ~]# mysql -uroot -pabc123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.