bin-log、错误日志配置
mysql 命令行设置
mysql> set @@global.log_bin=on; ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
my.ini 或者 my.cnf 在 [mysqld] 区块 添加或设置
server-id=1 log-bin=mysql-bin
错误日志配置
log-error=D:phpStudyMySQLdataerror.log
注意： 重启mysqld服务使配置生效
sql 语句查询日志相关信息
mysql> show variables like 'log_%'; +---------------------------------+----------------------------------+ | Variable_name | Value | +---------------------------------+----------------------------------+ | log_bin | ON | # 是否开启 bin log日志 | log_bin_trust_function_creators | OFF | | log_error | D:phpStudyMySQLdataerror.log | # 错误日志写入目录 | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | # 是否开启更新日志 | log_slow_queries | OFF | # 是否开启慢查询日志 | log_warnings | 1 | +---------------------------------+----------------------------------+
sql 语句查询二进制日志相关信息
mysql> show variables like '%bin%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sql_log_bin | ON | | sync_binlog | 0 | +-----------------------------------------+----------------------+
跟踪数据执行过程日志
mysql> show variables like 'general_%'; +------------------+----------------------------------+ | Variable_name | Value | +------------------+----------------------------------+ | general_log | OFF | | general_log_file | D:phpStudyMySQLdataAdmin.log | +------------------+----------------------------------+
设置开启/关闭 general_log , 配置文件中修改/添加 general_log=on/off
mysql 打开general log之后，所有的查询语句都可以在general log文件中以可读的方式得到；
注意 ：在Linux中只能设置到 /tmp 或 /var 文件夹下，设置其他路径出错 ，需要root用户才有访问此文件的权限
binlog日志操作命令
1.查看所有binlog日志列表 mysql> show master logs; 2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值 mysql> show master status; 3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件 mysql> flush logs; 注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志； 4.重置(清空)所有binlog日志 mysql> reset master; 5.binlog 日志存放位置 mysql> show variables like '%datadir%'; 注：mysql配置文件中的 datadir="路径" 默认datadir 指定的目录下
查看binlog 日志内容
1、 使用 show 命令查看
1. show binlog events; #只查看第一个binlog文件的内容
2. show binlog events in 'mysql-bin.000002'; #查看指定binlog文件的内容
3. show master statusG; #查看当前正在写入的binlog文件
4. show binary logs; #获取binlog文件列表
5. show binlog events in 'mysql-bin.000021' from 8224G; #指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起
6. show binlog events in 'mysql-bin.000021' from 8224 limit 10G; #指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条
7. show binlog events in 'mysql-bin.000021' from 8224 limit 2,10G; #指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 选项解析： IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件) FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) LIMIT [offset,] 偏移量(不指定就是0) row_count 查询总条数(不指定就是所有行) 截取部分查询结果： *************************** 20. row *************************** Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名 Pos: 11197 ----------------------------------------------------------> pos起始点: Event_type: Query ----------------------------------------------------------> 事件类型：Query Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的 End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点) Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句 *************************** 21. row *************************** Log_name: mysql-bin.000021 Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点) Event_type: Query Server_id: 1 End_log_pos: 11417 Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */ *************************** 22. row *************************** Log_name: mysql-bin.000021 Pos: 11417 Event_type: Query Server_id: 1 End_log_pos: 11510 Info: use `zyyshop`; DROP TABLE IF EXISTS `type` 这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；
2、用 mysqlbinlog 工具查看 结果与上面的一样
1 首先切换到 mysqlbinlog所在目录，在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项
2 mysqlbinlog /var/lib/mysql/mysql-bin.000001# 查看整个bin-log 日志
# 基于开始/结束时间 -d指定库的binlog hadoop是库名，/var/lib/mysql/mysql-bin.000001是二进制文件路径
3 mysqlbinlog --start-datetime='2016-08-02 00:00:00' --stop-datetime='2016-08-03 23:01:01' -d hadoop /var/lib/mysql/mysql-bin.000001
4 mysqlbinlog --start-position=2098 --stop-position=2205 -d hadoop /var/lib/mysql/mysql-bin.000001 #基于pos值
/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013
SET TIMESTAMP=1350355892/*!*/; ------------------------>开始的时间 BEGIN /*!*/; # at 1643330 -------------------->为事件的起点
----> 事件发生时间 server id为master的serverid 事件的终点 操作类型 线程id 花费的时间 错误码 #121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0 SET TIMESTAMP=1350355892/*!*/; Insert into T_test….) /*!*/; # at 1643885 #121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0 #xid 事务标识 COMMIT/*!*/;
binlog 的 工作模式
配置binlog 工作模式
my.ini 或者 my.cnf 在 [mysqld] 区块 添加或设置
binlog_format='ROW'
注意：放在mysqld模块下面 配置完成后需要重启mysql服务
查看binlog工作模式
mysql> show variables like "binlog%"; +-----------------------------------------+-----------+ | Variable_name | Value | +-----------------------------------------+-----------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | +-----------------------------------------+-----------+
三种模式的区别
ROW: 基于行的复制
优点：所有的语句都可以复制，不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了
缺点：binlog 大了很多，复杂的回滚时 binlog 中会包含大量的数据；主服务器上执行update语句时，所有发生变化的记录都会写到 binlog 中；比如有这样一条update语句：update product set owner_member_id='d' where owner_member_id='a',执行之后，日志中记录的不是这条update语句所对应的事件(mysql是以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记录成很多条记录被更新的很多事件。自然，bin-log日志的量会很大。
Statement: 基于sql语句的复制
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能
缺点：由于它是记录的执行语句，所以为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。另外就是,由于mysql现在发展比较快，很多的新功能加入，使mysql的复制遇到了不小的挑战,自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成mysql的复制问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如sleep()在有些版本就不能正确复制。
mixed模式 ：row 与 statement 结合
实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。新版本中的statement level还是和以前一样，仅仅记录执行的语句。而新版本的mysql中对row level模式被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete 等修改数据的语句，那么还是会记录所有行的变更。
参考引用：mysql通过bin_log恢复数据
mysql查看binlog日志内容
MySQL Binlog详解