在介绍InnoDB的在线REDO日志前,先介绍下InnoDB,InnoDB是mysql下的一个存储引擎,MYSQL下可选的存储引擎还有MyISAM,MEMORY,EXAMPLE,NDB Cluster,ARCHIVE,CSV,BLACKHOLE,FEDERATED。最常用的存储引擎就是InnoDB和MyISAM,InnoDB的特点是事务安全,而MyISAM主要用于管理非事务表。它提供高速存储和检索,以及全文搜索能力。MyISAM在所有MySQL配置里被支持,它是默认的存储引擎,除非你配置MySQL默认使用另外一个引擎。其他存储引擎在此不详细介绍。
那么InnoDB如何来实现其事务安全的呢?
其实主要是通过在线REDO日志和记录在表空间的UNDO信息来保证的。RED日志记录了InnoDB所做的所有物理变更和事务信息,通过REDO日志和UNDO信息,InnoDB保证了任何情况下的事务安全性。
MYSQL的REDO日志功能默认是打开的,要不它就无法保证其事务安全性。但是默认的文件是保存在其数据目录下,可以到.....\MySQL\MySQL Server 5.0\data该目录下,会发现以下2个文件:
ib_logfile0 ib_logfile1
这2个日志文件都是保存REDO日志的。只是无法用普通的文本编辑工具查看。
那么如何更改该日志的保存位置呢,方法如下:
1. 停止MYSQL服务。
2. 修改......\MySQL\MySQL Server 5.0\my.ini文件,在文件中加入一行:
innodb_log_group_home_dir="F:/mysql_log/mysql5.0/redo-log/redo.log";
3. 启动mysql服务。
发现启动不了,启动报错。具体错误不太明确,去查询错误日志文件,发现了如下错误:
091205 20:22:36 InnoDB: Operating system error number 123 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html
InnoDB: File name "F:\mysql_log\mysql5.0\redo-log\redo.log"\ib_logfile0
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
大致意思明白,报了123编号的错误,然后给了个路径让用户去查找看看是什么错误,于是打开
http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html 页面,
终于在Windows System Error Codes 列表中到了编号123对应的错误描述
123 | ERROR_INVALID_NAME | The file name, directory name, or volume label syntax is incorrect. |
意思是文件名,目录名或卷标符号不正确,我仔细检查了下
innodb_log_group_home_dir="F:/mysql_log/mysql5.0/redo-log/redo.log";
发现这里多了分号,而以前的其他设置项都没有分号,肯定是这个错误,去掉分号,重新启动MSYQL。
却发现还是报错,继续查看错误日志发现,错误日志依旧,到底是什么原因呢?
我仔细看了下这句:
InnoDB: File name "F:\mysql_log\mysql5.0\redo-log\redo.log"\ib_logfile0
想起默认路径下的REDO日志的文件名都是自动生成的,所以决定去掉文件名尝试下,于是把my.ini中的配置修改成如下:
innodb_log_group_home_dir="F:/mysql_log/mysql5.0/redo-log/"
重新启动MYSQL,正常启动。
到F:/mysql_log/mysql5.0/redo-log目录下查看,果然生成了ib_logfile0 ib_logfile1 这两个文件,呵呵,高兴惨了。
真的发现通过错误日志定位问题还真快。
不过,为什么默认生成2个文件呢,可否生成多个文件呢,看看了书,发现有这么一个参数innodb_log_files_in_group可以配置的。
于是到my.ini下增加如下配置:
innodb_log_files_in_group=4
这里暂时设置成4个文件,然后重新启动MYSQL。
果然生成了如下4个文件
ib_logfile0 ib_logfile1 ib_logfile3 ib_logfile14
总结:mysql的日志功能学习了几天,终于了解完了,发现错误日志和慢查询日志目前来说对普通开发者来说比较有用,REDO日志对用户理解INNODB存储引擎的事务安全性处理比较有帮助,2进制日志则在大型系统性能优化,或海量数据处理有帮助,当然,如果是DBA,则这些日志都是要经常用到的。
最后附上MYSQL的操作系统错误代码编号,方便查阅,当然也可以直接到http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html 进行在线查阅。
-
The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.
Number Macro Description 1 EPERM
Operation not permitted 2 ENOENT
No such file or directory 3 ESRCH
No such process 4 EINTR
Interrupted system call 5 EIO
I/O error 6 ENXIO
No such device or address 7 E2BIG
Arg list too long 8 ENOEXEC
Exec format error 9 EBADF
Bad file number 10 ECHILD
No child processes 11 EAGAIN
Try again 12 ENOMEM
Out of memory 13 EACCES
Permission denied 14 EFAULT
Bad address 15 ENOTBLK
Block device required 16 EBUSY
Device or resource busy 17 EEXIST
File exists 18 EXDEV
Cross-device link 19 ENODEV
No such device 20 ENOTDIR
Not a directory 21 EISDIR
Is a directory 22 EINVAL
Invalid argument 23 ENFILE
File table overflow 24 EMFILE
Too many open files 25 ENOTTY
Inappropriate ioctl for device 26 ETXTBSY
Text file busy 27 EFBIG
File too large 28 ENOSPC
No space left on device 29 ESPIPE
Illegal seek 30 EROFS
Read-only file system 31 EMLINK
Too many links -
The following table provides a list of some common Windows system error codes. For a complete list, see the Microsoft Web site.
Number Macro Description 1 ERROR_INVALID_FUNCTION
Incorrect function. 2 ERROR_FILE_NOT_FOUND
The system cannot find the file specified. 3 ERROR_PATH_NOT_FOUND
The system cannot find the path specified. 4 ERROR_TOO_MANY_OPEN_FILES
The system cannot open the file. 5 ERROR_ACCESS_DENIED
Access is denied. 6 ERROR_INVALID_HANDLE
The handle is invalid. 7 ERROR_ARENA_TRASHED
The storage control blocks were destroyed. 8 ERROR_NOT_ENOUGH_MEMORY
Not enough storage is available to process this command. 9 ERROR_INVALID_BLOCK
The storage control block address is invalid. 10 ERROR_BAD_ENVIRONMENT
The environment is incorrect. 11 ERROR_BAD_FORMAT
An attempt was made to load a program with an incorrect format. 12 ERROR_INVALID_ACCESS
The access code is invalid. 13 ERROR_INVALID_DATA
The data is invalid. 14 ERROR_OUTOFMEMORY
Not enough storage is available to complete this operation. 15 ERROR_INVALID_DRIVE
The system cannot find the drive specified. 16 ERROR_CURRENT_DIRECTORY
The directory cannot be removed. 17 ERROR_NOT_SAME_DEVICE
The system cannot move the file to a different disk drive. 18 ERROR_NO_MORE_FILES
There are no more files. 19 ERROR_WRITE_PROTECT
The media is write protected. 20 ERROR_BAD_UNIT
The system cannot find the device specified. 21 ERROR_NOT_READY
The device is not ready. 22 ERROR_BAD_COMMAND
The device does not recognize the command. 23 ERROR_CRC
Data error (cyclic redundancy check). 24 ERROR_BAD_LENGTH
The program issued a command but the command length is incorrect. 25 ERROR_SEEK
The drive cannot locate a specific area or track on the disk. 26 ERROR_NOT_DOS_DISK
The specified disk or diskette cannot be accessed. 27 ERROR_SECTOR_NOT_FOUND
The drive cannot find the sector requested. 28 ERROR_OUT_OF_PAPER
The printer is out of paper. 29 ERROR_WRITE_FAULT
The system cannot write to the specified device. 30 ERROR_READ_FAULT
The system cannot read from the specified device. 31 ERROR_GEN_FAILURE
A device attached to the system is not functioning. 32 ERROR_SHARING_VIOLATION
The process cannot access the file because it is being used by another process. 33 ERROR_LOCK_VIOLATION
The process cannot access the file because another process has locked a portion of the file. 34 ERROR_WRONG_DISK
The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1. 36 ERROR_SHARING_BUFFER_EXCEEDED
Too many files opened for sharing. 38 ERROR_HANDLE_EOF
Reached the end of the file. 39 ERROR_HANDLE_DISK_FULL
The disk is full. 87 ERROR_INVALID_PARAMETER
The parameter is incorrect. 112 ERROR_DISK_FULL
The disk is full. 123 ERROR_INVALID_NAME
The file name, directory name, or volume label syntax is incorrect. 1450 ERROR_NO_SYSTEM_RESOURCES
Insufficient system resources exist to complete the requested service