食品网站建设方案,wordpress页面内菜单,电商平台规则,建设银行给税对账在什么网站1.日志的概述
日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化#xff1b;也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时#xff0c;可以通过日志查看文件出错的原因#xff0…1.日志的概述
日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时可以通过日志查看文件出错的原因并且可以通过日志文件进行数据恢复。
MySQL有不同类型的日志文件用来存储不同类型的日志分为 二进制日志 、 错误日志 、 查询日志和慢查询日志 这也是常用的4种。MySQL 8又新增两种支持的日志 中继日志 和 事务日志 。使用这些日志文件可以查看MySQL内部发生的事情
慢查询日志记录所有执行时间超过long_query_time的所有查询方便我们对查询进行优化
查询日志记录所有连接的起始时间和终止时间以及连接发送给数据库服务器的所有指令对我们复原操作的实际场景、发现问题甚至是对数据库操作的审计都有很大的帮助
错误日志记录MySQL服务的启动、运行或停止MySQL服务时出现的问题方便我们了解服务器的状态从而对服务器进行维护
二进制日志记录所有更改数据的语句可以用于主从服务器之间的数据同步以及服务器遇到故障时数据的无损失恢复
中继日志用于主从服务器架构中从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容来同步主服务器上的操作
事务日志记录数据定义语句执行的元数据操作
除二进制日志外其他日志都是 文本文件 。默认情况下所有日志创建于 MySQL数据目录中
日志的弊端
日志功能会降低MySQL数据库的性能
日志会占用大量的磁盘空间
2.错误日志(error log)
它记录了mysqld启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时建议首先查看此日志
在MySQL数据库中错误日志功能是 默认开启 的。而且错误日志 无法被禁止 。
默认情况下错误日志存储在MySQL数据库的数据文件夹下/var/log/名称默认为 mysqld.log Linux系统 show variables like %log_err%; 错误日志记录信息
1.服务器启动和关闭过程中的信息
未必是错误信息比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等
2.服务器运行过程中的错误信息
比如sock文件找不到无法加载mysql数据库的数据文件如果忘记初始化mysql或data dir路径找不到或权限不正确等 都会记录在此
3.事件调度器运行一个事件时产生的信息
一旦mysql调度启动一个计划任务的时候它也会将相关信息记录在错误日志中
4.在从服务器上启动从服务器进程时产生的信息
在复制环境下从服务器进程的信息也会被记录进错误日志
删除/刷新日志
在mysql5.5.7之前数据库管理员可以删除很长时间之前的错误日志以保证mysql服务器上的硬盘空间。mysql数据库中可以使用mysqladmin命令开启新的错误日志。
mysqladmin –u root –p flush-logs
在mysql5.5.7之后服务器将关闭此项功能。只能使用重命名原来的错误日志文件手动冲洗日志创建一个新的
mv stu18.magedu.com.err stu18.magedu.com.err.old
mysqladmin flush-logs3.二进制日志(bin log)
MySQL的二进制日志binary log是一个二进制文件主要记录所有数据库表结构变更例如CREATE、ALTER TABLE…以及表数据修改INSERT、UPDATE、DELETE…的所有操作。二进制日志binary log中记录了对MySQL数据库执行更改的所有操作并且记录了语句发生时间、执行时长、操作数据等其它额外信息但是它不记录SELECT、SHOW等那些不修改数据的SQL语句
bin log主要应用场景
一是用于 数据恢复
二是用于 数据复制 在MYSQL8版本中默认二进制日志是开启着的 show variables like %log_bin%; 日志格式
binlog格式分为: STATEMENT、ROW和MIXED三种
STATEMENT
STATEMENT格式的binlog记录的是数据库上执行的原生SQL语句对数据进行修改的sql都记录在日志文件中
ROW
基于行的日志记录记录的是每一行的数据变更默认
MIXED
MIXED也是MySQL默认使用的二进制日志记录方式但MIXED格式默认采用基于语句的复制混合了STATEMENT和ROW两种格式默认采用STATEMENT在某些特殊情况下回自动切换为ROW进行记录 show variables like %binlog_format%; 查看日志
当MySQL创建二进制日志文件时先创建一个以“filename”为名称、以“.index”为后缀的文件再创建一个以“filename”为名称、以“.000001”为后缀的文件
MySQL服务 重新启动一次 以“.000001”为后缀的文件就会增加一个并且后缀名按1递增。即日志文件的 个数与MySQL服务启动的次数相同如果日志长度超过了 max_binlog_size 的上限默认是1GB就会创建一个新的日志文件 binlog的相关参数
max_binlog_size
可以通过max_binlog_size参数来限定单个binlog文件的大小默认1G如果当前binlog文件的大小达到了参数指定的阈值会创建一个新的binlog文件作为当前活跃的binlog文件后续所有对数据库的修改都会记录到新的binlog文件中。
对于binlog文件的大小有个需要注意的地方是binlog文件可能会大于max_binlog_size参数设定的阈值。由于一个事务所产生的所有事件必须记录在同一个binlog文件中所以即使binlog文件的大小达到max_binlog_size参数指定的大小也要等到当前事务的所有事件全部写入到binlog文件中才能切换这样就会出现binlog文件的大小大于max_binlog_size参数指定的大小的情况。
binlog_cache_size
当使用事务的表存储引擎如InnoDB存储引擎时所有未提交uncommitted的二进制日志会被记录到一个缓存中去等该事务提交committed时直接将缓冲中的二进制日志写入二进制日志文件而该缓冲的大小由binlog_cache_size决定默认大小为32K。此外binlog_cache_size是基于会话session的也就是说当一个线程开始一个事务时MySQL会自动分配一个大小为binlog_cache_size的缓存因此该值的设置需要相当小心不能设置过大。当一个事务的记录大于设定的binlog_cache_size时MySQL会把缓冲中的日志写入一个临时文件中因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态可以判断当前binlog_cache_size的设置是否合适。binlog_cache_use记录了使用缓冲写二进制日志的次数binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。
sync_binlog
在MySQL 5.7之前版本默认情况下二进制日志并不是在每次写的时候同步的磁盘用户可以理解为缓冲写。因此当数据库所在的操作系统发生宕机时可能会有最后一部分数据没有写入二进制文件中这会给恢复和复制带来问题。参数sync_binlog[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1即sync_binlog1表示采用同步写磁盘的方式来写二进制日志每次事务提交时就会刷新binlog到磁盘sync_binlog为0表示刷新binlog时间点由操作系统自身来决定操作系统自身会每隔一段时间就会刷新缓存数据到磁盘sync_binlog为N表示每N个事务提交会进行一次binlog刷新。如果使用Innodb存储引擎进行复制并且想得到最大的高可用性需要将此值设置为1。不过该值为1时确时会对数据库IO系统带来一定的开销。
但是即使将sync_binlog设为1还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时在一个事务发出COMMIT动作之前由于sync_binlog为1因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志但是提交还没有发生并且此时发生了宕机那么在MySQL数据库下次启动时由于COMMIT操作并没有发生这个事务会被回滚掉。但是二进制日志已经记录了该事务信息不能被回滚。对于这个问题MySQL使用了两阶段提交来解决的简单说就是对于已经写入到binlog文件的事务一定会提交成功 而没有写入到binlog文件的事务就会进行回滚从而保证二进制日志和InnoDB存储引擎数据文件的一致性保证主从复制的安全。
binlog-do-dbbinlog-ignore-db
参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的二进制日志。默认为空表示需要同步所有库的日志到二进制日志。
log-slave-update
如果当前数据库是复制中的slave角色则它不会将master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入要设置log-slave-update。如果需要搭建master–slave–slave架构的复制则必须设置该参数。
binlog-format
binlog_format参数十分重要用来设置二进制日志的记录格式,详情参考(6.5 binlog格式)
log_bin_trust_function_creators
默认为OFF这个参数开启会限制存储过程、Function、触发器的创建
删除日志
对于比较繁忙的业务系统每天生成的binlog数据巨大如果长时间不清除将占用大量磁盘空间 也可以在mysql的配置文件中配置二进制日志的国企时间设置了之后二进制日志过期会自动删除 show variables like %binlog_expire_logs_seconds%; 4.查询日志(general log)
查询日志里的内容不要被查询日志误导认为里面只存储select语句其实不然查询日志里面记录了数据库执行的所有命令不管语句是否正确都会被记录
insert查询为了避免数据冲突如果此前插入过数据当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错
update时也会查询因为更新的时候很可能会更新某一块数据
delete查询只删除符合条件的数据
因此都会产生日志在并发操作非常多的场景下查询信息会非常多那么如果都记录下来会导致IO非常大影响MySQL性能因此如果不是在调试环境下是不建议开启查询日志功能的
查询日志配置
参数general_log用来控制开启、关闭MySQL查询日志
参数general_log_file用来控制查询日志的位置
如果你要判断MySQL数据库是否开启了查询日志可以使用下面命令。general_log为ON表示开启查询日志OFF表示关闭查询日志 show variables like %general%; 打开查询日志
如果要开启查询日志可以设置以下配置
修改MySQL的配置文件/etc/my.cnf文件添加内容如下 如果开启了查询日志参数log_output控制着查询日志的存储方式 log_output可以设置为以下4种值:
FILE : 表示日志存储在文件中
TABLE : 表示日志存储在mysql库中的general_log表中
FILE, TABLE : 表示将日志同时存储在文件和general_log表中改值会徒增很多IO压力一般不会这样设置
NONE : 表示不记录日志即使general_log设置为ON 如果log_output设置为NONE也不会记录查询日志
log_output不仅控制查询日志的输出也控制着慢查询日志的输出即: log_output设置为FILE就表示查询日志和慢查询日志都存放在文件中设置为TABLE查询日志和慢查询日志都存放在mysql库中的general_log表中
查看log_output设置: show variables like log_output; 关闭查询日志
可以在my.cnf里添加,1开启0关闭,这样要重启才能生效
general-log 0
然后重启mysql
5.慢查询日志(slow query log)
慢查询会导致CPUIOPS内存消耗过高。当数据库遇到性能瓶颈时大部分时间都是由于慢查询导致的。 开启慢查询日志可以让MySQL记录下查询超过指定时间的语句之后运维人员通过定位分析能够很好的优化数据库性能
慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句还有INSERTDELETEUPDATECALL等DML操作只要超过了指定时间都可以称为慢查询被记录到慢查询日志中
默认情况下慢查询日志是不开启的只有手动开启了慢查询才会被记录到慢查询日志中
慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志默认未开启。long_query_time默认为10秒最小为0精度可以到微妙 show variables like %slow%; set global slow_query_logON;也可以打开慢查询日志只是对当前数据库有效如果MySQL数据库重启后就会失效。所以如果要永久生效就要修改配置文件 my.cnf
默认情况下不会记录管理语句也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes 6. 中继日志(relay log)
中继日志relay log只在主从服务器架构的从服务器上存在。从服务器slave为了与主服务器(Master)保持一致要从主服务器读取二进制日志的内容并且把读取到的信息写入本地的日志文件中这个从服务器本地的日志文件就叫中继日志。然后从服务器读取中继日志并根据中继日志的内容对从服务器的数据进行更新完成主从服务器的数据同步
relay log是复制过程中产生的日志很多方面都跟binary log差不多区别是: relay log是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上
中继日志作用
中继日志用于主从服务器架构中从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容来同步主服务器上的操作
中继日志是连接mastert(主服务器)和slave(从服务器)的信息它是复制的核心I/O线程将来自master的binlog存储到中继日志中中继日志充当缓冲这样master不必等待slave执行完成就可以发送下一个binlog show variables like %relay%; 相关参数 max_relay_log_size
标记relay log 允许的最大值如果该值为0则默认值为max_binlog_size(1G)如果不为0则max_relay_log_size则为最大的relay_log文件大小
relay_log
定义relay_log的位置和名称如果值为空则默认位置在数据文件的目录文件名为host_name-relay-bin.nnnnnnBy default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory
relay_log_index
同relay_log定义relay_log的位置和名称
relay_log_info_file
设置http://relay-log.info的位置和名称http://relay-log.info记录MASTER的binary_log的恢复位置和relay_log的位置
relay_log_purge
是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_recovery
当slave从库宕机后假如relay-log损坏了导致一部分中继日志没有处理则自动放弃所有未执行的relay-log并且重新从master上获取日志这样就保证了relay-log的完整性。默认情况下该功能是关闭的将relay_log_recovery的值设置为 1时可在slave从库上开启该功能建议开启。
relay_log_space_limit
防止中继日志写满磁盘这里设置中继日志最大限额。但此设置存在主库崩溃从库中继日志不全的情况不到万不得已不推荐使用
sync_relay_log
这个参数和sync_binlog是一样的当设置为1时slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区然后刷入relay log中继日志里这样是最安全的因为在崩溃的时候你最多会丢失一个事务但会造成磁盘的大量I/O。当设置为0时并不是马上就刷入中继日志里而是由操作系统决定何时来写入虽然安全性降低了但减少了大量的磁盘I/O操作。这个值默认是0可动态修改。
sync_relay_log_info
这个参数和sync_relay_log参数一样当设置为1时slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区然后刷入relay-log.info里这样是最安全的因为在崩溃的时候你最多会丢失一个事务但会造成磁盘的大量I/O。当设置为0时并不是马上就刷入relay-log.info里而是由操作系统决定何时来写入虽然安全性降低了但减少了大量的磁盘I/O操作。这个值默认是0可动态修改。
7.事务日志
数据库数据存放的文件称为data file日志文件称为log file数据库数据是有缓存的如果没有缓存每次都写或者读物理disk那性能就太低下了。数据库数据的缓存称为data buffer日志redo缓存称为log buffer既然数据库数据有缓存就很难保证缓存数据脏数据与磁盘数据的一致性
为了保证事务的ACID特性就不得不说MySQL InnoDB引擎的事务日志: 重做日志redo和回滚日志undo
注: 在数据库的世界里数据从来都不重要日志才是最重要的有了日志就有了一切
看查日志文件设置状态 show variables like innodb_%; innodb_log_files_in_group:DB 中设置几组事务日志默认是2 innodb_log_group_home_dir 事务日志存放目录不设置
1.重做日志(redo log)
redo log包括两部分一是内存中的日志缓冲(redo log buffer)该部分日志是易失性的二是磁盘上的重做日志文件(redo log file)该部分日志是持久的并且是事务的记录是顺序追加的性能非常高(磁盘的顺序写性能比内存的写性能差不了太多)
InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘事务就持久化了即使断电了InnoDB可以重放日志并且恢复已经提交的事务
持久化
事务被提交数据一定会被写入到数据库中并持久存储起来通常来说当事务已经被提交之后就无法再次回滚了
重做日志实现持久化
与原子性一样事务的持久性也是通过日志来实现的MySQL 使用重做日志 redo log 实现事务的持久性重做日志由两部分组成一是内存中的重做日志缓冲区因为重做日志缓冲区在内存中所以它是易失的另一个就是在磁盘上的重做日志文件它是持久的 重做日志执行
在MySQL中事务执行 commit 提交了之后但是服务器宕机了数据还没有写入磁盘在MySQL重启服务之后会重新执行这个重做日志写入数据
redo log参数
innodb_log_files_in_group
redo log 文件的个数命名方式如ib_logfile0iblogfile1... iblogfilen。默认2个最大100个
innodb_log_file_size
文件设置大小默认值为 48M最大值为512G注意最大值指的是整个 redo log系列文件之和即innodb_log_files_in_group * innodb_log_file_size 不能大于最大值512G
innodb_log_group_home_dir
文件存放路径
innodb_log_buffer_size
Redo Log 缓存区默认8M可设置1-8M。延迟事务日志写入磁盘把redo log 放到该缓冲区然后根据 innodb_flush_log_at_trx_commit参数的设置再把日志从buffer 中flush 到磁盘中
2.回滚日志(undo log)
undo log有两个作用提供回滚和多个行版本控制(MVCC)
在数据修改的时候不仅记录了redo还记录了相对应的undo如果因为某些原因导致事务失败或回滚了可以借助该undo进行回滚
undo log和redo log记录物理日志不一样它是逻辑日志。可以认为当delete一条记录时undo log中会记录一条对应的insert记录反之亦然当update一条记录时它记录一条对应相反的update记录
当执行rollback时就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候也是通过undo log来实现的当读取的某一行被其他事务锁定时它可以从undo log中分析出该行记录以前的数据是什么从而提供该行版本信息让用户实现非锁定一致性读取
回滚日志实现原子性
想要保证事务的原子性就需要在异常发生时对已经执行的操作进行回滚而在 MySQL 中恢复机制是通过回滚日志 undo log 实现的所有事务进行的修改都会先记录到这个回滚日志中然后再对数据库中的对应行进行写入。注意系统发生崩溃、数据库进程直接被杀死后当用户再次启动数据库进程时还能够立刻通过查询回滚日志将之前未完成的事务进行回滚这也就需要回滚日志必须先于数据持久化到磁盘上是我们需要先写日志后写数据库的主要原因。在日志文件中在事务中使用的每一条 insert into 都对应了一条 delete 每一条 update 也对应一条相反的 update 语句
回滚日志执行
1.手动执行回滚命令时会执行。
2.如果程序在事务执行之后提交命令执行之前出现了异常在下次 MySQL 服务重启的时候会执行
undo log相关参数 show global variables like %undo%; show global variables like %truncate%; innodb_max_undo_log_size
控制最大undo tablespace文件的大小当启动了innodb_undo_log_truncate 时undo tablespace 超过innodb_max_undo_log_size 阀值时才会去尝试truncate。该值默认大小为1Gtruncate后的大小默认为10M。
innodb_undo_tablespaces
设置undo独立表空间个数范围为0-128 默认为00表示表示不开启独立undo表空间 且 undo日志存储在ibdata 文件中。该参数只能在最开始初始化MySQL实例的时候指定如果实例已创建这个参数是不能变动的如果在数据库配置文 件 .cnf 中指定innodb_undo_tablespaces 的个数大于实例创建时的指定个数则会启动失败提示该参数设置有误。
innodb_undo_log_truncate
InnoDB的purge线程根据innodb_undo_log_truncate设置开启或关闭innodb_max_undo_log_size的参数值以及truncate的频率来进行空间回收和 undo file 的重新初始化。
innodb_purge_rseg_truncate_frequency
用于控制purge回滚段的频度默认为128。假设设置为n则说明当Innodb Purge操作的协调线程 purge事务128次时就会触发一次History purge检查当前的undo log 表空间状态是否会触发truncate
3.重做日志与回滚日志总结
在数据库系统中事务的原子性和持久性是由事务日志 transaction log 保证的在实现时也就是上面提到的两种日志欠着用于对事务的影响进行撤销后者在错误处理时对已经提交的事务进行重做它们能保证两点
1.发生错误或者需要回滚的事务能够成功回滚原子性。
2.在事务提交后数据没来得及写入磁盘就宕机时在下次重新启动后能够成功恢复数据持久性
在数据库中这两种日志经常都是一起工作的我们可以将他们整体看作一条事务日志其中包含了事务的ID、修改的行元素以及修改前后的值
