郴州市建设局网站节能科,wordpress.org,wordpress默认主题下载地址,中国建设银行官网站网点Mysql日志系统-服务层的日志
mysql给我们提供了很多有用的日志有mysql服务层提供的#xff0c;有innodb引擎层提供的#xff0c;下表是mysql服务层给我们提供的#xff1a;
日志类型写入日志的信息二进制日志记录了对MySQL数据库执行更改的所有操作慢查询日志记录所有执行…Mysql日志系统-服务层的日志
mysql给我们提供了很多有用的日志有mysql服务层提供的有innodb引擎层提供的下表是mysql服务层给我们提供的
日志类型写入日志的信息二进制日志记录了对MySQL数据库执行更改的所有操作慢查询日志记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询错误日志记录在启动运行或停止mysqld时遇到的问题通用查询日志记录建立的客户端连接和执行的语句中继日志从复制主服务器接收的数据更改
一、bin log日志
1、概述
二进制日志binnary log以【事件形式】记录了对MySQL数据库执行更改的所有操作。
binlog
记录了所有数据库【表结构】变更例如CREATE、ALTER TABLE…的二进制日志。记录了所有数据库【表数据】修改INSERT、UPDATE、DELETE…的二进制日志。但是不会不会记录SELECT和SHOW这类操作因为这类操作对数据本身并没有修改但可以通过查询通用日志来查看MySQL执行过的所有语句。
binlog是mysql server层维护的跟采用何种引擎没有关系记录的是所有的更新操作的日志记录。
binlog是在事务最终commit前写入的。
我们执行SELECT等不涉及数据更新的语句是不会记binlog的而涉及到数据更新则会记录。要注意的是对支持事务的引擎如innodb而言必须要提交了事务才会记录binlog。
binlog 文件写满后会自动切换到下一个日志文件继续写而不会覆盖以前的日志这个也区别于 redo logredo log 是循环写入的即后面写入的可能会覆盖前面写入的。 binlog有两个常用的使用场景 主从复制我们会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。数据恢复通过mysqlbinlog工具来恢复数据。
mysql8中的binLog默认是开启的5.7默认是关闭的可以通过参数log_bin控制;
2、数据恢复
1确认binlog开启log_bin变量的值为ON代表binlog是开启状态
show variables like %log_bin%;2为了防止干扰我们flush刷新log日志会结束当前bin log自此刻会产生一个新编号的binlog日志文件
flush logs;3查看所有binlog日志列表
show master logs;4查看master状态即最后(最新)一个binlog日志的编号名称及其最后一个操作事件pos结束点(Position)值这一步可有可无 5执行sql
先创建表并插入一些数据
DROP TABLE IF EXISTS my_student;
CREATE TABLE my_student (id int(11) NOT NULL,name varchar(255) DEFAULT NULL,score int(255) DEFAULT NULL,grade varchar(255) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;
INSERT INTO my_student(id, name, score, grade) VALUES (1, lucy, 80, a);
INSERT INTO my_student(id, name, score, grade) VALUES (2, lily, 90, a);
INSERT INTO my_student(id, name, score, grade) VALUES (3, jack, 60, c);
INSERT INTO my_student(id, name, score, grade) VALUES (4, hellen, 40, d);
INSERT INTO my_student(id, name, score, grade) VALUES (5, tom, 60, c);
INSERT INTO my_student(id, name, score, grade) VALUES (6, jerry, 10, d);
INSERT INTO my_student(id, name, score, grade) VALUES (7, sily, 20, d);执行删除操作假装误删除直接全部删除也可以把表删了都行一样的道理
delete from my_student;6查看binlog日志我们因为刷新了日志所以本次操作都会在最新的日志文件上 因为 binlog 的日志文件是二进制文件不能用文本编辑器直接打开需要用特定的工具来打开MySQL 提供了 mysqlbinlog 来帮助我们查看日志文件内容 cmd打开输入mysqlbinlog 进入到binlog的目录内,打开cmd输入下列命令查看全部的日志信息 # 进入到binlog的目录内,打开cmd输入下列命令查看全部的日志信息
mysqlbinlog -v SAYHELLO-bin.000096指定位置/时间查看binlog # 指定位置范围
mysqlbinlog -v SAYHELLO-bin.000096 --start-position0 --stop-position1000
# 指定时间范围
mysqlbinlog -v SAYHELLO-bin.000096 --start-datetime2023-03-21 21:47:08 --stop-datetime2023-03-21 21:50:08 真实的情况下我们的日志文件比较复杂内容比较多使用时间范围查询后任然可能需要花费时间去排查问题这里我们找到了误删除的位置 7执行恢复通过上一步的操作我们找到了删除的位置3972即第一个蓝框执行下面的语句
在这里插入图片描述
mysqlbinlog -v SAYHELLO-bin.000096 --stop-position3972 -v | mysql -uroot -p报错ERROR 1007 (HY000) at line 36: Cant create database bin_test; database exists因为它会将我们binlogw文件里面的数据再从头执行一遍到3972的位置所以我们可以先备份数据库再删除数据库。 恢复成功。
8至此数据已完全恢复了 binlog的数据恢复的本质 binlog的数据恢复的本质就是将之前执行过的sql从开始到指定位置全部执行一遍如果报错【当前表已经存在】就将数据库的表删除重新恢复。
当然也可以指定位置/时间段进行恢复。
3、格式分类 binlog 有三种格式 使用变量binlog_format查看当前使用的是哪一种 StatementStatement-Based ReplicationSBR每一条会修改数据的 SQL 都会记录在 binlog 中。RowRow-Based ReplicationRBR不记录 SQL 语句上下文信息仅保存哪条记录被修改。MixedMixed-Based ReplicationMBRStatement 和 Row 的混合体当前默认的选项5.7中默认row。 例子 我们举一个例子来说明row和statement的区别在下面的插入语句中我们有一个函数uuid()如果日志文件仅仅保存sql语句下一次执行的结果可能不一致所以Row格式的文件他保存的是具体哪一行修改成了什么数据记录的是数据的变化不是简单的sql
#下图是statement格式uuid()每次执行得到的结果不一样-》结果不一致问题
insert into my_student values (8,UUID(),45,d);::: warning Statement和row的优劣
:::
Statement 模式只记录执行的 SQL不需要记录每一行数据的变化因此极大的减少了 binlog 的日志量避免了大量的 IO 操作提升了系统的性能。由于 Statement 模式只记录 SQL而如果一些 SQL 中 包含了函数那么可能会出现执行结果不一致的情况。比如说 uuid() 函数每次执行的时候都会生成一个随机字符串在 master 中记录了 uuid当同步到 slave 之后再次执行就得到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。从 MySQL5.1.5 版本开始binlog 引入了 Row 格式Row 格式不记录 SQL 语句上下文相关信息仅仅只需要记录某一条记录被修改成什么样子了。不过 Row 格式也有一个很大的问题那就是日志量太大了特别是批量 update、整表 delete、alter 表等操作由于要记录每一行数据的变化此时会产生大量的日志大量的日志也会带来 IO 性能问题。
4、日志格式 binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。 header部分提供的是event的公共的类型信息包括event的创建时间服务器等等。data部分提供的是针对该event的具体信息如具体数据的修改。 常见的事件类型有 FORMAT_DESCRIPTION_EVENT该部分位于整个文件的头部每个binlog文件都必定会有唯一一个该eventWRITE_ROW_EVENT插入操作。DELETE_ROW_EVENT删除操作。UPDATE_ROW_EVENT更新操作。记载的是一条记录的完整的变化情况即从前量变为后量的过程ROTATE_EVENTBinlog结束时的事件用于说明下一个binlog文件。
一个event的结构如下我们在恢复数据的时候已经看到了 每个日志的最后都包含一个rotate event用于说明下一个binlog文件。binlog索引文件是一个文本文件其中内容为当前的binlog文件列表比如下面就是一个mysql-bin.index文件的内容。
5、binlog刷盘 二进制日志文件并不是每次写的时候同步到磁盘。 因此当数据库所在操作系统发生宕机时可能会有最后一部分数据没有写入二进制日志文件中这给恢复和复制带来了问题。 参数sync_binlog[N]表示每写多少次就同步到磁盘
如果将N设为1即sync_binlog1表示采用同步写磁盘的方式来写二进制日志这时写操作不使用操作系统的缓冲来写二进制日志就是每次写操作就会耍哦按。备注该值默认为0采用操作系统机制进行缓冲数据同步。
6、binlog实现主从同步
::: danger 数据库单点部署的问题
:::
服务器宕机会导致业务停顿影响客户体验。服务器损坏数据丢失不能及时备份造成巨大损失。读写操作都在同一台服务器在并发量大的情况下性能存在瓶颈。
那么我们就可以使用mysql的binlog搭建一个一主多从的mysql集群服务。
这样的服务可以帮助我们异地备份数据、进行读写分离提高系统的可用性。
1 主从复制工作原理剖析
Master 数据库只要发生变化立马记录到Binary log 日志文件中Slave 数据库启动一个I/O thread连接Master数据库请求Master变化的二进制日志Slave I/O获取到的二进制日志保存到自己的Relay log 日志文件中。Slave 有一个 SQL thread定时检查Realy log是否变化变化那么就更新数据 2怎么配置mysql主从复制 环境准备 安装两个mysql使用vmvare安装两个linux系统就可以
mysql1(master): 42.192.181.133:3306
mysql2(slave): 124.220.197.17:3306mysql 配置文件配 mysql1master: 配置文件设置开启bin_log已经开启的可以忽略且需要配置一个server-id
#mysql master1 config
[mysqld]
server-id 1 # 节点ID确保唯一# log config
log-bin master-bin #开启mysql的binlog日志功能mysql2slave: 需要开启中继日志
[mysqld]
server-id2
relay-logmysql-relay-bin
replicate-wild-ignore-tablemysql.%
replicate-wild-ignore-tablesys.%
replicate-wild-ignore-tableinformation_schema.%
replicate-wild-ignore-tableperformance_schema.%重启两个mysql让配置生效。
第三步 在master数据库创建复制用户并授权
1.进入master的数据库为master创建复制用户
CREATE USER repl124.220.197.17 IDENTIFIED BY Root12345_;2.赋予该用户复制的权利
grant replication slave on *.* to repl124.220.197.17
FLUSH PRIVILEGES;3.查看master的状态
show master status;
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000005 120| | mysql | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)4配置从库
CHANGE MASTER TO
MASTER_HOST 42.192.181.133
MASTER_USER repl
MASTER_PASSWORD Root12345_
MASTER_PORT 3306
MASTER_LOG_FILEmysql-bin.000020
MASTER_LOG_POS2735
MASTER_HEARTBEAT_PERIOD 10000; # MASTER_LOG_FILE与主库File 保持一致
# MASTER_LOG_POS120 #与主库Position 保持一致解释MASTER_HEARTBEAT_PERIOD表示心跳的周期。当MASTER_HEARTBEAT_PERIOD时间之内master没有binlog event发送给slave的时候就会发送心跳数据给slave。
5.启动从库slave进程
mysql start slave;
Query OK 0 rows affected (0.04 sec)6.查看是否配置成功
show slave status \G;Slave_IO_Running从库的IO线程用来接收master发送的binlog并将其写入到中继日志relag log Slave_SQL_Running从库的SQL线程用来从relay log中读取并执行binlog。 Slave_IO_Running、Slave_SQL_Running这两个进程的状态需全部为 YES只要有一个为 NO则复制就会停止。 Master_Log_File要同步的主库的binlog文件名。 Read_Master_Log_Pos已同步的位置即同步的 binlog 文件内的字节偏移量该值会随着主从同步的进行而不断地增长。 Relay_Log_File从库的中继日志文件对接收到的主库的 binlog 进行缓冲。从库的SQL线程不断地从 relay log 中读取 binlog 并执行。 Relay_Log_Posrelay log 中已读取的位置偏移量。 Seconds_Behind_Master: 主从同步延时 值为 0 为正常情况正值表示已经出现延迟数字越大从库落后主库越多。
7.在主库创建一个数据库、创建一张表执行一些sql语句进行测试。
3可能遇到的问题
在配置mysql主从复制的时候可能出现一下错误
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.原因
如果你使用了两台虚拟机一主一从从库的mysql是直接克隆的。在mysql 5.6的复制引入了uuid的概念各个复制结构中的server_uuid得保证不一样但是查看到直接克隆data文件夹后server_uuid是相同的。
解决
找到data文件夹下的auto.cnf文件修改里面的server_uuid值保证各个db的server_uuid不一样重启db即可。 cd /www/server/data修改server_uuid的值 使用
select uuid();生成一个uuid即可重启数据库。
