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

北在南方發表於2016-04-13
MySql
 安裝好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的基礎知識掌握不牢固。還是要努力學習~


相關文章