DjangoでWebアプリ開発する際にMySQLサーバーとSSL通信する方法をまとめます

SSL証明書の取得

SSL証明書を取得します．例えば，AWSのRDSでMySQLを使っているなら
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/ssl-certificate-rotation-mysql.html
を参照．

証明書をWebサーバの/usr/local/ssl/内等に置きます．証明書自体の権限は644，証明書までのディレクトリの権限は755にする必要が有るので注意．

MySQLサーバと通信できるかテスト

SSL通信出来る状態になっていれば

mysql -h ホスト名 -u ユーザー名 -p --ssl-ca=/usr/local/ssl/証明書.pem --ssl-mode=VERIFY_IDENTITY

で接続可能です．

Djangoの設定

Djangoのsettings.pyを書き換えます．具体的には，DATABASES={}内のOPTIONSに'ssl'を追加します．

settings.py

DATABASES = {
    'default': {
        ...
        'OPTIONS': {
                   ...
                   'ssl': {'ca' : '/usr/local/ssl/証明書.pem'},  # 追加
                   },
        ...
    }
}

以上です！

問題

• commentsテーブルとpostsテーブルの2つを作ろうとしたがうまく行かない。

php artisan migrate


Migrating: 2020_03_05_085907_create_comments_table

   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `comments` add constraint `comments_post_id_foreign` foreign key (`post_id`) references `posts` (`id`))

原因

• migrationsテーブルの中にmigrate実行履歴が入っていた。
• php artisan migrateをすると、migrationsテーブルの中に実行履歴が残るようだ。
• 別の原因でphp artisan migrate:rollbackを使わずに、drop tableしたせい。

解決

• drop tableのときに、delete from migrationsで関連する履歴を消しておく。
• あるいはちゃんとphp artisan migrate:rollbackをつかいましょう。

概要

現在制作しているラインボットにデータベースを使った機能を追加しようと思い、Herokuを用いてMySQLへの接続を試みたがかなり躓いた所があったので今回まとめていこうと思います。

環境

• macOS
• python3.7.1
• MySQL5.6.47
• mysqlclient1.4.6

この記事で書かないこと

• MySQLの詳細なクエリの使い方

手順

1.MySQLのインストール

MySQLそのものをpip install mysqlでインストールします。

2.コネクタのインストール

コネクタのインストールを行います。
正直役割がよく理解できていませんが、名前の通りMySQLのサーバーとの接続の為に必要だと思われます。
コネクタには目的や使用言語等でいくつもの種類がありますが、自分はmysqlclientを使用しています。
こちらもpip install mysqlclientでインストールします。

3.Herokuでの準備

自分の場合は後述のMySQLの準備などを全て終えた後に、いざ動作確認をしようとHerokuへコードをプッシュし動かしてみたのですが、MySQLのインポートエラーが出てしまいました。
原因は二つあったのですが、まず一つ目がプッシュしたファイルの内、requirementsの記載です。
こちらにMySQLのコネクターを記載していなかった為、エラーが発生しました（因みに、最初はよくわからずMySQL自体のバージョンを記載していたのですが、これだとプッシュした時点でエラーが発生します）。

二つ目は、HerokuでMySQLを使う際はClearDBというものを使うらしく、そこから作ったアカウントが必要になります。
詳しい登録方法は割愛しますが、注意点として殆どのClearDBについての記事で、登録した後環境変数のCLEARDB_DATABASE_URLをコピーして先頭をmysql2に変更してDATABASE_URLに設定すると書いてありますが、こちらの操作はRubyの場合にのみ必要みたいです。

その為、今回はheroku addons:create cleardb:igniteでClearDBを追加した際のCLEARDB_DATABASE_URLのアカウントをそのまま使用しMySQLに入ることにしました。
CLEARDB_DATABASE_URLにmysql://[username]:[password]@[hostname]/[db_name]?reconnect=trueの形で記載されているので、ターミナル上でmysql --host=[hostname] --user=[username] --password=[password] [db_name]と入力すれば入ることができます。

4.MySQLでの準備

無事MySQLに入ることができたら必要なテーブルなどを作成していくのですが、ここで問題が発生。
普通にテーブルを作ろうとモニタでクエリを打ち込んだのですが、ERROR 2013というエラーがでました。
エラーメッセージで調べてみると、connect_timeoutなどの数値を大きくすれば解決するようでしたが、根本的な解決策ではないというのとrootユーザーで入れば問題なく処理ができることから、ClearDB特有の問題があるのではないかと考えました。
そこで、ClearDBのサイトをよく見てみると、

データベースの編集等を行う際は、MySQL Workbench,Sequel Pro for Mac OS X,Navicatなどの使用を推奨します。

という旨の記載を発見。
これらは、直接クエリを入力せずにMySQLの操作を行うことができるGUIツールらしく、以前どなたかの記事でMySQL Workbenchを使っているのを見たのでこちらを試すことに。
詳しい操作方法などはこちらの記事を参考に、無事必要なテーブルを作成することができました。

余談ですがMySQLの練習がてら初めてrootユーザーでMySQLに入ろうとした際、パスワードがいくら探しても分かりませんでした。
MySQLをインストールしたときの所定のファイルに初期パスワードが記載してあるということまでは分かったのですが、いくら手順通りにやってもこのファイルを見つけることができませんでした。
結果としてこちらの記事を参考に何とかMySQLに入ることができました。

まとめ

長々と書きましたが要約すると

1. MySQLのインストール
2. コネクタのインストール
3. HerokuでClearDBの登録（requirementsにコネクタのバージョンを記載）
4. MySQL WorkbenchでClearDB内の編集

となります。

今回、データベースという新しい分野に挑戦してみて、今までやってきたコードを正確に書くということとは別の視点が必要になりとても勉強になりました。
ベータベースはほぼ全てのサービスで必須らしいので、今回を機にしっかりと知識を身に着け自分のスキルの幅を広げたいと思います。

参考

• MySQLの使い方
• 便利な公式ツールMySQL Workbenchの使い方と日本語化方法
• MySQLでrootパスワードを忘れた場合の対処方法
• HerokuでMySQLを利用する方法

MySQLコマンド

MySQLに入る

> cd C:\xampp\mysql\bin
> mysql -u root

データベースの作成

> create database product default character set utf8 collate utf8_general_ci;

データベース確認

> show database;
> select database();

データベース切り替え

> use product;

データベース削除

> drop database product;

テーブル作成

> create table users(
        id int not null auto_increment primary key,
        name varchar(255),
        score int
        );

テーブル構造の確認

> desc users;

データ確認

> select * from users;

ユーザー作成

> create user dbuser01@localhost identified by 'password';

ユーザー権限譲渡

> grant all on mydb01.* to dbuser01@localhost;

ユーザー確認

> select user();

ユーザー削除

> drop user dbuser01@localhost;

レコードのデータ挿入

> insert into users (id, name, score) values (1, 'john', 5.8);

フィールドに制限をかける

変なデータが入らないように

null　のフィールドをはじく

> score float not null

=>　データが　null　のときフィールドそのものがないことになる

データが無い時デフォルト値設定

> score float default 0.0

=>　score　が設定されていないとき、0.0の値が入る

重複がないようにする

> name varchar(20) unique

=>　重複したフィールドははじかれる

id　に対する主キーの設定

> id int unsigned primary key auto_increment

primary key　=>　null　ではない重複しない値になることが保証される
auto_inctement　=>　自動的に連番になる

フィールドを後から追加

> alter table users add column email varchar(255);

末尾に　after [フィールド名]　とすると、指定したフィールドの直後に追加する

フィールドを削除

> alter table users drop column email score;

フィールドの型を変える

> alter table users change name user_name varchar(80);

テーブルの名前を変える

> alter table users rename persons;

全てのフィールドを抽出する

> select * from users;

特定のフィールドのみを抽出する

> select id, name from users;

条件付きでフィールドを抽出する

> select * from users where score >= 6;

=>　score　が6以上のすべてのフィールドを選択する

文字列を抽出条件にする

> select * from users where name = 'john';

部分一致で抽出

> select * from users where name like = 't%';

=> 先頭が　t　の値を抽出

> select * from users where name like = '%t%';

=> t　を含む値を抽出

> select * from users where name like = 't%';

=> 末尾が　t　の値を抽出

文字数で抽出

> select * from users where name like = '_____';

=> 取得したい文字数分のアンダーバーを入れる

抽出結果の並び替え

> select * from users order by score asc;

=> 値を小さい順に並べる

> select * from user where score is not null order by score desc;

=> 値を大きい順に並べる且つ null を除外する

> select * from users limit 3;

=> 最初の3件のみ抽出

> select * from users limit 3　offset 3;

=> 最初の3件を飛ばして次の3件を抽出

> select * from users order by score desc limit 3;

=> 値を大きい順に3件抽出

レコードの更新

> update users set score = 5.9 where id = 1;

=> 1　の　id　を持つフィールドの値を更新

> update users set name = 'john', score = 2.9 where 'bob';

=> john の name を bob、score　を　2.9　に更新

全てのデータの削除

>　truncate table users;

外部ファイル読み込み

> \. ./create_other.sql

mysql-connector-pythonを使う

import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1', user='root', password='')
cursor = conn.cursor()

cursor.execute('CREATE DATABASE test_mysql_database')

cursor.close()
conn.close()

conn = mysql.connector.connect(host='127.0.0.1', database='test_mysql_database')
cursor = conn.cursor()
cursor.execute('CREATE TABLE persons('
               'id int NOT NULL AUTO_INCREMENT,'
               'name VARCHAR(14) NOT NULL,'
               'PRIMARY KEY(id))')
cursor.execute('INSERT INTO persons(name) values("Mike")')
conn.commit()

cursor.execute('SELECT * FROM persons')
for row in cursor:
    print(row)

cursor.close()
conn.close()

出力：

(1, 'Mike')