安裝好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;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| 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 |

| 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的基礎知識掌握不牢固。還是要努力學習~

【MySQL】一次修改mysql 預設路徑的經歷

相關文章