本文详细介绍了MySQL数据备份的重要性，涵盖物理备份（冷备、热备、温备）与逻辑备份（完全、差异、增量）的方法，以及实例演示了mysqldump和binlog的使用，包括交互式与无交互恢复，以及针对错误处理的增量恢复技巧。
目录
一、日志
MySQL 的日志默认保存位置为 /usr/local/mysql/data
日志类型与作用：
1.redo 重做日志：达到事务一致性（每次重启会重做）
作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redo log执行重做，达到事务一致性
2.undo 回滚日志作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交 隔离级别就是通过mvcc+undo实现
3.errorlog 错误日志
作用：Mysql本身启动，停止，运行期间发生的错误信息
4.slow query log 慢查询日志
作用：
4.1、记录执行时间过长的sql，时间阈值（10s）可以配置，只记录执行成功
4.22、在于提醒优化
5.bin log 二进制日志
作用：用于主从复制，实现主从同步
记录的内容是：数据库中执行的sql语句
6.relay log 中继日志
作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
7.general log 普通日志
作用：记录数据库的操作明细，默认关闭，开启后会降低数据库性能
二、数据备份的重要性
1、备份的主要目的是灾难恢复。
2、在生产环境中，数据的安全性至关重要。
3、任何数据的丢失都可能产生严重的后果。
4、造成数据丢失的原因：程序错误、人为操作错误、运算错误、磁盘故障、灾难 (如火灾、地震) 和盗窃。
三、数据备份类型
一、物理备份
数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份
1、 冷备份 (脱机备份) ：是在关闭数据库的时候进行的（tar）
2、热备份 (联机备份) ：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
3、温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）
二、逻辑备份
逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构。
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份。
一、 完全备份
每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长。
优点：
备份与恢复操作简单方便
缺点：
1、数据存在大量的重复
2、占用大量的备份空间
3、备份与恢复时间长
二、差异备份
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份。
三、增量备份
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
四、备份方式
1、物理冷备（使用tar）
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
2、专用备份工具 mysqldump 或 mysqlhotcopy
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表
3、启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
4、第三方工具备份
免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup
五、实例
一、冷备
[root@localhost ~]# systemctl stop mysqld #关闭数据库
[root@localhost ~]# yum -y install xz #安装压缩格式
[root@localhost ~]# cd /opt
[root@localhost opt]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost opt]# ls -lh
[root@localhost opt]# mv /usr/local/mysql/data/ /opt/
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# tar Jxvf /opt/mysql_all_2021-07-14.tar.xz -C /usr/local/mysql/
#此时解压后，没有data目录，有usr目录
[root@localhost mysql]# mv ./usr/local/mysql/data/ ./ #移动覆盖将data目录放到MySQL下
登录数据库验证
二、mysqldump
备份
1、单个库完全备份
[root@localhost ~]# mysqldump -uroot -p123 --databases py > /opt/py.sql #备份数据库py
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
2、多个库完全备份
[root@localhost opt]# mysqldump -uroot -p123 --databases py mysql > /opt/py-mysql.sql #备份数据库py和mysql
[root@localhost opt]# ls
3、完全备份指定的库的部分表
[root@localhost opt]# mysqldump -uroot -p123 -d py hsh > /opt/py_hsh.sql #备份数据库py中的表hsh，-d只备份表结构
[root@localhost opt]# grep -v "^--" /opt/py_hsh.sql | grep -v "^/" | grep -v "^$" #查看备份文件
[root@localhost opt]# mysqldump -uroot -p123 py hsh1 > /opt/py_hsh1.sql #不加-d备份完整的表，包含表数据
[root@localhost opt]# grep -v "^--" /opt/py_hsh1.sql | grep -v "^/" | grep -v "^$" #查看备份文件
恢复
方式一：交互式恢复
[root@localhost opt]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| py |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> drop database py;
Query OK, 3 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> source /opt/py.sql #执行备份文件
方式二：无交互恢复
[root@localhost opt]# mysqldump -uroot -p123 --databases py > /opt/py.sql
[root@localhost opt]# mysql -uroot -p123 -e 'drop database py;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost opt]# mysql -uroot -p123 < /opt/py.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| py |
| sys |
+--------------------+
ps；在备份时
1、加--databases是对库进行操作，回复时将备份的库完整恢复
2、不加--databases是对库中的所有表进行操作，恢复时只恢复表，使用于将库中表转移目标库中
增量备份及恢复
一、一般恢复
将所有备份的二进制日志内容全部恢复
[root@localhost opt]# vim /etc/my.cnf
log-bin=mysql-bin #开启bin-log
binlog_format = MIXED #指定二进制日志(binlog)的记录格式为MIXED（混合输入），STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
server-id = 1 #标识该语句最初是从哪个server写入的
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# systemctl restart mysqld
[root@localhost data]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/
[root@localhost data]# cd /opt
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 #需要解码才能查看文件
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
1、at ：开始的位置点
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30
[root@localhost opt]# mysql -u root -p123
mysql> create database hh;
mysql> use hh;
mysql> create table test1 (id int(4),name varchar(4));
mysql> insert into gg values(1,'abc'),(2,'def');
[root@localhost opt]# mysqladmin -u root -p123 flush-logs #生成新的日志
再次进入MySQL数据库中
mysql> drop database hh;#删除hh库
[root@localhost opt]# mysqladmin -u root -p123 flush-logs
[root@localhost opt]# mysqlbinlog --no-defaults / opt/mysql-bin.000002 | mysql -u root -p123 #基于mysql-bin.000002日志文件恢复
进入数据库中
mysql> show databases; #查看库，发现hh库恢复
+--------------------+
| Database |
+--------------------+
| information_schema |
| hh |
| mysql |
| performance_schema |
| py |
| sys |
+--------------------+
6 rows in set (0.02 sec)
二、基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作，发生错误节点之前的一个节点，上一次正确操作的位置点停止。
[root@localhost opt]# mysql -uroot -p123
mysql> create database hh;
mysql> use hh
#插入三条数据
mysql> insert into gg values(3,'a');
mysql> insert into gg values(4,'a');
mysql> insert into gg values(5,'b');
[root@localhost opt]# mysqladmin -u root -p123 flush-logs #生成日志
[root@localhost opt]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 #读取日志内容
在数据中删除hh库
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='563' /opt/mysql-bin.000001 | mysql -uroot -p123
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='661' /opt/mysql-bin.000001 | mysql -uroot -p123 #基于完全备份恢复前重新导入完备文件
再次进入数据库，查看hh库中的gg表
三、基于时间点恢复
跳过某个发生错误的时间点实现数据恢复，在错误时间点停止，在下一个正确时间点开始。
基于完全备份恢复前重新导入完备文件
mysqlbinlog --no-defaults --stop-datetime='2021-07-14 22:52:25' /opt/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog --no-defaults --start-datetime='2021-07-14 22:52:33' /opt/mysql-bin.000001 | mysql -uroot -p
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
