Most of the DBAs like me, haven't cared about the them..! But, they are the Unsung heroes of MySQL..!!
Yes, UNDO and REDO logs are the 2 different kind of logs; each with it's own purpose and you shouldn't get confused between them ..!!
As the name indicates, REDO logs are for Re-doing things..! The transactions which were already executed, but not committed to disk due to some DB crash/power off..! So, the REDO log records every transaction, holds until it gets committed and if needed, it will be used for crash recovery.
By default, there will be 2 REDO logs, named, ib_logfile0 and iblogfile1. As of version 5.6 or later, we can have up to 100 REDO logs using the System Variable innodb_log_files_in_group . Since, these logs are overwritten as soon as all the files are full, it is advised to have more files or files with larger size. By default, each log file will be 5 MB and the size can be set using the mysql variable innodb_log_file_size.
The maximum allowed combined size of all the files is 512 GB (innodb_log_file_size * innodb_log_files_in_group <= 512 GB).
The REDO log files location can be set using the variable innodb_log_group_home_dir , if not specified, default directory will be used.
Let's talk about UNDO logs..:
As the name indicates, REDO logs are for Re-doing things..! The transactions which were already executed, but not committed to disk due to some DB crash/power off..! So, the REDO log records every transaction, holds until it gets committed and if needed, it will be used for crash recovery.
By default, there will be 2 REDO logs, named, ib_logfile0 and iblogfile1. As of version 5.6 or later, we can have up to 100 REDO logs using the System Variable innodb_log_files_in_group . Since, these logs are overwritten as soon as all the files are full, it is advised to have more files or files with larger size. By default, each log file will be 5 MB and the size can be set using the mysql variable innodb_log_file_size.
The maximum allowed combined size of all the files is 512 GB (innodb_log_file_size * innodb_log_files_in_group <= 512 GB).
The REDO log files location can be set using the variable innodb_log_group_home_dir , if not specified, default directory will be used.
Let's talk about UNDO logs..:
This log stores copy of data that is being modified by any current transaction. So that, at the same time if any other transaction queries for the original data, this log will serve the purpose..!!These logs are also called Rollback Segments.
The path of the UNDO log table space can be set by the variable innodb_undo_directory.
In the earlier versions, these logs were invisible..!! As, UNDO logs were the part of System table space, i.e., ibdata1 and ibdata2. But, as of 5.6 and later, you can create multiple and separate UNDO log files(table space files).
Use variable innodb_undo_tablespaces to set the number of table space files. Max value is 126 and minimum 0. The Undo logs set by the variable innodb_undo_logs will be divided between the set table spaces. Maximum you can create 128 undo logs and it is the default value.
