LinuxサーバにMySQLを設定した

以下に投稿した機能を拡張するために、MySQLを家庭内ゲートウェイであるLinuxサーバに立ち上げて、RaspberryPI等、家庭内IoTシステムからremote accessして、データを共有できるようにした。

2017年6月12日月曜日

RaspberryPIを使って、コンセントを制御する。

 目次）MySQL化の理由は最後の背景で説明する。

1. MySQLのinstall
2. remoteアクセスの設定
1. 設定ファイルの変更
2. RasperryPiからのアクセス実験
3. rootではアクセスできない
3. Databaseの移動
1. RAID (mdadm)のpartitionへの移動 （堅牢化）
2.  Apparmor (CentOSでいう、SELinux） の無効化
4. おまけ）MySQL workbench on Mac OS-X
5. 背景
1. microSDカードの書き込み寿命
2. 対策
1. USB HDDの利用
2. MySQLなどremote databaseの利用

MySQLのinstall)
 Ubuntu 16.04 LTSなので、

　http://ksino.hatenablog.com/entry/2016/11/17/232619

2016-11-17 Ubuntu 16.04 LTSにMySQLをインストールする 

の通りに aptでinstallして、設定をすれば、まずはlocal machineで使えるようになった。上記にある、サンプルのtest_dbも、この通り動いた。

Remote Accessの設定）

1. LinuxサーバのUFW を設定して、標準の3306ポートを開いた。
   参考)
1. UFWの設定: http://qiita.com/yoh-nak/items/46935af3c5c4036e93b3
2. コンソールからGUFWのほうが簡単かも:
    http://sicklylife.at-ninja.jp/memo/ubuntu1404/gufw.html
2. ついでに /etc/sysctl.conf を書き換えてrebootしてIPv6も止めた。
   参考) http://l-w-i.net/t/ubuntu/ipv6_001.txt
3. remote accessの設定)
1. だが、telnet ipaddress 3306をやってもつながらない。
   
    /etc/mysql/mysql.conf.d/mysqld.cnf   の設定変更が必要
   bind-address           = 127.0.0.1
   でローカルマシンからしか接続できなくなっているので、これを、
   bind-address           = 0.0.0.0
   に書き換えてどこからでも接続できるようにする。
   参考）http://bit.ly/2uVx9Rl  How to allow mysql remote connections via particular interface
2. RasperryPIからのアクセス実験
1. 
   
   rasp01 ~ $ mysql -h 192.168.0.88 -u user -p
   Enter password: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 26
   Server version: 5.7.18-0ubuntu0.16.04.1 (Ubuntu)
   
   
   Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
2. :
   
   mysql> connect test_db;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   
   
   Connection id:    27
   Current database: test_db
   
   
   mysql> show tables;
   +-------------------+
   | Tables_in_test_db |
   +-------------------+
   | emp               |
   | lights            |
   +-------------------+
   2 rows in set (0.00 sec)
   
   
   mysql> select * from emp;
   +------+------+
   | id   | name |
   +------+------+
   |    1 | aaa  |
   |    2 | bbb  |
   +------+------+
   2 rows in set (0.00 sec)
   
   
   mysql> 
3. ところがRasperryPIからrootでMySQLに入れない。mysladmin でやってみるとアクセス権がないとのこと。
   
   rasp01 ~ $ mysqladmin -h Linux02 -u root -p version
   Enter password: 
   mysqladmin: connect to server at 'Linux02' failed
   error: 'Access denied for user 'root'@'192.168.0.122' (using password: YES)'
4. サンプルテーブルでの実験からも類推できるし、以下のようなやり方でできると書かれた webサイトもあったが。。。やり方がまずかったのか上手く行かない。mysql> grant all on *.* to 'root' identified by 'rootのパスワード';
5. どうもlocal machine上で 管理table mysqlの設定をいじればよいらしいがよくわからない。



Local MachineからMySQLにroot権限で入って見える特権データベース
（以前、SQLite3で、壊してひどいことになった。）






localhost以外で追加してみようとするが。。
SQLは初心者なので、、、よくわかりません。。

4. Databaseの移動）
1. Quitta http://bit.ly/2uXMLnp をみて、ファイルをmdadmのpartition (/homeからのsymblic linkへ移動）
2. が、/etc/init.d/mysql restart するとコケる。
   
   

   # journalctl -xe でみると、apparmor (CentOSでいう、SELinux)が拒絶している） 
3.   https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1610765 を参考に、Apparmorの設定 /etc/apparmor.d/usr.sbin.mysqld あたりを書き換えたが、なんどやっても上手く行かない。
4. CentOSでは、SELinuxは扱いにくいので、無効化（おすすめは、http://red.ht/2uZTaP1 にあるPermissive mode) なので、Ubuntuでも無効化することにした。
5. 　http://eset-support.canon-its.jp/faq/show/3223 をみて、
   Grubからdisableしてrebootして、問題になくデータベースは移動した。
   しかし、Databaseを移動すると何がどうなってダメなのか不明。
6. おまけ）rootになって # journalctl -xe をすると、Mac OS-XにインストールしたCanon printer drive (tools) のbonjure が、大量にbroadcastメッセージを出して、UFW (iptables) つまりlinuxサーバのfirewallが拒絶メッセージを出している。なんだか怪しい。。
5. おまけ）MySQL Workbench on MacbookPro OS-X El Capitan.
   Mac OS-Xからの確認用に、
   https://www.mysql.com/jp/products/workbench/ からダウンロードした。グラフィカルにMySQLサーバに触れる。
   

   

   MySQL Workbenchでサンプルdatabase/tableの操作

   
   
6. 背景）microSD cardの書き込み寿命

Makerfairを主催する米国の雑誌Makerとは比べ物にならないほど内容が詳細で充実した..日本の誇る雑誌 トラ技 2017/7月号 (CQ出版社の画面 http://bit.ly/2tO4iB3 を引用）にラズパイ特集があった。実は、CQ出版社にはバックナンバーがほとんどあって、買い逃した本も上記リンクから行けるCQ出版のweb shopの通販で送料無料で数日で届く。カード支払いも可能。

CQ出版社webのトラ技2017/7月号情報

CQ出版社webにあるトラ技2017/7月号の目次情報

その第4章が、
「数Kバイトの小データを10年間記録し続ける高信頼メモリの技術
寿命対決! コンビニSDカード vs 産業用SDカード」であり、
ラズパイで使った時のmicroSDカードの寿命を徹底評価していた。 http://bit.ly/2tO4iB3 のリンクから各章の1ページ目がpdfで閲覧できる。

SDカード（Flash memory)の詳細な仕組みから始まり、8GBのmicro SDカードを装着したラズパイで、4KBのファイルを1秒間に100個記録し続ける（古いのは消す）データロガーとして使ったときのmicro SDカードの寿命を算定していた。TPSM-GS1Sという産業用のSLC(Single level Cell)カードだと10年持つが、通常売られているMLCタイプの安いmicro SDカードだと18日でダメになるとのこと。（ここを特集するのは、さすが、トラ技。）

そりゃそうだ、wear levelingも適当な安価SDカードだし。。
さすがに、1秒間に100個ずつファイルを記録することもなく、1分間に1個。同じファイルへの書き込みはbufferされていると考えると、もうちょっと持つのかもしれないが、小さいファイルを更新しまくるlinux/unixのストレージが市販microSDカードというのは、いかにも心配である。

記事にある書き換え寿命の仕様（保障）値を引用すると、
SLCで10万回、p-SLCで2万回、MLCで3000回。
実効最小ではSLCで21万回、p-SLCで11.8万回、MLCで1.2万回。

　amazon.com http://amzn.to/2tNVmvM では、p-SLCの16GBのmicroSDカードが10枚 $815で、SLC 4GBのmicroSDカードが10枚 $805で売られている。---- 高い！！！！

拙blog: http://bit.ly/2sccXwe に掲載した、RaspberryPIを使ったコンセント制御。cron でアプリが1分ごとに起動して、タイマー機能を実現する作りになっている（簡単なので..) 。
機能拡張するのに状態管理をファイルでやろうと思ったが、危ない。。常時起動しているdaemonにするか、状態管理daemonと通信させるか、ちょっとやり方を考えたい。と、考えた結果、MySQLサーバを立ち上げて、今後増えるであろう、いろいろな家庭内IoT機器の情報をRelational Databaseで共有して、検索・変更を容易にすることにした。

コンセント制御では、WebベースなのでPHPを使っており、PHPにはMySQLをアクセスするライブラリもある。

Learning PHP, MySQL & JavaScript: With jQuery, CSS & HTML5 (Learning Php, Mysql, Javascript, Css & Html5) 4th Edition （amazon.com http://amzn.to/2uPRBmF でベストセラーらしい。以下表紙写真）にも、PHPとMySQLの連携について書かれている。

参考になりそうなR'REILLY本

ビデオサーバプログラムのその後の更新

2015年1月16日金曜日　Linuxを用いたテレビ録画Serverでのロケフリ視聴が快適

で投稿したビデオサーバはその後も便利に使っている．
その後，いろいろカスタマイズしたので，列挙しておく．

カスタマイズ内容）
USからsshで入って，コンソールからphp, html, cgi等を書き換えている．バグで時々 htmlが表示されなくなったりするが，console logで分析状況を表示してデバッグしてから，最後に本当にdatabaseを書き換えるようにすれば，databaseを壊すこともなく，デバッグできている．米国から日本にアクセスする距離もあるがpingで測る限りは往復144ms程度なので，sshでtext baseでやる限りはあまり気にならない．（remote desktopのようにグラフィックベースで，echo backする状況で文章を書くと，遅くてイライラするが....）

以下リスト1,2,4,5への対応状況がわかるスクリーンショット(2015/12/31時点のもの）を以下に引用する．

3TBのHDDを使って，H.264のSD画質 (640x360)で保存している（画質は，普通の番組であれば27インチフル画面で十分満足できるレベル) なので，相当に大量に録画可能である．ほぼほぼ満杯になったときのために，自動消去する機能はまだ必要性を感じていない．

録画済一覧のスクリーンショット

1. mpeg2(1080i HDTV)からH.264(640x360)に変換する ffmpegでエラーが出る番組があるので，設定を解析し修正．主に以下への対応が必要だった．ffmpegで分析し，perlスクリプトで結果を解析し，ffmpegへの変換オプションを作成するよう自動化している．
1. 解説音声，副音声のあるもののストリーム番号への対応
2. 2014/4月の朝ドラ新編成で一旦TS(mpeg2)の録画ストリーム番号が変わっていたので，自動検出するように変更
3. 常に変換ログをvideo置き場に残している
4. 録画スクリプトは簡単に変更できて，オリジナルのTSファイルを残すこともて設定で変更可能．常に残すとdisk spaceを圧迫する
2. サムネイル右クリックメニューで，mp4をダウンロードできる．これを追加して，
1. TSが残っているときに，番組サムネイルを右クリックすればTS（HD画質）をダウンロードできるようにした．
2. 番組説明ではmp4ファイルを流す．Google Chromeでは，自動的に動画viewerが立ち上がるが，IEやSafariではダメ．右クリックしてダウンロードしてからquick time playerとかで再生するしかない．
3. 本来は，AFCファイルを流す．これは番組説明のクリックとして残した．が，どのブラウザもAFCは表示できない．画像ビューアVLCでは表示可能なので，ブラウザ設定でAFC型ファイルとVLCを関連付ければ可能かもしれない．
4. ダウンロードしたmp4ファイルは，拡張子を.m4vに変えるとiTunesで再生可能だし，そのままiPhoneに転送して再生可能．
3. git repositoryを作り，commitする．
1. 適宜，repository等はcroneでNASにバックアップする．
2. .htaccessに不備があり，ログイン許可を書いたファイルが外部アクセスされ変更されていた．git statusで発覚．firewallのログから，アクセスが合ったのはアジアの近隣国．ログイン許可ファイルを修正したが，git statusから再度別方法でファイルが変更されていた．ここの穴も塞ぐと同時に，webからアクセス可能なipアドレスを変更．以降，不正アクセスは発生していない．
   BBrouterに穴を開けたり，外部アクセス可能なサーバを設置するときには，不正アクセス対策に加えて，容易に不正アクセスを検出できる手段が必要だと痛感した．
4. 録画済み一覧に，録画数，利用disk space，平均録画サイズ，可能録画本数の予測を表示するようにした．これは，recordedTable.phpを修正し，system()コマンドで，ls -al からawkをpipeで流すようにして値を求め，対応するtemplates/recordedTable.htmlを修正して表示している．
5. 録画済みの各エントリに，ファイルサイズをMBで表示する．
6. 一部を公開できるようにした．
1. 公開用のdirectoryを作成し，そこにパスワード無しでアクセスできるindel.htmlを作成
2. 上記directory配下のビデオ置き場に録画済みファイル(.mp4)をln すると，自動的に.mp4をlsするようperlのcgiスクリプトを作成し，それをデフォルトのhomeとして公開
3. 公開・非公開はlnコマンドとrmコマンドでハードリンクを変更するだけだが，録画済みファイルが増えてくると面倒なので，録画済み一覧に列を追加して，公開・非公開のボタンを設置．表示時にphpで公開用directoryにファイルがあるかないかで，表示を切り替えている
1. 今後，ボタンを押すことで公開・非公開を切り替えられるようにしたい．
7. 時々，録画が終わっても録画予約にあるエントリが残ることがある．これは，録画予約データベース(RESERVE_TBL)のcomplete フィールドが0のまま残るため．この状態だと，録画済み番組ファイルの削除もできない．
1. そこで，録画予約表示で使うreservationTable.php を改造し，録画予約状況表示時に録画終了時刻よりも現在時間のほうが遅いときには，強制的にcompleteフラグを立てるように変更(Smartyを使っているので，対応するPHPの配列を更新するだけであとは自動的に書き戻される）．ただし，厳密には以下の様なやり方．
1. 本来completeフラグを操作するrecorder.phpとの競合回避のため，終了時刻+600秒（10分）と現在時刻を比較している．
2. 強制的にcompleteフラグをセットしたときには，video/reservationTable.logにフラグをセットした旨を追記する．
1. PHPでのログの吐き出し方: http://www.php-labo.net/tutorial/php/file.html#write
3. completeの未設定が検出されたときにも，録画予約表示には該当するエントリも録画予約として表示している．実際には，この時点でMySQLのデータベースが書き換わるので，次回からは表示されない．
8. 番組表で，現時刻を示す，赤い横のラインが表示されるが，これがローカル時間になる．海外から番組表をアクセスすると本来あるべき日本時間ではなく，ブラウザの時間になる．これは，ブラウザのJavascriptで表示時に時間を取得し，現在時刻表示しているため．そこで，
1. templates/index.htmlで記述しており，現時刻ラインのIDはtableNowBas
2. templates/index.htmlに記載されているJavascriptを改造し，UTCのtimestamp( UNIXの秒表示(1971年よりの秒数)をミリ秒で表したもの）を取得，日本時間へ補正し，Date()で，日本時間のnow値を得ている．
3. デバッグは，Google Chromeのコンソールとコンソール出力を利用．
   ストリング表示すると，timezoneとしては，ブラウザのある地域のタイムゾーンのまま日本時間になるので気持ち悪いが，javascriptではタイムゾーンは見ていないので問題なく動く．
1. Date()の使い方:
   https://developer.mozilla.org/ja/docs/Web/JavaScript/Reference/Global_Objects/Date/prototype#Methods
2. Google Chromeのコンソールの使い方:
   https://developer.chrome.com/devtools/docs/console
3. console.log('JST now: %s', now.toString());　などでコンソール表示してデバッグしたが，Chromeコンソール専用の出力コマンドもあるらしい..
   See: http://blog.asial.co.jp/1152

今後やりたいカスタマイズ）

1. 録画予約の高度化
1. 簡易予約で，開始，終了時間を文字入力ではなく，iOSのような回転文字盤で変更できるようにする．これは，キーボードが日本語モードになっていると変換等でうっとおしいのと，数分ずらすつもりで入れ間違えるとおかしくなるため．
2. 長時間番組を分割する．例えば1時間ずつセクションにわける．理由は，
1. mpeg2録画終了後に，バッチでSDに変換をかけている．変換が終了したことは，録画済み番組表でサムネイルが表示できたこととため，長時間番組が見られるようになるまでに時間がかかる
2. 長時間番組ではスクロールバーでの位置合わせが微妙なうえに，実際にファイルが転送されるまで待つので画像表示開始までに時間がかかる．
3. 実時間に近い再生をしたい
1. TSファイルに書き出してから，ffmpegをかけるのではなく，pipeで繋いで実時間transcodeしたい．
2. さらには，TSファイルやmp4への書き込みが終了するまえに，視聴を開始したい．（実時間表示モード or タイムシフト表示モード）
4. 消去機能
1. チェックしておいてまとめて消去する機能
2. ある時点より古いファイルをまとめて消去する機能
3. 絞込検索で表示されたファイルに対して，1,2が適用できるようにしたい．
4. ディスクスペースが減ってきた時に古いファイルから自動消去する機能