MySQL関連_備忘録 - 鶏口牛後な日々

CSVをインポートする時

LOAD DATA LOCAL INFILEを使う。

この際、デフォルトでは設定ができていないので、エラーが出る。

クライアントもサーバー側も、ローカルインポートOKの設定にする必要がある。

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile';

でサーバ側はOK。

クライアント側は、一度quitしてから、

mysql --local-infile=1 -u <user> -p

で入り直すと LOAD DATA LOCAL INFILE ができるようになる。

実際の文は以下のような感じ;

LOAD DATA LOCAL INFILE "<csv_file_path>" 
INTO TABLE <table_name> 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES 
(@1, @2, @3, @4, @5) 
SET column_name1=@1, column_name2=@2, column_name3=@3, column_name4=@4, column_name5=@5;

これで、タイトル行を無視して、テーブルのカラムを指定して入れ込むことができた。上記の例は、区切り文字は、カンマで、ダブルクオーテーションで囲まれている場合があると言う時のもの

上記でエラーが出た

ERROR 1300 (HY000): Invalid utf8mb4 character string: '<エラーが起きた部分>'

これは、調べると、 CHARACTER SET utf8mb4 をINTO TABLE と FIELDS TERMINATED BY の間に挿入しろ、という記事があった。下記のような感じ。

LOAD DATA LOCAL INFILE "<ファイルパス>"
INTO TABLE table_name
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES
(@1, @2, @3, @4, @5, @6) //インポートしたいのは何行目かを指定
SET column_name1=@1, column_name2=@2, column_name3=@3, column_name4=@4, column_name5=@5, column_name6=@6

ただ、csv自体が変なエンコードになっていることも少なくなく・・・（これには結構悩まされています） macなので、Numbersがデフォルトでcsvを開くのですが、その時は見える。でも、vimで開いて、 set fenc? だと、 latin とかであることが結構ある。。。この時は、唯一ちゃんと開けているNumbersで、exportで utf-8 を指定してエクスポート。このファイルを上記コードの参照もとファイルパスに入れ込んだら、なんとかQuery OK出ました。。。

MySQL Workbenchでは、このutf-8でエクスポートしなおしたファイルも読んでくれなかった・・・これからもこのエラーは何度か出会いそうな気がします。

結果をlessで見たい

pager less -S

これでless表示してくれる

解除は、 nopager

横長の結果を縦で見たい

<セレクト文>¥G

最後にバックスラッシュとGを入れると縦表示してくれる。

使用中（LISTEN）のポートを調べたい

sudo lsof -i -P | grep "LISTEN"

lsof は、List of Open Filesの略らしい。今開いているプロセスをリストで表示してくれる。 -i は、ネットワークソケットを対象にするオプション。 -P は、ポート名の代わりに、ポート番号を表示するオプション。

これで、LISTEN中のポート番号を確認できる。

MySQLはどこにある？

MySqlの公式ページからダウンロードしたdmgファイルでインストールしたものです。

/usr/local/mysql~~~ 辺りにありました。

Homebrewでインストールした場合、 vim /usr/local/Cellar にインストールしたパッケージのリストがありますので、ここでMySqlがあるかを確認します。

MySQLサーバーをスタートさせる

sudo /usr/local/mysql/support-files/mysql.server start

この時、 mysql というフォルダが、インストールしたmysqlのバージョンを含んだネーミングになっていることがあります。

バージョンごとにこのフォルダが作られ、それごとにsupport-files/mysql.server などもあるということに注意が必要です。

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'　が出る

これは、MySQLサーバーがスタートしていないときに、 mysql -u <ユーザ名> -p でログインしようとするときに出るエラーです。

Activity Monitorなどで、 mysqld が起動しているかどうかを検索してみるといいでしょう。

この上の、「MySQLサーバーをスタートさせる」の方法で、サーバーをスタートさせることで、解消できるはずです。

dockerコンテナからローカルのMySQLに接続したい

これまでは、ブリッジ機能などがあって云々、まだ私にはよく理解できない感じだったのですが、ドッカーのv1.8から、 localhost の代わりに、 host.docker.internal を指定すればいいだけにしてくれたらしい。その時は、dockerコンテナ内で動いているphp Laravelのアプリから、ローカルのMySQLに接続する形にしようとしていたので、php Laravelの .env ファイル内の、 DB_HOST のところに、この host.docker.internal を指定してやるだけで、ローカルのMySQLに接続することができるようになりました。

いろいろ、バージョンあがるごとに作り込んでくれているみたいで助かります。しかし、ネットワークとか、コンテナとかは、まだまだ勉強が必要なことを思い知る毎日です〜

mysqlのパスワードを変更する

その時ログインしているユーザーについて、パスワードを変更したい場合は、

SET PASSWORD="new_password";

だけで良いようです！めちゃ簡単ですね。

ローカルに置いているmysqlのパスワードを、律儀にちょっとわかりにくいものにしていたら、ログインごとに毎回メモを見返さないと思い出せなくて、いっそのことと簡単なものに変えました。小さい積み重ねでも、結構な時間になりますよね。

ユーザーの権限を変更する

  SELECT user,host,password FROM mysql.user;

これで今のユーザーやホスト名を確認できる

SHOW GRANTS FOR root@'localhost’;

権限を確認

GRANT ALL ON testdatabase.* TO testuser@'%' IDENTIFIED BY 'password’;

これで権限付与 ※ identified以降は不要だったかも。

flush privileges;

これで行けるはず。

EOF