The MySQL server keeps its own logs for disaster recovery, tuning, replication, etc. InnoDB, does however maintain a set of logs for housekeeping and auto-recovery. These logs are important, and any operation performed on the server that requires the back up of logs should include these as well.

It is recommended that three logs be used in a rotating fashion. MySQL provides the following parameters for configuring InnoDB logging:

• innodb_log_arch_dir
  
  • Trivial.
  • Deprecated as of 4.0.6.
    Since MySQL keeps its own logs, it is not necessary to write archive logs.

• innodb_log_archive
  
  • Trivial.
  • This option should be set to 0.
    There is currently no need to archive InnoDB logs because the MySQL server keeps its own for the purpose.

• innodb_log_buffer_size
  
  • Size of the memory buffer used to store transaction information.
  • Measured in megabytes.
    Larger values imply a need for fewer disk writes when large transactions are executed. Since this parameter is on a per thread basis it should be kept reasonably small (less than 4MB). Particular situations may call for larger buffers such as the use of text and blob columns.

• innodb_log_file_size
  
  • The size of each log file on disk.;
  • Measured in megabytes or gigabytes.
    The size of the log files involves performance trade offs: A large file means less disk I/O, however the recovery time is much greater in the event of a crash. Given the stability of InnoDB when properly configured, assume crashes are rare and use the formula:
    

    

• innodb_log_files_in_group
  
  • Number of files in the log group.
  • Measured in number of files. 3 implies three files.
    As each file becomes full, InnoDB moves to the next. When the last file is full, it overwrites the first. Three files are recommended.

• innodb_log_group_home_dir
  
  • Path to the InnoDB log files.
  • Specified as a string representing the absolute path to a file.
    In high performance environments, where the tablespace is given its own raw device or drive array, it makes sense to specify this directory to be on a different array or network device. This value must be identical to the innodb_log_arch_dir parameter.

• innodb_mirrored_log_groups
  
  • Trivial.
  • Specifies the number of copies of the InnoDB log files.
    Since MySQL keeps its own logs, it is not necessary to write archive logs. However a real time copy of the logs should be stored off the database host.