MySQL的日志和常见的存储引擎

• MySQL的日志管理

• 日志类别:

  • 错误日志:
    • 服务器启动和关闭过程中的信息,slave服务器启动和关闭过程中的信息;
    • 服务器运行过程中发生的错误信息;
    • 事件调度器运行一个时间产生的信息;
    • 主从服务器架构中,slave服务器上启动从服务器进程时产生的信息;
    • log_warnings:默认是没有记录服务器运行过程中的警告信息的,;
      
    • log_errors:用于记录错误日志,以及文件名称,通常在数据目录中,主机名+.err为后缀;
      
  • 一般查询日志:
    
    • general_log:用于表示是否启用一般查询日志的;
    • general_log_file,默认是关闭的,建议关闭;
    • log:表示是否记录所有的查询新到log_file文件当中;
    • log_output {FILE|TABLE|NONE};,通常表文件是需要自己进行创建的;
  • 慢查询日志:
    • 执行了很长时间的查询操作的日志,用于定位服务器的查询性能问题;1s就是很长查询时间;
    • long_query_time :用于定义多长时间就是慢查询的,单位ie是秒钟,支持毫秒级别的解析度;
    • log_slow_queries:表示是否记录慢查询的;
    • slow_query_log_file:慢查询日志的文件;
  • 二进制日志:
    • 按照二进制格式进行记录快速的记录DML,DCL以及DDL语句产生的信息,任何引起数据改变或者潜在引起数据库改变的信息都回被记录,这些信息是用于实现复制以及及时点恢复的;需要使用专门的命令mysqlbinlog来打开文件,用于重放之前的操作,里面记录的是
    • 及时点还原:
      • 在数据文件损坏时,利用备份恢复一部分的数据,然后通过二进制日志找到数据点进行数据文件的恢复;
      • 如果数据库没有备份,但是所有的二进制日志文件都在,那么进行重放会和浪费时间;备份之前的二进制日志都是没有必要的;
    • 二进制日志中记录的是事件:
      • 事件产生的时间:起始时间,
      • 事件的在日志中的相对位置;
      • 可以通过时间和位置定位具体的时间;
    • 二进制文件:
      • 索引文件:记录所有的二进制日志文件mysql-bin-index,这个是一个文本文件,最后一个通常时正在使用的;
    • 二进制日志的记录格式:
      • 基于语句的改变:如果语句进行重放对于之前的操作是没有多大影响的;
      • 基于行:使用重放得到的日志信息和之前的信息不一致,就记录行的改变;
      • 混合方式记录:
    • 二进制日志文件的组成:
      • 索引文件:mysql-bin.index是文本文件
      • 二进制文件: mysql-bin00001;
  • 中继日志:格式和二进制日志相同,要求在从服务器上面需要执行Master的日志;
  • 事务日志:事务性存储引擎,保证事务特性的重要组件,将随机IO转换称为顺序IO,事务型存储引擎才需要这种日志;记录的信息包括:事务的ID号,原始数据以及新的数据;只能够对于表中的数据进行ROLLBACK,但是不能够对于表ROLLBACK;

• 查看和服务器日志相关的变量

mysql> show global variables like '%log%';
+-----------------------------------------+---------------------------------+
| Variable_name                           | Value                           |
+-----------------------------------------+---------------------------------+
| back_log                                | 50                              |
| binlog_cache_size                       | 32768                           |  //二进制日志
| binlog_direct_non_transactional_updates | OFF                             |
| binlog_format                           | MIXED                           |
| binlog_stmt_cache_size                  | 32768                           |
| expire_logs_days                        | 0                               |
| general_log                             | OFF                             |  //一般查询日志
| general_log_file                        | /mysql/mydata/server60.log      |
| innodb_flush_log_at_trx_commit          | 1                               |  //innodb事务日志
| innodb_locks_unsafe_for_binlog          | OFF                             |
| innodb_log_buffer_size                  | 8388608                         |
| innodb_log_file_size                    | 5242880                         |
| innodb_log_files_in_group               | 2                               |
| innodb_log_group_home_dir               | ./                              |
| innodb_mirrored_log_groups              | 1                               |
| log                                     | OFF                             |  //二进制日志
| log_bin                                 | ON                              |
| log_bin_trust_function_creators         | OFF                             |
| log_error                               | /mysql/mydata/server60.com.err  |
| log_output                              | FILE                            |
| log_queries_not_using_indexes           | OFF                             |
| log_slave_updates                       | OFF                             |
| log_slow_queries                        | OFF                             | //满查询日志
| log_warnings                            | 1                               |
| max_binlog_cache_size                   | 18446744073709547520            | //
| max_binlog_size                         | 1073741824                      |
| max_binlog_stmt_cache_size              | 18446744073709547520            |
| max_relay_log_size                      | 0                               |
| 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                     | /mysql/mydata/server60-slow.log |   //用于启用或者关闭二进制日志
| sql_log_bin                             | ON                              |
| sql_log_off                             | OFF                             |  //是否立即写入磁盘文件中
| sync_binlog                             | 0                               |
| sync_relay_log                          | 0                               |
| sync_relay_log_info                     | 0                               |
+-----------------------------------------+---------------------------------+
41 rows in set (0.00 sec)

• 查看当前使用的二进制文件

mysql> show master status;

• 打开慢查询日志

mysql> SET GLOBAL  slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

• 查询日志状态
  
• 这个选项也自动为ON
  
• 对于设计文件的操作,需要重新启动服务器才能够生效,但是对于打开或者关闭某个功能的操作,可以进行动态设定;
• 查看当前正在使用的二进制日志文件
  
• 查看二进制日志文件里面记录的信息
  
• 并且支持通过FROM来指定开始显示的位置;

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000012' FROM 2;

• 使用mysqlbinlog:

• 如果需要滚动日志可以使用mysql> FLUSH LOGS,

  • --start-datetime:表示日志开始的记录时间;
  • --stop-datetime:表示日志记录的结束时间;
  • 例如:mysqlbinlog --start-datetime='2018-02-14 15:58:34' mysql-bin.000018
  • 这两个选项需要同时使用;
  • --start-position:表示日志开始的记录位置;
  • --stop-position:表示日志结束的位置;
  • 例如:mysqlbinlog --start-position=177 --stop-position=358 mysql-bin.000018;
    

• 并且可以直接将上面的日志文件直接读取,并且进行重定向;

• 关于FLUSH LOGS:

  • 1.可以进行二进制日志的滚动,但是对于错误日志只是打开然后关闭;
  • 2.从服务器上面滚动中继日志;

• 对于二进制日志文加是不能够使用rm -f进行删除的,会导致MySQL文件的崩溃;,

• 删除二进制日志文件

• 二进制日志在进行备份之后才可以进行删除;

• 通常有下面三种格式:

• PURGE BINARY LOGS TO 'mysql-bin.000003';:表示删除这个日志文件之前的日志文件;
  

• PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';:表示删除某个具体的时间点之前的日志,这个时间点需要结合日志文件里面记录的时间进行分析,不可以随意指定;

• 关于二进制日志的指令

• binlog_format:表示二进制日志的格式;

• binlog_cache_size:二进制日志的缓存大小;

• binlog_stmt_cache_size:跟事务相关的二进制日志的缓存大小,不建议调大,因为丢失的可能性会变大;

• log_bin:是否启用二进制日志这是个总开关,这个可以接受文件路径,用于指定日志文件的记录位置;

• sql_log_bin:用于控制二进制日志文件信息是否记录进入文件,作用范围是全局和会话级别属于动态变量,可以用于限定log_bin,在进行及时点恢复数据时,这个选项是需要关闭的;

• sync_binlog:用于设定多久同步一次二进制日志文件的;0表示不同步,任何正数值,都表示对于二进制多少次写操作之后同步一次,当autocommit为1每条语句的执行都会引起二进制日志文件同步,否则,每个事务的提交都会引起二进制日志文件同步;

• max_binlog_cache_size:最大二进制日志文件的的缓存大小;

• sql_log_off:表示是否将一般查询日志记录进查询日志文件的

• expire_logs_days:

  • 表示日志的过期天数;
  • 0表示不进行限定;
  • 二进制日志是用来保证数据库崩溃时,可以正常的还原数据的,事务日志是用来保证事务本身的可靠性的;

• FIO:Fusion IO用于加快数据读写;IO/PS:IO/每秒操作数;

• 关于中继日志和事务日志的区别

• 中继日志:从服务器上面的二进制日志文件中复制过来的事件,并且保存为日志文件;

• 事务日志:

  • 事务型存储引擎,用于保证事务的ACID原子性,一致性,隔离性,持久性;
  • 通常是这种格式TID<old_value><new_table>;
  • 只能够对于表中数据的操作进行ROLLBACK;不能够对于DROP table的操作进行ROLLBACK;
  • 二进制日志是用于进行即时点还原的,不能够撤销,但是事务日志是可以进行撤销的;

• 事务日志的选项:

• innodb_flush_log_at_trx_commit:事务日志是存在缓存的[这个缓存是内核提供的缓存],首先需要同步到日志文件中,然后同步到数据文件中,这个参数的含义是将缓存中的文件操作信息同步到日志文件中;

  • 0:每一秒中同步一次,并且提交内核直接写入磁盘,不进行缓存,并且执行磁盘FLUSH操作;
  • 1:每事务提交或者每磁盘刷写,就进行同步,并且执行磁盘刷写;保证事务刷写,这种性能较低,但是安全性较高;
  • 2:表示每事务提交时,但是不执行磁盘FLUSH;

• innodb_log_buffer_size:表示事务日志的内存缓存大小;

• innodb_log_file_size:表示日志文件的大小;

• innodb_log_files_in_group:两个事务日志,也就是事务日志组的概念,一个写满,去写另一个,然后另一个事务日志执行同步;

• innodb_log_group_home_dir:表示日志组所在的目录,通常是ib_logfile0 ib_logfile1默认是5M大小的连续空间,建议将事务日志和数据分开存放,并且建议作为镜像进行存储;

• innodb_mirrored_log_groups:表示是否对于事务日志组作镜像的,不能够手动操作事务日志;

• 关于存储引擎

• MyISAM:
  

• 不支持事务,难以支持外键,默认只支持表锁,在读的场景高时,性能比较好,支持的索引类型:B树索引,FULLTEXT索引,空间索引,支持表压缩
  

• InnoDB:事务性存储引擎,支持行级锁,并且支持外键,支持对于表的并行写入,支持的索引类型:B树索引,聚簇索引,自适应hash索引,支持raw磁盘设备也就是裸设备,就是不进行创建文件系统的设备,InnoDB存储引擎是可以进行管理的;支持分布式设备;
  

• MRG_MYISAM: 对于两个表结构一样的,可以使用这个存储引擎作为抽象层,将这两个表的内容合并在一起;
  

• CSV:利用文本文件的格式来进行存取,方便进行移植;
  

• ARCHIVE:对于不进行操作的数据,保存并且进行归档,用于进行数据挖掘;
  

• MEMORY:将内存存储引擎,性能高,数据不安全,用于创建临时表;

• blackholl

• 如果需要支持其他的存储引擎,就需要重新进行编译MySQL来加载新的存储引擎;