MySQL学习笔记（一）MySQL的日志

说明

  • 最近学习MySQL DBA功课，这里记录一下笔记。
  • 作者：yfang (yangfang at fudan.edu.cn)
  • 修改时间：2008-1-3
  • 原创文章，欢迎转载，转载请注明出处及作者信息，谢谢

MySQL服务器

MySQL logs

通用查询日志（General Qurey Log）
  • 内容 通用查询日志里记录了所有客户端连接和断开信息，所有执行的SQL（不论是否成功执行）
  • 配置 开启选项--log[=file_name]
  • 特点 通用查询日志里记录了服务器的大量行为，所以会随着服务器运行而迅速增大，特别是对于比较繁忙的服务器，磁盘开销很大，正常运行时建议关闭
  • 应用 通用查询日志主要用于服务诊断，平时运行不建议开启
  • 默认 默认不开启，默认文件名为hostname.log，默认路径是数据库data目录
  • MySQL5.1中的通用查询日志 在MySQL5.0中，通用查询日志只能使用文件存储。而在MySQL5.1中，存放在文件中或是存放在表中是可选的。全局的general_log和general_log_file变量可以控制是否记录这个日志。将general_log设为0（或OFF）即可禁用这个log。general_log_file指定了存放log的文件名。当通用查询日志开启的时候，--log-output选项可以控制log的存放方法，可选的值包括（*TABLE*，放在表中：*FILE*，放在文件中；*NONE*，优先考虑其他位置）。
慢查询日志（Slow Query Log）
  • 内容 记录服务器中运行超过long_query_time（默认10）秒的查询
  • 配置 开启选项--log-slow-queries[=file_name]以记录慢查询日志，开启选项--log-queries-not-using-indexes将会把所有没有使用索引的查询记录到日志中，即使它的运行时间小于long_query_time秒。
  • 应用 通常用来检查SQL合理性和有效性，索引的使用情况，查询的瓶颈诊断，有助于优化查询，如果方便的话建议开启
  • 默认 默认不开启，默认文件名是主机名-slow.log，默认路径是数据库data目录
  • MySQL5.1中的慢查询日志 和通用查询日志一样，受到--log-output选项的控制，如果选择存放在数据库中，会放到mysql数据库的slow_log表中。
  • 其他 使用mysqldumpslow命令可以摘要查看慢查询日志内容
二进制日志（Binary Log）
  • 内容 记录服务器中所有更新操作
  • 配置 开启选项--log-bin以记录慢查询日志
  • 应用 用于记录数据库的所有更新操作，通常用于数据恢复和replication时slave更新操作，建议开启
  • 默认 默认不开启，默认文件名是形如主机名-bin.000001的文件名，默认目录是数据库data目录
  • Rotate 当MySQL服务重启或是日志到达max_binlog_size(1.1G左右)或是FLUSH LOGS执行的时候，二进制日志会rotate一下。
  • 删除日志 设定全局变量expire_logs_days或是SQL命令PURGE BINARY LOGS
-- 日志被rotate后，如果更新时间大于expire_logs_days天即会被自动删除
SET GLOBLE expire_logs_days = 1;

-- 删除三天前的日志
PURGE BINARY LOGS BEFORE now() - interval 3 day;

-- 删除不比mysql-bin.010新的日志
PURGE BINARY LOGS TO 'mysql-bin.010';


  • 因为binlog是repliacation必须的，所以删除binlog的时候要格外小心，否则会导致slave同步失败，你需要权衡slave和master的同步差和各种其他因素，比如一个繁忙的服务器可能一个小时就有几个G的log，你可能不能保存太多在磁盘上，等等。
  • 二进制日志是存储引擎无关的
  • 查看二进制日志 因为文件是二进制的，所以不能简单的查看，需要使用mysqlbinlog工具进行查看
# 查看两个binlog文件
mysqlbinlog host-bin.000001 host-bin.000002 | more


  • 日志形式 主要包括基于SQL命令（Statement Based）的形式和基于行更改（ROW Based）的形式。前者是传统和默认形式，只记录SQL更新命令，好处是记录和传输代价小，坏处是一方面这种形式因持续复制而受很多限制（比如使用了random），另一方面你需要把数据库中所有表和所有行都进行同步，而不能只复制一个表用于replication。后者是MySQL5.1新加的功能，用来克服上面的不足，增强灵活性，通过变量binlog_format实现，特别支持Mixed形式，只到需要的时候改变成row-based replication。基于行的日志可能加速slave上的复杂查询。当slave和master的mysql版本或是行结构不同时，基于行的日志是不能用的，只能用基于Statement的。两种机制在行锁上的开销也不同，大部分操作基于行的要好一些。由于基于satatement的日志是可读的，所以可以方便的在数据库crash的时候恢复到以前的一个点，而基于行的日志不可读，也做不到这一点。
-- 修改binlog 形式，启动后再执行也可以
SET [GLOBLE|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];


升级MySQL

  • 备份永远是重要的，所以请先备份你的数据库
  • 停止服务
  • 在原目录覆盖安装新程序
  • 开启服务
  • 如果是二进制安装，通常配置文件会保存下来，所以你不需要做更多的操作，如果担心，备份配置文件也是明智的选择。
mysql_upgrade程序
  • mysql_upgrade程序可以检查数据库是否又不兼容的地方，如果有的话，会修复表，被检查和修复过的表会标记成新的mysql版本号，下次升级的时候会重新判断是否需要修复表。
  • mysql_upgrade程序同时会升级系统表，所以你可以在更新后使用新功能。
  • mysql_upgrade程序在mysql_upgrade.info文件中保留MySQL的版本号，以便快速检查跳过所有被检查过的表。用--force可以屏蔽这个文件
  • mysql_upgrade的实现：
# 检查并修复不兼容的表
# 在不支持mysql_upgrade的老版本中，可以手动执行下面的命令
mysqlcheck --check-upgrade --all-databases --auto-repair
mysql_fix_privilege_tables


  • mysql_upgrade需要在服务正常运行的时候执行。

-- Main.yfang - 03 Jan 2008