mysql日志管理

binlog

是逻辑日志 会记录整个数据库运行期间变更性的,语句

1备份恢复必须依赖二进制日志

2复制环境必须依赖二进制日志

3分析大事务

配置

log_bin binlog的开关和binlog的前缀

server_id 必须要打开

binlog_format binlog日志的格式 ROW/Statement/mixed 只影响dml语句记录格式

ddl只会记录 statement格式

row格式 记录数据行的变化

Statement格式

mixed格式 自动判断是用什么格式

sync_binlog =1 刷新参数

1代表每次事务提交都要刷新binlog

0代表由os cache判断什么时候刷新

2多少组事务去刷磁盘

在线查看binlog

show binary logs;

Log_name 日志名字

File_size 日志的大小,日志量当前日志文件偏移量

Encrypted 是不是加密的

binlog使用只会使用最后一个

show master status ; 当前在使用哪个binlog文件,Position号是多少

整个日志里面的记录单元是evnet

每个evnet都有开始和结束位置点用,Position表示

show binlog events in 'mysql-bin.000004';

查看事件,主要关注 文件名 ,事件起点, 事件终点

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000004 linux系统查看文件

想查看某一端时间的日志

mysqlbinlog --start-datetime='2021-12-12-18 10:00:00' --stop-datetime'2021-12-18 10:00:00' --base64-output=decode-rows mysql-bin.000004

通过position号查询

mysqlbinlog --start-postion=19646 --stop-postion=200000 --base64-output=decode-rows mysql-bin.000004

生产恢复痛点

1.跨度太大,起点难找 (一般备份加日志)

2. 跨多个文件 (分段截取,时间截取,gtid)

3. 日志文件记录了,所有库所有表的日志(可以用my2sql)

gtid特性

是对于一个已提交事务的编号 ,并且是一个全局唯一的编号.

官方定义如下

gtid = server_uuid : transaction_id

重要参数

gtid-mode=on #gtid是不是开启

enforce-gtid-consistency=true #是不是强制gtid一致性

log_slave-updates=1#从库当中强制将gtid写入binlog中

如何查看gtid信息

show master status;

在最后一列显示Executed_Gtid_Set

按照一个事务滚动一个gtid号码

无论夸多少文件号码都是一直增长的

如何截取gtid1-3号

mysqlbinlog --include-gtids='94a10acd-44dc-11ec-85c0-00163e06d66f:1-3' /data/mysql/binlog_3306/mysql-bin.000006

截取1,3跳过2号

mysqlbinlog --include-gtids='94a10acd-44dc-11ec-85c0-00163e06d66f:1-3' --exclude-gtids='94a10acd-44dc-11ec-85c0-00163e06d66f:2' /data/mysql/binlog_3306/mysql-bin.000006

gtid是具备幂等性的,我们截取出来的日志文件是带有已经执行过的gtid信息当我们把日志文件执行,发现数据并为恢复

mysqlbinlog --skip-gtids --include-gtids='94a10acd-44dc-11ec-85c0-00163e06d66f:1' /data/mysql/binlog_3306/mysql-bin.000006 >/tmp/t2.sql

需要加上--skip-gtids这个参数跳过gtid的记录,如果开启了gtid,用position号截取也要加这个参数

binlog 删除和清理

可以手动也可以自动

expire_logs_days = 8.0之前是这个参数,保留几天就写几

binlog_expire_logs_seconds = 8.0用这个以秒为准

至少要保留1轮全备周期的binlog

也可以手工清理

紧急情况磁盘不够了

紧急rm掉

如果能登录到数据库

purge binary logs to 'mysql-bin.000005';

删除到哪个文件

删除全部从0开始主从必蹦(gtid也会重置)

reset master;

binlog滚动

重启会滚动一个新的日志文件

命令 flush logs;手工滚动一个新的出来

日志文件大小设置

max_binlog_size 默认是1g一般会设置小点

slowlog

主要记录执行较慢的sql语句

开关 slow

slow_query_log=1

文件位置及名字

slow_query_log_file=/data/mysql/slow.log

设置慢查询时间

long_quer_time=0.1

没有走索引的语句也记录只记录最近1000条

log_queries_not_using_indexes=1000

分析

mysqldumpslow工具分析

-s c 按照次数排序

-t 5 显示前几个

mysqldumpslow -s c -t 5 /data/mysql/data_3306/slow.log

第三方工具

pt

安装

https://www.percona.com/downloads/percona-toolkit/LATEST/

官网下包

yum安装

yum localinstall percona-toolkit-3.3.1-1.el7.x86_64.rpm

使用方法

pt-query-digest /data/mysql/data_3306/slow.log 可以直接跟文件

备份

作用 故障恢复(PITR), 升级迁移

逻辑备份

备份语句,

mysqldump 单线程串行

load(针对单表)

mydumoer 多线程

mysql shell

物理备份

备份数据文件,数据页

xtrabackup 建议8.0.12版本后

clone plugin mysql8.0版本后的

mysqldump

链接参数

本地备份

mysqldump -uroot -p123456 -S /tmp/mysql.sock

远程备份

mysqldump -uroot -p123456 -h 10.0.0.51 -P3006

备份参数

mysqldump -uroot -p123456 -S /tmp/mysql.sock -A >/tmp/1.sql

备份全库数据

mysqldump -uroot -p123456 -S /tmp/mysql.sock -B 库名 >/tmp/1.sql

单库备份

mysqldump -uroot -p123456 -S /tmp/mysql.sock 库名 表名 >/tmp/1.sql

单表备份

如果只备份表结构加 --no-data

特别参数

--master-data=

--master-data=1

会将 position号 和文件名写入备份

会将change master 语句写入dump文件中

在从库导入后，配置主从无需再指定文件名和位置

--master-data=2

会将 position号 和文件名写入备份

会将change master 语句写入dump文件中，只不过会被注释掉

在故障恢复的时候,是备份加binlog恢复

--single-transaction 加了master-data会上锁

备份的时候会开启一个事务创建一致性快照

只支持mvcc的多版本的只对innodb的表管用

--max_allowed_packet=128M

即最大允许传输包的大小，也就是查询出结果后，把结果发送到客户端时，每个网络包的最大大小。

-R -E --triggers 不加这些参数不会备份高级对象

--set-gtid-purged= 默认是auto如果手动开启了会把gtid信息会记录进去,off就不会记录

构建主从的时候可以设置on 把我们主库的备份恢复到从库之前的带gtid信息的binlog,就不会同步

逻辑导入导出load data

先导出

select * from 库名.表名 into outfile '/tmp/test.csv';

select * from help_keyword into outfile '/tmp/t1.csv' fields enclosed by "'";

导出并设置字段引用符把我们每一个字段用引号引起来注意只能用表里没有引用的

select * from help_keyword into outfile '/tmp/t0.csv' fields terminated by ',' enclosed by "'";

设置字段引用符和字段分隔符

想导出,先需要查看'secure_file_priv'参数

使用mysqldump导出csv

mysqldump -uroot -p123456 库名 --fields-terminated-by ',' --fields-enclosed-by "'" -T /tmp

导入可以用load或者mysqlport

mysqlimport -uroot -p123456 导入哪个库 --fields-terminated-by ',' --fields-enclosed-by "'"

/tmp/t1.txt

导出的时候,什么格式导入就什么格式

物理备份工具

xtrabackup

在mysql8.0之后要使用对应的 pxb 8.0对应的版本工具

mysql8.0.20版本需要使用8.0.12以上版本

只支持服务器端

https://www.percona.com/downloads/

下载地址

下载安装包直接安装

针对innodb表支持热备份

其他不是热备份

mysql clone-plugin(8.9.17)

版本必须是一致的

支持两种克隆方式 本地克隆 远程克隆

克隆过程中出现ddl操作会失败

本地克隆

1加载插件

install plugin clone soname 'mysql_clone.so';

或者写到mysql配置文件

plugin-load-add=mysql_clone.so

clone=force_plus_permanent

可以通过视图去查看

select plugin_name,plugin_status from information_schema.plugins where plugin_name like 'clone';

2创建克隆专用用户