MySQL日志:

查询日志:查询记录日志,会耗费I/O,压力大,建议不开启。可存文件或表中。

慢查询日志:查询执行时长超过指定时长的查询操作所记录日志,阻塞

错误日志:默认配置文件启用。

二进制日志:redolog,发生的所有改变操作,记录日志。

中继日志:保存从服务器上数据日志,从主服务器复制而来,不能直接使用。

事务日志:事务性

ACID, 随机I/O转换为顺序I/O

日志有关的参数

MariaDB [(none)]> show global variables like ‘%log%’;

+——————————————-+————————————————————————————————————–+

| Variable_name                             | Value                                                                                                        |

+——————————————-+————————————————————————————————————–+

| aria_checkpoint_log_activity              | 1048576                                                                                                      |

| aria_log_file_size                        | 1073741824                                                                                                   |

| aria_log_purge_type                       | immediate                                                                                                    |

| aria_sync_log_dir                         | NEWFILE                                                                                                      |

| back_log                                  | 150                                                                                                          |

| binlog_annotate_row_events                | OFF                                                                                                          |

| binlog_cache_size                         | 32768                                                                                                        |

| binlog_checksum                           | NONE                                                                                                         |

| binlog_commit_wait_count                  | 0                                                                                                            |

| binlog_commit_wait_usec                   | 100000                                                                                                       |

| binlog_direct_non_transactional_updates   | OFF                                                                                                          |

| binlog_format                             | MIXED                                                                                                        |

| binlog_optimize_thread_scheduling         | ON                                                                                                           |

| binlog_stmt_cache_size                    | 32768                                                                                                        |

| expire_logs_days                          | 0                                                                                                            |

| general_log                               | OFF                                                                                                          |

| general_log_file                          | node3.log                                                                                                    |

| gtid_binlog_pos                           | 0-1-222                                                                                                      |

| gtid_binlog_state                         | 0-1-222                                                                                                      |

| innodb_api_enable_binlog                  | OFF                                                                                                          |

| innodb_flush_log_at_timeout               | 1                                                                                                            |

| innodb_flush_log_at_trx_commit            | 1                                                                                                            |

| innodb_locks_unsafe_for_binlog            | OFF                                                                                                          |

| innodb_log_arch_dir                       | ./                                                                                                           |

| innodb_log_arch_expire_sec                | 0                                                                                                            |

| innodb_log_archive                        | OFF                                                                                                          |

| innodb_log_block_size                     | 512                                                                                                          |

| innodb_log_buffer_size                    | 8388608                                                                                                      |

| innodb_log_checksum_algorithm             | innodb                                                                                                       |

| innodb_log_compressed_pages               | ON                                                                                                           |

| innodb_log_file_size                      | 50331648                                                                                                     |

| innodb_log_files_in_group                 | 2                                                                                                            |

| innodb_log_group_home_dir                 | ./                                                                                                           |

| innodb_mirrored_log_groups                | 1                                                                                                            |

| innodb_online_alter_log_max_size          | 134217728                                                                                                    |

| innodb_undo_logs                          | 128                                                                                                          |

| innodb_use_global_flush_log_at_trx_commit | ON                                                                                                           |

| log_bin                                   | ON                                                                                                           |

| log_bin_trust_function_creators           | OFF                                                                                                          |

| log_error                                 | /mydata/data/node3.zye.com.err                                                                               |

| log_output                                | FILE                                                                                                         |

| log_queries_not_using_indexes             | OFF                                                                                                          |

| log_slave_updates                         | OFF                                                                                                          |

| log_slow_filter                           | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |

| log_slow_rate_limit                       | 1                                                                                                            |

| log_slow_verbosity                        |                                                                                                              |

| log_warnings                              | 1                                                                                                            |

| max_binlog_cache_size                     | 18446744073709547520                                                                                         |

| max_binlog_size                           | 1073741824                                                                                                   |

| max_binlog_stmt_cache_size                | 18446744073709547520                                                                                         |

| max_relay_log_size                        | 1073741824                                                                                                   |

| relay_log                                 |                                                                                                              |

| relay_log_index                           |                                                                                                              |

| relay_log_info_file                       | relay-log.info                                                                                               |

| relay_log_purge                           | ON                                                                                                           |

| relay_log_recovery                        | OFF                                                                                                          |

| relay_log_space_limit                     | 0                                                                                                            |

| slow_query_log                            | OFF                                                                                                          |

| slow_query_log_file                       | node3-slow.log                                                                                               |

| sql_log_bin                               | ON                                                                                                           |

| sql_log_off                               | OFF                                                                                                          |

| sync_binlog                               | 0                                                                                                            |

| sync_relay_log                            | 0                                                                                                            |

| sync_relay_log_info                       | 0 

————————————————————————————-

查询日志:

log_output = {TABLE|FILE|NONE}    日志输出位置(与慢查询一样)

log_output = TABLE,FILE    可以多取值

FILE: gerenal_log

general_log = {ON|OFF}         是否启用查询日志

general_log_file = www.log 当log_output有FILE类型时,日志信息的记录位置;

——————————————————————————————-

慢查询日志:

SELECT @@GLOBAL.long_query_time;

+————————–+

| @@GLOBAL.long_query_time |

+————————–+

|                10.000000 |    单位秒

+————————–+

slow_query_log = {ON|OFF}:    是否启用慢查询日志

slow_query_log = /path/to/somefile: 日志文件路径

        

        log_output = {TABLE|FILE|NONE}    日志输出位置(与慢查询一样)

log_output = TABLE,FILE    可以多取值


log_slow_rate_limit =          限制速率 

log_slow_verbosity =           详细格式查看,影响I/O

——————————————————————————————-

错误日志:

[root@node3 data]# ls

aria_log.00000001  ib_logfile1        mysql-bin.000002  mysql-bin.000007  mysql-bin.000012  node2.zye.com.pid

aria_log_control   multi-master.info  mysql-bin.000003  mysql-bin.000008  mysql-bin.000013  node3.zye.com.err

hellodb            mydb               mysql-bin.000004  mysql-bin.000009  mysql-bin.000014  node3.zye.com.pid

ibdata1            mysql              mysql-bin.000005  mysql-bin.000010  mysql-bin.000015  performance_schema

ib_logfile0        mysql-bin.000001   mysql-bin.000006  mysql-bin.000011  mysql-bin.index   test

        记录:

mysqld启动和关闭过程中输出的信息;

mysqld运行中产生的错误信息;

event scheduler运行一个event时产生的日志信息;

在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息;

log_error = /path/to/somefile

log_warnings = {ON|OFF}:    是否记录警告信息于错误日志中;

————————————————————————————–

二进制日志:

    数据恢复

[root@node3 data]# ls

aria_log.00000001  ib_logfile1        mysql-bin.000002  mysql-bin.000007  mysql-bin.000012  node2.zye.com.pid

aria_log_control   multi-master.info  mysql-bin.000003  mysql-bin.000008  mysql-bin.000013  node3.zye.com.err

hellodb            mydb               mysql-bin.000004  mysql-bin.000009  mysql-bin.000014  node3.zye.com.pid

ibdata1            mysql              mysql-bin.000005  mysql-bin.000010  mysql-bin.000015  performance_schema

ib_logfile0        mysql-bin.000001   mysql-bin.000006  mysql-bin.000011  mysql-bin.index   test


SHOW {BINARY | MASTER} LOGS: 查看主服务器端处于由mysqld维护状态中的二进制日志文件;

SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]:显示指定的二进制日志文件中的相关事件

MariaDB [(none)]> show master logs;

+——————+———–+

| Log_name         | File_size |

+——————+———–+

| mysql-bin.000001 |       347 |

| mysql-bin.000002 |       264 |

| mysql-bin.000003 |       264 |

| mysql-bin.000004 |       575 |

| mysql-bin.000005 |       245 |

| mysql-bin.000006 |       331 |

| mysql-bin.000007 |     23266 |

| mysql-bin.000008 |     65284 |

| mysql-bin.000009 |       369 |

| mysql-bin.000010 |      1681 |

| mysql-bin.000011 |     20714 |

| mysql-bin.000012 |       345 |

| mysql-bin.000013 |       537 |

| mysql-bin.000014 |       705 |

| mysql-bin.000015 |       477 |

+——————+———–+

15 rows in set (0.09 sec)

MariaDB [(none)]> show binlog events in ‘mysql-bin.000015’;

+——————+—–+——————-+———–+————-+————————————————+

| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |

+——————+—–+——————-+———–+————-+————————————————+

| mysql-bin.000015 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.13-MariaDB-log, Binlog ver: 4 |

| mysql-bin.000015 | 248 | Gtid_list         |         1 |         287 | [0-1-221]                                      |

| mysql-bin.000015 | 287 | Binlog_checkpoint |         1 |         326 | mysql-bin.000015                               |

| mysql-bin.000015 | 326 | Gtid              |         1 |         364 | GTID 0-1-222                                   |

| mysql-bin.000015 | 364 | Query             |         1 |         477 | SET PASSWORD FOR ‘root’@’localhost’=”         |

+——————+—–+——————-+———–+————-+————————————————+

MariaDB [(none)]> show binlog events in ‘mysql-bin.000015’\G

*************************** 1. row ***************************

   Log_name: mysql-bin.000015

        Pos: 4

 Event_type: Format_desc

  Server_id: 1

End_log_pos: 248

       Info: Server ver: 10.0.13-MariaDB-log, Binlog ver: 4

*************************** 2. row ***************************

   Log_name: mysql-bin.000015

        Pos: 248

 Event_type: Gtid_list

  Server_id: 1

End_log_pos: 287

       Info: [0-1-221]

*************************** 3. row ***************************

   Log_name: mysql-bin.000015

        Pos: 287

 Event_type: Binlog_checkpoint

  Server_id: 1

End_log_pos: 326

       Info: mysql-bin.000015

*************************** 4. row ***************************

   Log_name: mysql-bin.000015

        Pos: 326     

 Event_type: Gtid

  Server_id: 1

End_log_pos: 364

       Info: GTID 0-1-222

*************************** 5. row ***************************

   Log_name: mysql-bin.000015

        Pos: 364

 Event_type: Query

  Server_id: 1

End_log_pos: 477

       Info: SET PASSWORD FOR ‘root’@’localhost’=”

5 rows in set (0.00 sec)

重新记录位置

MariaDB [(none)]> flush logs;

Query OK, 0 rows affected (0.30 sec)

MariaDB [(none)]> show master logs;

+——————+———–+

| Log_name         | File_size |

+——————+———–+

| mysql-bin.000001 |       347 |

| mysql-bin.000002 |       264 |

| mysql-bin.000003 |       264 |

| mysql-bin.000004 |       575 |

| mysql-bin.000005 |       245 |

| mysql-bin.000006 |       331 |

| mysql-bin.000007 |     23266 |

| mysql-bin.000008 |     65284 |

| mysql-bin.000009 |       369 |

| mysql-bin.000010 |      1681 |

| mysql-bin.000011 |     20714 |

| mysql-bin.000012 |       345 |

| mysql-bin.000013 |       537 |

| mysql-bin.000014 |       705 |

| mysql-bin.000015 |       520 |

| mysql-bin.000016 |       365 |

+——————+———–+

16 rows in set (0.00 sec)

MariaDB [(none)]> show binlog events in ‘mysql-bin.000016’\G

*************************** 1. row ***************************

   Log_name: mysql-bin.000016

        Pos: 4

 Event_type: Format_desc

  Server_id: 1

End_log_pos: 248

       Info: Server ver: 10.0.13-MariaDB-log, Binlog ver: 4

*************************** 2. row ***************************

   Log_name: mysql-bin.000016

        Pos: 248

 Event_type: Gtid_list

  Server_id: 1

End_log_pos: 287

       Info: [0-1-222]

*************************** 3. row ***************************

   Log_name: mysql-bin.000016

        Pos: 287

 Event_type: Binlog_checkpoint

  Server_id: 1

End_log_pos: 326

       Info: mysql-bin.000015

*************************** 4. row ***************************

   Log_name: mysql-bin.000016

        Pos: 326

 Event_type: Binlog_checkpoint

  Server_id: 1

End_log_pos: 365

       Info: mysql-bin.000016

4 rows in set (0.00 sec)

注意:对于不修改数据的不记录二进制日志;按照语句记录不准确。对于函数无法恢复。

日志记录格式:

基于“语句”记录;statement

基于“行”记录;row

“混合” :mixed

二进制日志文件的构成:

日志文件:文件名前缀.文件名后缀  mysql-bin.000002

索引文件:文件名前缀.index     mysql-bin.index

服务器变量:/etc/my.cnf

  log_bin = /path/to/somefile(前缀)  记目录如果修改之前的后缀就没有用,

          binlog_format = MIXED

  sql_log_bin = ON               是否记录二进制  

  max_binlog_size = 1073741824(B) 二进制日志文件的单文件上限;自动滚动

  max_binlog_cache_size = 18446744073709547520  存到内存在到磁盘

  max_binlog_stmt_cache_size = 18446744073709547520  语句缓存

  sync_binlog = 0:设定事务提交多久同步一次二进制日志文件;0表示不同步有mysql线程同步;任何正值都表示修改多少个语句后同步一次;

命令使用

MariaDB [(none)]> show binlog events in ‘mysql-bin.000016’;

+——————+—–+——————-+———–+————-+————————————————+

| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info                                           |

+——————+—–+——————-+———–+————-+————————————————+

| mysql-bin.000016 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.13-MariaDB-log, Binlog ver: 4 |

| mysql-bin.000016 | 248 | Gtid_list         |         1 |         287 | [0-1-222]                                      |

| mysql-bin.000016 | 287 | Binlog_checkpoint |         1 |         326 | mysql-bin.000015                               |

| mysql-bin.000016 | 326 | Binlog_checkpoint |         1 |         365 | mysql-bin.000016                               |

+——————+—–+——————-+———–+————-+————————————————+

4 rows in set (0.00 sec)

MariaDB [(none)]> show binlog events in ‘mysql-bin.000016’ from 248;

+——————+—–+——————-+———–+————-+——————+

| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info             |

+——————+—–+——————-+———–+————-+——————+

| mysql-bin.000016 | 248 | Gtid_list         |         1 |         287 | [0-1-222]        |

| mysql-bin.000016 | 287 | Binlog_checkpoint |         1 |         326 | mysql-bin.000015 |

| mysql-bin.000016 | 326 | Binlog_checkpoint |         1 |         365 | mysql-bin.000016 |

+——————+—–+——————-+———–+————-+——————+

3 rows in set (0.04 sec)

MariaDB [(none)]> show binlog events in ‘mysql-bin.000016’ from 248 limit 2;

+——————+—–+——————-+———–+————-+——————+

| Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info             |

+——————+—–+——————-+———–+————-+——————+

| mysql-bin.000016 | 248 | Gtid_list         |         1 |         287 | [0-1-222]        |

| mysql-bin.000016 | 287 | Binlog_checkpoint |         1 |         326 | mysql-bin.000015 |

+——————+—–+——————-+———–+————-+——————+

2 rows in set (0.00 sec)

事件具体

[root@node3 data]# mysqlbinlog mysql-bin.000011

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#151025  6:49:28 server id 1  end_log_pos 248 Start: binlog v 4, server v 10.0.13-MariaDB-log created 151025  6:49:28

BINLOG ‘

+AosVg8BAAAA9AAAAPgAAAAAAAQAMTAuMC4xMy1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAEEwQAJQ2Euw==

‘/*!*/;

# at 248

#151025  6:49:28 server id 1  end_log_pos 287 Gtid list [0-1-130]

# at 287

#151025  6:49:28 server id 1  end_log_pos 326 Binlog checkpoint mysql-bin.000010

# at 326

#700101  8:00:00 server id 1  end_log_pos 365 Binlog checkpoint mysql-bin.000011

# at 365

#151025 18:19:37 server id 1  end_log_pos 403 GTID 0-1-131

/*!100001 SET @@session.gtid_domain_id=0*//*!*/;

/*!100001 SET @@session.server_id=1*//*!*/;

/*!100001 SET @@session.gtid_seq_no=131*//*!*/;

# at 403

#151025 18:19:37 server id 1  end_log_pos 558 Query thread_id=37 exec_time=0 error_code=0

SET TIMESTAMP=1445768377/*!*/;

SET @@session.pseudo_thread_id=37/*!*/;

SET @@session.foreign_key_checks=0, @@session.sql_auto_is_null=0, @@session.unique_checks=0, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=524288/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */

/*!*/;

# at 558

#151025 18:19:37 server id 1  end_log_pos 596 GTID 0-1-132

/*!100001 SET @@session.gtid_seq_no=132*//*!*/;

# at 596

#151025 18:19:37 server id 1  end_log_pos 718 Query thread_id=37 exec_time=0 error_code=0

use `hellodb`/*!*/;

SET TIMESTAMP=1445768377/*!*/;

DROP TABLE IF EXISTS `classes` /* generated by server */

/*!*/;

# at 718

#151025 18:19:37 server id 1  end_log_pos 756 GTID 0-1-133

/*!100001 SET @@session.gtid_seq_no=133*//*!*/;

# at 756

#151025 18:19:37 server id 1 end_log_pos 1068 Query thread_id=37 exec_time=0 error_code=0

SET TIMESTAMP=1445768377/*!*/;

CREATE TABLE `classes` (

  `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

  `Class` varchar(100) DEFAULT NULL,

  `NumOfStu` smallint(5) unsigned DEFAULT NULL,

  PRIMARY KEY (`ClassID`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

/*!*/;

# at 1068

#151025 18:19:37 server id 1  end_log_pos 1106 GTID 0-1-134

/*!100001 SET @@session.gtid_seq_no=134*//*!*/;

# at 1106

#151025 18:19:37 server id 1  end_log_pos 1218 Query thread_id=37 exec_time=0 error_code=0

SET TIMESTAMP=1445768377/*!*/;

/*!40000 ALTER TABLE `classes` DISABLE KEYS */

/*!*/;

# at 1218

#151025 18:19:37 server id 1  end_log_pos 1256 GTID 0-1-135

/*!100001 SET @@session.gtid_seq_no=135*//*!*/;

BEGIN

/*!*/;

# at 1256

#151025 18:19:37 server id 1  end_log_pos 1517 Query thread_id=37 exec_time=0 error_code=0

SET TIMESTAMP=1445768377/*!*/;

INSERT INTO `classes` VALUES (1,’Shaolin Pai’,10),(2,’Emei Pai’,7),(3,’QingCheng Pai’,11),(4,’Wudang Pai’,12),(5,’Riyue Shenjiao’,31),(6,’Lianshan Pai’,27),(7,’Ming Jiao’,27),(8,’Xiaoyao Pai’,15)

/*!*/;

# at 1517

#151025 18:19:37 server id 1  end_log_pos 1589 Query thread_id=37 exec_time=0 error_code=0

SET TIMESTAMP=1445768377/*!*/;

COMMIT

/*!*/;

# at 1589

#151025 18:19:37 server id 1  end_log_pos 1627 GTID 0-1-136

/*!100001 SET @@session.gtid_seq_no=136*//*!*/;

二进制日志的格式:

# at 19364

#140829 15:50:07 server id 1  end_log_pos 19486 Query thread_id=13 exec_time=0 error_code=0

SET TIMESTAMP=1409298607/*!*/;

GRANT SELECT ON tdb.* TO tuser@localhost

/*!*/;

# at 19486

事件发生的日期和时间;(140829 15:50:07)

事件发生在服务器的标识(server id)

事件的结束位置:(end_log_pos 19486)

事件的类型:(Query)

事件发生时所在的服务器执行此事件的线程的ID:(thread_id=13)

语句的时间戳与将其写入二进制文件中的时间差:(exec_time=0)

错误代码:(error_code=0)

事件内容:(SET TIMESTAMP=1409298607/*!*/;

           GRANT SELECT ON tdb.* TO tuser@localhost)            

GTID事件专属:

事件所属的全局事务的GTID:(GTID 0-1-2)

二进制日志的查看命令:

mysqlbinlog 客户端命令

-j, –start-position=#:从指定的事件位置查看,从下一个新位置

–stop-position=#:只显示到指定的事件位置

–start-datetime=name YYYY-MM-DD hh:mm:ss

–stop-datetime=name YYYY-MM-DD hh:mm:ss

-u  用户

-h  主机

-p  密码

————————————————————————————-

中继日志:

事务日志(innodb存储引擎):

innodb_flush_log_at_trx_commit            | 1     提交之后立即刷新

  innodb_log_file_size                      | 50331648                                                                                                     |

innodb_log_files_in_group                 | 2                                                                                                            |

innodb_log_group_home_dir                 | ./                                                                                                           |

innodb_mirrored_log_groups                | 1    是否镜像。当没储时可以镜像。