Openwrt上mysql innodb的使用及相關異常情況，openwrtinnodb

Openwrt上mysql innodb的使用及相關異常情況，openwrtinnodb

        首先在menuconfig中配置mysql，具體是在Utilities->database->mysql-server。這樣直接編譯就可以了，不過這樣預設是不支援innodb儲存引擎的，估計是考慮openwrt一般是啟動並執行硬體有關，不需要這麼複雜的儲存引擎（佔用空間也比MyISAM儲存引擎大）。但是，如果需要支援innodb儲存引擎該怎麼辦？這類型的網上文章不多，我是之前參照國外的論壇加自己測試得出的。

Openwrt支援innodb儲存引擎
修改feeds/oldpackages/libs/mysql/Makefile
在--with-server之前加--with-innodb

修改feeds/oldpackages/libs/mysql/conf/my.cnf
在[mysqld]中加default-storage-engine=INNODB
在bind-address = 127.0.0.1下載添加
innodb
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2

/etc/init.d/mysqld指令碼start函數中添加

start() {local datadir=$(sed -n -e "s/^[[:space:]]*datadir[[:space:]]*=[[:space:]]*[\"']\?\([^\"']*\)[\"']\?/\1/p" /etc/my.cnf)local mysqldatadir="/mnt/data/mysql/"        local mysqltmpdir="/mnt/data/tmp/"local mysqlsrvdir="/srv/mysql/"        if [ ! -d "$mysqldatadir" ]; then                echo "create dir /mnt/data/mysql/..."                mkdir -p "$mysqldatadir"        fi        if [ ! -d "$mysqltmpdir" ]; then            echo "create dir /mnt/data/tmp/..."mkdir -p "$mysqltmpdir"fiif [ ! -d "$mysqlsrvdir" ]; then            echo "create dir /srv/mysql/..."mkdir -p "$mysqlsrvdir"fiif [ ! -d "$datadir" ]; thenerror "Error: datadir '$datadir' in /etc/my.cnf doesn't exist"return 1fiif [ ! -f "$datadir/mysql/tables_priv.MYD" ]; thenmysql_install_db --forceservice_stop /usr/bin/mysqld#error "Error: I didn't detect a privileges table, you might need to run mysql_install_db --force to initialize the system tables"#return 1fiservice_start /usr/bin/mysqld}

        至此，基於innodb的MySQL就可以正常運行了。
        在基於netgear3700v2硬體的openwrt上，會出現初始化innodb的情況，異常表現為建立socket失敗，提示如Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'。其實這個是因為初始化innodb失敗造成的，因為netgear3700v2的flash只有16M，不能滿足innodb的運行需求，資料檔案大小總和至少要達到 10 MB。通過敲入mysqld --skip-grant&命令可能會列印出類似如下的資訊：
root@BDCOM_AP:/# mysqld --skip-grant&
root@BDCOM_AP:/# 150203  1:32:31  InnoDB: Initializing buffer pool, size = 8.0M
150203  1:32:31  InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 64 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 640 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!
150203  1:32:31 [ERROR] Plugin 'InnoDB' init function returned error.
150203  1:32:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150203  1:32:31 [ERROR] Unknown/unsupported table type: INNODB
150203  1:32:31 [ERROR] Aborting

150203  1:32:31 [Note] mysqld: Shutdown complete

        上述這個莫名其妙的問題經過驗證確實是由於flash空間不夠引起的。解決的辦法是，在路由器上掛載一個隨身碟。掛載隨身碟的配置項為：
kernel modules ->filesystems->kmod-fs-vfat
kernel modules->native language support->kmod-nls-cp437/kmod-nls-iso8859-1/kmod-nls-utf-8
kernel modules->usb support->kmod-usb-core/kmod-usb-ohci/kmod-usb-storage/kmod-usb-usb2
。
     還有，在init初始化指令碼中如果順序執行多個mysql操作的時候，如mysql_install_db --force命令之後接著mysqladmin -u root password '123456'則可能會失敗，因為還沒等mysql_install_db執行完成就會執行mysqladmin命令。解決此問題的辦法是加sleep時間，具體多長時間可以視情況而定。