モチベーション

MySQL with InnoDB の一貫性が、なぜ・どのように実現されているのかを、できるだけ簡素にまとめたかったので、忘備録を兼ねて、作成。

本文

1. MySQL with InnoDB (以下、単純に MySQL) は、BTree 構造でデータをディスクとメモリにおいて保持する RDB である。
2. MySQL は、 Buffer Pool に対して読み書きを行うことで、操作を高速化する。
   1. Buffer Pool は、メモリ上に展開されたディスクデータの作業場である。
   2. Buffer Pool は、 Page 単位でディスクに対するキャッシュのような振る舞いをする。
3. MySQL における Consistency は、 select 文については MVCC、更新については共有・排他ロックで実現される。
   1. MVCC とは、今現在の Buffer Pool の値ではなく、直近ある時点における値を読みめるようにするための制御のこと。
   2. 更新における共有・排他ロックは、更新の際にデータの走査に利用したインデックスの range と、発見されたレコードそれぞれに対して実施される。
   3. 更新文の中で、実際にデータを書き換えるために捜査した場合は排他ロックがかかり、ただ値を取得するだけの場合には共有ロックがかかる。
      1. ただ値を取得するだけ、とは、例えばサブクエリが該当する。
4. Buffer Pool への更新は、 redo log によって永続化される。
   1. redo log は、 Buffer Pool への更新内容を記録した、 round robin なリングバッファとして機能するファイルである。
      1. これにより、 redo log を再度適用することで、更新内容を復元できる。
   2. Buffer Pool への更新は、まず Innodb Log Buffer (メモリ領域)に溜められ、ある一定程度たまると flush されてディスクに書き込まれる。
      1. 更新 transaction commit の際にも flush は行われる。
   3. redo log 上の通し番号を LSN (Log Sequence Number) という。
   4. トランザクション中の更新文は、 commit の前であっても redo log を作成する。
      1. roll back は、その rollback による変更が redo log に記述される。
5. Buffer Pool の内容自体をディスクへ同期することを checkpoint を作成する、という。
6. MySQL は、 Buffer Pool のページに対して、そのページを最後に更新した際の LSN が、小さいものから順に checkpoint を作成していく。
   1. 永続化後は該当 redo log は不要になり、その領域が再度使える。
      1. redo log がリングバッファなのはこれが理由
7. ロールバックや MVCC での過去データは Undo ログによって実現される。
   1. Undo ログ一覧を History List と呼ぶ
   2. Undo ログは Buffer Pool として作成される。
      1. redo log も付与される。
8. いらなくなった Undo ログを削除することをパージという。
9. レコード削除は、最初は論理削除で、パージのタイミングで物理削除される。

最後に

実は、 fuzzy checkpointing がどうして consistent になるのか、はここでは述べられていないし、自分もいまいち理解できていないです。

具体的には、最小未反映 LSN のとある対象ページが、最大 LSN な変更の対象となっていた場合には、そのページを永続化するためにはその最小 LSN ~ 最大 LSN までの変更をすべてディスクに書き出し直さなければならないはずだが、しかしそういう自体が発生した場合の checkpointing がどのように行われていくかが、いまいち自分もわかっていないです。（そして文献もあまり見つからない。。）

誰か詳しい人などいれば、指摘していただけると幸いです m(_ _)m

MYSQLをlinuxに構築

勤務先でSQLを使用する機械が多くあり、
勉強の為にも自宅環境にてSQLを使用したいという思いから、
自宅のPC(VMWAERにてlinux環境あり)にてMYSQLを構築することにした。

無料で使用できるものは現状２種類あり、MYSQLとPOSTALSQLがオープンソースのソフトとして無償ダウンロードできる。
仕様の違いやSQLの方言の違い等差異があるが、職場で使用している環境の物や自分の学びたい方を選ぶと良いかもしれない。

MYSQLのyumインストール

MYSQLのホームページからリポジトリを追加する

$ sudo rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

https://dev.mysql.com/downloads/repo/yum/

なお筆者の環境はこれまでVMWAREでlinuxを構築しただけだったので
これを機にteratermをインストールし、コピーペーストや日本語化が簡単に出来るよう環境を整備し直した。

リポジトリが追加できたら次にyumインストールを行う。

$ sudo yum install mysql-community-server

インストールが無事に終えたら次はMYSQLの起動を行う

$ sudo systemctl start mysqld.service

ここで自分はうまく起動することができなかったのだが、
実は最初にパスワード変更をする必要があり、
パスワードにもルールがあって多少ややこしい。

パスワードが最低 1 つの数値文字を含み、1 つの小文字および大文字を含み、1 つの特殊文字 (英数字以外) を含む必要があるという条件があるのだ、

忘れてしまいそうなのでパスワードをrootにし、validate_password_length = 4 に、validate_password_policy = LOW にする。

これにて無事起動する事ができた。

次に簡単なデータベースを作成

テーブルの作成も行う

今月はSQLについてインストールと設定に時間を取られてしまったが、
これから会社で使用しているような物を実践してみたり、将来的にはPHP等を使用して掲示板等を作成してみたい。

Raspberry Pi Advent Calendar 2019 6日目の記事です

概要

Raspberry Pi 3を自宅のWebサーバにしてWebアプリをデプロイ、
自宅のネットワーク外からでもアクセスできるようにすることが目標です。

Raspberry Pi 3のOSにはRaspbianを選択。
centOSがなぜか起動できなかったんでこっちでやります。

手順

microSDのフォーマット

ラズパイ用のmicroSDをFAT32でフォーマットします。
因みにフォーマットする側のホストOSはLinuxMint18.3'Sylvia'
利用するソフトはgpartedです。

まずは、apt install gpartedでgpartedをインストールした後、起動します。(スーパーユーザ権限で)
すると、GUIでポチポチやるだけでmicroSDをフォーマットできます。
以下はフォーマット後の画面です。

説明の必要がないくらい簡単なので、特に作業の委細は述べません。

microSDにOSインストール

インストール、とはいっても、公式サイトからダウンロードしたNOOBSの中身ををmicroSDにぶち込んで起動するだけです。

公式が遅い時はミラーからどうぞ。

参考

https://qiita.com/tukiyo3/items/c8555eacb67466b81bc8

LAN外からssh接続できるように設定

OSのインストールが完了したていで話を続けます。

ここからはラズパイ内で設定をいじっていくことになるわけですが、毎回ラズパイ本体にキーボードとマウス、ディスプレイを繋いで作業をするのは面倒なので、sshで接続できるようにします。

まず、sudo raspi-configでラズパイのssh接続設定を有効にします。

GUIで操作できますので「5. Interfacing Options」→「P2 SSH」と選択し、enabledしてください。

変更はreboot後に適用されます。

次に、ラズパイのローカルipを固定します。固定のファイルは/etc/dhcpcd.confになります。ネット上では　/etc/network/interfacesだという記事が多いのですが、どうやらRaspbian Jessieから変更があったらしい。

/etc/dhcpcd.conf

interface [インターフェース名]
static ip_address=[固定したいローカルip]
static routers=[デフォルトゲートウェイのip]
static domain_name_servers=[DNSサーバのip]

と記述すればipが固定されます。
これでひとまずLAN内からはssh接続ができます。

ssh pi@[固定したip] -p 22

とコマンドを打って接続を確認。

公開鍵認証方式の設定

ここまでで、パスワード認証方式でのssh接続はできました。しかし、のちのちLAN外から接続するとなると、セキュリティの面で公開鍵認証方式を利用するのが好ましいです。

自分はこの記事の「公開鍵認証方式の設定」を参考にしました。

ルータのポートフォワーディング設定

LAN外から届いたssh接続をLAN内のラズパイ宛に届けてやるための設定をします。

ご家庭で使用中のルータの設定画面を開いてみてください。

右下の詳細設定を選択します。

「セキュリティー」→「ポート変換」を選択し、ssh(22),http(80),https(443)番ポートへのアクセスを全て先ほどの固定したipが受け取るように設定します。

これで外部からアクセスできるはずです。しかしこのままでは、グローバルipが変わってしまった際の対処が面倒。なのでDDNSを取得して解決します。

DDNSを取得

MyDNSでドメインを取得します。

サイトで登録後、IDとパスワードがメールで来るのでそれをもとにログインします。

ログイン後、「DOMAIN INFO」を選択し、取得したいドメインを記載します。
ドメインが他と干渉しなければOKとでるはずなので、取得は完了。

ただ、ドメインを取得しても、ドメイン名を関連づけたい回線から定期的にIPアドレスを通知しないといけません。
実際、１週間以上通知がないとドメインが使えなくなってしまうみたいです。

通知の方法はいくつかあるのですが、ここではラズパイ側から毎時間httpのbasic認証でアクセスをすることにしました。

/etc/cron.hourly/mydns

#!/bin/sh

wget -O - 'http://ID:PASS@www.mydns.jp/login.html' > /dev/null

こんなシェルスクリプトをcronに書いておく。

そしてcrontab -u pi -eで設定ファイルに書き込みます。

Select an editor.  To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.tiny
  3. /bin/ed

Choose 1-3 [1]: 2

easiestって言ってくれるあたり優しさを感じます。

書き込む内容は以下

0 * * * * /etc/cron.hourly/mydns >/dev/null 2>&1

crontab -lで現在の設定が確認できます。

また、cronの動作の確認は公式サイトのLOG INFOでできるはずです。
ただし、サーバの負荷の関係で同じアクセスに関しては24時間に一度しか表示されないので注意。

参考

https://qiita.com/mizuki_takahashi/items/89699f87fb10d812748a
http://dotnsf.blog.jp/archives/1034948871.html

デプロイ

なんだかんだここが一番だるい。

Raspbianにapache2が標準でついてたのでもうこれでいいや。

/etc/apache2/apache2.conf

<Directory "/var/www/[laravelapp]/public">
        Options Indexes FollowSymLinks
        AllowOverride None
        Require all granted
</Directory>

/etc/apache2/sites-available/000-default.conf

DocumentRoot "/var/www/[laravelapp]/public"

と記述。

矛盾がないように/var/www/下でgit clone [リモートリポジトリのURL]をします。

あとは、php,mysql,composer等入れて各種設定を済ますだけなのだが、ここが一番大変だった。いろいろごちゃごちゃやったので記憶がないです。

phpの拡張機能、特にPDOとか、mbstringあたりのエラーが出まくって大変だった。

php.iniのコメントアウト外し、apache2の再起動とかを忘れると無駄に時間を食います。

そしてやっとこさトップページが出たのでかんせ〜いと思ったら...

トラブル

ログイン画面や登録画面へ行くと404エラーが出る。

ルーティングはしっかり機能しているはずなのに何で...

原因はすぐにわかりました。

先ほどのapache2.confにAllowoverride Allが記述されていないのが原因でした。

これがないとpublic下の.htaccessが動かんらしい。

よくみると、初期状態ではAllowoverride Noneとなってますね...

参考

https://qiita.com/msht0511/items/b32122413745d0a3d50a

同様の理由で、apacheのmod_rewrite機能も有効化しないといけないみたいでした。

sudo a2enmod rewrite
sudo service apache2 restart

で解決。

おわり

最近作った弐寺のクリアランプマネージャです。(予告なくサービスを公開を終了するかもしれません)→地力Estimator

ぜひこちらの記事も読んでみてください。

rubyをバージョンアップをして、budnle installを叩いたら、

Fetching: mysql2-0.3.18.gem (100%) 
Building native extensions. This could take a while... 
p 
ERROR: Error installing mysql2: 
    ERROR: Failed to build gem native extension. 

    /Users/my_username/.rvm/rubies/ruby-2.1.2/bin/ruby -r ./siteconf20150614-72129-orqsb7.rb extconf.rb 
checking for ruby/thread.h... yes 
checking for rb_thread_call_without_gvl() in ruby/thread.h... yes 
checking for rb_thread_blocking_region()... yes 
checking for rb_wait_for_single_fd()... yes 
checking for rb_hash_dup()... yes 
checking for rb_intern3()... yes 
----- 
Using mysql_config at /usr/local/bin/mysql_config 
----- 
checking for mysql.h... yes 
checking for errmsg.h... yes 
checking for mysqld_error.h... yes 
----- 
Don't know how to set rpath on your system, if MySQL libraries are not in path mysql2 may not load 
----- 
----- 
Setting libpath to /usr/local/Cellar/mysql/5.6.25/lib 
----- 
creating Makefile 

make "DESTDIR=" clean 

make "DESTDIR=" 
compiling client.c 
compiling infile.c 
compiling mysql2_ext.c 
compiling result.c 
linking shared-object mysql2/mysql2.bundle 
ld: warning: directory not found for option '-L/Users/travis/.sm/pkg/active/lib' 
ld: library not found for -lssl 
clang: error: linker command failed with exit code 1 (use -v to see invocation) 
make: *** [mysql2.bundle] Error 1 

make failed, exit code 2 

Gem files will remain installed in /Users/my_username/.rvm/rubies/ruby-2.1.2/lib/ruby/gems/2.1.0/gems/mysql2-0.3.18 for inspection. 
Results logged to /Users/my_username/.rvm/rubies/ruby-2.1.2/lib/ruby/gems/2.1.0/extensions/x86 

上記のようにmysqlのエラーが出る。。。。

もしもインストールをしていなければ、

bundle config --local build.mysql2 "--with-ldflags=-L/usr/local/opt/openssl/lib --with-cppflags=-I/usr/local/opt/openssl/include"

こいつを叩いて、bundle installしてみて、

Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (LoadError)

上記のエラーが出るようなら、

$ brew install openssl
$ export LIBRARY_PATH=$LIBRARY_PATH:/usr/local/opt/openssl/lib/

これでPATHを指定してから、再度budnle install
これでも無理なら、、、、

brew update && brew upgrade

これで行けるケースもあります。
ただ、これでも

Library not loaded: /usr/local/opt/openssl/lib/libssl.1.0.0.dylib (LoadError)

こいつが出るようなら、
rbenv をアンインストールする。

$ ruby -v
ruby 2.3.4p301

でrubyのバージョン確認

$ rbenv unistall 2.3.4

$ rbenv install --list
$ rbenv install 2.3.4

インストールするlistの確認をして、rubyのバージョンを指定してinstallする。

$ gem install bundler

その後、bundle installする。
ただ、念の為にgemfile.rockを消してからやるとよい

メモ書きの感じで書いたので、まだ理解不足です。
ご指摘等あればお願いします。

Amazon Lightsailで構築したRedmineはデフォルトではインスタンス内に入っているMySQLを使用しています。
これを、Amazon Lightsailで別に構築したMySQLを使わせてみようと言うのが今回の趣旨です。

大まかな流れとしては以下のようになります。
1. Redmine側DBのダンプを取得
2. 新しく立てたDBにスキーマを作成してリストア
3. Redmineの設定ファイルを編集し、新しく立てたDBを使うようにする

1. Redmine側DBのダンプを取得

Lightsailで立てたインスタンスにsshで入ったらまず、mysqlのrootユーザーのパスワードを確認します。
rootのパスワードは~/bitnami_application_passwordに書かれています。
また、このパスワードはRedmineの管理者ユーザーのパスワードにもなっています。 (ログインIDはuser)

パスワードを確認できたら以下のコマンドを実行します。
bitnami_redmine_dump.sqlという名前でダンプファイルが作成されます。
mysqldump -u root -p bitnami_redmine > bitnami_redmine_dump.sql

このタイミングで何か適当なプロジェクトでも作っておくとRedmineのDBを切り替えた時に確認しやすいかもしれません。
切り替えが上手くいけばこのプロジェクトは表示されなくなるはずですから。

前

後 (新しいDBには"前"の時点でのダンプを使うので、切り替えが上手くいけばPJ_2は表示されなくなるはず)

2. 新しく立てたDBにスキーマを作成してリストア

新しく立てた方のmysqlにログイン
mysql -u dbmasteruser -p -h （エンドポイント）
(ユーザー名、パスワード、ホスト（エンドポイント）はコンソールから確認できます。)

ダンプのリストア先となるスキーマを作る
create database bitnami_redmine

mysqlからログアウト
exit

作ったスキーマにダンプをリストア
mysql -u dbmasteruser -p -h （エンドポイント） bitnami_redmine < bitnami_redmine_dump.sql

テーブルを確認するとPJ_1のレコードを確認できる。

mysql> select id, name from bitnami_redmine.projects;
+----+------+
| id | name |
+----+------+
|  1 | PJ_1 |
+----+------+
1 row in set (0.00 sec)

3. Redmineの設定ファイルを編集し、新しく立てたDBを使うようにする

このファイルは/home/bitnami/apps/redmine/htdocs/config/database.ymlにあります。

production:
  adapter: mysql2
  database: bitnami_redmine
  host: localhost
  username: bitnami
  password: xxxxxxxxxx
  encoding: utf8
  socket: /opt/bitnami/mysql/tmp/mysql.sock

production の database, host, username, password をそれぞれ新しく立てたDBに合わせて書き換えます。
書き換え終わったら保存してRedmineのインスタンスを再起動しましょう。

プロジェクト一覧を見てみるとPJ_2が表示されていないので、これでうまく切り替えできたことが分かりました。

環境

CentOS Linux release 8.0.1905 (Core)
Minimal installにgcc関係・vim・gitくらい入れたもの

web

Install_and_configure_apache2.4.37

[C8LX@root ~]# dnf install httpd httpd-devel httpd-tools mod_ssl -y
[C8LX@root ~]# httpd -v
Server version: Apache/2.4.37 (centos)
Server bui lt:   Oct  7 2019 21:42:02

[C8LX@root ~]# cd /etc/httpd/conf/
[C8LX@root /etc/httpd/conf]# cp httpd.conf httpd.conf.bk

[C8LX@root /etc/httpd/conf]# cat httpd.conf.bk | egrep -v "(\#|^$)" > httpd.conf

[C8LX@root /etc/httpd/conf]# vim httpd.conf
-:Listen 80
+:Listen 0.0.0.0:80

-:    DirectoryIndex index.html
+:    DirectoryIndex index.php index.html index.cgi
(末尾に追記)
ServerTokens Prod

[C8LX@root /etc/httpd/conf]# cd

[C8LX@root ~]# firewall-cmd --list-all
  services: cockpit dhcpv6-client ssh

[C8LX@root ~]# firewall-cmd --remove-service={dhcpv6-client,cockpit} --zone=public --permanent

[C8LX@root ~]# firewall-cmd --add-serevice={http,https} --zone=public --permanent

[C8LX@root ~]# firewall-cmd --reload

[C8LX@root ~]# firewall-cmd --list-all
  services: http https ssh

[C8LX@root ~]# echo "test page" > /var/www/html/index.html

[C8LX@root ~]# chmod -R apache:apache /var/www

[C8LX@root ~]# systemctl start httpd

[C8LX@root ~]# systemctl enable httpd

ブラウザで http://サーバIP/ へアクセスして、表示されればOK。

PHP

Install_and_configure_php7.2.11

[C8LX@root ~]# dnf install php php-fpm php-mbstring \
php-devel php-mysqlnd -y

[C8LX@root ~]# php -v
PHP 7.2.11 (cli) (built: Oct  9 2018 15:09:36) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies

[C8LX@root ~]# vim /etc/php.ini
-:;date.timezone =
+:date.timezone = Asia/Tokyo
-:expose_php = On
+:expose_php = Off
-:;mbstring.language = Japanese
+:mbstring.language = Japanese
-:;mbstring.internal_encoding =
+:mbstring.internal_encoding = UTF-8
-:;mbstring.http_input =
+:mbstring.http_input = UTF-8
-:;mbstring.http_output =
+:mbstring.http_output = UTF-8
-:;mbstring.encoding_translation = Off
+:mbstring.encoding_translation = On
-:;mbstring.detect_order = auto
+:mbstring.detect_order = auto
-:;mbstring.substitute_character = none
+:mbstring.substitute_character = none

[C8LX@root ~]# echo -n '<?php\nphpinfo();\n?>' > /var/www/html/index.php

[C8LX@root ~]# cp /etc/php-fpm.d/www.conf /etc/php-fpm.d/www.conf.bk
[C8LX@root ~]# vim /etc/php-fpm.d/www.conf
-:;listen.owner = nobody
+:listen.owner = apache
-:;listen.group = nobody
+:listen.group = apache
-:;listen.mode = 0660
+:listen.mode = 0660

[C8LX@root ~]# systemctl restart httpd

ブラウザでhttp://サーバIP/index.phpへアクセスして、phpinfo()が表示されればOK。

mysql

Install_and_configure_mysql8.0

[C8LX@root ~]# dnf install mysql-server mysql-devel -y

[C8LX@root ~]# vim /etc/my.cnf.d/mysql-server.cnf
(末尾に追記)
+:character-set-server=utf8
+:bind-address=0.0.0.0
#+:collation-server=utf8_unicode_ci

[C8LX@root ~]# vim /etc/my.cnf
([client]直下に追記)
+:default-character-set=utf8
+:[mysql]
+:default-character-set=utf8

[C8LX@root ~]# cp /usr/lib/systemd/system/mysqld.service \
/etc/systemd/system/

[C8LX@root ~]# vim /etc/systemd/system/mysqld.service
-:ExecStart=/usr/libexec/mysqld --basedir=/usr
+:ExecStart=/usr/libexec/mysqld --basedir=/usr --skip-mysqlx

[C8LX@root ~]# systemctl daemon-reload

[C8LX@root ~]# systemctl start mysqld.service

[C8LX@root ~]# systemctl status mysqld.service
   Active: active (running)

[C8LX@root ~]# netstat -ant
tcp    0    0    0.0.0.0:3306    0.0.0.0:*    LISTEN

[C8LX@root ~]# mysql -u root
(※以下大文字は任意)
mysql> create database DBNAME;
mysql> create user USERNAME@localhost;
mysql> grant all privileges on DBNAME.* to USERNAME@localhost;
mysql> alter user USERNAME@localhost identified by 'PASSWORD';
mysql> quit;

[C8LX@root ~]# vim test.php
<?php
    try {
        $dbcon = new PDO('mysql:host=localhost;dbname=DBNAME','USERNAME','PASSWORD');
        printf("success\n");
    }
    catch(PDOException $e){
        printf("failure\n");
        exit;
    }   
?>  

[C8LX@root ~]# php test.php
success

[C8LX@root ~]# firewall-cmd --add-service=mysql --zone=public --permanent

[C8LX@root ~]# firewall-cmd --reload

[C8LX@root ~]# firewall-cmd --list-all
  services: http https mysql ntp samba samba-client ssh

ひとまずここまで。SQL苦手だからいつも検索するんだけど、
ちょくちょく全部大文字になってる場合があるの何故なんだろう。

問題

migration実行時に
SQLSTATE[HY000]: General error: 1005 Can't create table 'hoge.#sql-935_2e' (errno: 150) (SQL: alter table `m_answer_options` add constraint `m_answer_options_question_id_foreign` foreign key (`question_id`) references `m_questions` (`id`))
のエラーが出た。

原因

外部キーを設定するときに、参照先のテーブルがBIGINT、参照元がINTになっている。

(例)
【参照先のマイグレーションファイル】
yyyy_mm_dd_061000_create_m_questions_table.php
$table->BigIncrements('id');

【参照元のマイグレーションファイル】
yyyy_mm_dd_061032_create_m_answer_options_table.php
$table->foreign('question_id')->references('id')->on('m_questions');

対応方法

参照先テーブルを作成するマイグレーションファイル(yyyy_mm_dd_061000_create_m_questions_table.php)のbigIncrementsをincrementsに変更した。

(例)
【修正前】
$table->BigIncrements('id');

【修正後】
$table->increments('id');

参考ページ

https://github.com/laravel/framework/issues/28793

はじめに

railsでのDBテーブルの作成方法、カラムの追加など一連のデータベース操作についてまとめます。

railsでのテーブルの生成方法

テーブルを生成しましょう。
①テーブルのmigrationファイルを作成します。具体的な作成コマンドとしては以下になります。

$ rails g model モデル名 フィールド:型


※例えばpostモデルでnameカラム（string型）とuser_idカラム（integer型）を作成したい場合は以下になります。
$ rails g model Post name:string user_id:integer

②コマンドを打ち込むとmigrationファイルが新規で出来上がっているはずなのでそれを以下のコマンドで実行します。
実行後はdbサーバー上にpostsテーブルが出来上がります。

$ rails db:migrate

カラム追加方法

既に作成したテーブルにカラムを追加したい場合は下記の手順で追加します。

①追加したい項目を下記のようにコマンド入力する。

rails g migration Addカラム名Toテーブル名 カラム名:データ型

# 例えば既にあるpostテーブルにcategoryカラム（integer型）を作成したい場合は以下になります。
rails g migration AddCategoryToPosts category:integer

②db/migrateフォルダにマイグレーションファイルが出来上がっていますので、確認、必要があれば追記してください。
下記ファイルにはhogehogeカラムを追記しています。

class AddCategoryToPosts  < ActiveRecord::Migration[5.2]
  def change
    add_column :posts, :category, :integer
    add_column :posts, :hogehoge, :text #追記
  end
end

③編集終わりましたら、以下のコマンドで実行します。
これでテーブルにカラムが追加されます。

$ rails db:migrate

カラムの型の変換

既にあるテーブルのカラムの型を変えたいときは以下の手順で変更を行います。
カラム追加と似た手順となります。

①型変更したいカラムを下記のようにコマンド入力しmigrationファイルを作る。

$ rails g migration change_data_カラム名_to_テーブル名

# 例えばpostテーブルのhogehogeカラムを型変更したい場合は以下になります。
rails g migration change_data_hogehoge_to_posts

②db/migrateフォルダにマイグレーションファイルが出来上がっていますので、class内に以下のメソッドを追加してください。

def change
  change_column :posts, :hogehoge, :integer
end

③編集終わりましたら、以下のコマンドで実行します。
これでテーブルにカラムが追加されます。

$ rails db:migrate

終わりに

この記事で誤っている箇所や追記したほうが良い点がありましたらコメント欄などでご指摘いただけますとありがたいです！
ActiveRecordと通常のsqlコマンドは書き方違うので勉強必要ですね・・

使用する機会があり、メモ程度にまとめてみます。

環境

PHP : 5.6.30
MySQL : 10.1.21
Laravel : 5.1.46

テーブル

以下サンプルテーブルです。

id	name	created_date	updated_date
1	Suzuki Taro	2019-01-01 12:00:00	2019-01-01 12:00:00
2	Tanka Hanako	2019-01-25 10:00:00	2019-01-29 12:00:00
3	Takahashi Jiro	2019-02-01 16:00:00	2019-02-01 16:00:00

元のSQL

SELECT name FROM users
WHERE (created_date BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59'
OR (updated_date < '2019-02-01' AND id >= 1));

クエリビルダでの書き方

$test = Users::query()
   ->select('name')
   ->where(function ($query) {
                // created_dateが2019-01-01中
                $query->whereBetween('created_date', ['2019-01-01 00:00:00', '2019-01-01 23:59:59'])
                    // またはupdated_dateが2019-02-01より前、かつidが1以上
                    ->orWhere(function ($query) {
                        $query->where('updated_date', '<', '2019-02-01')
                            ->where('id', '>=', 1);
                    });
            })

dockerで開発をしている際に、
Could not write unix socket lock file /var/run/mysqld/mysqld.sock.lock
といったエラーが出てMySQLが起動できなくなってしまったので、
その対処法をメモ。

エラーにある意味どおり、書き込みができないとあるので、
MySQLが正常に終了出来ずにlockが外れなかったのが原因かな？
とりあえずコンテナとイメージの削除をしてみる。

# コンテナとイメージを一括削除
$ docker container prune
$ docker image prune

# ビルドしてコンテナ立ち上げ
docker-compose up --build

なおった
イメージの容量が30GB超えてて驚いた

コンテンツ

1. 環境説明
2. DBバックアップ概要
3. DBバックアップ手順
   1. DB dump
   2. 暗号化処理
   3. Logrotate
   4. git push
4. リストア概要
5. リストア方法
   1. バックアップデータの取得
      1. githubのデータを利用する
      2. 本番サーバからゲットする
   2. リストアを実行する

環境説明

mysql DB（Version 5.7）のDBを遠隔バックアップするためにGithubを利用してみたので、その作業メモを公開します。

DBバックアップ概要

DBバックアップは、大まかにいうと下記の手順でそれぞれの処理を行っている。
mysqlの機能を利用し、laravelのDBをdumpして、（SQL文として）ファイルに書き出している。
githubを利用して、BackUpを行っている。プライベートリポジトリではあるが、暗号化処理を行ってから、githubにpushする。
githubでも世代管理はできるが、緊急時の速度性や作業ミスを誘発しないために、Linuxのlogrotateを利用して、世代管理も行う。
準備できたものをgitにpushしていく。

この工程をcronで実行している。

DBバックアップ手順

①DB dump

mysqlのdumpコマンドを利用している。

 /usr/bin/mysqldump  --defaults-extra-file=/home/ubuntu/.mylogin.cnf seeker > /home/ubuntu/DBBK/db_dump.sql

ポイントは、cronで実行するので、PW情報などを設定ファイル(.mylogin.cnf)で渡していること。

ubuntu@la01:~$ cat ~/.mylogin.cnf 
[client]
user = myuser
password = 'XXXXXXXX'

passwordは、伏字にしていますので、適宜、変更ください。

②暗号化処理

opensslのコマンドを利用して、暗号化。

/usr/bin/openssl enc -e -aes-256-cbc -salt -k "XYZXYZXYZ" -in /home/ubuntu/DBBK/db_dump.sql -out /home/ubuntu/DBBK/db_dump.sql.enc

passwordは、伏字にしていますので、適宜、変更ください。

③Logrotate

logrotateコマンドを利用している。

/usr/sbin/logrotate  /home/ubuntu/DBBK/logrotate.conf 2>/home/ubuntu/DBBK/logrotate_error

特にこだわりポイントもないが、confファイルも記載しておく。

~/DBBK$ cat logrotate.conf 

logrotate.conf

/home/ubuntu/DBBK/db_dump.sql.enc {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
/home/ubuntu/DBBK/logrotate_error {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
ubuntu@la01:~/DBBK$ cd
ubuntu@la01:~$ cat  /home/ubuntu/DBBK/logrotate.conf 
/home/ubuntu/DBBK/db_dump.sql.enc {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
/home/ubuntu/DBBK/logrotate_error {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}

④git push

ディレクトリを移動し、git add、git commitして、git push　（特になんてことはない
コマンドにすると

 (cd /home/ubuntu/DBBK && git add . && git commit -m "Automatic Commit" && git push)

gitの連携は、confファイルと鍵交換。

まずは、confファイルを編集する。
ユーザ名はGitHubUserName

ubuntu@la01:~/DBBK/.git$ cat config 

[core]
        repositoryformatversion = 0
        filemode = true
        bare = false
        logallrefupdates = true
[remote "origin"]
#       url = https://github.com/GitHubUserName/krow.git
        url = git@github.com:GitHubUserName/krow.git
        fetch = +refs/heads/*:refs/remotes/origin/*
[branch "master"]
        remote = origin
        merge = refs/heads/master
[user]
        name = GitHubUserName
        email = GitHubUserName@gmail.com

githubのSSHの鍵は
webにログイン後　settings>SSH and GPG keys
で設定できる。
この辺りはWebにいっぱいあるので、割愛。

リストア概要

リストアに関する情報をまとめる。これは、ほぼリストアの手順書である。

リストア手順

DBバックアップ情報を取得し、リストアを実行する

DBバックアップ情報を取得

githubのデータを利用する

githubからGetする方法を記載します。

ubuntu@la02:~$ pwd
/home/ubuntu
ubuntu@la02:~$ mkdir restore
ubuntu@la02:~$ cd restore/
ubuntu@la02:~/restore$ git clone https://github.com/GitHubUserName/krow.git

gitのcloneで得たファイルは暗号化処理させているので、復号化する。

 openssl enc -d -aes-256-cbc  -salt -k "XYZXYZXYZ" -in db_dump.sql.enc.1 -out db_dump.sql

※PWは、バックアップ時に設定したものを利用してください。

本番サーバからゲットする

本番サーバのDBが生きていて、テスト環境などを作りたい場合のため、簡易に実行できるので、こっちも記載しておく。
本当の障害時は、本番サーバのDBがおかしくなっているだろうから、こちらは使えないかもしれないが。

コピー先サーバのアカウント:ubuntu
コピー先サーバのIPアドレス：203.0.113.2

@本番サーバでの作業

~$ cd DBBK/
~/DBBK$ scp db_dump.sql ubuntu@203.0.113.2:

リストアを実行する

①databaseの作成

@リストアサーバでの作業
rootでmysqlを実行する。（これは、mysqlのセキュリティ設定にも依存するが）

:~$ sudo su
[sudo] password for ubuntu: 
root@# mysql -u root

以下はmysqlでの作業

mysql> create database ladb2;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on ladb2.* to 'myuser2'@'localhost' identified by 'hoge1234';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> quit
Bye

ladb2というDatabaseをつくり、myuser2というユーザにladb2への変更権限等すべての権限を付与する。
但し、myuser2がアクセスする際にはパスワードが必要で、そのパスワードは'hoge1234'だよって設定をした。

root@la02:/home/ubuntu/restore/krow# exit

mysqlでの作業が終わったら、rootで作業することを止める。

②SQLの流し込み

ubuntu@la02:~/restore/krow$ mysql -u myuser2 -p ladb2 <  db_dump.sql
Enter password: 

③確認

:~$ mysql -u myuser2 -p ladb2
Enter password: 

以下は、データの確認方法。

mysql> show tables;

テーブルが見えたら、まあ、大丈夫でしょう。

MAMPを使ってSQLのお勉強と思ったら開始早々、以下のメッセージがログの中に出て、つまづいた。

Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade.

mysql_upgradeしろとあるが、コマンドラインから打ってもそのようなコマンドはありません、と返ってくる。

MAMP→Tools→Upgrade MySQL databasesからできた。

↓

Upgradeを押す
↓

完了。
MAMPでサーバー再起動するとエラーメッセージが消える。

macに慣れてないせいで、こんなショボいことに気づくのに２時間も費やした・・・。

MySQLの状態確認

service mysql status

または

/etc/init.d/mysqld status

MySQLの起動

mysql.server start

または

/etc/init.d/mysqld start

MySQLの停止

mysql.server stop

または

/etc/init.d/mysqld stop

MySQLの再起動

mysql.server restart

または

/etc/init.d/mysqld restart

MySQLログイン

mysql -u root 

CBcloud Advent Calendar2019 一日目の記事の続編となります。

前職との相違点

前職はフロントエンドに完全に集中して実装することが許される環境でした。
相違点を簡単にまとめてみます。

前職

※所属していた会社そのものではなく、常駐先の環境となります。

• 大企業
• 客先常駐
• 応答率90％以上の完璧なレベルのヘルプセンターで、エンジニアがユーザーの問い合わせに直接対応することがない。
• 完全な役割分担がなされ、フロントエンド実装のみが我々のチームに依頼されてくる。
  • フロントエンド系のバグ修正。
  • 新機能のフロントエンド実装。
• 障害が起きた場合、専門のチームが対応。
• マーケティング部門で入念に検討・分析・企画された上で具体的な案件としてチケットが作られ、エンジニアが対応する体制が確立されており、スケジュールも余裕を持った期間が取られている。
• エンジニアがたくさんいる

CBcloud

• ベンチャー企業（スタートアップ）
• 本社勤務
• 問い合わせについて一次対応者が返答できない場合に、その問い合わせが直接エンジニアに回ってくる。¹
• 役割分担がなく、インフラ・バックエンド・フロントエンドをできる人がメインでやり、興味があればいくらでも手を出せる。
• 依頼内容・実装の要望がフロントエンドに収まらず多岐にわたる。
• 障害が起きた場合、自分たちが直接対応する。
• 差し込み対応が多く、短い期間で可能な限りを実装することが多い。
• エンジニアには機能開発全体を任されている
• エンジニアは少数

フロントエンドだけでは、できることに限界があった

新機能実装をする場合、前職だとフロントエンドだけを担当し、他は任せきりで何の問題もありませんでした。
現職の場合、機能開発全体をやり切る必要があり、UIだけでなくサービスの仕組みづくりそのものが求められています。

おのずとフロントエンド実装のみで対応する場合、できる範囲に限界が見えてきました。
実は入社した時から「フロントエンドは教えるので、バックエンドを教えてください」と言っており、
それもあって少しずつScala、そしてRubyを書いていくことになります。

ログイン処理の変更

急にScalaの案件を任されたと思ったらこれだよ！
2週間ほど苦しんだ覚えしかありません。

ここがとっかかりで、他にもScalaコードの修正対応を続けていくことで経験を重ね、
データの変更などの処理がある程度書けるようになり、リファクタリングもできるようになりました。

パスワード再発行画面＋API

こちらはいろいろあってRuby on Railsです。
Grape環境を入れる所から始めました。
やはり環境作りと実装で2週間ほど苦しみました。
Rubyのif文の書き方やmapなどの構文も最初はおぼつきませんでしたが、リファレンスを読んだり、他の詳しいエンジニアに都度聞きながら覚えていきました。²
とはいえ、Rubyの実装自体はScalaに比べると素直に書けるので、一番の鬼門は環境構築だったと思います。
今ではデータを変更するだけなら難なくできるようになりました。

クエリのチューニング

入社当時からサーバーが不安定になることが度々あり、
フロントエンドだけ行っているときは手出しできない領域でした。

• 少なくとも結合用のキーにはindexを貼ること
• スローログが出ている怪しいクエリはexplainで計測すること
• クエリを見直し、不要なサブクエリが発行されている箇所を改善すること

しかし、他のエンジニアと一緒に解決していったことで、クエリのチューニングができるようになりました。

データ分析・統計・可視化

前職だとSQLを全く触る必要がなかったのですが、
SQLツールを当たり前に使い、データ出しや分析のためにクエリを書くことが増えたため、
必然的にMySQLクエリを書くスキルが磨かれました。

Metabase

毎回生のクエリを書いて出すのは大変＋データ出し用のページを実装するのも手間ということで、
Metabaseを使うようになりました。
変数も使えるので可変になる部分は変数で定義しておけます。

Metabase環境構築自体はつよつよエンジニアに任せてノータッチだったりしますが、
出来上がった環境で現在までに50個ほどクエリを書いています。

GoogleDataPortal

他のエンジニアからすると「使いにくい、わかりにくい」との評価でした。
ただ、実際使ってみるとGoogleスライド＋データの可視化ツールといった感じで、
GoogleスライドとMySQLが使えれば使えるという感じで、
今までDBに持っているだけだったデータの可視化に大きく貢献してくれています。

これらのサービスを使うまでは独自のページを実装したり、依頼の都度SQLツールでクエリを叩いてCSV出力する泥臭いことが多かったです。
metabaseやGoogleDataPortalを使うと、クエリを書くことに集中できる＋URL共有できる＝Slackで簡単に伝えられるので便利です。

また、事業会社で重要な「日々の数字を意識すること」がグラフ化・可視化されたことで行いやすくなりました。

個人向けサービスの改善企画

MySQLのクエリを本格的に書かざるを得なかったのがこの案件でした。
当時Excelは使えたのですが、Select文とwhere句を覚え立ての段階でやりました。
CSV化したデータをExcelに張り付け、どこの数字を上げると、最終的な売上に一番貢献するのかを算出し、
それを基にやるべき施策を導き出しました。

感覚ではなく、データを基に企画し、施策を決めるという初めての経験でした。
作ったExcelデータについては予想以上の分析を提示できていたらしく、CEOから好評だったようです。

やれることをいろいろやったら、身についた

いろいろやったら、スキルが身についてきました。

今の自分にできないことを簡単にできる凄い人を見ると「神だ！」と思ったり、
「自分にはとてもできない！すごい！」と感じて終わったりすることが度々あると思います。

しかしながら、「神」と崇めているだけでは何も技術は身につかないですし、
最初は何もできない所から始めて当然なので、無暗に崇め奉らないこと、
「自分もやってみよう」と考えてみることが大事なのかな、と今では思っています。

contributions

参考までに2016年〜現在までのGitHub contributionsの数を載せておきます。
扱う領域が増えた分、自分が編集する箇所も増え、contributionsが年々増えていっているのが分かります。

年	contributions
2016	133
2017	1064
2018	1601
2019	2230

まとめ

• フロントエンドだけだと機能開発が1から10まで出来ない
• 教えてもらったり調べたりして、ScalaとRubyを覚えた
• SQL文が書けるとどこに行っても役に立つ
• データ可視化ツールを活用して、本質部分に集中する
• 「神」で思考を止めないこと

---

1. 電話応対を直接行うわけではなく、電話応対している方がSlack上で問い合わせてくるため、そこに回答を提示する形。入社当時は電話応対者がいない場合に直接電話対応することも、無くはなかったです。 ↩

2. このRubyに詳しいエンジニアはPickGo以外のプロダクトを担当していたため、環境を作ったり直接実装するのは私の担当でした。 ↩

3. 前職だとコミットはGitHub Enterprise上だったので、寂しい数値となっています。 ↩

この記事は初学者がエラーになったときの対処法を記載したものになります。

同じ初学者の方は、一度記事を全部読んで同じ症状に当てはまる場合実行してみてください。

現状

現在私は、１つのrailsアプリケーションを作り、AWSのEC2、Mysql、Nginx、Capistrano等を使い自動デプロイを行う際に置きたエラーになります。

エラー症状

自動デプロイ後　IP　で繋いだ所下記の画面が表示される。

原因

私の場合、デプロイ後に

サーバー環境

kill -QUIT `cat tmp/pids/unicorn.pid` 

上記のコマンドを訳もわからずに実行した結果表示されました。
（上記コマンドは、ユニコーンの停止を行うコマンドです。）

エラー解説

We're sorry, but something went wrong.
If you are the application owner check the logs for more information.
とは、

ローカル環境

bundle exec cap production deploy

を行いましたが、unicornもしくはnginxにエラーが起こっている場合に起こる可能性のあるエラーになります。
私の場合デプロイを行ったのにもかかわらず、unicornサーバーを落とした為エラーになりました。
また、このエラーはlogに残らない場合もあります。

解決策

エラーの原因がunicorn,Nginxどちらにあるかわからない為、両方を再起動させましょう。

まず、unicornのサーバーがまだ可動しているか確認を行います。

サーバー環境

ps aux | grep unicorn
(unicornのサーバー状態を調べるコマンド)
実行後↓
ec2-user  7263  0.0  8.6 335420 87328 ?        Sl   16:34   0:02 unicorn master               
ec2-user  7341  0.0  9.6 349224 97032 ?        Sl   16:34   0:00 unicorn worker[0] 
ec2-user  7763  0.0  0.2 110540  2092 pts/3    S+   17:14   0:00 grep
※見られたくない部分もある為カットしている部分あります。

サーバーが停止している場合１行しか出てこないです。

unicorn masterの行にある左から2番目の番号（７２６３）を

サーバー環境

kill 7263
kill -9 7263
どっちでも良い

にて停止してください。
その後

Nginxを再起動させます。

サーバー環境

sudo service nginx reload
（リロードさせるコマンド）
実行後↓
Stopping nginx:                                            [  OK  ]
Starting nginx:                                            [  OK  ]

※下記コマンドは行わなくても大丈夫です。
ついでにMysqlも再起動させときます。

サーバー環境

sudo service mysqld restart
（リロードさせるコマンド）
実行後↓
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

その後

ローカル環境

bundle exec cap production deploy
（自動デプロイさせるコマンド）

以上でエラーは解除されると思います。

はじめに

SQLの基本的な構文をまとめてみます。

バージョン

• MySQL8.0

SQL構文

• DBを作成する

create database if not exsists DB名
  character set=utf8mb4
  collate=utf8mb4
  encryption='N';

create database if not exsists DB名
→ DBが既に存在しない場合に作成する。
character set=utf8mb4
→ 文字コードを指定する。
collate=utf8mb4_bin
collateは照合順序と言われる。
encryotion
暗号化を意味する。
Yを指定した場合は暗号化される。　

• DBの一覧を表示する

show databases;

• DBを削除する

drop database DB名;

• テーブルを作成する

create table テーブル名 (カラム 型), (カラム 型);

• テーブル一覧を表示する

show tables;

• カラムを表示する

show columns from テーブル名;

• テーブルを削除する

drop table テーブル名;

• カラムを追加する

alter table テーブル名 add column カラム 型名;

オプションで追加する場所を指定できる。
first ・・・ 一番最初に追加する。
after カラム名・・・ カラム名の後に追加する。

• カラム名を変更する

・カラム１をカラム２に変更する
alter table テーブル名 rename column カラム` to カラム2;

• 属性を変更する

alter table テーブル名 modify column カラム名 型名;

• カラムを削除する

alter table テーブル名 drop column カラム名;

• データを投入する

insert into テーブル名 values(
  'データ'
  'データ'
);

• 特定のカラムにデータを投入する

insert into テーブル名(カラム名) values(データ);

• テーブルデータを表示する

select * from テーブル名;

特定のカラムの場合
select カラム名 from テーブル名;

• not null制約を設定する

alter table テーブル名 modify カラム 型 not null;

• テーブルデータを削除する

trancate テーブル名;

• カラムデータを削除する

delete from テーブル名 where カラム名(pk) = データ番号

• プライマリーキーの設定

alter table modify カラム名 int primary key;

• プライマリーキーに連番を振る

alter table テーブル名 modify カラム名 int auto_increment;

• データを変更する

update テーブル名 set カラム名='変更後のデータ' where カラム名(pk) = データ番号;

where構文

• 特定のデータを表示する

select * from テーブル名 where 条件;

• 条件をand/or を使用して絞り込む

select * from テーブル名 where 条件 and 条件;
select * from テーブル名 where 条件 or 条件;

• あいまい検索をする

前方一致
select * from テーブル名 where カラム名 "%キーワード";

後方一致
select * from テーブル名 where カラム名 "キーワード%";

部分一致
select * from テーブル名 where カラム名 "%キーワード%";

order by句

• 昇順に並び替える

select * from テーブル名 order by カラム名 asc;(省略可)

• 降順に並び替える

select * from テーブル名 order by カラム名 desc;

limit句

・表示する件数を制限する

10件のみ表示する
select * from テーブル名 limit 10;

10〜20件表示する
select * from テーブル名 limit 10, 20;

where、order by、limitを複合して条件を指定する

select * from テーブル名 where カラム名 like "データ%" order by id desc limit 10

他のテーブルからデータを取得する

• 前提としてこのような商品テーブルとカテゴリーテーブルの２つのテーブルがあることを想定します。

• 商品テーブル

id	カテゴリー	商品名	価格
1	1	ワンピース	8000
2	2	サッカーシューズ	5000
3	3	キャップ	2000
4	4	iphone	70000

• カテゴリーテーブル

id	カテゴリー名
1	洋服
2	靴
3	帽子
4	null

• 内部結合でカテゴリー名を取得する

select * from 商品 inner join カテゴリー on 商品.カテゴリー = カテゴリー.id;

まず、 inner join カテゴリーで連結したいテーブル名を指定。
次に、商品テーブルのカテゴリーidとカテゴリーテーブルのidと一致するカテゴリー名を取得する。

• where句を用いた場合

select * from 商品 inner join カテゴリー on 商品.カテゴリー = カテゴリー.id where 条件;

これでカテゴリー名を取得することができました。
しかし、これでは商品のiphone は取得できません。inner joinはどちらのテーブルにも存在するものを取得するためカテゴリーに属していないiphoneは表示されません。
そこで次に、外部結合を行って表示させます。

• 外部結合でカテゴリー名を取得する

select * from 商品 left join カテゴリー
on 商品.カテゴリー = カテゴリー.id

商品 left join カテゴリー では、商品とカテゴリーを左右に並べた際に、商品テーブルにカテゴリーテーブルを結合するという意味になります。

left joinの反対にright joinがありますが、これはleft joinとは逆の動きになります。

select * from 商品　right join カテゴリー on 商品.カテゴリー =  カテゴリー.id;

カテゴリーテーブルに商品テーブルを結合するという意味になり、カテゴリーテーブルを全て表示し、商品テーブルはあった場合に表示するという動きになります。

以上です。

参考

• SQL素人でも分かるテーブル結合(inner joinとouter join)