`mysqldump`すると`mysqldump: [Warning] Using a password …`が出て、解決しようとしたら`mysqldump: Got error: 1045: Access denied for user`エラーが出た

  • 投稿日
  • 更新日
  • 著者 アルム＝バンド
  • カテゴリー MySQL
WordPressサイトのDBをバックアップするためにmysqldumpでSQLを吐き出すシェルスクリプトをcronで回していました。 今までは 
mysqldump --opt <DBNAME> --default-character-set=utf8 --u <DBUSER> --password=<PASSWORD> | gzip > <BACKUPFILEPATH>.sql.gz
こんな感じのコマンドでした。

mysqldump: [Warning] Using a password on the command line interface can be insecure.の対処

上述のコマンドをうっかり新しいサーバでも同じ内容のバックアップスクリプトを走らせてしまいました。 5.7以上のMySQLではパスワードをコマンド内で直に指定すると以下の警告が出力されてしまいます。
mysqldump: [Warning] Using a password on the command line interface can be insecure.

巷でもよく見かけるあるあるトラブルに自分も嵌まってしまいました。まー確かにセキュリティ的によろしくないですよね……。 ということでシェルスクリプト内のコマンドを以下のように修正しました。 
mysqldump --defaults-extra-file=<(printf '[mysqldump]\nuser=%s\npassword=%s\n' <DBUSER> <PASSWORD>) --default-character-set=utf8mb4 --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
本筋とは関係ないところですが
  • --default-character-setを昨今の絵文字などの状況からutf8mb4に変更
  • --optはやめて--single-transactionに変更
  • --quick追加
とか大幅に変更していますが、肝となるのは--defaults-extra-file。この指定で「設定ファイルに記述されている内容を使ってDBに接続」します(※後述「余談」も参照)。 ただし、設定ファイルを一々用意するのが面倒なので、printfで出力した文字列をファイルとして読み込ませる方法を採用しました。 ……コマンド内にユーザ名やパスワードが直に指定されていることは変わっていない気はしますが、警告はこれで回避できます。

mysqldump: Got error: 1045: Access denied for user '<DBUSER>'@'localhost' (using password: YES) when trying to connectの対処

さて、このコマンドでバックアップが取れるか実験。 
# mysqldump --defaults-extra-file=<(printf '[mysqldump]\nuser=%s\npassword=%s\n' <DBUSER> <PASSWORD>) --default-character-set=utf8mb4 --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
mysqldump: Got error: 1045: Access denied for user '<DBUSER>'@'localhost' (using password: YES) when trying to connect
……あれ？
mysqldump: Got error: 1045: Access denied for user ”@’localhost’ (using password: YES) when trying to connect
別のエラーが発生してしまいました。 ここで1時間程嵌まってしまい格闘。結果的には、パスワードの中に特殊文字が使われているとそこで文字列が途切れてしまうというのが原因でした。 今回の場合は、<PASSWORD>はHOGE#fugafugaのように、中に#が入ったものを使っていました。 試しにpassword=をpasswd=のようにおかしなパラメータ名に変更すると、 
# mysqldump --defaults-extra-file=<(printf '[mysqldump]\nuser=%s\npasswd=%s\n' <DBUSER> <PASSWORD>) --default-character-set=utf8mb4 --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
mysqldump: [ERROR] unknown variable 'passwd=HOGE'.
mysqldump: [ERROR] unknown variable ‘passwd=HOGE’.
のように、#の前のHOGEまでで切れていることが確認できます。 ということで、printf内の指定と引数の双方を"(ダブルクォーテーション)で括ることにします。 
# mysqldump --defaults-extra-file=<(printf '[mysqldump]\nuser="%s"\npassword="%s"\n' "<DBUSER>" "<PASSWORD>") --default-character-set=utf8mb4 --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
#
無事にダンプ出力がされました！

余談: mysqldump: [ERROR] unknown variable 'defaults-extra-file=/dev/fd/XX'.

--defaults-extra-fileのオプションですが、一つ罠がありまして。 このオプションを最初に指定しないとエラーになるのです。 例えば、 
mysqldump --default-character-set=utf8mb4 --defaults-extra-file=<(printf '[mysqldump]\nuser="%s"\npassword="%s"\n' "<DBUSER>" "<PASSWORD>") --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
のように--default-character-setの次、2番目に--defaults-extra-fileオプションを指定すると以下のようになってしまいます。 
# mysqldump --default-character-set=utf8mb4 --defaults-extra-file=<(printf '[mysqldump]\nuser="%s"\npassword="%s"\n' "<DBUSER>" "<PASSWORD>") --quick --single-transaction <DBNAME> | gzip > ./tmp.sql.gz
mysqldump: [ERROR] unknown variable 'defaults-extra-file=/dev/fd/XX'.
mysqldump: [ERROR] unknown variable ‘defaults-extra-file=/dev/fd/XX’.
unknown variableのエラーが出てしまいました。 そのため、--defaults-extra-fileは最初に指定するようにしましょう。

参考

mysqldump: [Warning] Using a password on the command line interface can be insecure.回避のためprintf出力の設定を読み込ませる方法

パスワード内に特殊文字が使われている場合にmysqldump: Got error: 1045: Access denied for user '<DBUSER>'@'localhost' (using password: YES) when trying to connectになる件

--defaults-extra-fileを最初に指定しないとmysqldump: [ERROR] unknown variable 'defaults-extra-file=/dev/fd/XX'.になる件

その他

この記事を書いた人

アルム＝バンド

フロントエンド・バックエンド・サーバエンジニア。LAMPやNodeからWP、Gulpを使ってejs,Scss,JSのコーディングまで一通り。たまにRasPiで遊んだり、趣味で開発したり。

記事一覧