江哥架构师笔记mysql的日志分为：
查询日志：general_log
慢查询日志：log_slow_queries
错误日志：log_error， log_warnings
二进制日志：binlog
中继日志：relay_log，主从复制中，为完成复制功能而设定的
事务日志：innodb_log
1、查询日志
作用：记录查询语句，
日志存储位置：可以是文件：file，可以是表：table (mysql.general_log)
mysql> show variables like '%log%'; +-----------------------------------------+---------------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------------+ | general_log | OFF | | general_log_file | /var/run/mysqld/mysqld.log | | log_output | FILE |
可用的选项
general_log={ON|OFF} #默认off，想永久有效，可以设置在配置文件中，centos6设置：general_log。centos7设置：general_log,或者general_log = ON 都可以
general_log_file=HOSTNAME.log #默认为/var/lib/mysql/mysqld.log #要注意设置的目录要对mysql有写权限
log_output={FILE|TABLE|NONE}
#设置日志的输出方式，可以是输出到文件中：FILE，或者输出到数据库的表中，或者设置为：FILE,TABLE，两个都记录
#默认是文件中，如果是表中，则是mysql.general_log表
示例
mysql> set global general_log = OFF; Query OK, 0 rows affected (0.00 sec) mysql> set global log_output='table'; #之后的所有操作都会记录下来 Query OK, 0 rows affected (0.00 sec)注意：建议不要开启，因为mysql本来就是io密集型，会增加负担，除非为了排错
2、慢查询日志
慢查询：运行时间超出指定时长的查询，变量名：long_query_time
存储位置：
可以是文件：FILE
可以是表：TABLE，mysql.slog_log
查看配置：
mysql> select @@long_query_time; +-------------------+ | @@long_query_time | +-------------------+ | 10.000000 | +-------------------+ 1 row in set (0.00 sec)
选项
log_slow_queries={ON|OFF} #默认是关闭的，和上面一样，centos6配置文件设置启动：log_slow_queries，快要被废弃了
slow_query_log={ON|OFF} #配置文件中添加上面一条，这条会自动开启了，有时候还要添加这条
slow_query_log_file=/var/run/mysqld/mysqld-slow.log #默认
log_output={FILE|TABLE|NONE}
long_query_time #当时间超过该变量定义的时间会被记录在上面的文件中
log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#慢查询过滤的语句，这些慢查询不会被记录到慢查询中，这个变量好像只有centos7的mariadb上面有，
log_slow_rate_limit #日志记录速率限制，这个变量好像只有centos7的mariadb上面有，
log_slow_verbosity #详细信息这个变量好像只有centos7的mariadb上面有，
测试
mysql> show variables like 'slow_query_log%'; #默认是关闭的 +---------------------+---------------------------------+ | Variable_name | Value | +---------------------+---------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/run/mysqld/mysqld-slow.log | +---------------------+---------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like 'log_slow%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | OFF | +------------------+-------+ 1 row in set (0.00 sec) mysql> exit; Bye [root@localhost ~]#vim /etc/my.cnf log_slow_queries [root@localhost ~]#service mysqld restart mysql> show variables like 'slow_query_log%'; +---------------------+---------------------------------+ | Variable_name | Value | +---------------------+---------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/run/mysqld/mysqld-slow.log | +---------------------+---------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like 'log_slow%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | log_slow_queries | ON | +------------------+-------+ 1 row in set (0.00 sec)
测试中，有可能即使表锁定，但是可以从缓存中直接读数据，导致测试失败。将缓存关闭：query_cache_type = OFF;就可以关闭缓存
3、错误日志：
全局变量：log_error | /var/log/mariadb/mariadb.log
记录信息：
(1) mysqld启动和关闭过程 输出的信息； (2) mysqld运行中产生的错误信息； (3) event scheduler运行时产生的信息； (4) 主从复制架构中，从服务器上，复制线程启动时产生的日志；
log_error= 路径
log_warnings={ON|OFF} #设置为on，表示警告信息也会被记录到错误日志信息中，比如插入数据的时候有时候会产生警告信息，这样就可以在日志中看到具体的警告信息，建议开启。如果想要永久生效，写到配置文件中
4、二进制日志：
开启二进制日志后， 当在操作数据库的每个操作都会记录在二进制日志中，当在另一台空服务器上重新导入这个二进制日志后（相当于所有的操作都执行了一遍），这个服务器的数据库内容就和前面一个数据库内容一样了。用于恢复数据库数据。
binlog_format={STATEMENT|ROW|MIXED} #设定记录方式，具体看相关的博文
STATEMENT：语句；记录执行的每个语句，但是遇到now函数这些跟当前时间有关系的函数，即使相同语句，跑出来的结果也不同，需要上下文 ROW：行；不记录执行的语句，而是记录执行结果，好处是结果一定是相同的，但是如果结果很庞大，则二进制日志会非常大 MIXED：混编；将上面的两个好处结合在一块进行记录
典型的恢复过程：当前的数据库挂掉了。某一个时间点备份的数据库+从这个时间点开始到当前的二进制日志，来恢复到当前的时间点的数据文件
二进制日志文件不要和数据库文件放在同一个存储设备上面，以防存储设备出问题导致二进制日志和数据库两个都不能用，也没有办法用二进制日志恢复数据。为了提高mysql磁盘的读写io，也为了数据安全
因为是二进制日志是二进制文件，不能使用cat命令查看其内容，必须是用专门的工具来查看
服务器变量说明：
show variables like "%log_bin%";
#命令行命令，查看二进制日志是否开启，默认是OFF，是个只读变量，不能修改
#当在配置文件中设置：log_bin=/PATH/TO/BIN_LOG_FILE，重启mysql服务，会自动变为ON
#如果需要临时关闭二进制日志功能，在命令行执行：set sql_log_bin=off; 只对此session有效
#如果想永久关闭，注释配置文件：log-bin=/data 和binlog_format=mixed 然后重启服务
sql_log_bin={ON|OFF} #命令行命令，要不要记录二进制于指定的文件中，默认就是：ON，而下面的log_bin定义日志文件记录在哪里/PATH/TO/BIN_LOG_FILE
log_bin=/PATH/TO/BIN_LOG_FILE #配置文件里用，log_bin定义日志文件记录在哪里/PATH/TO/BIN_LOG_FILE
#在配置文件中设置：log_bin=master-log 这个是相对路径文件，表明二进制日志名字叫master-log，在mysql的数据目录下面。
#在mysql的变量中无法查看此路径
sync_binlog={1|0} #默认是0，表明当随系统缓存一段时间二进制日志在内存中后将二进制日志刷到硬盘中，具体看相关博文
max_binlog_size=1073741824 #默认是1GB，就是说当二进制日志单个文件超过1GB的时候，会自动滚动
max_binlog_size = 100M
expire_log_days ={0..99}
#设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。
mysql> select 1073741824/1024/1024/1024;
+---------------------------+
| 1073741824/1024/1024/1024 |
+---------------------------+
| 1.000000000000 |
+---------------------------+
1 row in set (0.00 sec)
常用的变量
max_binlog_size：二进制日志最大值，当超过后会自动滚动，默认1G字节大小
max_binlog_cache_size：缓存大小，写操作非常多的时候，先缓存到内存中，一段时间后同步到磁盘中，但是有可能掉电丢失数据，binlog会在内存中缓存，事务日志也会在内存中缓存
sync_binlog：当有事件产生的时候，会将内存中数据同步到磁盘中，为了事物安全，这个一般要开启
[root@localhost /tmp]#ls #与二进制日志相关的文件类型就这两个 master-log.000001 master-log.index
查看二进制日志文件列表：下面两种都可以
mysql> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-log.000001 | 150 | | master-log.000002 | 106 | +-------------------+-----------+ 2 rows in set (0.00 sec) mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-log.000001 | 150 | | master-log.000002 | 106 | +-------------------+-----------+ 2 rows in set (0.00 sec)
查看当前服务正在使用的二进制日志文件：一般是最后一个文件，
mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-log.000002 | 6746 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
查看二进制 日志文件中的事件：哪个二进制日志文件，
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] mysql> show binlog events in 'master-log.000002'\G; *************************** 1. row *************************** Log_name: master-log.000002 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.73-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: master-log.000002 Pos: 106 Event_type: Query Server_id: 1 End_log_pos: 261 Info: CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ *************************** 3. row *************************** Log_name: master-log.000002 Pos: 261 Event_type: Query Server_id: 1 End_log_pos: 357 Info: use `hellodb`; DROP TABLE IF EXISTS `classes`
select语句不会记录，像update语句会记录，即会修改mysql的数据记录
二进制日志不能使用cat来进行查看，会显示乱码
当重启mysql服务，二进制日志就会重新启动建立一个日志文件进行记录，文件名一直往上增长，也可以在mysql的命令行里面执行：flush logs; #手动刷新，将重启一个日志文件记录
mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-log.000001 | 150 | | master-log.000002 | 6746 | +-------------------+-----------+ 2 rows in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-log.000001 | 150 | | master-log.000002 | 6790 | | master-log.000003 | 106 | +-------------------+-----------+ 3 rows in set (0.00 sec) [root@localhost /tmp]#ll #可以看到文件大小和上面mysql命令行里面的文件大小是一样的 total 28 -rw-r--r-- 1 root root 7786 May 24 23:35 hellodb.sql -rw-rw---- 1 mysql mysql 150 May 24 23:30 master-log.000001 -rw-rw---- 1 mysql mysql 6790 May 24 23:43 master-log.000002 -rw-rw---- 1 mysql mysql 106 May 24 23:43 master-log.000003 -rw-rw---- 1 mysql mysql 69 May 24 23:43 master-log.index [root@localhost /tmp]#cat master-log.index #这个索引文件里面也会记录bin文件位置 /tmp/master-log.000001 /tmp/master-log.000002 /tmp/master-log.000003
继续上面的操作，模拟正常的插入，删除，查询操作，二进制日志只会记录能修改文件的语句条目，像查询：select不会改变数据，不会记录到二进制日志中。
mysql> insert into courses (course) values ('puppet');
Query OK, 1 row affected (0.01 sec)
mysql> delete from courses where CourseID=6;
Query OK, 1 row affected (0.01 sec)
mysql> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 7 | Dagou Bangfa |
| 8 | puppet |
+----------+----------------+
7 rows in set (0.00 sec)
然后查看刚才最近的二进制日志文件，#Server_id是指这个语句是由哪个服务器产生的语句，因为插入语句是在数据库里面的最后插入的，为了数据的准确，数据库会自动加入一条插入id的语句
mysql> show binlog events in 'master-log.000003';
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
| master-log.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |
| master-log.000003 | 106 | Intvar | 1 | 134 | INSERT_ID=8 |
| master-log.000003 | 134 | Query | 1 | 246 | use `hellodb`; insert into courses (course) values ('puppet') |
| master-log.000003 | 246 | Query | 1 | 348 | use `hellodb`; delete from courses where CourseID=6 |
+-------------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> show master status; #这里再查看master状态，表明下一条数据是从这个位置开始写的，这个位置也是上一条语句的结尾
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 348 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysqlbinlog：专门的日志查看工具，除了上面通过mysql命令行查看具体的内容外，也可以使用这个命令来查看上面的二进制日志文件，也是一种方法
选项：
--start-datetime= --stop-datetime= 例如：shell> mysqlbinlog --start-datetime="2005-12-25 11:25:56" binlog.000003 -j, --start-position=# --stop-position=# --user, --host, --password #获取其他主机的日志文件内容，当然这个用户要有获取二进制日志的权限
二进制日志事件格式：这里面只要有时间戳的，时间都是该二进制文件最后被修改的时间，没有什么用
# at 553 #160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1472608568/*!*/; BEGIN /*!*/; 事件发生的日期时间：#160831 9:56:08 事件发生的服务器id：server id 1 事件的结束位置：end_log_pos 624 事件的类型：Query 事件发生时所在服务器执行此事件的线程的ID： thread_id=2 语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0 错误代码：error_code=0 事件内容：SET TIMESTAMP=1472608568/*!*/;
将二进制日志文件导出为sql语句，因为二进制日志里面记录的就是操作语句，可以使用命令将这些sql操作语句导出来，在机器上面重新放一遍。
shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
删除二进制日志文件
PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除MySQL BIN-LOG 日志，这样被给定的日志成为第一个。
例如：
PURGE MASTER LOGS TO 'MySQL-bin.010'; #将10之前的日志文件都删除 PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; #清除3天前的 binlog PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。MASTER和BINARY是同义词。
执行上面的命令之后，在mysql的命令行里面，还有在linux日志目录下面，还有master-log.index文件里面，都会自动删除掉。
如果还有一个从服务器正常工作，并且从服务器当前正在读取正在试图删除的日志文件，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器服务停止了，并且主节点清理了从节点想要读取的日志，则从属服务器启动后不能正常复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。
要清理日志，需按照以下步骤：
1. 在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。
2. 使用SHOW MASTER LOGS获得主服务器上的一系列日志。
3. 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的，这是清单上的最后一个日志。
4. 制作您将要删除MySQL BIN-LOG 日志的所有日志的备份。（这个步骤是自选的，但是建议采用。）
5. 清理所有的日志，但是不包括目标日志
5、中继日志：
relay_log 从服务器上的中继日志路径
从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
6、事务日志：
事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，是为了事务的隔离而设立的，具体查看表锁相关博文。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后保存在硬盘上的事务日志文件里面，然后将事务日志中的内容写入到最终的数据库文件中。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。即：内存缓存–>事务日志—>数据库文件
如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回最终的数据库中，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。
在mysql中默认以ib_logfile0,ib_logfile1名称存在,可以手工修改参数，调节 开启几组日志来服务于当前mysql数据库,mysql采用顺序，循环写方式，每开启一个事务时， 会把一些相关信息记录事务日志中(记录对数据文件数据修改的物理位置或叫做偏移量); 作用:在系统崩溃重启时，作事务重做；在系统正常时，每次checkpoint时间点，会将之前写入事务 应用到数据文件中。下面的两个文件就是事务日志存放的位置，可以在数据库中设置。
[root@localhost /var/lib/mysql]#ll total 20492 -rw-rw---- 1 mysql mysql 5242880 May 24 23:17 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 May 24 23:14 ib_logfile1
MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关1.MyISAM：不支持事务，用于只读程序提高性能 2.InnoDB：支持ACID事务、行级锁、并发
上面的概念要理解，备份要用到，不然不知所云
mysql> show global variables like '%log%'; +-----------------------------------------+---------------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------------+ | innodb_flush_log_at_trx_commit | 1 | #在事务提交时innodb是否同步日志从缓冲到文件中1表示事务以提交就同步不提交每隔一秒同步一次，性能会很差造成大量的磁盘I/O； #定义为2表示只有在事务提交时才会同步但是可能会丢失整个事务 | innodb_log_buffer_size | 1048576 | #根据下面计算，事务日志缓存1MB后会将内存中的文件刷入硬盘上的事务日志中 | innodb_log_file_size | 5242880 | #根据下面计算，硬盘上每个事务日志文件大小5MB | innodb_log_files_in_group | 2 | #一共有两个事务日志文件 | innodb_log_group_home_dir | ./ | #在数据目录同目录下 mysql> select 1048576/1024/1024; +-------------------+ | 1048576/1024/1024 | +-------------------+ | 1.00000000 | +-------------------+ 1 row in set (0.00 sec) mysql> select 5242880/1024/1024; +-------------------+ | 5242880/1024/1024 | +-------------------+ | 5.00000000 | +-------------------+ 1 row in set (0.00 sec)
–
–
–
评论前必须登录！
注册