how to add and resize innodb log 如何增加innodb日志文件和修改大小

how to add and resize innodb log
如何增加innodb日志文件和修改其大小

关于"ib_logfile0 is of different size"错误的解决


重启mysql服务时,发现无法启动

# service mysqld restart
Stopping MySQL:                                            [  OK  ]
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

查看日志/var/log/mysqld.log

# less /var/log/mysqld.log
...
090721 02:25:47  mysqld started
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 52428800 bytes!
090721  2:25:47 [ERROR] Default storage engine (InnoDB) is not available
090721  2:25:47 [ERROR] Aborting

090721  2:25:47 [Note] /usr/libexec/mysqld: Shutdown complete

090721 02:25:47  mysqld ended

显示错误"log file ./ib_logfile0 is of different size 0"
报告日志文件大小有误

原来是因为修改过一次配置文件, /etc/my.cnf中有关日志文件的配置被配置管理工具Puppet自动改成了

innodb_log_file_size = 50M
innodb_log_files_in_group = 3

而默认的, innodb日志文件只有2个, 大小是5M

# ls -l /var/lib/mysql/
total 20528
-rw-rw---- 1 mysql mysql 10485760 Jul 21 02:25 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul 21 02:25 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 May 19 08:34 ib_logfile1
drwx------ 2 mysql mysql     4096 May 19 08:34 mysql
drwx------ 2 mysql mysql     4096 May 19 08:34 test

当mysql服务启动时自动检测innodb日志文件大小是否正确, 不正确则不能起来


然后修改配置文件, 大小改为默认的5M

innodb_log_file_size = 5M
innodb_log_files_in_group = 3

再重启还是起不来

# service mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

日志显示, mysql启动还要检查日志文件的数量

090721 04:14:58  mysqld started
090721  4:14:58  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
090721  4:14:58 [ERROR] Default storage engine (InnoDB) is not available
090721  4:14:58 [ERROR] Aborting

090721  4:14:58 [Note] /usr/libexec/mysqld: Shutdown complete

090721 04:14:58  mysqld ended

mysql发现第3个日志文件不存在, 但它不能自动创建

# ll
total 20528
-rw-rw---- 1 mysql mysql 10485760 Jul 21 02:25 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Jul 21 02:25 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 May 19 08:34 ib_logfile1
-rw-rw---- 1 mysql mysql        0 Jul 21 04:14 ib_logfile2
drwx------ 2 mysql mysql     4096 May 19 08:34 mysql
drwx------ 2 mysql mysql     4096 May 19 08:34 test

ib_logfile是innodb存储引擎的日志文件, 记录innodb数据的变化, 相当于oracle的在线日志文件
修改数据内容时, 首先修改数据缓冲区的数据块, 然后将这个变化记录到日志文件里, 最后定时(或触发)刷新数据缓冲区中的脏数据块到数据文件.

修改日志文件大小的正确方法见手册13.2.5. Adding, Removing, or Resizing InnoDB Data and Log Files

1. 设置innodb_fast_shutdown参数等于1
查看innodb_fast_shutdown

mysql> show global variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>

如果不等于1,那么改成1

mysql> set global innodb_fast_shutdown=1;
Query OK, 0 rows affected (0.00 sec)

mysql>

2.正常停止MySQL服务
service mysqld stop
或
mysqladmin shutdown
这步保证脏数据块都刷新到磁盘, 不再需要重做

3.修改my.cnf配置文件

innodb_log_file_size = 50M
innodb_log_files_in_group = 3

4.将原来的日志文件删除,改名或移到另外一个地方

# cd /var/lib/mysql
# mv ib_logfile0 ib_logfile0.bak
# mv ib_logfile1 ib_logfile1.bak
# mv ib_logfile2 ib_logfile2.bak

5.重启MySQL服务

# service mysqld start
Starting MySQL:                                            [  OK  ]

自动创建了3个日志文件

090722 02:48:05  mysqld started
090722  2:48:05  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 50 MB
InnoDB: Database physically writes the file full: wait...
090722  2:48:06  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 50 MB
InnoDB: Database physically writes the file full: wait...
090722  2:48:07  InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 50 MB
InnoDB: Database physically writes the file full: wait...
090722  2:48:09  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090722  2:48:09  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 44044.
InnoDB: Doing recovery: scanned up to log sequence number 0 44044
090722  2:48:09  InnoDB: Started; log sequence number 0 44044
090722  2:48:09 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

MySQL貌似工作正常




外部链接:
InnoDB logfiles
The Binary Log

High performance MySQL
By Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek Balling

Log size differences create possibility of corruption
InnoDB Performance Tuning




-fin-