前言
数库作为信息系统的核心担当着重要的角色,数据库备份,是在数据丢书的情况下,能即时恢复重要数据,防止数据丢失的一种重要手段。一个合理的数据库备份,应该能够在数据丢失时,有效的恢复数据。
备份的主要目的是灾难恢复,备份还可以测试应用、回滚数据修改、查询历史数据、审计等。
而备份、恢复中,日志起到了很重要的作用
日志管理
MySQL 的日志默认保存位置为 /usr/local/mysql/data
##配置文件
vim /etc/my.cnf
一、日志管理
(1)错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启。
vim /etc/my.cnf
[mysqld]
log-error=/home/mysql/mysql_error.log #指定日志的保存位置
(2)二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
vim /etc/my.cnf
[mysqld]
general_log=ON
general_log_file=/home/mysql/mysql_general.log
(3)一般情况下它在Mysql主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #也可以log_bin=mysql-bin
#使用相对路径,则文件存储在默认目录/usr/local/mysql/data/中
(4)慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便提醒优化,默认是关闭的
vim /etc/my.cnf
[mysqld]
slow_query_log=ON
slow_query_log_file=/home/mysql/mysql_slow_query.log
long_query_time=5 #慢查询时间,设置超过5秒执行的语句被记录,缺省时为10秒
二、配置四种日志
1、修改配置文件/etc/my.cnf
vim /etc/my.cnf
[mysqld]
##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
log-error=/home/mysql/mysql_error.log
##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
general_log=ON
general_log_file=/home/mysql/mysql_general.log
##二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
log-bin=mysql-bin #也可以log_bin=mysql-bin
#使用相对路径,则文件存储在默认目录/home/mysql/中
##慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便于优化,默认是关闭的
slow_query_log=ON
slow_query_log_file=/home/mysql/mysql_slow_query.log
long_query_time=5 #慢查询时间,设置超过5秒执行的语句被记录,缺省时为10秒
2、重启服务(mysqld)
systemctl restart mysqld
三、查询日志是否开启
#首先先登录到数据库中
mysql -u root -p[密码]
#查看错误日志存放位置
show variables like 'log_error';
#查看通用查询日志是否开启
show variables like 'general%';
#查看二进制日志是否开启
show variables like 'log_bin%';
#查看慢查询日功能是否开启
show variables like '%slow%';
#查看慢查询时间设置
show variables like 'long_query_time';
#在数据库中设置开启慢查询的方法,即以修改变量值的方式开启。但重启服务后会失效。
set global slow_query_log=ON;
注:variables 表示变量 like 表示模糊查询
#xxx(字段)
xxx% 以xxx为开头的字段
%xxx 以xxx为结尾的字段
%xxx% 只要出现xxx字段的都会显示出来
xxx 精准查询
四、分割日志
mysql-bin.index 文件会对二进制日志进行分割。
每次重启 mysql 或 在数据库中 "flush logs"刷新日志之后,会生成一个新的二进制日志。
前言
备份的主要目的是灾难恢复,备份还可以测试应用、回滚数据修改、查询历史数据、审计等。
而备份、恢复中,日志起到了很重要的作用
一、数据备份的重要性
备份的主要目的是灾难恢复。
在生产环境中,数据的安全性至关重要。
任何数据的丢失都可能产生严重的后果。
造成数据丢失的原因:
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
二、数据库备份的分类和备份的策略
(1)物理备份
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。
物理备份方法:
冷备份(脱机备份) :是在关闭数据库的时候进行的
热备份(联机备份) :数据库处于运行状态,依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
(2)逻辑备份
逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份。
即以sql语句的形式,把库、表结构、表数据保存下来。
数据库的策略
完全备份(全量备份):每次对数据库进行完整的备份。
差异备份:备份自从上次完全备份之后被修改过的文件。
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份。
三、常见的备份方法
1、物理冷备
备份时数据库处于关闭状态,直接打包数据库文件(tar)
备份速度快,恢复时也是最简单的
2、专用备份工具 mysqldump 或 mysqlhotcopy
mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表
3、启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
MySQL支持增量备份,进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制,对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。
4、第三方工具备份
免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup
四、mysql完全备份介绍及数据备份与恢复
什么是完全备份
是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
完全备份的优点
1、优点:
备份与恢复操作简单方便
2、缺点:
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长
(一)、数据库完全备份分类
1、物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可
2、mysqldump备份与恢复
MySQL自带的备份工具,可方便实现对MySQL的备份
可以将指定的库、表导出为SQL 脚本
使用命令mysq|导入备份的数据
五、完全备份与恢复 实操演练
完全备份
systemctl stop mysqld #先关闭服务
mkdir /backup/ #创建备份目录
rpm -q xz #使用xz工具进行压缩,检查xz工具是否已安装
yum install xz -y #如果没安装,可以先yum安装
tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data #打包数据库文件。/usr/local/mysql/data 为数据库文件存放目录
cd /backup/ #切换到备份目录
ls #查看目录内容
tar tf mysql_all_2022-06-05.tar.xz #查看tar包内的文件
完全恢复
#主机A,使用scp命令将tar包传给另一台主机B
scp /backup/mysql_all_2022-06-05.tar.xz 192.168.72.70:/opt
##主机B的操作##
systemctl stop mysqld #关闭mysql
cd /opt/
mkdir /opt/bak/ #创建备份目录
tar Jxf mysql_all_2022-06-05.tar.xz -C /opt/bak/ #将tar包解压到备份目录
cd /opt/bak/ #切换到tar包的解压目录
\cp -af usr/local/mysql/data/ /usr/local/mysql #将data目录复制到/usr/local/mysql/目录下,覆盖原有文件
systemctl start mysqld #启动mysql
mysql -u root -p #登录数据库查看
show databases;
use yuji;
show tables;
select * from class;
mysqldump介绍
mysqldump是常用的逻辑备份工具。
mysqldump可以将指定的库、表导出为SQL脚本。
六、增量备份与恢复 实操演练
(1)MySQL增量备份介绍
使用mysqldump进行完全备份存在的问题
备份数据中有重复数据
备份时间与恢复时间过长
增量备份是什么:
是自上一次备份后增加/变化的文件或者内容
(2)增量备份的特点
没有重复数据,备份量不大,时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复,而且要对所有增量备份进行逐个反推恢复
MySQL增量备份的方法
MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份
(3)MySQL二进制日志对备份的意义
二进制日志保存了所有更新或者可能更新数据库的操作
二进制日志在启动MySQL服务器后开始记录,并在文件达到 max_binlog_size所设置的大小或者接收到flush logs命令后重新 创建新的日志文件
只需定时执行flush logs方法重新创建新的日志,生成二进制文 件序列,并及时把这些日志保存到安全的地方就完成了一个时间 段的增量备份
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
#可选,指定二进制日志(binlog)的记录格式为MIXED(混合输入)
server-id = 1
#可加可不加该命令
systemctl restart mysqld
(4)MySQL数据库增量恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
mysqldump -uroot -p123456 sky lcdb3 > /opt/sky_lcdb3.sql
mysql -uroot -p123456 -e 'drop table sky.lcdb3;'
mysql -uroot -p123456 -e 'use sky;show tables;'
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点,上一次正确操作的位置点停止
#仅恢复到操作 ID 为“623"之前的数据,即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='1793' /opt/mysql-bin.000006 | mysql -uroot -p密码
#仅恢复"user4"的数据,跳过"user3"的数据恢复
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000006 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000006 | mysql -uroot -p
#恢复从位置为400开始到位置为623为止
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
在错误时间点停止,在下一个正确时间点开始
#仅恢复到16:41:24 之前的数据,即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000006 | mysql -uroot -p
#仅恢复"user4"的数据,跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-2216:41:24' /opt/mysql-bin.000006 | mysql -uroot -p
如果恢复某条SQL语之前的所有数据,就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据,就从这个语句的位置节点或者时间点start