MySQL InnoDB Enabled pada OpenWrt Backfire 10.03.1

Bagi sebagian orang, mungkin ada yang membutuhkan storage engine InnoDB pada MySQL server-nya, namun secara default InnoDB pada OpenWrt tidak diaktifkan (enabled). Setelah browsing akhirya cuma nemu satu refrensi, yang pada akhirnya meng-enable-kannya harus dengan cara meng-compile MySQL server-nya.

Bagi yang membutuhkan firmware mod OpenWrt TP-LINK TL-WR741ND ver 1.x, bisa diambil di:

https://drive.google.com/folderview?id=0B1Aj8bC-AJMKb0xtVjFuMkpmR0k&usp=sharing

Compile

Cara meng-compile-nya bisa lihat pada tulisan saya sebelumnya di sini. Tapi singkatnya bisa dengan cara dibawah ini:

svn co svn://svn.openwrt.org/openwrt/branches/backfire
cd backfire
./scripts/feeds update -a
./scripts/feeds install -a
make defconfig
make prereq
make menuconfig

Contoh pada compile ini saya menggunakan wireless-router TL-WR741ND v1.x

Target System (Atheros AR71XX/AR7240/AR913x)
Target Profile (TP-LINK TL-WR741ND v1)
Utilities --> database --> <M> mysql-server
(sebagai module)
Exit & Save
make

mysql-inno

Setelah download dan compile, tahap selanjutnya:

Edit file Makefile

nano feeds/packages/libs/mysql/Makefile

Tambahkan “–with-innodb \” tanpa tanda petik setelah “–with-server \
Save & Exit

mysql-inno1

Edit file my.cnf

nano feeds/packages/libs/mysql/conf/my.cnf

Tambahkan tiga baris script setelah “bind address”

innodb
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2

Save & Exit

mysql-inno2

Lalu compile mysql-nya

make package/feeds/packages/mysql/compile

Setelah download dan compile, maka jadilah 2 file yang bisa diambil pada direktori /backfire/bin/ar71xx/packages:

mysql-server_5.1.53-6_ar71xx.ipk
libmysqlclient_5.1.53-6_ar71xx.ipk

Install

Copy-kan 2 file tersebut ke router, bisa menggunakan perintah scp (linux) atau winscp (windows) ke directory /tmp, caranya bisa dilihat di sini bagian “scp”.
Kemudian install (pada proses instalasi, akan men-download dan meng-install beberapa file dependencies):

opkg update
opkg install /tmp/libmysqlclient_5.1.53-6_ar71xx.ipk
opkg install /tmp/mysql-server_5.1.53-6_ar71xx.ipk

Edit file /etc/my.cnf, pada “datadir” dan “tmpdir” sesuaikan dengan kebutuhan

———potong
############ Don’t put this on the NAND #############

    1. Figure out where you are going to put the databases
    2. And run mysql_install_db –force

datadir         = /mnt/sda3/sql/

######### This should also not go on the NAND #######
tmpdir          = /tmp/

skip-external-locking
bind-address            = 127.0.0.1
innodb
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
———–potong

Save & Exit

Jalankan berikut ini:

mkdir -p /srv/mysql  –> sesuaikan dengan kebutuhan
mysql_install_db --force

/etc/init.d/mysqld start
/etc/init.d/mysqld enable
mysqladmin -u root password 'new-password'

Jalankan perintah mysql untuk mengecek InnoDB sudah enable:

root@g-wrt:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.53 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> select version();
+———–+
| version() |
+———–+
| 5.1.53    |
+———–+

1 row in set (0.10 sec)

mysql> show variables like ‘have_innodb’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| have_innodb   | YES   |
+—————+——-+
1 row in set (0.00 sec)
mysql> show engines;
+————+———+————————————————————+————–+——+————+ | Engine     | Support | Comment | Transactions | XA   | Savepoints | +————+———+————————————————————+————–+——+————+ | CSV        | YES     | CSV storage engine | NO           | NO   | NO         | | MRG_MYISAM | YES     | Collection of identical MyISAM tables | NO           | NO   | NO         | | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         | | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        | | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         | +————+———+————————————————————+————–+——+————+ 5 rows in set (0.00 sec)

Referensi:
https://forum.openwrt.org/viewtopic.php?id=36212
http://wiki.openwrt.org/doc/howto/lamp?s%5B%5D=mysql
http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
https://gatoti14.wordpress.com/2012/06/22/buildroot-openwrt-backfire-10-03-1-pada-tl-wr741nd-ver-1-6/
https://gatoti14.wordpress.com/2012/06/14/mem-flash-openwrt-pada-tl-wr741nd-ver-1-6/