MySQL数据库的重要文件

前言

    在使用MySQL数据库的时候，必须要熟悉MySQL数据库中的一些重要的文件，在查找错误、备份数据库、主从复制等各种应用场景中都需要用到这些文件。如果这些文件没有设置好或者没有开启，将对后面的维护工作带来巨大的不便。

1.参数文件

    MySQL数据库和其他数据库一样，都有一个记录数据库参数的参数文件，数据库在启动的时候读取这个文件，并且设置文件中配置的相关参数。与Oracle数据库不同，MySQL数据库即使没有参数文件，数据库也会正常启动，使用系统默认的参数配置。但是默认的配置仅是一些最基本的配置，在实际生产环境中往往不能满足要求，所以建议一定要配置好MySQL的配置文件，将需要的参数配置好。否则可能需要在数据库运行时重启数据库。

    MySQL数据库的配置文件名为my.cnf，通过mysql --help|grep my.cnf命令可以查到这个文件所在的路径，如：

MySQL数据库会从以上几个路径下顺序查找my.cnf，一般情况下，my.cnf参数文件都放在/etc/目录下，如果在这些目录下不知有一个my.cnf文件，那么MySQL数据库会以最后找到的一个为准，所以要保证参数文件的唯一性。

2.错误日志

    错误日志文件对MySQL的启动、运行、关闭的过程进行了记录。当数据库发生问题的时候，首先应该到这个文件中查询错误日志以定位问题。可以通过SHOW VARIABLES LIKE 'log_error"\G;命令来查找当前MySQL数据库的错误日志所在位置。

3.慢查询日志

    这个日志对于MySQL数据库的使用者而言，非常非常的重要。通过开启和设置相关参数，就可以通过这个日志帮助开发人员找到应用程序中存在性能问题的SQL语句。通过命令show variables like 'slow_query_log'\G;可以查看慢查询日志是否开启：

这个参数默认是不开启的，建议在MySQL数据库启动前，在my.cnf配置文件中设置相关参数：

long_query_time=2

slow-query-log=on

slow-query-log-file=/home/mysql/logs/mysql_slow.log

通过以上配置就可以开启慢查询日志，慢查询日志的开启会对数据库的性能参数一些影响，但是非常有限，相比于它给我们带来的便利，这点影响是可以忽略不计的。

    使用慢查询日志对SQL语句进行监控可以通过以下几个参数的配置：

    long_query_time=秒数，配置这个参数后，慢查询日志就会记录执行时间超过指定秒数的SQL语句，方便开发人员优化。

    log_queries_not_using_indexes=ON，设置这个参数后，数据库会将没有使用索引的SQL语句记录在慢查询日志中，在MySQL5.6.5之后，为了配合这个参数的使用，增加了参数log_throttle_queries_not_using_indexes，这个参数的意思是慢查询日志每分钟记录多少没有使用索引的SQL语句，默认值是0，代表没有限制。在高访问量数据库的应用系统中，一分钟可能会有大量的SQL语句输出。所以应该对这个参数进行一个合理的设置。还有很多参数设置可以帮助开发者更好的利用慢查询日志，这里不再赘述，建议大家多看看MySQL的官方手册，可以更好的理解慢查询日志。有人让过介绍过学习MySQL（或者其他的技术）看什么书好。我总是让他们先看官方的用户手册，因为官方手册是最权威的，更新最及时的参考材料。

    另外，查看慢查询日志需要通过MySQL自带的mysqldumpslow命令查看，否则你会一头雾水。

4.二进制日志

    二进制日志同样非常非常重要，尤其是对DBA来说，二进制日志文件损坏或者丢在就是噩梦。所以一般DBA对二进制日志文件和数据文件看的同样重要，在备份数据的同时一定也会备份二进制日志文件，这是一个好习惯，所有跟数据库打交道的人都应该遵守。二进制日志文件的开启，也会对数据库的性能造成一定的影响，同样也是非常小的，有的书上说是1%，但二进制日志文件是必须开启的。这里强烈建议将日志文件，包括二进制日志，慢查询日志等，设置到与数据文件不同的磁盘上。这样可以降低日志写操作对数据库的IO性能的影响。

    二进制日志文件可以通过配置文件中的log_bin参数进行配置，如log_bin=/home/mysql/logs/mysql-bin。配置之后二进制日志文件会以mysql-bin.XXXXXX的名称存放在相应目录下。后面跟着6位流水号。如：

   

    二进制日志文件中记录的是对数据库的修改操作，比如DELETE、INSERT、UPDATE等，对于SELECT或SHOW等查看操作理论上不会记录。正因为二进制日志的这个特点，二进制日志主要有以下几个作用：

    恢复：用户可以通过二进制日志文件对数据进行时间点恢复；

    复制：用户可以通过二进制日志文件的复制与执行，将一台MySQL服务器上的数据同步到另一台机器上，MySQL数据库的主从复制就是通过对二进制日志文件的复制来实现的；

    审计：通过二进制日志文件的审计，可以判断是否有对数据库的注入攻击。

    以下这些参数影响着二进制日志文件的信息和行为：

    max_binlog_size：指定了单个二进制日志文件的最大值，默认值是1G，如果超过了，后缀流水号+1。

    binlog_cache_size：这个参数代表二进制日志文件的写入缓存区的大小，默认为32K，当使用InnoDB存储引擎时，所有未提交的事务都会被记录到这个缓冲区中，等commit之后才会写入到二进制日志文件中，这个参数是基于会话（session）的，当一个线程开始一个事务时，它会独用这个缓冲区，因此这个值设置不能过大。但也不能设置的过小，当一个事务的记录大于这个值，MySQL就会把缓冲中的日志写入到一个临时文件中。可以通过SHOW GLOBAL STATUS命令查看binlog_cache_disk_use的状态，这个参数值表示临时文件二次写的次数，如果临时文件二次写次数过大，就说明要增加bin_cache_size的值。

    sync_binlog;这个参数代表是否使用操作系统的缓冲日志来写二进制日志。建议将这个参数的值设置为1，同时配合着将innodb_support_xa的值也设置为1。这样可以保证二进制日志和InnoDB存储引擎数据文件同步。但是不使用缓冲区写日志，将会对性能产生一些影响。

    binlog-do-db和binlog-ignore-db：这两个参数在做MySQL数据库的主从同步配置时非常重要。binlog-do-db表示要同步那些数据库的二进制日志，而binlog-ignore-db表示忽略那些数据库的二进制日志。在主从同步时，主库的系统数据库就可以忽略掉，而只同步我们关心的数据库。

    log-slave-update：这个参数表示，在主从复制环境中，slave角色是否将master的二进制日志写入到自己的二进制日志文件中。如果写入就需要设置这个参数。如果设置master->slave-slave的同步架构，就必须设置这个参数。

    binlog_format：这个参数表示二进制日志的格式，这个参数的值有很多，这里建议大家使用ROW，这样能使二进制日志更加可靠。

    查看二进制文件的内容，需要通过MySQL自带的mysqlbinlog攻击查看。

5.PID文件

    这个文件记录着MySQL实例启动时，进程的ID，不需要我们进行配置，但是如果没有这个文件，MySQL实例启动时会报错。这是就需要手工创建这个文件。

6.表空间文件

    InnoDB采用存储的数据按表空间（tablespace）进行存储。默认情况下，在数据目录下有一个名为ibdata1的文件，大小是10M。这就是默认的表空间文件。可以通过配置参数innodb_data_file_path对其进行设置。用户可以通过多个文件组成一个表空间：

    innodb_data_file_path=/dr1/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里将ibdata1和ibdata2两个文件组成了一个表空间。并将这两个文件放到了不同的磁盘上，这样可以使磁盘的负载平均，有利于提升数据库的整体性能。

    设置了innodb_data_file_path参数后，所有基于innoDB存储引擎的表都会存储到一个共享表空间中，在实际生产环境中，不建议这么操作，而是根据情况通过参数innodb_file_per_table参数，为一些大表建立独立的表空间。并将它们放置在不同的磁盘上。独立表空间的命名规则是：表名.ibd。

7.InnoDB重做日志

    重做日志也是一个非常重要的日志文件，它记录了对InnoDB存储引擎的事务日志。当数据库宕机时，比如突然掉电，InnoDB可以通过重做日志恢复数据。默认情况下在InnoDB存储引擎的数据目录下有两个名为ib_logfile0和ib_logfile1的文件。这就是MySQL数据库的InnoDB存储引擎重做日志文件。每个InnoDB存储引擎至少有一个重做日志组，每个文件组下至少有2个重做日志文件。通常为了提高数据库的可靠性，可以设置多个镜像日志组，将不同的文件组放在不同的磁盘上。重做日志的写入是按照循环方式吸入的，即0文件写满了写1文件，1文件写完满了在覆盖0文件继续写。通过以下参数可以对重做日志的记录和行为产生影响：

    innodb_log_files_in_group：该参数设置重做日志组中日志的个数，默认是2。

    innodb_mirrored_log_groups：指定了日志镜像文件组的数量，默认是1。可以将这个值设置成2，并将镜像文件组设置到不同的磁盘上。

    innodb_log_group_home_dir：该参数指定日志文件组坐在路径，默认为./，表示在MySQL数据库的数据目录下。

    innodb_flush_log_at_trx_commit：该参数知道重做日志的提交方式，强烈建议把该值设置为1，即每当有事务提交时，就必须确保事务都已写入重做日志。这样才能有效的保证重做日志的可靠性。

总结

    这些就是MySQL数据库中一些基本的文件，了解这些文件的作用可以帮助我们更好的使用MySQL数据库，奖励大家在完成数据库实例之后，在数据库投入使用之前，一定要检查一下这些文件是否已经开启或者相关参数是否设置好。因为这回对以后的维护带来莫大好处。