What's New Qiita

20210118のMySQLに関する記事は7件です。

  • 投稿日：2021-01-18T18:16:30+09:00

【MySQL】中央値+αをストアドプロシージャで動的に計算する

はじめに

日頃、業務でデータを分析をおこなっています。
分析するデータはMySQLに置いておき、簡単な集計はSQL、複雑な計算はRやPythonで
という棲み分けをしていますが、チームにはSQLはわかるけど他はチョット...というメンバーもいるので、統計量などはSQLでさくっと見れるようにしたいところ。
今回プロシージャの勉強もしつつ、現状実装されていない（はず）の中央値や四分位数、
相関係数を簡単に求められるようにしたいと考えました。

使用したデータ

ECのデータをKaggleから取得します。
英国を拠点とするオンライン小売業者の8カ月間 (2010年12月1日～2011年12月9日) の全取引を含むトランザクションデータセット
https://www.kaggle.com/carrie1/ecommerce-data/home

実行環境

・macOS Catalina バージョン10.15.17
　MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
・mysql  Ver 8.0.22 for osx10.15 on x86_64 (Homebrew)
・MySQL Workbench version 8.0.21

my.cnfの設定（環境に応じて適宜読み替えてください）

vi /usr/local/etc/my.cnf

[mysqld]
sql_mode=''
secure-file-priv = ''

なお、データベースの構築方法などは割愛します。

データの準備（すでに手元にデータがあれば不要）

CREATE TABLE `ECdata` (
`InvoiceNo` varchar(10) DEFAULT NULL,
`StockCode` varchar(15) DEFAULT NULL,
`DESCription` varchar(100) DEFAULT NULL,
`Quantity` int DEFAULT NULL,
`InvoiceDate` datetime DEFAULT NULL,
`UnitPrice` decimal(10,3) DEFAULT NULL,
`CustomerID` varchar(20) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL
);

#import
LOAD DATA INFILE '~/ディレクトリ/data.csv'
INTO TABLE ECdata
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES 
(InvoiceNo, StockCode, DESCription,Quantity, @InvoiceDate,UnitPrice,CustomerID,Country)
SET InvoiceDate = STR_TO_DATE(@InvoiceDate, '%c/%e/%Y %k:%i'); #月/日/年 時:分

#空の件数を確認
SELECT
count(CASE WHEN InvoiceNo = "" THEN 1 ELSE NULL END )AS InvoiceNo_count
,count(CASE WHEN StockCode = "" THEN 1 ELSE NULL END )AS StockCode_count
,count(CASE WHEN DESCription = "" THEN 1 ELSE NULL END )AS DESCription_count
,count(CASE WHEN Quantity = "" THEN 1 ELSE NULL END )AS Quantity_count
,""AS InvoiceDate_count #,count(CASE WHEN InvoiceDate = "" THEN 1 ELSE NULL END )AS InvoiceDate_count # 設定にもよるがエラーになる
,"" AS UnitPrice_count#,count(CASE WHEN UnitPrice = "" THEN 1 ELSE NULL END )AS UnitPrice_count # UnitPrice = ""だと0がhitする
,count(CASE WHEN CustomerID = "" THEN 1 ELSE NULL END )AS CustomerID_count
,count(CASE WHEN Country = "" THEN 1 ELSE NULL END )AS Country_count
FROM ECdata;

#国別の件数を確認
SELECT Country, count(*) FROM ECdata
GROUP BY Country  ORDER BY count(*) DESC;

#合計金額を更新
ALTER TABLE ECdata ADD COLUMN TotalPrice float ; 
UPDATE ECdata SET TotalPrice = UnitPrice * Quantity;

#今回は最も多い国(UK)に絞って計算を行うことにしました
SELECT count(*)
FROM ECdata
WHERE (Quantity > 0 AND TotalPrice > 0)
AND Country = "United Kingdom";

#Recency Frequency Monetalyをそれぞれ計算します。
CREATE TABLE RFMdata
SELECT CustomerID,count(distinct InvoiceNo) AS F,sum(TotalPrice)AS M 
,datediff("2011-12-31 00:00:00",max(InvoiceDate)) AS R #日付が古く元のデータと差が大きいので近いところで11年の年末を設定
FROM ECdata
WHERE (Quantity > 0 AND TotalPrice > 0)
AND Country = "United Kingdom"
GROUP BY CustomerID;

プロシージャの作成

中央値

計算の流れとしては次のようになります。
①.データの行数をカウント
②.2で割った時に余りがあれば行数が奇数→floor(x/2)で 整数値 を取得
　割り切れれば偶数→floor(x/2)-1で 整数値-1 を取得 
　→offsetに使用
③.2で割った時に奇数であれば1を、偶数であれば2を取得
　→limitに使用
④.①〜③の数値を使って中央値の値をavg()で計算します。
例えば、データ数が5件であれば、2で割って2.5になるので、floorによって2（②より）が返ってきます。
offsetによって2行目まで飛ばして3行目からデータを使用することになります。
limitはこの時1（③より）となるので3行目のデータが中央値として取得できます。
4の時はoffsetが1、limitが2になるので、2行目と3行目の値の平均値が取得できます。

第一四分位、及び第三四分位も似たような出し方になっています。

#計算したい列とテーブル名を入力すると中央値をユーザー定義変数にsetする
DROP PROCEDURE IF EXISTS select_median;
delimiter //
CREATE PROCEDURE select_median(IN col text, IN tablename text, OUT median decimal(20,3))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @all_row_count = NULL;
    BEGIN
        SET @row_count_sql = concat('SELECT count(*) into @all_row FROM ',@tablename);
        PREPARE stmt FROM @row_count_sql;
        EXECUTE stmt ;
        DEALLOCATE PREPARE stmt;
        SET @all_row_count = @all_row;
    END;

    SET @all_ofs = cast((SELECT CASE WHEN @all_row_count % 2 = 0 THEN floor(@all_row_count/2) -1 else floor(@all_row_count/2) END )AS SIGNED);
    SET @all_lim = (SELECT CASE WHEN @all_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @all_sql = concat('SELECT avg(col) into @median FROM (SELECT ', @col,' AS col FROM ', @tablename,' ORDER BY col ASC LIMIT ? OFFSET ?) AS tmp');
    PREPARE stmt_median FROM @all_sql;
    EXECUTE stmt_median USING @all_lim,@all_ofs;
    DEALLOCATE PREPARE stmt_median;
    SET median = @median;
END//
delimiter ;

第一四分位数

DROP PROCEDURE IF EXISTS select_first_quartile;
delimiter //
CREATE PROCEDURE select_first_quartile(IN col text, IN tablename text, IN median decimal(20,2), OUT first_quartile decimal(20,2))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @first_quartile_row_count = NULL;
    BEGIN
        SET @row_sql = concat('SELECT count(*) into @first_quartile_row FROM ',@tablename,' WHERE ', @col,' < ',@median);
        PREPARE row_counter FROM @row_sql;
        EXECUTE row_counter;
        DEALLOCATE PREPARE row_counter;
        SET @first_quartile_row_count = @first_quartile_row;
    END;

    SET @ofs = CAST((SELECT CASE WHEN @first_quartile_row_count % 2 = 0 THEN floor(@first_quartile_row_count/2) -1 else floor(@first_quartile_row_count/2) END )AS SIGNED);
    SET @lim = (SELECT CASE WHEN @first_quartile_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @sql = CONCAT('SELECT avg(col) into @first_quartile FROM (SELECT ', @col,' AS col FROM ',@tablename,' WHERE ',@col,' < ',median,' ORDER BY col ASc limit ? offSET ?) AS tmp');

    PREPARE stmt_first_quartile FROM @sql;
    EXECUTE stmt_first_quartile USING @lim,@ofs;
    DEALLOCATE PREPARE stmt_first_quartile;
    SET first_quartile = @first_quartile;
END//
delimiter ;

第三四分位数

第一四分位数の出し方とほとんど同じです

DROP PROCEDURE IF EXISTS select_third_quartile;
delimiter //
CREATE PROCEDURE SELECT_third_quartile(IN col text, IN tablename text, IN median decimal(20,2), OUT third_quartile decimal(20,2))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @third_quartile_row_count = NULL;
    BEGIN
        SET @row_sql = concat('SELECT count(*) into @row FROM ',@tablename,' WHERE ',@col,' > ',@median);
        PREPARE row_counter FROM @row_sql;
        EXECUTE row_counter;
        DEALLOCATE PREPARE row_counter;
        SET @row_count = @row;
    END;
    SET @ofs = CAST((SELECT CASE WHEN @row_count % 2 = 0 THEN floor(@third_quartile_row_count/2) -1 else floor(@third_quartile_row_count/2) END )AS SIGNED);
    SET @lim = (SELECT CASE WHEN @third_quartile_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @sql = concat('SELECT avg(col) into @third_quartile FROM (SELECT ', @col,' AS col FROM ',@tablename,' WHERE ',@col,' > ',median,' ORDER BY col ASc limit ? offSET ?) AS tmp');
    PREPARE stmt_third_quartile FROM @sql;
    EXECUTE stmt_third_quartile USING @lim,@ofs;
    DEALLOCATE PREPARE stmt_third_quartile;
    SET third_quartile = @third_quartile;
END//
delimiter ;

相関係数

相関係数の計算式をsqlに置き換えています。

DROP PROCEDURE IF EXISTS select_corr;
delimiter //
CREATE PROCEDURE select_corr(IN first_col text, IN second_col text, IN tablename text, OUT corr_value decimal(20,5))
BEGIN
    SET @col1 = first_col;
    SET @col2 = second_col;
    SET @avg_sql = concat('SELECT avg(',@col1,'),avg(',@col2,') into @s, @t FROM ',tablename);
    PREPARE stmt_avg FROM @avg_sql;
    EXECUTE stmt_avg ;
    DEALLOCATE PREPARE stmt_avg;

    SET @corr_sql = concat('SELECT (sum((',@col1,' - @s) * (',@col2,' - @t))/count(*))/ (stddev_pop(',@col1,') * stddev_pop(',@col2,'))  into  @corr FROM ',tablename);
    PREPARE stmt_corr FROM @corr_sql;
    EXECUTE stmt_corr ;
    DEALLOCATE PREPARE stmt_corr;
    SET corr_value = round(@corr,5);
END//
delimiter ;

出力

テスト用のデータを当てはめて計算します。

#最大、最小、平均、標準偏差、標本数をセット
SELECT max(F),min(F),avg(F), stddev(F),count(*) into @max, @min, @ave, @std,@cnt FROM RFMdata;

#中央値、四分位を取得
#(カラム名,テーブル名, 出力したい値をセットする変数)
CALL select_median("F", "RFMdata", @median);

#(カラム名,テーブル名,中央値 ,出力したい値をセットする変数)
CALL select_first_quartile("F", "RFMdata",@median, @first_quartile);
CALL select_third_quartile("F", "RFMdata",@median, @third_quartile);

#(相関を見たい列1, 相関を見たい列2, テーブル名, 出力したい値をセットする変数)
CALL select_corr("F", "M", "RFMdata", @corr);

#表示
SELECT @min,@first_quartile, @median, @ave, @third_quartile, @max,@std,@cnt,@corr;

おわりに

ひとまずそれぞれの計算を実行できるようになりました。
作成したプロシージャをCALLするプロシージャを作れば、さらにまとめて出力も可能だと思います。
また、今回はプロシージャを作成したスキーマ上でなければ実行ができないので、
用意されている関数のようにどこでも使えないのは課題となりました。（UDF勉強してみる）　

先にも書きましたが、RやPython(numpy,pandas)であればこんな面倒なことはせずとも用意されている関数でさっと出すことができます。
しかしながら、今回このように作成することにより、プロシージャの挙動やどうやって計算しているのかと、それぞれの数式の確認もできたので良かったかなと思います。
こっちの書き方の方がいいよ、などありましたら是非ご教授いただけますと幸いです！

参考

secure-file-priv
https://qiita.com/bohebohechan/items/207e87786b1e30f60abe

元データ
https://www.kaggle.com/carrie1/ecommerce-data

RFM
https://www.kaggle.com/abdulmeral/rfm-analysis-for-successful-customer-segmentation

中央値
https://qiita.com/nkojima/items/2c483d4ddbdb29439c87

相関係数
https://sci-pursuit.com/math/statistics/correlation-coefficient.html

ストアドファンクション内では動的にSQL実行ができない
http://www.mysql.ru/docs/mysql-man-5.1-en/restrictions.html#:~:text=SQL%20prepared%20statements%20(%20PREPARE%20%2C%20EXECUTE,strings%20and%20then%20execute%20them).

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T17:01:31+09:00

AWS Aurora（MySQL）のスロークエリログめも

概要

Aurora（MySQL）でスロークエリをテーブル（mysql.slow_log）に記録し確認できる

スロークエリログがテーブルに記録されるようにする

DBパラメータグループで以下を設定

パラメータ名 設定値 説明
log_output 1 スロークエリログを有効にするかどうか
long_query_time 5 ログに記録されるクエリの最短実行時間（秒）。任意の値を設定する
log_output TABLE ログの出力先（TABLE or FILE）

参考: MySQL データベースログファイル - Amazon Relational Database Service

スロークエリログを確認する

SELECT * FROM mysql.slow_log;

スロークエリログをローテーションする

放っておくと無限にスロークエリログがテーブルに溜まっていくので、ログが多くなってきたらローテーションすると良い。

↓のプロシージャを実行するとログがローテーションされる

CALL mysql.rds_rotate_slow_log;
  • 現在のログテーブル（slow_log）がバックアップのログテーブル（slow_log_backup）にコピーされ、現在のログテーブルは空っぽになる。
  • バックアップのログテーブル（slow_log_backup）がすでに存在する場合は、現在のログテーブルをバックアップにコピーする前に、削除される。

参考: mysql.rds_rotate_slow_log - Amazon Relational Database Service

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T14:26:29+09:00

Laravelで画像をアップロードし、パスをDBに保存する方法

はじめに

Laravelで画像をアップロードし、DBに保存する方法について解説していきます。

まず、今回は画像はプロジェクト内のディレクトリに保存をし、画像のパスをデータベース（以下DB）に保存していきます。
データベースに画像を保存することもできますが、その場合はサイズが大きくなってしまい処理に時間がかかってしまいます。
そのため、画像のパスのみをDBに保存するのが主流のようです。

また、今回はLaravelのプロジェクトは作成し、DBへ接続済であることを前提に進めていくのでご了承ください。

-各バージョン
-laravel 6.x
-PHP 7.4.9
-mySQL 5.7.30

モデルとテーブルを作成する

今回は読書の記録をするアプリを推定して作っていきます。データはシンプルに画像、本の題名、著者名にしたいと思います。

早速、モデルとテーブルをコマンドで作成します。

php artisan make:model Book -m

上記のコマンドを実行すると、Bookモデルとbooksテーブルが作成されます。

続いて、booksテーブルにDBに保存する項目を入力していきます。

books_table_php
public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title', 100);
            $table->string('author', 100);
            $table->string('image');
            $table->timestamps();
        });
    }

そのままマイグレーションします。

php artisan migrate

問題なくマイグレーションできれば、DBにbooksというテーブルが作成されているはずです。

ビューを作成する

続いて、入力フォームを作っていきます。

upload.blade.php
<form method="post" action="{{ route('upload_image') }}" enctype="multipart/form-data">
   @csrf
   <input type="file" name="image">
   <label for="title">題名</label>     
   <input id="title" type="text" name="title">
   <label for="author">作者</label>     
   <input id="author" type="text" name="author">
   <input type="submit" value="登録">
</form>

まず、画像ファイルをアップデートするには必ずformにenctype="multipart/form-dataが必要になります。
また、@csrfはCSRFトークンというもので、外部からの悪意のある攻撃から守るための記述であり、Laravelのフォームには記述が必須となっています。

input内を見ていきます。
画像をアップロードする場合はtype属性をfileに指定します。そしてname属性には先ほどbooks_table_phpで指定したカラム名を指定します。

コントローラーを作成する

まずはコマンドでコントローラーを作成します。

php artisan make:controller BookController

BookControllerが作成されたら、利用するモデル名と、使用するヘルパ関数名を冒頭に記述します。もちろん、あとからその都度記述することも可能です。

BookController.php
use App\Book;
use Illuminate\Support\Str;

そして、画像を保存するための処理を書いていきます。

php
public function store(Stock $request)
  {
      $book = new Book();

      $book->title = $request->input('title');
      $book->author = $request->input('author');

       // 保存されているデータを$formに格納する
       $form = $request->all();
       //　もし$formにimageデータがあったら
       if (isset($form['image'])) {
         // $fileにイメージデータを格納する
         $file = $request->file('image');
         // getClientOrientalExtension()でファイルの拡張子を取得する
         $extension = $file->getClientOriginalExtension();
         $file_token = Str::random(32);
         $filename = $file_token . '.' . $extension;
         // 表示を行うときに画像名が必要になるため、ファイル名を再設定
         $form['image'] = $filename;
         $file->move('uploads/books', $filename);
        }
        $book->save();

        return redirect('home');
  }

コードにコメントを書いていないところを解説していきます。

まず、getClientOriginalExtension()でファイルの拡張子を取得した後に、Str::random()でランダムな文字列を取得します。Str::random()は(　)内に指定された文字数分、ランダムな文字列を作成することができる関数です。今回は32文字を指定しました。
こうして$filenameには画像のパスが「ランダムな32文字 . 拡張子」の状態で保存されていることになります。

続いてこちらを、move()で任意のファイルに保存するように設定します。move('指定したファイル', 保存するファイル名)の順番で記述をすると、Publicフォルダ内に指定したファイルが自動生成され、アップロードした画像が保存されるようになります。

最後に save()メソッドでデータを保存し、今回はredirect()でhomeに戻るように指定しています。

ルーティングを設定する

upload.blade.php内でformのactionをroute('upload_image')と指定したので、今回はupload_imageをname()で指定し、アクセスをしたら、BookControllerのStoreアクションにつながるように記述します。

web.php
Route::post('/upload', 'BookController@store')->name('upload_image');

これで完成です！

さいごに

今回はLaravelで画像をアップロードし、DBに保存する方法について解説しました。
次回以降、保存したデータを表示する方法や編集方法に関しても解説していけたらと思います！

参考

https://youtu.be/il9gsKH9V-8

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T10:15:06+09:00

【Mysql】接続できないエラー原因と対処まとめ

はじめに

mysqlに接続できないエラーに何度も直面し、その度調べていたので、今回は備忘録としてまとめてみました。
参考になれば幸いです

(1)sockファイルでのエラー

エラー内容

$ mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

原因

mysql.sockファイルがないこと

対処

# sockファイルを作成する
$ sudo touch /tmp/mysql.sock

# 動作確認→別のエラーが発生している
$ mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (38)

原因

mysql.sockファイルが入っているディレクトリの権限が原因

対処

# 権限変更する
$ sudo chown mysql:mysql /tmp

# サーバーを立ち上げる（ここ重要）
$ sudo mysql.server start

# 動作確認
$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22 Homebrew

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

完了！

(2)PIDファイルに関するエラー

発生事例とエラー内容

mysqlサーバーを再起動しようとしたら下記エラーが発生。

$ mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/var/mysql/****.pid).

原因

  1. pidファイルがない
  2. pidファイルがあるフォルダの権限がおかしい
# 「/usr/local/var/mysql」にpidファイルがあるか確認
$ ls /usr/local/var/mysql
#=> ファイル一覧が表示されるのでpidファイルがあるか確認する

対処

1. 「/usr/local/var/mysql」にpidファイルを作成

# pidファイルを作成
$ touch /usr/local/var/mysql/*****.local.pid

※「*****」に入る文字はマシンのホスト名
※ホスト名はuname -nで確認できる。

2. pidファイルがあるフォルダの権限を変更する

# /user/local/var/mysql以下のファイルの所有者をすべて_mysqlにする
$ sudo chown -R _mysql:_mysql /usr/local/var/mysql/

# 再度コマンドを打つと動作する
$ sudo mysql.server restart
Password:
Shutting down MySQL
. SUCCESS!
Starting MySQL
. SUCCESS!

完了！！

参考

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T00:29:20+09:00

docker-composeでDjangoからMysqlコンテナに繋がらなかったのを何とか解決してみた時の話

Djangoの開発環境をDockerで作ろうとしたところ、どうしても一つだけうまくいかなかったのがDjangoからMysqlへログインするところでした。

かれこれ3日くらいずっと悩まされ続けたのがこれです。。

django.db.utils.OperationalError: (2005, "Unknown MySQL server host 'mysql' (11001)")

色々問題を切り分けてみて、mysqlコンテナのIPの名前解決はうまくいってそうだし、portsとexposeの部分も特にミスっていないし、、とすると問題はMySQLコンテナ内に入った後の挙動。

日本語のサイトにはいい情報が見つからなかったのですが、英語のドキュメントを色々あさっているうちにようやく解決できたのでまとめてみました。

環境

  • Windows 10
  • Docker for Windows

前提

  • 既にpythonコンテナの中に入ってDjangoのプロジェクトを生成していること

ディレクトリ構成

proj/
  ├ docker/
  │   ├ nginx/
  │   │  └ default.conf
  │   └ python/
  │      ├ Dockerfile
  │      └ requirements.txt
  ├ django_proj (以下略)
  ├ docker-compose.yml
  └ manage.py

方法

docker-compose.ymlのmysqlの設定に注意！（それだけ！でした。笑）

docker-compose.yml
version: "3.9"

services:
  python:
    build: ./docker/python
    command: gunicorn composeexample.wsgi:application --bind 0.0.0.0:8000 --reload
    volumes:
      - .:/code
    expose:
      - "8000"

  nginx:
    image: nginx:1.19
    volumes:
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf
      - ./static:/etc/nginx/static
    ports:
      - "80:80"
    depends_on:
      - python

  mysql:
    image: mysql:5.6
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
      - ./docker/db/data:/var/lib/mysql
    expose:
      - "3306"
    environment:
      - MYSQL_ROOT_USER=root
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_ROOT_HOST=%           # この1行が今回のポイントでした

  phpmyadmin:
    image: phpmyadmin
    ports:
      - "8080:80"
    environment:
      - PMA_ARBITRARY=1

MySQL公式ページに以下の記載がありました。

MYSQL_ROOT_HOST: By default, MySQL creates the 'root'@'localhost' account. This account can only be connected to from inside the container as described in Connecting to MySQL Server from within the Container. To allow root connections from other hosts, set this environment variable. For example, the value 172.17.0.1, which is the default Docker gateway IP, allows connections from the host machine that runs the container. The option accepts only one entry, but wildcards are allowed (for example, MYSQL_ROOT_HOST=172...* or MYSQL_ROOT_HOST=%).

デフォルトではroot@localhostでアカウントを作ってしまうので、rootユーザーとしてログインできるのはlocalhost、すなわちdockerコンテナ内からのみとなってしまうようです。

なので、MYSQL_ROOT_HOST=%の記述を追加しておけば、どのIPアドレスからもrootユーザーとしてログインできるようになるので、違うコンテナからのログインも受け付けられるということなんだと思います。（たぶん）

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T00:29:20+09:00

docker-composeでDjangoからMysqlに繋がらない

Djangoの開発環境をDockerで作ろうとしたところ、どうしても一つだけうまくいかなかったのがDjangoからMysqlへログインするところでした。

かれこれ3日くらいずっと悩まされ続けたのがこれです。。

django.db.utils.OperationalError: (2005, "Unknown MySQL server host 'mysql' (11001)")

色々問題を切り分けてみて、mysqlコンテナのIPの名前解決はうまくいってそうだし、portsとexposeの部分も特にミスっていないし、、とすると問題はMySQLコンテナ内に入った後の挙動。

日本語のサイトにはいい情報が見つからなかったのですが、英語のドキュメントを色々あさってようやく解決できたのでまとめてみました。

環境

  • Windows 10
  • Docker for Windows

前提

  • 既にpythonコンテナの中に入ってDjangoのプロジェクトを生成していること

ディレクトリ構成

proj/
  ├ docker/
  │   ├ nginx/
  │   │  └ default.conf
  │   └ python/
  │      ├ Dockerfile
  │      └ requirements.txt
  ├ django_proj (以下略)
  ├ docker-compose.yml
  └ manage.py

解決方法

docker-compose.ymlのmysqlの設定に注意！（それだけ！でした。笑）

docker-compose.yml
version: "3.9"

services:
  python:
    build: ./docker/python
    command: gunicorn composeexample.wsgi:application --bind 0.0.0.0:8000 --reload
    volumes:
      - .:/code
    expose:
      - "8000"

  nginx:
    image: nginx:1.19
    volumes:
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf
      - ./static:/etc/nginx/static
    ports:
      - "80:80"
    depends_on:
      - python

  mysql:
    image: mysql:5.6
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
      - ./docker/db/data:/var/lib/mysql
    expose:
      - "3306"
    environment:
      - MYSQL_ROOT_USER=root
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_ROOT_HOST=%           # この1行が今回のポイントでした

  phpmyadmin:
    image: phpmyadmin
    ports:
      - "8080:80"
    environment:
      - PMA_ARBITRARY=1

MySQL公式ページに以下の記載がありました。

MYSQL_ROOT_HOST: By default, MySQL creates the 'root'@'localhost' account. This account can only be connected to from inside the container as described in Connecting to MySQL Server from within the Container. To allow root connections from other hosts, set this environment variable. For example, the value 172.17.0.1, which is the default Docker gateway IP, allows connections from the host machine that runs the container. The option accepts only one entry, but wildcards are allowed (for example, MYSQL_ROOT_HOST=172...* or MYSQL_ROOT_HOST=%).

デフォルトではroot@localhostでアカウントを作ってしまうので、rootユーザーとしてログインできるのはlocalhost、すなわちdockerコンテナ内からのみとなってしまうようです。

なので、MYSQL_ROOT_HOST=%の記述を追加しておけば、どのIPアドレスからもrootユーザーとしてログインできるようになるので、違うコンテナからのログインも受け付けられるということなんだと思います。（たぶん）

参考

https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/docker-mysql-more-topics.html#docker_var_mysql-root-host

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2021-01-18T00:29:20+09:00

docker-compose使用時にDjangoからMySQLにログインできない

Djangoの開発環境をDockerで作ろうとしたところ、どうしても一つだけうまくいかなかったのがDjangoからMySQLへログインするところでした。

かれこれ3日くらいずっと悩まされ続けたのがこれです。。

django.db.utils.OperationalError: (2005, "Unknown MySQL server host 'mysql' (11001)")

色々問題を切り分けてみて、mysqlコンテナのIPの名前解決はうまくいってそうだし、portsとexposeの部分も特にミスっていないし、、とすると問題はMySQLコンテナ内に入った後の挙動。

日本語のサイトにはいい情報が見つからなかったのですが、英語のドキュメントを色々探してようやく解決できたのでまとめてみました。

環境

  • Windows 10
  • Docker for Windows

前提

  • 既にpythonコンテナの中に入ってDjangoのプロジェクトを生成していること

ディレクトリ構成

proj/
  ├ docker/
  │   ├ nginx/
  │   │  └ default.conf
  │   └ python/
  │      ├ Dockerfile
  │      └ requirements.txt
  ├ django_proj (以下略)
  ├ docker-compose.yml
  └ manage.py

解決方法

docker-compose.ymlのmysqlの設定に注意！（それだけ！でした。笑）

docker-compose.yml
version: "3.9"

services:
  python:
    build: ./docker/python
    command: gunicorn composeexample.wsgi:application --bind 0.0.0.0:8000 --reload
    volumes:
      - .:/code
    expose:
      - "8000"

  nginx:
    image: nginx:1.19
    volumes:
      - ./docker/nginx/default.conf:/etc/nginx/conf.d/default.conf
      - ./static:/etc/nginx/static
    ports:
      - "80:80"
    depends_on:
      - python

  mysql:
    image: mysql:5.6
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
      - ./docker/db/data:/var/lib/mysql
    expose:
      - "3306"
    environment:
      - MYSQL_ROOT_USER=root
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_ROOT_HOST=%           # この1行が今回のポイントでした

  phpmyadmin:
    image: phpmyadmin
    ports:
      - "8080:80"
    environment:
      - PMA_ARBITRARY=1

MySQL公式ページに以下の記載がありました。

MYSQL_ROOT_HOST: By default, MySQL creates the 'root'@'localhost' account. This account can only be connected to from inside the container as described in Connecting to MySQL Server from within the Container. To allow root connections from other hosts, set this environment variable. For example, the value 172.17.0.1, which is the default Docker gateway IP, allows connections from the host machine that runs the container. The option accepts only one entry, but wildcards are allowed (for example, MYSQL_ROOT_HOST=172...* or MYSQL_ROOT_HOST=%).

デフォルトではroot@localhostでアカウントを作ってしまうので、rootユーザーとしてログインできるのはlocalhost、すなわちdockerコンテナ内からのみとなってしまうようです。

なので、MYSQL_ROOT_HOST=%の記述を追加しておけば、どのIPアドレスからもrootユーザーとしてログインできるようになるので、違うコンテナからのログインも受け付けられるということなんだと思います。（たぶん）

参考

https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/docker-mysql-more-topics.html#docker_var_mysql-root-host

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧