MySQLに別のサーバーから接続する — bind-addressとユーザーの接続元

MySQLへのリモート接続。パソコンとデータベースサーバー、鍵と設定の歯車。

手元に入れたMySQLは、たいてい自分からしか繋がりません。アプリとDBを別の台に分けた瞬間に出る2つのエラーを、両方わざと出してから直します。

前提: VMware に Ubuntu Server を入れる と 初期設定の要点8つ を終えた VM が 1台あること。2台あるとより実際に近い形になりますが、1台でも全部そのまま再現できます(下の「VM が1台しかないとき」を参照)。この記事では DB サーバーの IP を 192.168.10.90 として書きます。SQL そのものは プロセスを止めても消えない場所 — MySQLとSQLの基本 で扱っているので、この回は「サーバーとして立てて、別の台から繋がるようにする」ところに絞ります。

手元の PC に入れた MySQL は、たいてい自分からしか繋がりません。

それで困らないのは、アプリと DB が同じ1台に載っているうちだけです。アプリサーバーと DB サーバーを分けた瞬間に、繋がらなくなります。 そして、そのときのエラーが2種類あって、どちらが出たかで直す場所がまったく変わります。

この回は、わざと繋がらない状態から始めて、2種類のエラーを両方出してから直します。

対象レベル: 初級〜中級 / 区分: 必要に応じて読む

目次

先に知る言葉

この記事で初めて出てくる言葉です。 詳しくは用語集にもまとめてあります。

言葉意味
bind-addressMySQL がどのアドレス宛てで待つかの設定。既定は自分自身だけ
接続元MySQL の利用者は「名前」と「どこから来たか」の組で決まる
ポート機械の中のどのプログラムかを決める番号。MySQL は 3306
ファイアウォール通してよい通信を決める関所

VM が1台しかないとき

この記事は、1台でも 1 つも省略せずに再現できます。 鍵は接続先の書き方です。

打ち方どう繋がるか
mysql -u root -pソケットという特別な通り道。ネットワークを通らない
mysql -h localhost ...同じくソケット。 名前が localhost だと通り道が変わる
mysql -h 127.0.0.1 ...自分宛てだが、ネットワークを通る
mysql -h 192.168.10.90 ...これを使う。 別の台から来たのと同じ扱いになる
bash
$ ip -4 addr show | grep inet          # 自分の LAN の IP を確認する
$ mysql -h 192.168.10.90 -u root -p    # その IP を指定して、自分自身に繋ぐ

ここが、この記事でいちばん役に立つかもしれません。 localhost と 127.0.0.1 は同じ意味だと思われがちですが、MySQL では通り道が変わります。 localhost はソケット、127.0.0.1 はネットワーク。「繋がるはずなのに繋がらない」の原因が、この1語の違いだったことがあります。

LAN の IP を指定すれば、ERROR 2003 も ERROR 1045 も、ユーザーの接続元の話も、すべて1台のまま同じように起こせます。 2台用意できるようになったら、同じ手順をそのまま別の台から打ってください。

この記事でわかること

  • MySQL が既定では自分自身からしか受け付けないこと、その設定行
  • ERROR 2003 と ERROR 1045 は別の問題で、直す場所が違うこと
  • 'app'@'localhost' と 'app'@'%' が別のユーザーであること
  • 初期設定のコマンドが何を聞いていて、どう答えるのが正しいか
  • 文字コードを間違えると絵文字だけ保存に失敗すること

この記事でやること

  1. MySQL を入れて、初期設定を通す
  2. 別の台から繋いで、1つ目のエラーを出す(山場①)
  3. 待ち受けアドレスを直して、2つ目のエラーを出す(山場②)
  4. 接続元を絞ったユーザーを作って、繋がるまで(山場③)
  5. 文字コードとバックアップを整える

① 入れて、初期設定を通す

DB サーバー役の VM で実行します。

bash
$ sudo apt update
$ sudo apt install -y mysql-server
$ mysql --version
$ systemctl is-active mysql
$ systemctl is-enabled mysql
mysql  Ver 8.0.39-0ubuntu0.24.04.2 for Linux on x86_64
active
enabled

enabled になっていることを確認してください。 ここが disabled だと、再起動したときに DB だけ上がってきません。「昨日まで動いていたのに朝から繋がらない」の原因の1つがこれです。

初期設定は、5つ聞かれる

bash
$ sudo mysql_secure_installation
聞かれることどう答えるかそう答えるとどうなる
パスワード検証の仕組みを入れるか学習用なら ny にすると弱いパスワードが弾かれる
root のパスワードを設定するかUbuntu の既定では聞かれないroot は auth_socket(sudo mysql で入る方式)なので、Skipping password set for root as authentication with auth_socket is used by default. と出て飛ばされる
匿名ユーザーを消すかy名前を名乗らずに繋げる口が閉じる
root のリモート接続を禁止するかyroot で外から繋げなくなる
test データベースを消すかy誰でも書ける初期DBが消える

「root のリモート接続を禁止するか」は必ず y にしてください。 ここを n にすると、外から root で繋げる DB が出来上がります。パスワードが1つ漏れただけで、全データの読み書きと削除ができる状態になります。アプリ用のユーザーは、このあと別に作ります。

② 別の台から繋いで、1つ目のエラーを出す

アプリ側の VM で実行します。

bash
$ sudo apt install -y mysql-client
$ mysql -h 192.168.10.90 -u root -p
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.90:3306' (111)

これが1つ目のエラーです。 「接続そのものができない」という意味で、認証まで到達していません。 DB サーバー側で理由を見ます。

bash
$ sudo ss -ltnp | grep 3306
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*  users:(("mysqld",pid=1893,fd=23))

127.0.0.1 でしか待ち受けていません。 これが既定です。自分自身からの接続しか受け取らない、という設定で入っています。

待ち受けるアドレスを変える

bash
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
bind-address            = 0.0.0.0
mysqlx-bind-address     = 127.0.0.1
bash
$ sudo systemctl restart mysql
$ sudo ss -ltnp | grep 3306
$ sudo ufw allow from 192.168.10.0/24 to any port 3306
LISTEN 0 151 0.0.0.0:3306 0.0.0.0:*  users:(("mysqld",pid=2210,fd=23))

0.0.0.0 は「どのアドレスでも受ける」という意味です。 学習用の閉じたネットワークなら問題ありませんが、実務ではファイアウォールで接続元を必ず絞ってください。 インターネットに面した 3306 番は、公開した瞬間から総当たりを受けます。上の ufw の行が、その最低限です。

③ 2つ目のエラーを出す

アプリ側の VM から、もう一度繋ぎます。

bash
$ mysql -h 192.168.10.90 -u root -p
ERROR 1130 (HY000): Host '192.168.10.100' is not allowed to connect to this MySQL server

エラー番号が変わりました。ここが重要です。

エラー意味直す場所
2003接続できない待ち受けアドレス・ファイアウォール・経路
1130接続はできたが、その接続元のアカウントが1つも無いユーザーの接続元
1045接続元に合うアカウントはあるが、認証で拒否されたユーザー名・パスワード・権限

2003 が 1130 に変わったということは、ネットワークの問題は片付いたという意味です。Ubuntu の MySQL では root は 'root'@'localhost' しか無いので、別の台からの接続には合うアカウントが無く、1130 になります。④で接続元を絞ったユーザーを作ったあと、そのユーザーでパスワードを間違えると 1045 になります。ここから先は DB の中の話になります。エラー番号が切り分けの境界線になっています。

④ 接続元を絞ったユーザーを作る

DB サーバー側で実行します。

bash
$ sudo mysql -u root -p
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CREATE USER 'app'@'192.168.10.%' IDENTIFIED BY 'ChangeMe_2026';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'192.168.10.%';
FLUSH PRIVILEGES;

SELECT user, host FROM mysql.user WHERE user = 'app';
+------+---------------+
| user | host          |
+------+---------------+
| app  | 192.168.10.%  |
+------+---------------+

MySQL のユーザーは「名前」と「接続元」の組で1人です。 'app'@'localhost' と 'app'@'192.168.10.%' は別人で、パスワードも権限も別々に持ちます。「パスワードは合っているのに 1045 が出る」の大半が、これです。接続元の部分が合っていません。

bash
$ # アプリ側の VM から
$ mysql -h 192.168.10.90 -u app -p appdb
mysql> SELECT DATABASE(), USER(), CURRENT_USER();
+------------+---------------------+---------------------+
| DATABASE() | USER()              | CURRENT_USER()      |
+------------+---------------------+---------------------+
| appdb      | app@192.168.10.100  | app@192.168.10.%    |
+------------+---------------------+---------------------+

USER() は名乗った相手、CURRENT_USER() は実際に一致した定義です。 この2つを並べると、どのユーザー定義で入れたのかが分かります。権限が思ったとおりに効かないときは、まずこれを見ます。

権限が足りない状態も、作って見ておく

bash
$ mysql> CREATE TABLE appdb.t1 (id INT);
ERROR 1142 (42000): CREATE command denied to user 'app'@'192.168.10.100' for table 't1'

GRANT に CREATE を入れていないので、表が作れません。アプリが動くのに必要な最小限だけを渡す、というのはこういう状態のことです。 移行やマイグレーションのときだけ CREATE を足し、済んだら外します。

⑤ 文字コードとタイムゾーン

bash
$ mysql> SHOW VARIABLES LIKE 'character_set_server';
$ mysql> SHOW VARIABLES LIKE 'collation_server';
$ mysql> SELECT NOW(), @@global.time_zone, @@session.time_zone;
設定この値だとこう動く
utf8mb4絵文字も保存できる。 これが正解
utf8(旧称)3バイトまで。絵文字だけ保存に失敗する
time_zone = SYSTEMOS の時刻設定に従う

「日本語は通るのに絵文字だけエラーになる」は、この設定が原因です。 一部の文字だけ失敗するので、文字コードの問題だと気づくのに時間がかかります。データベースを作るときに utf8mb4 を明示するのが、いちばん簡単な予防です。

⑥ 落ちたときに見る場所と、毎日のバックアップ

bash
$ sudo tail -30 /var/log/mysql/error.log
$ sudo journalctl -u mysql -n 30 --no-pager
bash
$ mysqldump -h 127.0.0.1 -u root -p \
    --single-transaction --routines --triggers --events \
    --databases appdb > ~/appdb.sql
$ tail -1 ~/appdb.sql

最後の行に完了の記録が出ていることを確認してください。 取れたつもりのバックアップが途中で切れている、という事故を防げます。戻せることまで確かめる手順は バックアップは戻せて初めてバックアップ にまとめました。

完成の判定

#確かめること合格の形
1自動起動になっているsystemctl is-enabled mysql が enabled
22003 を出せた待ち受けが 127.0.0.1 の状態で確認
32003 が 1130 に変わったネットワークが片付いた証拠
4別の台から繋げたCURRENT_USER() が想定どおり
5権限が絞れているCREATE が 1142 で拒否される
6文字コードが utf8mb4絵文字を1件入れて確認

つまずいたところ

パスワードは合っているのに 1045 が出る

接続元の部分が違います。 'app'@'localhost' しか作っていないのに、別の台から繋いでいませんか。SELECT user, host FROM mysql.user; で、いま何が登録されているかを一覧してください。名前だけ合っていても、接続元が合わなければ別人です。

設定ファイルを直したのに反映されない

MySQL の設定は複数のファイルに分かれていて、後から読まれたものが勝ちます。いま効いている値を直接聞くのが確実です。

bash
$ mysql> SHOW VARIABLES LIKE 'bind_address';

再起動したら繋がらなくなった

systemctl is-enabled mysql を確認してください。disabled なら、OS が起動しても MySQL は起動しません。 sudo systemctl enable mysql で有効にします。手で start して動いたから大丈夫、とはなりません。

0.0.0.0 にしたまま本番に持っていってしまった

ファイアウォールで接続元を絞ってください。 待ち受けを広げること自体は普通の運用ですが、そこに何の制限も無いと、インターネットから総当たりを受けます。ufw status で、3306 番に付けた条件を必ず確認してください。

後片づけ

bash
$ mysql> DROP DATABASE IF EXISTS appdb;
$ mysql> DROP USER IF EXISTS 'app'@'192.168.10.%';
bash
$ sudo ufw delete allow from 192.168.10.0/24 to any port 3306
$ sudo systemctl stop mysql
$ sudo apt purge -y mysql-server
$ rm -f ~/appdb.sql

ダンプファイルにも中身が入っています。 練習用でも消してください。

よくある質問

ERROR 2003 と ERROR 1045 は、どう使い分けて読みますか
2003は接続そのものができていない状態で、認証まで届いていません。待ち受けアドレス、ファイアウォール、経路のいずれかを疑います。1045は接続はできたが認証で拒否された状態なので、ユーザー名、接続元、パスワード、権限を疑います。2003が1045に変わったら、ネットワーク側は片付いたという意味です。この番号の変化そのものが、切り分けの進捗を示しています。
bind-addressを0.0.0.0にするのは危険ではないですか
それ単体では危険です。どのアドレスからの接続も受け付ける設定なので、必ずファイアウォールで接続元を絞ってください。アプリサーバーのIPやセグメントだけを許可する形が基本です。インターネットに面した状態で3306番を開けると、公開した直後から総当たりの試行を受けます。待ち受けを広げることと、誰でも繋げることは別だと考えてください。
‘app’@’localhost’ と ‘app’@’%’ は同じユーザーではないのですか
別のユーザーです。MySQLはユーザー名と接続元の組で1人を表すので、パスワードも権限も別々に持ちます。同じ名前で作っても、片方に与えた権限がもう片方に効くことはありません。パスワードが合っているのに拒否される、という症状のほとんどがこれです。mysql.userを一覧して、どの組が登録されているかを確認してください。
utf8 と utf8mb4 は何が違いますか
1文字あたりに使えるバイト数が違います。旧来のutf8は3バイトまでしか扱えないため、4バイトを使う絵文字などが保存できません。日本語は通るのに絵文字だけエラーになる、という分かりにくい形で現れます。utf8mb4なら4バイトまで扱えるので、こちらを使ってください。データベースを作るときに明示しておくのが、いちばん簡単な予防です。
アプリと同じ1台にDBを入れてはいけませんか
学習や小規模な用途なら問題ありません。1台で足りるうちは、分けないほうが構成も運用も単純です。分けるのは、負荷が偏ったとき、DBだけ別のスペックにしたいとき、複数のアプリから同じDBを使いたいときです。ただし分けた瞬間に、この記事で扱った待ち受けアドレスとユーザーの接続元が問題になるので、先に知っておく価値があります。

まとめ — 理解できたこと

  • MySQL は既定で自分自身からしか受け付けない。 bind-address の1行
  • 2003 は接続、1045 は認証。 番号が変わったら前に進んでいる
  • ユーザーは「名前+接続元」で1人。 名前だけ合っても別人
  • 待ち受けを広げたら、必ずファイアウォールで絞る
  • utf8mb4 でないと、絵文字だけ保存に失敗する
  • enabled でないと、再起動後に上がってこない

参考リンク

次に読む

この記事は 【第5段階】バックエンドとデータベース の 5 / 29 本目です。

記事
← 前の記事SQLのJOINと集計 — 行が消える・増える理由とGROUP BYの基本
→ 次の記事URLに動詞を入れない — REST APIの設計

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›Relational Databases — MySQL
  • MySQL のインストールと初期設定
  • ユーザーと権限(GRANT)
  • 待ち受けアドレスと接続元の制御

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次