mysql数据库文件

mysql数据库文件
InnoDB 和 MyISAM的区别
Innodb隔离级别原理实现
InnoDB主备复制

一、mysql和Innodb文件

    1.配置文件my.cnf： 配置了mysql的参数，通过show VARIABLES like '％connect_timeout％'查看mysql 参数

    2. 错误日志： 错误日志文件对MySQL的启动、运行、关闭过程进行了记录。

mysql> show variables like 'log_error';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| log_error     | ./mysql-err.log |
+---------------+-----------------+
1 row in set (0.01 sec)

    3.慢查询日志： MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。
    long_query_time    ：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
    slow_query_log      ：  指定是否开启慢查询日志
    log_slow_queries    ：  指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
    slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
    min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
    log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引
    log_throttle_queries_not_using_indexes:  表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。 该值默认为0，表示没有限制。在生产环境下，若没有使用索引，此类SQL语句会频繁地被记录到slow log，从而导致slow log文件的大小不断增加，故DBA可通过此参数进行配置。

    4.查询日志：   查询日志记录来所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

    5.二进制日志： 二进制日志（binary log）记录来对MySQL数据库执行更改的所有操作，但是不包括SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。可用于数据恢复，主备复制，数据库审计。
    max_binlog_size: 指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制文件。
    binlog_cache_size: 指定二进制缓存大小，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等改事务提交时直接将缓存中的二进制日志写入二进制日志文件。
    sync_binlog:  指定缓存同步策略，系统默认的设置是sync_binlog=0。
    0: 当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
    N: 当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。如果sync_binlog＝1表示采用同步写磁盘的方式写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。
    innodb_support_xa=1可以解决当sync_binlog＝1时，二进制日志已写入磁盘，但是事务还没有提交，并且此时发生了宕机，重启时事务被回滚，但是二进制日志没有被回滚的情况。

    6.表结构定义文件： MySQL都有一个以frm为后缀名的文件，这个文件记录来该表的表结构定义。
   
    7.表空间文件： InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。
    innodb_data_file_path: 指定表空间文件的路径和大小。
    innodb_file_per_table: 若设置来参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓存BITMAP等信息，其余信息还是存放在默认的表空间。
    MyISAM：
    .myd 即 my data，表数据文件
    .myi 即my index，索引文件




    8.重做日志文件： 重做日志文件用来实现事务的持久性。
    每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。用户可以设置多个的镜像日志组（mirrired log groups），将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志文件1中。
    innodb_log_file_size: 指定每个重做日志文件的大小。
    innodb_log_files_in_group: 指定来重做日志组中重做日志文件的数量。
    innodb_log_mirrored_log_groups: 指定了日志镜像文件组的数量，默认为1，表示只有一个文件组，没有镜像。
    innodb_log_group_home_dir: 指定了日志文件组所在路径。
   innodb_flush_log_at_trx_commit：用来控制重做日志刷新到磁盘的策略。
    0: 表示事务提交时不进行写入重做日志操作，这个操作仅在maser thread中完成，而master thread每1秒会进行一次重做日志文件的fsync操作。
    1: 表示事务提交时必须调用一次fsync操作。
    2: 表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行fsync操作。这个设置下，当MySQL数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。



二、进制日志和重做日志比较
   

• 重做日志是在InnoDB存储引擎层产生，而二进制日志是在MySQL数据库的上层产生。
• 重做日志只记录针对InnoDB存储引擎本身的事务日志，而二进制日志会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。
• 两种日志记录的内容形式不同。二进制日志是一种逻辑日志，其记录的是对应的SQL语句，而重做日志是物理格式日志，其记录的是对于每个页的修改。
• 写入磁盘的时间点不同。二进制日志只在事务提交完成后进行一次写入，而重做日志在事务进行中不断被写入。
• 目的不同。二进制日志可用于数据恢复，主备复制，数据库审计，而重做日志是用于重做已提交，但还未刷新到磁盘的事物。

三、事务提交
    一个事务执行时实际发生过程简化描述如下：

• 先记录 undo/redo log，确保日志刷到磁盘上持久存储。
• 更新数据记录，缓存操作并异步刷盘。
• 提交事务前，将事务日志持久化到 binlog。
• 提交事务，在 redo log 中写入commit记录。

总结：这样的话，只要 binlog 没写成功，整个事务是需要回滚的，而 binlog 写成功后即使 MySQL Crash 了都可以恢复事务并完成提交。要做到这点，就需要把 binlog 和事务关联起来，而只有保证了 binlog 和事务数据的一致性，才能保证主从数据的一致性。所以 binlog 的写入过程不得不嵌入到纯粹的事务存储引擎执行过程中，并以内部分布式事务（xa 事务）的方式完成两阶段提交。