安装好mysql rpm 包之后,将数据文件和日志文件的路径由/var/lib/mysql 迁移到 /opt/mysql/data /opt/mysql/log ,并调整了innodb_data_file_path 和innodb_log_file_size 的值,启动数据库的时候报错:
关于数据库文件的报错!
120709 19:12:32 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 19:12:32 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: auto-extending data file /opt/mysql/data/ibdata1 is of a different size
InnoDB: 640 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 128000 pages, max 0 (relevant if non-zero) pages!
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
120709 19:12:33 [ERROR] Plugin 'InnoDB' init function returned error.
120709 19:12:33 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
ibdata1 is of a different size 是由于 innodb_data_file_path 的值(文件大小)设置错误引起~
如错误提示中所说: ibdata1 的值640个pages 配置文件中的值128000个pages 不同。所以问题的原因找到了,修改my.cnf 中innodb_data_file_path 中ibdata1 的值即可!
方法如下:
640/64=10M
innodb_data_file_path = ibdata1:10M:autoextend
root@AY120621100302d64e92e # du -sm ibdata1
11 ibdata1 使用系统命令查看实际值比配置文件中的值大1M~!
重新启动数据库 又报关于日志的错误
120709 20:47:22 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 20:47:22 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Error: log file /opt/mysql/data/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
120709 20:47:22 [ERROR] Plugin 'InnoDB' init function returned error.
120709 20:47:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
显然是日志文件的实际大小和my.cnf 的值不同!注意 error 提示当前的数据库是不支持innodb 存储引擎的!
登录数据库中查看:
创建innodb的表失败!
root@localhost : test 20:57:38> create table t2 engine=innodb as select 1,now();
Query OK, 1 row affected, 2 warnings (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
root@localhost : test 20:58:00> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1286 | Unknown table engine 'innodb' |
| Warning | 1266 | Using storage engine MyISAM for table 't2' |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)
数据库中并没有显示支持innodb!
root@localhost : (none) 20:59:30> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)
正常关闭server的情况下,修改my.cnf 参数之后没有删除ib_logfile文件,ib_logfile文件中记录些innodb引擎非常有用的信息比如说默认的innodb默认的配置信息,又是在未正常关闭server情况下操作的,所以导致重启后的server不支持innodb引擎。
(关于innodb参数设置不当也导致
系统不支持innodb存储引擎
)
解决步骤:
1 关闭mysql数据库 ,观察 错误日记的信息,确保正常关闭!
2 修改innodb_log_file_size = 512M (按照自己的实际情况)
3 使用mv 命令将ib_logfile0 ib_logfileN 做备份!
4 重新启动数据库,并观察 错误日记的信息!
5 如果启动成功,则删除之前备份的旧日志文件
root@AY120621100302d64e92e # service mysql stop
Shutting down MySQL.120709 21:02:18 mysqld_safe mysqld from pid file /opt/mysql/data/AY120621100302d64e92e.pid ended
[ OK ]
[1]+ Done /usr/bin/mysqld_safe
root@AY120621100302d64e92e # pwd
/opt/mysql/data
root@AY120621100302d64e92e # ls
ibdata1 ib_logfile0 ib_logfile1 mysql test
root@AY120621100302d64e92e # mv ib_logfile0 ib_logfile0.bak
root@AY120621100302d64e92e # mv ib_logfile1 ib_logfile1.bak
root@AY120621100302d64e92e # /usr/bin/mysqld_safe &
[1] 14317
You have new mail in /var/spool/mail/root
root@AY120621100302d64e92e # 120709 21:03:06 mysqld_safe Logging to '/opt/mysql/log/mysql-error.log'.
120709 21:03:06 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
进行测试 创建innodb的表成功!
root@AY120621100302d64e92e # mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost : (none) 21:04:38> use test;
Database changed
root@localhost : test 21:04:42>
root@localhost : test 21:04:42> create table t3 engine=innodb as select 1,now();
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
root@localhost : test 21:04:50>
root@localhost : test 21:04:52> show create table t3 \G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`1` int(1) NOT NULL DEFAULT '0',
`now()` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost : test 21:04:59> exit
Bye
附上最后修改后 错误日记的信息
120709 21:03:06 mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data
120709 21:03:06 [Note] Plugin 'FEDERATED' is disabled.
启动的过程中,mysqld会发现日志文件不存在,它会自己创建日志文件!
120709 21:03:06 InnoDB: Log file /opt/mysql/data/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data/ib_logfile0 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
120709 21:03:29 InnoDB: Log file /opt/mysql/data/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /opt/mysql/data/ib_logfile1 size to 512 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120709 21:03:54 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...
120709 21:03:54 InnoDB: Started; log sequence number 0 44556
120709 21:03:54 [Warning] 'user' entry 'root@localhost.localdomain' ignored in --skip-name-resolve mode.
120709 21:03:54 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
120709 21:03:54 [Note] Event Scheduler: Loaded 0 events
120709 21:03:54 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.40-community-log' socket: '/opt/mysql/data/mysql.sock' port: 3306 MySQL Community Server (GPL)
整个过程总结下来,还是对mysql的基础知识掌握不牢固。还是要努力学习~
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-734994/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22664653/viewspace-734994/