カテゴリー: mysql

Dockerを使ってMySQL 8.0をパスワードなしでローカル接続する
以下の手順で、MySQL 8.0をDockerで起動し、パスワードなしでローカル接続できるように設定する方法を説明します。

手順 1: Dockerコンテナの起動

まず、パスワードなしでMySQLコンテナを起動します。MYSQL_ALLOW_EMPTY_PASSWORD環境変数をyesに設定します。
```
docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d -p 3306:3306 mysql:8.0
```
このコマンドは、以下のことを行います：
1. mysqlという名前のコンテナを作成
2. MySQLのルートユーザーのパスワードを空に設定
3. コンテナをバックグラウンドで実行
4. ローカルの3306ポートをコンテナの3306ポートにマッピング
手順 2: MySQLクライアントで接続

次に、ローカルのMySQLクライアントを使ってMySQLサーバーに接続します。パスワードなしで接続するために、-pオプションを省略します。
```
mysql -h 127.0.0.1 -P 3306 -u root
```
注： mysql のコンテナが立ち上がるまでの間
```
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2
```
というエラーが表示されます。その際数秒待って再度接続を試みてください。

トラブルシューティング

コンテナが正しく起動しているか確認:
```
docker ps -a
```
コンテナのログを確認:
```
docker logs mysql
```
MySQLサーバーが正しく起動していない場合、ログを確認してエラーメッセージをチェックし、適切な対策を講じてください。
2024年6月5日
SQL: Null値の扱い方 – COALESCE関数を使った実践的なガイド
データベースを操作するとき、null値をどのように扱うかは一般的な問題です。実際、Nullはデータが存在しないことを示し、これが意図的である場合もあれば、データが不足しているためである場合もあります。したがって、null値を適切に処理することはデータ管理の重要な部分です。今日は、SQLのCOALESCE関数を用いたnull値の扱い方について解説します。COALESCE関数は、引数のリストから最初の非null値を返します。

COALESCE関数とは？

COALESCE関数は、引数リストから最初の非null値を返すSQLの関数です。COALESCE関数は、以下のように使用します：
```
COALESCE(value1, value2, ..., valueN)
```
value1からvalueNまでの値が順番に評価され、最初の非null値が返されます。すべての値がnullの場合、COALESCE関数はnullを返します。

COALESCE関数の具体的な使用例

例として、以下のテーブルを考えてみましょう。
```
CREATE TABLE Employees (
id INT PRIMARY KEY,
firstName VARCHAR(100),
lastName VARCHAR(100),
email VARCHAR(100)
);
```
このテーブルで、firstNameとlastNameのどちらかがnullの場合に、その代わりに’email not provided’という文字列を表示したいとします。この場合、COALESCE関数を使用できます。
```
SELECT id, COALESCE(firstName, 'email not provided') AS firstName, COALESCE(lastName, 'email not provided') AS lastName FROM Employees;
```
これにより、firstNameまたはlastNameがnullの場合、その値の代わりに’email not provided’と表示されます。

数値データに対するCOALESCE関数の使用

COALESCE関数は数値データにも適用できます。たとえば、ある数値の列がnullの場合、その代わりに0を表示したいとします。この場合もCOALESCE関数を使用できます。
```
SELECT id, COALESCE(salary, 0) AS salary FROM Employees;
```
これにより、salaryがnullの場合、その値の代わりに0が表示されます。

まとめ

COALESCE関数はSQLの強力なツールであり、null値を簡単に扱うことができます。適切なデフォルト値を設定することで、データの一貫性を維持しつつ、欠損データを適切に扱うことができます。SQLのクエリにCOALESCE関数を組み込むことで、データ分析やレポート生成のプロセスをよりスムーズに、より効率的に進めることが可能になります。また、特定のデータタイプに対して特定のデフォルト値を設定することも可能です。例えば、数値型がnullの場合には0を、文字列型がnullの場合には空文字列（”）をデフォルト値とするなど、データの種類により異なる扱い方をすることができます。
```
SELECT
COALESCE(NENDO, '') AS NENDO,
COALESCE(HANKI, '') AS HANKI,
COALESCE(OROSI_DAIHYO_CD, '') AS OROSI_DAIHYO_CD,
COALESCE(MSDSH_HANTEI_JISSEKI_NEW, 0) AS MSDSH_HANTEI_JISSEKI_NEW,
FROM YOUR_TABLE;
```
以上のように、COALESCE関数を使用すると、一貫性を保ったままかつ効率的にデータベース内のnull値を扱うことができます。COALESCE関数のこのような特性は、データベース操作の質を向上させ、データ解析の正確性を保つ上で重要な役割を果たします。

これで、SQLのCOALESCE関数についての基本的なガイドと具体的な使用例を提供しました。この記事が、日々のデータベース操作で遭遇する可能性のあるnull値の問題に対する理解と解決の助けになることを願っています。COALESCE関数をうまく使いこなして、より洗練されたデータ処理を実現していけたら幸いです。
2023年5月23日

mysql のコンテナをすぐに使う

何も考えず mysql をすぐに用意する方法

docker run --name instant-mysql -v ${PWD}/data:/var/lib/mysql \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e TZ=Asia/Tokyo \
-d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

接続方法は以下の通り

mysql -u root -ppassword -h 127.0.0.1

2021年11月12日

M1 mac で Dockerを使ってMySQL 5.7を外部から接続できるように設定する
Dockerを使ってMySQL 5.7を外部から接続できるように設定する

Dockerを使ってMySQLを簡単に起動する際、外部からの接続設定にちょっとしたコツが必要です。

この記事では、その手順をシンプルにまとめています。

1. 概要

基本的には、MySQLの設定を変更して、どのホストからでも接続を受け入れるようにする必要があります。加えて、Dockerコンテナ内のMySQLユーザー設定も変更する必要があります。

2. 手順

以下のコードを実行します:

git clone https://github.com/GitSumito/CodeArsenal.git cd CodeArsenal/mysql-external-access sh ./main.sh

3. 詳細な手順の説明

custom_my.cnf という設定ファイルを作成し、MySQLがどのホストからでも接続を受け入れるように bind-address を設定します。

DockerでMySQLを起動します。この際、上で作成した custom_my.cnf をMySQLの設定ディレクトリにマウントします。

MySQLが完全に起動するのを待ちます（この例では30秒待機していますが、環境によっては調整が必要です）。

docker exec を使用してMySQLコンテナ内でユーザー設定を行います。この例では、任意のホストからの接続を受け入れるrootユーザーを作成しています。

4. 注意点

この設定は開発やテスト環境での利用を想定しています。セキュリティの観点から、本番環境での利用は推奨しません。特に、パスワードなしのrootユーザーを外部から接続できるようにする設定は、公開環境での利用は避けてください。
2021年8月19日

mysql でストアドプロシージャを登録・変更・削除する

ストアドプロシージャはサーバーに格納できる一連の SQL です。これが一度登録されるとクライアントは個々のステートメントを繰り返し発行す必要はなくなります。

ユーザー作成

ユーザーに権限を付与する

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `database`.* TO 'user'@'10.1.%';

ひとつひとつ説明する。

SELECT, INSERT, UPDATE, DELETE これは普通の DML なので省略する。

CREATE ROUTINE … ストアドプロシージャを作成する権限

ALTER ROUTINE … ストアドプロシージャを削除・変更する権限

EXECUTE … ストアドプロシージャを実行する権限

ストアドプロシージャを登録する

ストアドプロシージャーを実行する際、 delimiter を一時的に変更すると登録しやすい。

delimiter // 
create procedure sample()
    -> begin
    -> select count(*) from user;
    -> end
    -> //

delimiter ;

ストアドプロシージャを確認する

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS;
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db               | Name    | Type      | Definer            | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| database | sample  | PROCEDURE | root@%             | 2021-06-29 14:47:44 | 2021-06-29 14:47:44 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
| database | sample1 | PROCEDURE | user@10.1.% | 2021-06-29 14:57:08 | 2021-06-29 14:57:08 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
+------------------+---------+-----------+--------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

ストアドプロシージャを実行する

call sample1();
+----------+
| count(*) |
+----------+
|   188376 |
+----------+
1 row in set (0.00 sec)

2021年6月30日

コンテナ内の mysql にリクエストされたクエリを確認する

開発している際コンテナの mysql にどのようなクエリ走ったのか確認したいことがある。

スロークエリを 0秒に設定することで、DB にリクエストされた全クエリを確認することができる。

mysql> show variables like 'slow%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/5c364d250748-slow.log |
+---------------------+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

slow_query_log が OFF になっている場合、出力されない。

set global slow_query_log_file = '/tmp/mysql-slow.log';
set global long_query_time = 0;
set global slow_query_log = ON;

上記コマンドを実行し、再度確認すると、スロークエリが出力されるようになってる。

mysql> show variables like 'slow%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_launch_time    | 2                   |
| slow_query_log      | ON                  |
| slow_query_log_file | /tmp/mysql-slow.log |
+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

スロークエリが 0秒に設定されたか確認するのは

show global variables like 'long_query_time';

コマンドを実行する

mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql>

結果


SET timestamp=1624328575;
INSERT INTO `xxx_transactions` (`id`,`operation`,`request`,`requested_at`,`response`,`responsed_at`,`metadata`,`status`,`failure_code`,`failure_message`,`created_by`,`created_at`) VALUES (105,'request',NULL,NULL,NULL,NULL,NULL,'succeeded',NULL,NULL,'123456789','2021-06-22 02:22:55.1492209');

無事流れたクエリを確認することができた。

2021年6月22日

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
はじめに

パスワードを更新を促されるメッセージ。パスワードポリシーに則った形で変更するパスワードを必要がある。

パスワード変更方法
```
set password for root@localhost=password('hogeHoge@Hog123');
```
結果以下の通りなれば成功
```
mysql> set password for root@localhost=password('hogeHoge@Hog123');
Query OK, 0 rows affected, 1 warning (0.05 sec)
```
参考情報

https://qiita.com/RyochanUedasan/items/9a49309019475536d22a
2019年11月29日
redash パラメータを動的に変えてクエリを実行する
Notice!

以下の記事で redash のみで行う方法をまとめております。
こちらの記事をご覧ください。

https://tsukada.sumito.jp/2020/02/28/redash-api-query-parameter/

はじめに（本記事はサードパーティを利用した方法です）

redashはとても便利だが、パラメータを動的に変えてクエリを投げる様な事はオフィシャルにはできない

しかし、それもサードパーティを利用し、少し手を加える事で実現可能になる。

検証環境の構築

サンプルをダウンロード

サンプルのredashと、適当なデータが入ったmysqlをダウンロード
```
git clone https://github.com/GitSumito/redash-blue
```
セットアップ
```
docker-compose run --rm server create_db docker-compose up
```
http://localhost/setup

へ接続するとセットアップ画面が表示されるので入力する

ログインすると、右上のメニューから　Edit Profile を選択する

API keyが表示されるのでひかえる

データソースにmysqlを追加する

設定は以下の通り

この時、保存した際のURLを確認する

/data_sources/1　とある。

これがデータソース番号となる。今回登録したデータソースは1番ということがこれでわかる。

クエリを書く
```
select * from city where CountryCode = "{{code}}"
```
このクエリを書くと、下にテキストボックスが表示される

これでExecuteボタンを押すと、where CountryCode = “JPN” が実施される

クエリを保存すると、以下の様なURLになっている

後ろにパラメータがついているがそれは無視すると、クエリ番号1番となっていることがわかる。

mozilla/redash_client をインストールする

https://github.com/mozilla/redash_client

mozillaでredash clientというものが存在している。

これを ariarijp さんが改修してくれていた。神！

（今現在masterに取りこめられていないが、非常に便利な機能だ）

差分が公開されているので、これを元にオリジナルのmozillaを一部改修する

https://github.com/mozilla/redash_client/pull/70/files#diff-80f7450d3a8b47a2d0a622873d1a67fe

これにより、動的クエリを受け付けることができるようになる
```
from pprint import pprint

import os
import pystache

from redash_client.client import RedashClient

api_key = os.environ["REDASH_API_KEY"]

client = RedashClient(api_key)
client.BASE_URL = 'http://127.0.0.1/'
client.API_BASE_URL = client.BASE_URL + 'api/'

query = client.get_query(1)
sql = pystache.render(query['query'], {
  'code': 'JPN',
})

result = client.get_query_results(sql, query['data_source_id'])

pprint(result, width=160)
```
その後、環境変数に

REDASH_API_KEY

をセットする
```
export REDASH_API_KEY=*********************
```
実行結果
```
$ python3 mozilla.py 
[{'CountryCode': 'JPN', 'District': 'Tokyo-to', 'ID': 1532, 'Name': 'Tokyo', 'Population': 7980230},
 {'CountryCode': 'JPN', 'District': 'Kanagawa', 'ID': 1533, 'Name': 'Jokohama [Yokohama]', 'Population': 3339594},
 {'CountryCode': 'JPN', 'District': 'Osaka', 'ID': 1534, 'Name': 'Osaka', 'Population': 2595674},
 {'CountryCode': 'JPN', 'District': 'Aichi', 'ID': 1535, 'Name': 'Nagoya', 'Population': 2154376},
 {'CountryCode': 'JPN', 'District': 'Hokkaido', 'ID': 1536, 'Name': 'Sapporo', 'Population': 1790886},
 {'CountryCode': 'JPN', 'District': 'Kyoto', 'ID': 1537, 'Name': 'Kioto', 'Population': 1461974},
 {'CountryCode': 'JPN', 'District': 'Hyogo', 'ID': 1538, 'Name': 'Kobe', 'Population': 1425139},
```
scriptのcodeを変えて実行
```
$ python3 mozilla.py 
[{'CountryCode': 'USA', 'District': 'New York', 'ID': 3793, 'Name': 'New York', 'Population': 8008278},
 {'CountryCode': 'USA', 'District': 'California', 'ID': 3794, 'Name': 'Los Angeles', 'Population': 3694820},
 {'CountryCode': 'USA', 'District': 'Illinois', 'ID': 3795, 'Name': 'Chicago', 'Population': 2896016},
 {'CountryCode': 'USA', 'District': 'Texas', 'ID': 3796, 'Name': 'Houston', 'Population': 1953631},
 {'CountryCode': 'USA', 'District': 'Pennsylvania', 'ID': 3797, 'Name': 'Philadelphia', 'Population': 1517550},
 {'CountryCode': 'USA', 'District': 'Arizona', 'ID': 3798, 'Name': 'Phoenix', 'Population': 1321045},
```
取得結果が変わった。

色々活用できる場がありそうだ。
2019年4月4日
ERROR 1064 (42000) at line 101: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1
はじめに

mysqldumpで取得した結果を、そのままmysqlへ流しこもうとしたらエラーになった。

実施したコマンド

変数には適宜設定値が入る。

やりたいことはmysqldumpでデータを取得し、標準出力で別のサーバへ流し込む。
```
mysqldump -u$dbbackupuser $dbbackupname $table -h$dbbackuporiginal -p$dbbackuppass | mysql -u$dbrestoreuser -h$dbrestoreserver -p$dbrestorepass $dbrestorename 
```
発生したエラー
```
ERROR 1064 (42000) at line 101: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
```
原因

mysqldumpコマンドはデフォルトでmysqlの独自構文が入ってしまうことがあり、微妙なmysqlのversionの差異がこの独自構文を受け付けないことがある。

対処

ANSIフォーマットで出力させるオプション “` –compatible=ansi “` を追加する

after
```
mysqldump --compatible=ansi -u$dbbackupuser $dbbackupname $table -h$dbbackuporiginal -p$dbbackuppass | mysql -u$dbrestoreuser -h$dbrestoreserver -p$dbrestorepass $dbrestorename 
```
2019年3月26日
mysql 5.7で実行された全クエリをログに出力する
はじめに

どのようなクエリが実施されたのかトレースする時に使う

設定

/etc/my.cnfの設定変更
```
general_log=ON
general_log_file=/var/log/mysqld-query.log
```
general_logの記載を追加する

パーミッション設定
```
touch /var/log/mysqld-query.log
chown mysql:mysql /var/log/mysqld-query.log
chmod 640 /var/log/mysqld-query.log
```
上記設定を入れたら再起動する
```
systemctl restart mysqld
```
確認
```
cat /var/log/mysqld-query.log

2019-03-14T05:39:18.710354Z	    3 Query	INSERT INTO s_bookmark (  id, bookmark_at, 
```
2019年3月14日

カテゴリー: mysql

手順 1: Dockerコンテナの起動

手順 2: MySQLクライアントで接続

トラブルシューティング

COALESCE関数とは？

COALESCE関数の具体的な使用例

数値データに対するCOALESCE関数の使用

まとめ

Dockerを使ってMySQL 5.7を外部から接続できるように設定する

1. 概要

2. 手順

3. 詳細な手順の説明

4. 注意点

ユーザー作成

ストアドプロシージャを登録する

ストアドプロシージャを確認する

ストアドプロシージャを実行する

はじめに

パスワード変更方法

参考情報

Notice!

はじめに（本記事はサードパーティを利用した方法です）

検証環境の構築

サンプルをダウンロード

セットアップ

データソースにmysqlを追加する

クエリを書く

mozilla/redash_client をインストールする

はじめに

実施したコマンド

発生したエラー

原因

対処

はじめに

設定

パーミッション設定

確認