レンタルサーバー（ロリポップ）でMySQLバックアップをしようとしたら、Access deniedエラーが出ていた話

先日、ロリポップにLaravelをインストールしようとして、うっかりデータベースのデータを上書きしてしまいました。

ロリポップサポートに泣きつく

ロリポップの有料サービスである「バックアップオプション」を導入していなかったため、サポートに泣きついたアホは私です。
なんかわからないけど、今回は無償で前日夕方頃のデータまで復元してもらえました。神。

https://support.lolipop.jp/hc/ja/articles/6506768636947

同じようなうっかりさんは、こちらから泣きついてみてください。

ちゃんとバックアップ取ろうと決意した日

人は、痛みを感じてからでないと予防をしない生き物なのかもしれません。少なくとも私はそうです。今は反省しています。
たいしたデータじゃなくてよかったですが、これが顧客データとかだとうっかりてへぺろ☆とかではとても済まされないので、バックアップを取る習慣をつけようと思い立ちました。
cronでバッチ回して、1日1回自動でバックアップ取れば手間もかからないし。うん。

function connectDb($db_name,$host_db,$user_db,$pass_db){ //DB接続用関数
    $db_param = "mysql:dbname=".$db_name.";host=".$host_db;
  try {
    $pdo = new PDO($db_param, $user_db, $pass_db);
    $pdo->query('SET NAMES utf8;');
    return $pdo;
  } catch (PDOException $e) {
      // エラー処理
      exit;
  }
}

// 変数設定 //
$host_db = "DBホスト名";
$user_db = "DBユーザー名";
$pass_db = "DBパスワード";
$db_name = "DB名";

$pdo = connectDb($db_name,$host_db,$user_db,$pass_db);
$sql = "SHOW FULL TABLES"; //すべてのテーブルの名前を取得
$stmt = $pdo->prepare($sql);
$stmt->execute();
$allTables = $stmt->fetchAll(PDO::FETCH_ASSOC);
unset($pdo);

$dir_path = "./".date('Ymd'); //バックアップした日付を名前にしてフォルダ作成

if(file_exists($dir_path) || mkdir($dir_path)){

    foreach($allTables as $at){ //テーブル毎に名前をつけてバックアップ

        $table = $at[key($at)];
        $file_path = "./".date('Ymd')."/".$table.".sql";
       
        $cmd = "mysqldump -h{$host_db} -u{$user_db} -p{$pass_db} {$db_name} {$table} > {$file_path}";
        shell_exec($cmd);
    }

}else{
    // エラー処理
}

ほいほい、これをcronで定期実行してやればよいね。
ロリポップは、管理画面からcronの設定ができるので非常に便利です。もちろんSSHからでもできます。

ん？なんかエラーメールがきてる。

Enter password: mysqldump: Got error: 1045: Access denied for user 'XXXXXXXX' (using password: NO) when trying to connect

毎朝5時にcronを実行し、処理が終わったらメールを飛ばしてもらうように設定しているのですが（ロリポップ管理画面から設定可能）、軒並みエラーがでているぞ…？
エラー検証のためSSHからコマンドを実行してみましたが、やはり同じエラー。フォルダをのぞくと、一応sqlファイルとしては保存されているものの、0KBになっていたので、DBの情報は正常に出力されていない様子。

ロリポップはコマンドの書き方が微妙に違う？

こちらの記事を参考にさせていただきました。
コマンドの並びを変えただけで、正常にバックアップできました。
並び順は下記を参考にしてください。

//NG
$ mysqldump -h{ホスト名} -u{ユーザー名} -p{パスワード} {DB名} {テーブル名} > {保存先ファイルパス}

// OK
$ mysqldump {DB名} {テーブル名} -u{ユーザー名} -p{パスワード} -h{ホスト名} > {保存先ファイルパス}

「ポート番号が入っていないんじゃない？」という回答もいくつか見かけたのですが、ロリポップに関してはポートの入力は必ずしも必要ない感じでした。

パスワード丸出しですよのエラーが出た。

[Warning] Using a password on the command line interface can be insecure.

SSHで叩いて確認していると、毎回出てきたこのエラー。どうやら、「コマンドラインでパスワード直打ちしてたらセキュリティ的に危ないよ」と警告してくれている模様。
さて、どうしたものかとインターネットの海をさまよう。

mysqlやmysqldumpで「 [Warning] Using a password on the command line interface can be insecure.」という警告が表示される場合の対処

なるほど、別ファイルにパスワードを保存しておいて、mysqldumpの実行時にそれを呼び出してくると良いみたい。
記事を参考に、私は「sqlaccess.conf」という名前でパスワードのみ保存してやってみました。
mysqldumpの第一パラメータに「--defaults-extra-file=ファイル名」を追記してください。

 $ mysqldump --defaults-extra-file=sqlaccess.conf {DB名} {テーブル名} -u{ユーザー名} -p{パスワード} -h{ホスト名} > {保存先ファイルパス}

PHPで動かす場合は、shell_exec()でコマンドを実行すればOK。

$cmd = "mysqldump -h{$host_db} -u{$user_db} -p{$pass_db} {$db_name} {$table} > {$file_path}";
shell_exec($cmd);