1. 错误日志(Error log)
(1)错误日志的介绍
错误日志(error log): 记录 MySQL 服务进程 mysqld 在自动 / 关闭或远行过程中遇到的错误信息
(2)错误日志的调整
在 my.cnf 配置文件里面查看错误日志 log-error
- [root@mysql ~]# sed -n 78,80p /data/3306/my.cnf
- [mysqld_safe]
- log-error=/data/3306/mysql_zbf3306.err
- pid-file=/data/3306/mysqld.pid
2. 查询日志(Query Log)
(1)查询日志的介绍
查询日志(query log): 又可分两类
普通查询日志(general query log): 记录客户端连接信息和执行的 SQL 语句信息
慢查询日志 (slow query log): 记录执行时间超出指定值(long_query_time) 的 SQL 语句
(2)普通查询日志的调整(general query log)
普通查询日志不重要, 在工作中是不开的
a. 我们先登录 mysql 看一下 general log 有没有开启
- mysql> show variables like %general_log%;
- +------------------+---------------------------+
- | Variable_name| Value |
- +------------------+---------------------------+
- | general_log | OFF|
- | general_log_file | /data/3306/data/mysql.log |
- +------------------+---------------------------+
- 2 rows in set (0.00 sec)
通过查询我们得知 general_log 没有开启, 下面我们将 general_log 开启
b. 在线开启 general_log
- mysql> set global general_log=ON;
- Query OK, 0 rows affected (0.00 sec)
c. 查看 general_log 是否开启
- mysql> show variables like %general_log%;
- +------------------+---------------------------+
- | Variable_name| Value |
- +------------------+---------------------------+
- | general_log | ON|
- | general_log_file | /data/3306/data/mysql.log |
- +------------------+---------------------------+
- 2 rows in set (0.00 sec)
- [root@mysql ~]# ll /data/3306/data/mysql.*
-rw-rw----. 1 mysql mysql 363 2 月 13 01:02 /data/3306/data/mysql.log
d. 测试 general_log 作用
general query log: 记录客户端连接信息和执行的 SQL 语句信息: 下面我们来验证一下
创建一个库
- mysql> create database nn;
- Query OK, 1 row affected (0.01 sec)
接下来我们查看一下 mysql.log 普通查询日志
- [root@mysql ~]# cat /data/3306/data/mysql.log
- /usr/local/mysql/bin/mysqld, Version: 5.5.32-log (Source distribution). started with:
- Tcp port: 3306 Unix socket: /data/3306/mysql.sock
- Time Id CommandArgument
- 180213 1:11:05 25 Query create database nn
我们看这里面记录了我刚才创建库的语句
(3)慢查询的调整(重要, 优化 SQL 语句做增量备份主从同步都需要)
- [root@mysql ~]# sed -n "33,35p" /data/3306/my.cnf
- long_query_time = 1
- #log-slow-queries = /data/3306/slow.log
- #log_queries_not_using_indexes
long_query_time 参数多长时间记录慢查询, log-slow-queries 慢查询的位置,
log_queries_not_using_indexes
参数是没使用索引的语句记录到 log 里面去
3. 二进制日志(Binary Log)
(1)二进制日志的介绍
二进制日志(binary log): 记录数据被修改的相关信息
(2)二进制日志的调整
- [root@mysql ~]# egrep "\[mysqld]|log-bin|log-sla" /data/3306/my.cnf
- [mysqld]
- log-bin = /data/3306/mysql-bin
- log-slave-updates
log_bin 是记录 binlog
sql_log_bin 临时不记录 binlog
4.binlog 日志的三种模式
4.1 Statement Level 模式
每一条会修改的数据的 sql 都会记录到 master 的 binlog 中, slave 在复制的时候 sql 进程会解析成和原来 master 端执行过的相同的 sql 来再次执行
优点: Stament level 下的优点首先就是解决了 row level 下的缺点, 不需要记录每一行数据的变化, 减少 bin-log 日志量, 节约 IO, 提高性能因为他只需要记录在 Master 上所执行的语句的细节, 以及执行语句时候的上下文的信息
缺点: 由于他是记录的执行语句, 所以为了让这些语句在 slave 端也能正确执行, 那么他还必须记录每条语句在执行的时候的一些相关信息, 也就是上下文信息, 以保证所有语句在 slave 端执行的时候能够得到和在 master 端执行时候相同的语句另外就是, 由于 MySQL 现在发展比较快, 很多的新功能不断的加入, 使 MySQL 的复制遇到了不小的挑战, 自然复制的时候涉及到越复杂的内容, bug 也就越容易出现在 statement level 下, 目前已经发现的就有不少情况会造成 MySQL 的复制出现问题, 主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现, 比如: sleep()函数在有些版本中就不能正确复制, 在存储过程中使用了 last_insert_id()函数, 可能会是 slave 和 master 上得到不一致的 id 等等, 由于 row level 是基于每一行来记录的变化, 所以不会出现类似的问题
4.2 Row Level 模式
日志中会记录成每一行数据被修改的形式, 然后再 slave 端再对相同的数据进行修改
优点: 在 row level 模式下, binlog 中可以不记录执行的 sql 语句的上下文相关的信息, 仅仅只需要记录那一条记录被修改了, 修改成什么样了, 所以 row level 的日志内容会非常清楚的记录下每一行数据修改的细节, 非常容易理解而且不会出现某些特定情况下的存储过程, 或 function, 以及 trigger 的调用和触发无法被正确复制的问题他能够解决 statement level 模式解决不了的问题
缺点: row level 模式下, 所有的执行的语句当记录到日志中的时候, 都将以每行的修改为记录, 这样可能会产生大量的日志内容, 比如这样的一条 update 语句: update student set name=nishisheiwhere name=linzhongniao, 执行之后, 日志中记录的不是这条 update 语句而是这条语句所更新的每一条记录的变化情况, 这样就记录成很多条记录被更新的很多个事件自然, bin-log 日志的存储量就会很大
4.3 Mixed 模式
Mixed 模式是前两种模式的混合, 在 Mixed 模式下, MySQL 会根据执行的每一条具体的 sql 语句来划分对待记录的日志形式, 也就是在 Statement 和 Row 之间的选择一种, 觉得哪一种模式好就用哪一种记录新版本中的 Statement level 还是和以前一样, 仅仅记录执行的语句而新版本的 MySQL 中对 row level 模式也被做了优化, 并不是多有的修改都会以 row level 来记录, 想遇到表结构变更的时候就会以 statement 模式来记录, 如果 sql 语句确实就是 update 或者 delete 等修改数据的语句, 那么还是会记录所有行的变更
5. 调整 binlog 日志模式方法
5.1 在配置文件中修改的方法
下面是 Mysql5.5 默认模式也是我们前面主从复制默认的模式
- mysql> show variables like %binlog_format%;
- +---------------+-----------+
- | Variable_name | Value |
- +---------------+-----------+
- | binlog_format | STATEMENT |
- +---------------+-----------+
在配置文件中修改的参数如下
- [root@mysql ~]# sed -n "39,42p" /data/3306/my.cnf
- log-bin = /data/3306/mysql-bin
- #binlog_format = "STATEMENT"
- #binlog_format = "ROW"
- binlog_format = "MIXED"
官方推荐是用混合模式
5.2 在线修改立即生效方法
(1)运行时在线修改, 当前会话生效
- mysql > set session binlog_formant = STATEMENT;
- mysql > set session binlog_formant = ROW;
- mysql > set session binlog_formant = MIXED;
(2)全局生效, 所有用户都生效:
- mysql > set global binlog_format = ROW;
- mysql > set global binlog_format = STATEMENT;
- mysql > set global binlog_format = MIXED;
(3)实战演示全局修改 binlog 日志模式以及 row 的原理演示
a. 修改为 row 模式
- mysql> set global binlog_format = ROW;
- Query OK, 0 rows affected (0.00 sec)
如果查询默认模式没有修改突出登录, 再登录查看默认模式就修改了
- mysql> show variables like %binlog_format%;
- +---------------+-------+
- | Variable_name | Value |
- +---------------+-------+
- | binlog_format | ROW |
- +---------------+-------+
- 1 row in set (0.00 sec)
b. 刷新 binlog
为了更好的演示我们刷新一下 binlog, 这时候我们再更新数据的时候就会往新生成的 binlog 日志文件里面写了
[root@mysql 3306]#mysqladmin - uroot - p123456 - S / data / 3306 / mysql.sock flush - logs
c. 登录 mysql 修改 lingzhongniao 库的数据
我们将 linzhongniao 库中 student 表中的 name 字段的数据都修改为 linzhongniao2
- mysql> update student set name=linzhongniao2;
- Query OK, 9 rows affected (0.00 sec)
- Rows matched: 9 Changed: 9 Warnings: 0
- mysql> select * from student;
- +----+---------------+
- | id | name |
- +----+---------------+
- | 1 | linzhongniao2 |
- | 3 | linzhongniao2 |
- | 5 | linzhongniao2 |
- | 6 | linzhongniao2 |
- | 8 | linzhongniao2 |
- | 10 | linzhongniao2 |
- | 11 | linzhongniao2 |
- | 13 | linzhongniao2 |
- | 15 | linzhongniao2 |
- +----+---------------+
- 9 rows in set (0.00 sec)
d. 查看 binlog 日志文件
[root@mysql 3306]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000026
省略
- #180214 0:52:30 server id 1 end_log_pos 245 Table_map: `linzhongniao`.`student` mapped to number 33
- #180214 0:52:30 server id 1 end_log_pos 617 Update_rows: table id 33 flags: STMT_END_F
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=1
- ### @2=linzhongniao1
- ### SET
- ### @1=1
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=3
- ### @2=linzhongniao1
- ### SET
- ### @1=3
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=5
- ### @2=linzhongniao1
- ### SET
- ### @1=5
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=6
- ### @2=linzhongniao1
- ### SET
- ### @1=6
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=8
- ### @2=linzhongniao1
- ### SET
- ### @1=8
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=10
- ### @2=linzhongniao1
- ### SET
- ### @1=10
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=11
- ### @2=linzhongniao1
- ### SET
- ### @1=11
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=13
- ### @2=linzhongniao1
- ### SET
- ### @1=13
- ### @2=linzhongniao2
- ### UPDATE `linzhongniao`.`student`
- ### WHERE
- ### @1=15
- ### @2=linzhongniao1
- ### SET
- ### @1=15
- ### @2=linzhongniao2
- # at 617
- #180214 0:52:30 server id 1 end_log_pos 644 Xid = 39
- COMMIT/*!*/;
- DELIMITER ;
- # End of log file
我们看上面的内容可以发现 row 模式记录每一行记录被修改的内容, 修改成什么样了, 这样就会导致 bin-log 日志的存储量就会很大
来源: http://www.bubuko.com/infodetail-2498832.html