これで克服！　MySQLでつまづくポイント

オープンソースで1位、全体で2位のシェアを持つMySQL

MySQLはオープンソースのデータベースでシェア1位、データベース全体でも有償のOracleに次ぐ2位で、色々なシステムで使用されているデータベースです。

このWebサイトはWordPressというソフトウェアを使ってWebサイトの画面を作ったり、記事を書いたりしていますが、WordPressで使用されているデータベースもMySQLなのです。WordPressは数多くのWebサイトで使われていますので、MySQLを自分では使ったことが無いという人でもMySQLが使われたシステムを使ったことはあるわけですね。また、有名なところではFacebookがMySQLの最大ユーザーで、何千台ものMySQLクラスターサーバーを運営しているようです。

PostgreSQLに比べて難解なMySQL

ただ、同じくオープンソースのデータベースの中でシェア2位のPostgreSQLに比べて、色々とつまづくところが多いなと感じます。今回、私が担当しているお客さんがMySQLを使ってビッグデータ解析を行いたいということで、そのサポートで色々と自分自身の勉強にもなり、MySQLで陥りやすいハマり場所が分かってきましたので、この記事で紹介していこうと思います。

パスワードが合っているはずなのにログインできない

MySQL8.0のインストール自体はインストーラー通り進めばいいので、そこまでつまづくことは無いと思います。最初のつまづきポイントはログインできないというところでした。rootユーザーのパスワードが合っているはずなのに、何回やってもうまくいかないというもの。

コマンドを見せてもらうと、

mysql.exe -u root -p PASSWORDCode language: DOS .bat (dos)

と打っていたんです。PASSWORDの部分はrootユーザーのパスワードです。パスワードを与えているのに、またパスワード入力が求められ、そこでも正しいパスワードを入れているのにログインに失敗したそうです。

こちらのドキュメントのImportantセクションに買いてあるように、-pでパスワードを与える場合は、

mysql.exe -u root -pPASSWORDCode language: DOS .bat (dos)

とつなげて書く必要があるんです。これ、ハマりますよね。

また、

mysql.exe -u root -pCode language: DOS .bat (dos)

と書いて実行すると、パスワード入力が求められます。このほうが一般的かもしれませんが、CSVファイルを複数インポートするときなどバッチファイルから実行するときは、パスワードを同時に入力してやる必要があるので、その場合は-pPASSWORDとつなげて書くことに注意しましょう。

mysqld.exe？　MySQL80？どっちから起動するの？

最初は動いていたのですが、うまくいかなくなってMySQLのデーモンを起動し直すとき、mysqld.exeを使うのか、MySQL80というWindowsサービスを使うのか、分からなくなるときがあります。

結論としては、同じコマンドオプションを与えればどちらも同じ挙動になるのですが、Windows起動時に自動起動したいのであればWindowsサービスのMySQL80を使ったほうが良いです。

ただ、mysqld.exeとnet start MySQL80またはsc start MySQL80はコマンドオプションが違う可能性があります。例えば、mysqld.exeでは何もオプションを指定していないので、オプションファイル(my.iniかmy.cnf)がデフォルトの優先順のパスから読み込まれます。例えば、%WINDIR%\my.ini(例：C:\Windows\my.ini)があればそれが読み込まれますし、%WINDIR%になくて、C:\my.iniがあればそのオプションファイルが読み込まれます。

つまり、mysqld.exeだと、オプションファイルは上記のテーブルで上の順から見つかったものが読み込まれます。

一方で、MySQL80のWindowsサービスから起動した場合、サービスのプロパティから実行ファイルのパスを確認しておく必要があります。例えば、MySQLのインストーラーからWindowsサービスの登録を行うと、「MySQL80」というサービス名で下記のような実行ファイルで登録されます。

スクリーンショットでは見切れてしまっていますが、以下のようになっていました。

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" MySQL80Code language: DOS .bat (dos)

–defaults-fileでオプションファイルの指定がされていて、ここではC:\ProgramData\MySQL\MySQL Server 8.0\my.iniが読み込まれる設定になっています。つまり、MySQL80のWindowsサービスから起動すると、ProgramDataにあるオプションファイルが読み込まれるので、mysqld.exeを実行するときと挙動が変わります。

ちなみに、mysqld.exe --installでWindowsサービスに登録ができますが、オプションファイルは上記のTable4.1の優先順で読み込まれ、またサービス名は「MySQL」という名前になります。こちらのドキュメントに詳細がありますが、MySQL80という名前で、オプションファイルはC:\ProgramData\MySQL\MySQL Server 8.0\my.iniを使うようにサービス登録したければ以下のように行います。my.iniの絶対パスに空白が含まれるのでクオーテーションかダブルクオーテーションでくくる必要があります。

mysqld.exe --install MySQL80 --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" Code language: DOS .bat (dos)

CSVファイルがアップロードできない

次にハマったのはCSVのアップロードです。PostgreSQLのときは何も変えなくてもできたのですが、MySQLはデフォルトのセキュリティが高めですね。

secure-file-privの設定

ローカルにあるCSVファイルをLoad Data Local Infileでインポートしようとすると、

mysql> LOAD DATA LOCAL INFILE "C:/Data/data1.csv" INTO TABLE public.mytable;Code language: PHP (php)

以下のエラーが出ました。

ERROR 1290 (HY000) at line 1: The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

デフォルトだとファイルのアップロード元がC:/ProgramData/MySQL/MySQL Server 8.0/Uploadsになっていたので、違うフォルダからアップロードしようとするとこのエラーが出てしまうわけです。

my.iniを管理者権限で編集し、secure-file-privを空(“”)にします。

# Secure File Priv.

secure-file-priv=""Code language: Vim Script (vim)

local_infile問題

次にぶち当たったのはlocal_infileの設定です。さて、再び同じクエリを実行すると

mysql> LOAD DATA LOCAL INFILE "C:/Data/data1.csv" INTO TABLE public.mytable;Code language: SQL (Structured Query Language) (sql)

今度は「ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides」のエラーです。サーバー側とクライアント側両方でlocal_infileの設定をしないといけないようです。

まず、サーバー側のlocal_infileの設定を見てみます。

mysql> show global variables like 'local_infile';Code language: SQL (Structured Query Language) (sql)

リターンはこんな感じ。OFFになっていますね。

+---------------+-------+
| Variable_name | Value |
+---------------+-------+

| local_infile | OFF |

+---------------+-------+
1 row in set, 1 warning (0.01 sec)Code language: JavaScript (javascript)

対処法としては、以下のコマンドをmysqld.exe起動後に実行するか、

mysql> set global local_infile=true;Code language: SQL (Structured Query Language) (sql)

my.iniを管理者権限で開き、[mysqld]の中に以下の設定を追記すればOKです。

[mysqld]

local-infileCode language: CSS (css)

my.ini編集後はMySQL80を再起動してください。

実はこれだけでは不十分で、クライアント側でもlocal-infileの設定が必要です。mysqlを実行する際に、--local-infile=1を付けます。

mysql.exe -h localhost -p -u USERNAME --local-infile=1Code language: DOS .bat (dos)

これでCSVファイルのインポートができるはずです。

mysql> LOAD DATA LOCAL INFILE "C:/Data/data1.csv" INTO TABLE public.mytable;Code language: SQL (Structured Query Language) (sql)

データ保存場所を変更する

段々と動くようになってきて、次にぶち当たったのはデータ保存場所を変えること。MySQL80のデフォルトでは保存場所がC:/ProgramData/MySQL/MySQL Server 8.0/Dataになっていますが、ビッグデータをMySQLにインポートするとCドライブが足りなくなってしまうので、Dドライブにしたいというものです。ここでdatadirを変更してデータベースを初期化する必要があります。

まず、datadirはmy.iniを編集します。管理者権限でファイルを開き、デフォルトの設定にコメント(#)を付けて、変えたいDドライブに変更します。ちなみに、Windowsのフォルダパスはバックスラッシュ(\)ですが、my.iniではスラッシュ(/)にするか、バックスラッシュを2つ重ねる(\\)のがお作法です。

#datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
datadir=D:/Data/MySQL_DataCode language: PHP (php)

これでMySQL80のサービスを再起動しようとすると、サービスがすぐに止まってしまいます。

こういうときはログファイルを確認しましょう。datadirの中に<ホスト名>.errというログファイルがあるはずです。上記の場合、D:\Data\MySQL_Data\ホスト名.errがあります。中を見てみると、

2020-10-15T23:45:50.557346Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2020-10-15T23:45:50.557373Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-10-15T23:45:50.557420Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.21) starting as process 14508
2020-10-15T23:45:50.576971Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2020-10-15T23:45:50.577498Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

2020-10-15T23:45:50.577900Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-10-15T23:45:50.578677Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.21) MySQL Community Server -GPL.
Code language: JavaScript (javascript)

ハイライトした行を見るとどうやら初期化に失敗しているようです。

mysqld.exe –initializeで初期化

Dドライブに空のフォルダを作ったら、初期化をする必要があります。以下のコマンドで、オプションファイルのパスを指定して、my.iniで指定したdatadirのフォルダを–initializeで初期化します。–consoleはデバッグ用です。

mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --initialize --console
Code language: DOS .bat (dos)

以下のように出力されました。

2020-10-16T01:03:40.350391Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

2020-10-16T01:03:40.350443Z 0 [System] [MY-013169] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.21) initializing of server in progress as process 12976
2020-10-16T01:03:40.373119Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-16T01:03:41.203862Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-16T01:03:42.640680Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: H8e6u?Flq>ir
Code language: JavaScript (javascript)

初期化はうまくできたようです。ここで最後の行にrootユーザーのパスワードが作成されています。これを初期化後のデータベースにアクセスする際に使います。

保存場所を変えたらrootユーザーでログインできない

先ほどの例では、「–console」を付けてinitializeをしていたので、rootユーザーの初期パスワードが表示されていますが、このオプションを付けていないと、ログファイルにのみ出力されるだけで、パスワードが変わったことなんて思いもしません。

保存場所を変える前に使っていたrootユーザーのパスワードでmysqlのログインができなくなった、と報告が来たのですが、よくよく確認すると初期化後のパスワード変更に気付いていなかったようでした。

新しいdatadirの中に<ホスト名>.errのログファイルが作成されていますので、そこに上記のハイライト部分のようにテンポラリなパスワードが書かれていることを確認しましょう。

上記の場合、

mysql.exe -h localhost -p -u root --local-infile=1Code language: DOS .bat (dos)

でパスワードを求められたらH8e6u?Flq>irを入力すれば良いのです。そして、rootユーザーのパスワードを変更します。NEWPASSWORDの部分は変更したいパスワードに読み替えてください。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';Code language: SQL (Structured Query Language) (sql)

振り返って

MySQLをここまでディープにサポートしたのは初めてだったのですが、結構癖がありますね。私は普段PostgreSQLやMicrosoft SQL Serverを使用することが多いのですが、ここでMySQLについてかなり勉強になりました。かと言って、今使っているWordPressのMySQLを色々とイジるなんて怖いことはしないつもりですw