- 投稿日：2020-02-13T16:03:53+09:00
【MySQL】外部キー制約を追加したり確認したり【ふ〜ん】
laravelを勉強していて、マイグレーションファイルから外部キー制約を付けていたのですが、後から付けたい〜！ってなったときにMySQLの方で付ける方法知りたかったのでメモ
ALTER TABLE テーブル名 ADD FOREIGN KEY (カラム名) REFERENCES テーブル名(カラム名);これでつく！！！
例えば、userとtwitterの関係が1対多だとすると、
twittersテーブルのuser_idと、usersテーブルのidを紐付ける必要があるので、
ALTER TABLE twitters ADD FOREIGN KEY (user_id) REFERENCES users(id);これでいいはず。
外部キー制約を確認する方法は、
show create table twitters;これを実行すると、
| twitters | CREATE TABLE `twitters` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `content` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `user_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |こんなのが出てくるので、確認できるよ〜！！！！！
初心者すぎてぴえん。
- 投稿日：2020-02-13T01:48:00+09:00
MySQLのデータが人質にとられた
はじめに
前回の記事で紹介していたWebサービスのshijimiのデータがなくなりました（泣）
どうやら，データを人質に取られたみたい．
ランサムウェアだね．SCの勉強中だからね．（実は，最初に公開したときから約2週間後にも同様の現象がありました．そのときは，原因不明でした．）
調査
サービスはconohaのVPSを利用しています．
自分のサーバが何かのタイミングで感染したのでしょうか？
AVは未導入でした．あちゃちゃ．
mysql_dataフォルダに，謎のplease_read_me_vvvがありますね．
自分の環境では，文字化けして読めませんでしたが，参考サイトによると，
「おまえのデータ盗んでやったぜ！！返してほしければ10日以内に0.03bitcoin払いやがれ！！」
とのこと．
ちなみに，
です．最近上がってきてますからね．原因をいくつか考えてみました．
案1
外部からMySQLをいじれたっけ？
あ～，接続許可ポートにMySQL:3306おるやん．
これはまずい．
だれでもアクセスできそうですね．
あとは，ブルートフォース攻撃で突破されたんかなぁ．追記
上の設定で，特定のポートだけを許可すると，サーバーにアクセスが届く前に遮断されるようです．
他のポートを開放したいなっていうときに，どうしようも無くなる．よって，「全て許可」のまま，firewall-cmdで設定するのが良さそうです．
でも，遮断したポートに外部から繋がってるぞ！？
Dockerでマストドンやってたら、遮断したはずのポートが丸見えだった件によると，dockerで開放したポートはファイアーウォールを無視するようです．
内部だけに開放するよう，127.0.0.1:3306:3306みたいにすると上手くいきました！！案2
dockerコンテナ構築用に，データベースのenvファイルを置いているのですが，外部から見られたら一発アウトですね．
イメージファイル作ったら，破棄すべき？
root権限さえ乗っ取られなければ，大丈夫だとふんでいる．不幸中の幸い
まだ，shijimiには新規登録者いませんでしたので，セーフです！
また，conohaの粋な計らいでSSD容量が50GB→100GBになったようです．
たぶん，サーバを再構築しないと適用されないのかな．
ちょうどいいので，一旦サーバを破壊しましょう．
当分shijimiにはアクセスできないかもです．おわりに
セキュリティやらないとなってことで，情報処理安全確保支援士(SC)の勉強真っ只中でおきた事件でした．
お金払っちゃだめっ！ぜったい！！
- 投稿日：2020-02-13T00:33:56+09:00
GradleでFlywayでMySQLをマイグレーション
概要
タイトル的なことをやろうと思ったら、新しくてちょうど良い感じの「こと始め的」な記事が見つけられなかったため、やってみたついでにまとめてみた。
→ MySQLDBをFlywayでマイグレーションする専用プロジェクト(by Gradle)を作ってみた。経緯
- 趣味開発にFlywayのDBマイグレーションを導入したかった
- SpringBootのアプリに組み込みじゃなくて専用プロジェクトにしたかった
- コマンドラインからやる気はなかった
- そういえば「./gradlew flywayMigrate」ってなんか見たことあるぞ
環境
- OS : macOS Mojave(10.14.6)
- Gradle : 5.2.1
- Flyway : 6.2.2
- MySQL : 5.7.24
前程知識
- Flyway (この辺お読み頂ければ良いのでは)
やってみた?
MySQLの準備
MySQL側で最低限の準備をする。
schema、userを作成し、userに権限を付与。mysqlmysql> CREATE DATABASE localdb DEFAULT CHARACTER SET utf8; Query OK, 1 row affected (0.01 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | localdb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> CREATE USER 'flywayuser'@'localhost' IDENTIFIED BY 'flywaypass'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT user, host FROM mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | flywayuser | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec) mysql> GRANT ALL ON localdb.* TO 'flywayuser'@'localhost' IDENTIFIED BY 'flywaypass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'flywayuser'@'localhost'; +-----------------------------------------------------------------+ | Grants for flywayuser@localhost | +-----------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'flywayuser'@'localhost' | | GRANT ALL PRIVILEGES ON `localdb`.* TO 'flywayuser'@'localhost' | +-----------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql>Flyway by Gradleのプロジェクト作成
プロジェクト用ディレクトリ(今回はflyway-skeleton)配下で
gradle initする。
色々聞かれたが全部デフォルトで突破した。console$ gradle init Starting a Gradle Daemon (subsequent builds will be faster) Select type of project to generate: 1: basic 2: cpp-application 3: cpp-library 4: groovy-application 5: groovy-library 6: java-application 7: java-library 8: kotlin-application 9: kotlin-library 10: scala-library Enter selection (default: basic) [1..10] Select build script DSL: 1: groovy 2: kotlin Enter selection (default: groovy) [1..2] Project name (default: flyway-skeleton): BUILD SUCCESSFUL in 20s 2 actionable tasks: 2 executed $直下に生成された
build.gradleの中身を書く。build.gradlebuildscript { repositories { mavenCentral() } dependencies { /*flywayのバージョンが古いとgradleのバージョンに対応してない(？)みたいで動かず、ひとまず最新に*/ classpath 'org.flywaydb:flyway-gradle-plugin:6.2.2' /*MySQL5.7に対応してそうなバージョンをググッて適当に設定*/ classpath 'mysql:mysql-connector-java:5.1.45' } } plugins { id "java" id "org.flywaydb.flyway" version "6.2.2" } repositories { mavenCentral() } flyway { /*さっきMySQLで準備したやつ*/ url = 'jdbc:mysql://localhost:3306/localdb?&allowPublicKeyRetrieval=true&useSSL=false' user = 'flywayuser' password = 'flywaypass' }SQLの準備
マイグレーション用SQLの置き場所を作る。
デフォルトで./src/main/resources/db/migrationらしい。console$ mkdir -p src/main/resources/db/migration $ $ du 120 ./gradle/wrapper 120 ./gradle 0 ./.gradle/vcs-1 56 ./.gradle/buildOutputCleanup 48 ./.gradle/5.2.1/executionHistory 0 ./.gradle/5.2.1/vcsMetadata-1 8 ./.gradle/5.2.1/fileChanges 48 ./.gradle/5.2.1/fileHashes 104 ./.gradle/5.2.1 160 ./.gradle 0 ./src/main/resources/db/migration 0 ./src/main/resources/db 0 ./src/main/resources 0 ./src/main 0 ./src 456 . $上記で作成したディレクトリに命名規則に沿ったSQLを配置する。
V00_000_001__Create_InitialTable.sql-- ユーザテーブル CREATE TABLE `t_user` ( `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, `created_at` datetime DEFAULT NULL, `created_by` varchar(45) DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `updated_by` varchar(45) DEFAULT NULL, PRIMARY KEY (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8Flyway tasksを実行
まずは
gradle flywayInfoで状態確認。
上記で作ったSQLファイルがPendingになっている。（まだDBには何も実行されない）console$ gradle flywayInfo > Task :flywayInfo Schema version: << Empty Schema >> +-----------+------------+---------------------+------+--------------+---------+ | Category | Version | Description | Type | Installed On | State | +-----------+------------+---------------------+------+--------------+---------+ | Versioned | 00.000.001 | Create InitialTable | SQL | | Pending | +-----------+------------+---------------------+------+--------------+---------+ BUILD SUCCESSFUL in 0s 1 actionable task: 1 executed $mysqlmysql> show tables from localdb; Empty set (0.00 sec) mysql>
gradle flywayMigrateでマイグレーションを実行する。console$ gradle flywayMigrate BUILD SUCCESSFUL in 0s 1 actionable task: 1 executed $ $ gradle flywayInfo > Task :flywayInfo Schema version: 00.000.001 +-----------+------------+---------------------+------+---------------------+---------+ | Category | Version | Description | Type | Installed On | State | +-----------+------------+---------------------+------+---------------------+---------+ | Versioned | 00.000.001 | Create InitialTable | SQL | 2020-02-12 22:50:51 | Success | +-----------+------------+---------------------+------+---------------------+---------+ BUILD SUCCESSFUL in 0s 1 actionable task: 1 executed <-------------> 0% WAITING > IDLE $mysqlmysql> show tables from localdb; +-----------------------+ | Tables_in_localdb | +-----------------------+ | flyway_schema_history | | t_user | +-----------------------+ 2 rows in set (0.00 sec) mysql> select * from localdb.flyway_schema_history; +----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+ | installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success | +----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+ | 1 | 00.000.001 | Create InitialTable | SQL | V00_000_001__Create_InitialTable.sql | -615596340 | flywayuser | 2020-02-12 22:50:51 | 16 | 1 | +----------------+------------+---------------------+------+--------------------------------------+------------+--------------+---------------------+----------------+---------+ 1 row in set (0.00 sec) mysql>上手いことできたっぽい。終わり。
その他
flywayRepairとかflywayCleanとかFlywayの基本的な使い方については割愛。- gradleが入ってない環境にプロジェクトを配布したら
./gradlew flywayInfoみたいな感じ（のはず）。- 記事では触れてないが、環境毎のMySQLにマイグレーションできるよう拡張（Repository参照）。
Repository
https://github.com/kazzool/flyway-skeleton
参考文献
おわりに
Qiita初投稿の記事がFlywayって。。。
- 投稿日：2020-02-13T00:04:53+09:00
乃木坂メンバーブログのタイトル長さ選手権
こんばんは、乃木オタエンジニアです。先日、某メンバーのブログタイトルが長いこと人気メンバーのマネだと言われ、炎上のネタになっていて気になりました。そこで、私は以前、ブログ全記事スクレイピングし、そのデータがDBに全て残っているのでSQLでちょちょっとブログのタイトルの長さランキング取ってみました。
以前の記事 → 乃木坂46公式ブログのスクレイピング～2011年からの全ての記事をDBに収めて画像も保存してやる！～実際に流したSQLはこんなん。
select member_name ,AVG(CHAR_LENGTH(title)) as avarage_title_length from blog_articles group by member_name order by avarage_title_length desc実行結果はこんな感じ
"member_name","avarage_title_length" "齋藤飛鳥",187.0151 "掛橋沙耶香",36.0000 "金川紗耶",32.8148 "運営スタッフ",31.4010 "佐藤楓",26.5562 "和田まあや",25.1511 "向井葉月",20.5333 "早川聖来",20.1538 "筒井あやめ",20.1538 "高山一実",18.7571 "山下美月",18.6706 "研究生",18.4950 "清宮レイ",17.6538 "秋元真夏",17.3662 "柴田柚菜",17.1429 "樋口日奈",16.6295 "北川悠理",16.2963 "遠藤さくら",16.0000 "中村麗乃",15.9160 "中田花奈",15.3146 "伊藤純奈",15.1982 "与田祐希",15.0882 "松村沙友理",15.0815 "寺田蘭世",15.0077 "田村真佑",14.6923 "賀喜遥香",14.4615 "矢久保美緒",14.1538 "梅澤美波",14.0370 "伊藤理々杏",14.0282 "井上小百合",13.7735 "生田絵梨花",13.3581 "吉田綾乃クリスティー",13.2584 "岩本蓮加",13.1679 "阪口珠美",11.9316 "白石麻衣",11.5188 "久保史緒里",10.6154 "北野日奈子",10.3929 "星野みなみ",10.0398 "大園桃子",9.3818 "佐々木琴子",8.7203 "山崎怜奈",8.3864 "堀未央奈",7.3388 "新内眞衣",6.5158 "鈴木絢音",6.4566 "渡辺みり愛",6.0742炎上していた某メンバーは確かに上位ですが、飛鳥ちゃんはダントツの長さなのです。こんなことでメンバーを叩くのは間違いですね。
ちなみにmaxを取ってみるとこんな感じ
"member_name","MAX_title_length" "齋藤飛鳥",255 "北野日奈子",255 "中村麗乃",226 "掛橋沙耶香",140 "柴田柚菜",136 "山崎怜奈",122 "金川紗耶",119 "寺田蘭世",115 "中田花奈",112 "和田まあや",91 "梅澤美波",90 "松村沙友理",85 "佐々木琴子",83 "樋口日奈",83 "佐藤楓",76 "岩本蓮加",73 "研究生",70 "運営スタッフ",67 "向井葉月",65 "伊藤純奈",64 "高山一実",60 "渡辺みり愛",56 "井上小百合",53 "筒井あやめ",53 "与田祐希",48 "山下美月",44 "伊藤理々杏",43 "堀未央奈",42 "生田絵梨花",39 "早川聖来",39 "阪口珠美",36 "秋元真夏",35 "清宮レイ",35 "鈴木絢音",34 "北川悠理",33 "遠藤さくら",32 "新内眞衣",31 "賀喜遥香",31 "久保史緒里",28 "白石麻衣",27 "星野みなみ",27 "大園桃子",26 "吉田綾乃クリスティー",24 "矢久保美緒",22 "田村真佑",22これは...もしかしてchar(255)の文字列がオーバーフローしている疑惑ですね。直さなきゃ。
そのうち乃木坂の音ゲーのランキングのスクレイピングをしたいなぁ。誰かサブ端末をください