1.错误日志
1.1 配置方法
默认:开启状态,/数据存放文件/错误日志.err
查看数据 select @@datadir;
ls -l /错误日志地方
定制 方法:
1.vim /etc/my.cnf
log_error=/tmp/mysql.log
说明:日志目录必须得提前有,且mysql有权限写入
2.重启
/etc/init.d/mysqld restart
1.2 怎么看错误日志
- vim /错误日志位置
[error]
2.binlog二进制日志
- 作用:(差不多=sql层的行为)
主要记录数据库变化(DDL,DML,DCL)性质的日志。是逻辑层性质日志.
数据恢复,主从复制中应用
2.默认:8.0版本以前,没有开启,我们建议生产开启.8.0以后自动开启.
配置方式:
vim /etc/my.cnf
server-id=(数字) #主机编好。主从中使用,5.7以后开binlog要加此参数
log_bin=/data(数据存放路径)/binlog/mysql-bin #日志存放目录+日志名前缀 例如:mysql-bin.00001
sysnc_binlog=1 #binlog日志刷盘侧略,双一中的第二个1。每次事物提交立即刷写到磁盘。
binlog_format=row #binlog的记录格式row模式
说明: 一定要和数据盘分开
重新启动
binlog 记录
binlog是sql层的功能
1.记录sql语句种类
DDL : 原封不动的记录当前的DDL(stateement语句方式)
DCL: 原封不动的记录当前DCL(statement语句方式)
DML: 只记录已经提交的事务DML(insert, update ,delete)
1.1DML 三种记录方式
binlog_format(binlog的记录格式) 参数影响
1) statement (5.6默认) SBR(stateement based r) :语句模式原封不动的记录当前DML
2)ROW(5.7默认) RBR(row based r) :jilu数据行的变化,有时需要借助工具分析
3)mixed(混合)MDR(mixed based r) :以上两种的混合
优缺点:
statement : 直观,可读性高,日质量少,但不够严谨
row :可读性很低,日志量大,足够严谨
1.2 binlog的查看
1.2.1 文件位置查看
是否开启
select @@log_bin;
查看位置
select @@log_bin_basename
1.2.2 二进制内置查看命令
(1) 查看目前有几个日志
show binary logs;
(2)查看当前再用的binlog
show master status;
查看日志的流程
打开日志
mysqlbinlog mysql-bin.尾号 >/文件目录/中的sql
翻译:
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.尾号 >/tmp/b.sql
日志截取恢复
1.滚动一个新的日志
flush logs;
2.模拟数据环境,然后删库了(不要跑路)
3.数据恢复
一、分析binlog (找到起点和终点)
起点:
| mysql-bin.000005 | 219 | 292 | BEGIN
终点:
mysql-bin.000005 | 489 | 584 | drop database shuju
二、截取日志:
mysqlbinlog --start-position=起始 --stop-position =终止 /文件位置/哪个文件 > /想保存的位置/文件名
三、恢复
在恢复钱,临时关闭日志记录
然后:source
成功后再次开启日志:
set sql_log_bin=1;
四、验证数据:
1.如果需要的日志在多个文件中分布,该怎么解决!
用时间来截取
mysqlbinlog --start-datetime= --stop-datetime= 多个文件 mysql-bin.001,msyql-bin,mysql-bin…
可以混合使用
2.如果创建了几年,期间一直在用数据库,很多很多日志文件中都有很多操作,被删了,怎么恢复,数据超级多!
如果有全备的话 ,将数据库恢复到上一次全备的地点,然后在通过binlog 恢复到被删数据库的前一天
2.binlog日志维护操作
1. 日志滚动
mysql> flush logs;
查看最大容量
mysql> select @@max_binlog_size;
滚动
mysqladmin -uroot -p flush-logs;
mysqldump -F
重启数据库自动滚动
2.2日志的删除
1.注意:别用rm 命令删除日志;
2.2.1 自动删除机制
3306 [shuju]>select @@expire_logs_days;
默认是0,单位是天,代表永不删除。
2.2.2 手动删除
Examples:
PURGE BINARY LOGS TO ‘mysql-bin.010’; (删除到哪一个为止)
PURGE BINARY LOGS BEFORE ‘2019-04-02 22:46:26’; (删除到哪个时间为止)
2.2.3 全部清空
mysql> reset master;
比较危险,在主库执行此操作,主从必宕。不同步
3.binlog的GTID模式管理 (5.6以后)(清空gtid历史执行记录:reset master)
5.6中不开启,没有这个功能
5.7中的gtid,即使不开也会有自动生成
3306 [(none)]>select @@session.gtid_next;
set session.gtid_next=‘anonymous’;
3.2 gtid 是对于一个已提交事务的编号,并且是一个全局唯一的编号
官方定义
gtid = server_uuid : tranasction_id
3.2 重要参数介绍
vim /etc/my.cnf
gtid-mode=on
enforce_gtid_consistency=true
log_slave_updates=1
3.3 具有gtid 之后,怎么截取日志
- -include-gtids : 截取
- -exclude-gtids :过滤 帮助跨多文件截取
- -skip-gtids :去掉里面的gtid的信息,阻止幂等性
例子:
mysqlbinlog - -skip-gtids --include-gtids=‘gtid(你自己的):从几到几(1起始-10终点)’ 日志文件 >/你想要放的地方tmp/自定义.sql
但是没有恢复成功
- GTID具有幂等性
开启gtid后,只要数据库执行过事件,有这个gtid,再去执行gtid,它会直接跳过。
4.slowlog慢日志(想修改慢日志时间:set global long_query_time=时间)
4.1 帮助我们进行语句优化工具日志 pt-query-digest
4.2 然后配置
默认慢日志不开启,1是开启,0是关闭
查看是否开启
select @@slow_query_log;
show variables like ‘slow_%’;
(是否开启,存储位置,不走索引的语句)
慢语句的时间
vim /etc/my.cnf
编辑
重启数据库生效
例:模拟慢语句
查看排序
mysqldumpslow -s c(次数排序) -t(前几条) 5 /慢日志位置/慢日志
5.备份恢复
1.MySQL数据损坏类型
1.1物理损坏
磁盘损坏: 硬件,磁道坏,dd,格式化,
文件损坏: 数据文件损坏,redo损坏
1.2 逻辑损坏
drop ,delete ,truncate ,update
2.运维人员在备份,恢复的职责
2.1 设计备份、容灾策略
使用什么备份工具备份,备份周期设计,备份监控方法
容灾策略
备份:用什么备份
架构:高可用,演示从库,灾备库
2.2 定期的备份、容灾检查
2.3 定期的故障恢复演练
2.4 数据损坏时的恢复
2.5 数据迁移工作
3.mysql 常用备份工具
3.1 逻辑备份方式
- mysqldump :
- replication
- 主从方式
- mydumper
- load data in file
3.2 物理备份方式
- mysql enterprise backup(企业版)
- percona xtrabackup (PBK,XBK)
4 mysqldump(MDP)应用
4.1 介绍
逻辑备份工具。备份的是sql语句
4.2 备份方式:
4.2.1 innodb表
innodb表
可以采取快照备份的方式
开启一个独立的事务,获取当前罪行的一致性快照,将快照数据,放在临时表中,转换成sql,保存到sql文件中。
4.2.2 非新innodb表
需要锁表备份。触发FTWRL。全局锁表。转换成sql,保存到sql文件中。
4.3 mysqldump 的核心参数
4.3.1 连接参数 (mysql连接)
-u
-p
-h
-P
-s
4.3.2 mysqldump独有的备份参数
- -A 全备
musqldump -uroot -p(密码) -S(不在这个表里去其他表) /备份文件 -A >/备份位置/备份名.sql
- -B 备份一个或者多个库
mysqldump -uroot -proot -B 库名 库名1 库名2 -S 数据存放位置(默认在tmp下找) >/备份位置/备份名称
- 备份 单表或多表
mysqldump -uroot -密码 -S /数据文件位置 库名 表1 表2 >/想要备份到的位置/备份名.sql
-
--
flush-logs
备份完之后,重新创建一个日志文件,写入数据
4.3.3 备份高级参数
比较完善备份语句,生产:
mysqldump -uroot -pmima -A - -master-data=2 --single-transaction -R -E - -triggers - - max_allowed_packet=64m >/存放位置/备份名字.sql
* - -master-date =2
功能:
1.备份时自动记录binlog信息
2.自动锁表和解表
3.配合single transaction 可以减少锁表时间
例子:每周日23:00 全备,周1-6 binlog备份。所有备份是完整的.
周三时,有一个核心运维人员进行了删库操作。
思路: 恢复全备+ 所有需要binlog恢复
问题难点: binlog 的截取 -----起点和终点的截取
方法一: 备份开始时,切割日志 -F
方法二: 备份开始时,自动记录日志文件信息 - -master-data=2
- - -single-transaction
对于innodb引擎表备份时,开启一个独立事物,获取一致性快照,进行备份
- -R -E - -triggers
- - -max_allowed_packet=64m(可设置)
如果小了,会报 got a packet bigger than ‘max_allowed_packet’ bytes
全备
mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64m >/tmp/quanbei_
date +%F
.sql
(防止备份是密码泄露,可以在.my.cnf设置) 不建议
在登录和备份时多不需要输入密码
小结 : 数据量较少的时候,建议使用mysqldump 100G以内
- 1.获得表结构
sed -e’ /./ {H;$!d;}’ -e ‘x; /CREATE TABLE `city`/!d;q’ quanbei.sql >createtable.sql- 2.获取insret into 语句,用于数据的恢复
grep -i ‘insert into `city`’ quanbei.sql>data.sql &- 3. 获取单库的备份
sed -n '/^ - - current database : `world`/,/ ^ - - current database: `/p ’ all.sql >world.sql
5.percona xtrabackup (XBK)
1.安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
2.介绍
物理备份工具,拷贝数据文件
innodb表:
热备份:业务正常发生的时候,影响较小的备份方式
- checkpoint,将已提交的数据页刷新到磁盘。记录一个lsn
- 拷贝innodb表相关的文件(ibdatal,frm,ibd…)
- 备份期间产生新的数据变化的redo也会备份走
非innodb表:
温备份:锁表备份
- FTWRL,触发全局锁
- 拷贝非innodb表的数据
- 解锁
再次统计LSN,写入到专用文件
记录二进制日志位置记录下来。
所有备份文件统一存放在一个目录下
5.1XBK应用
5.1.1 前提
1)数据库启动
2)能连上数据库
3)默认会读取[mysqld] 中 -----> datadir=数据位置
4)服务器端工具
5.1.2 全备
备份命令
[root@localhost xbk]# innobackupex --user=root --password=root --no-timestamp /mysql/data/xbk/quanbei_`date +%F`
注:文件需要有写入权限,后面的存储备份文件没有,会自动生成
5.1.3 备份结果
xtrabackup_binlog_info #记录备份后binlog位置点信息,binlog的截取起点,gtid
xtrabackup_info # 总信息
xtrabackup_checkpoints #备份过程中lsn记录,方便做增量备份
5.1.4 xbk全备的恢复
备份逻辑
1.checkpoint ,记录lsn号码
2.information_schema.xxx
3.拷贝innodb文件,过程中发生的新变化redo也会被保存,保存至备份路径
4.FTWRL,全局锁定
5.拷贝非innodb表:non_innodb ,保存至备份路径 ,拷贝完解锁
6.生成备份相关的信息文件。binlog,lsn
7.刷新last lsn
8.完成备份
备份处理:prepare
1.redo 前滚,undo回滚,把数据变成一致的状态 ,模仿CSR过程
innobackupex --apply-log /你全备的位置/mysql/data/xbk/quanbei_2020_…/
2.拷贝
cp -a /data/xbk/quanbei_2020_…/* /你的数据路径下/mysql/data/
3.授权
chown -R mysql.mysql /数据路径/mysql/data/*
4.启动数据库
/etc/init.d/mysqld start
5.登录
5.2 xbk的增量备份恢复
语句:
innobackupex - - user=root - -password=root - - no-timestamp(不产生时间路径) - - incremental(开启增量备份的开关) - -incremental-basedir=基于上一次备份的位置(参照物) /mysql/data/xbk/quanbei_2… /想要储存增量备份的位置/mysql/data/xbk/inc1
看一下有没有备份失败
xbk + inc + binlog 备份恢复手段
- 恢复思路:
- 合并,整理prepare所有inc增量到全备。
- 恢复数据,启动数据库。
- 截取binlog日志。
- 恢复日志。
- 恢复过程
1.合并,prepare 所有inc备份到全备
#基础全备整理
innobackupex - -apply-log - -redo-only(最后一个inc不需要) /全备位置
2.合并,prepare inc(增量) 到全备里
innobackupex - - apply-log - - redo-only - -incremental-dir=/data/backup/inc(哪一个增量位置合并到全备中) /全备位置
3.整体再次prepare整个备份
innobackupex --apply-log /全备位置
xbk备份脚本