WireGuardでVPN環境初構築記録

家と作業拠点との間をVPNで接続したくなり、格闘の末なんとか目的を達成できたので、その記録。
実際に色々ハマりながら、なんとか動作する設定に至ると、「あぁこういうことだったのか」と感じることも多く、面白いですね。

環境とここでの用語

家には実は2回線あったことを思い出し(1回線は全然使っていなかったのだけど解約もしていなかった)、これは「2拠点接続の勉強をするのに最適ではないか!」ということで、今回の着手に至った次第。
思い出したほうの回線にラズパイを接続してルータにてDMZ指定。同じネットワークにWindowsのノートPCが一台接続されている状態にしました。こちらをここでは便宜上「サーバ側」とか「RPi側」とか呼ぶことにします。
一方のアクセス元となる環境は、普段の自分の家の環境。WindowsPCが動いています。こちらここでは便宜上「クライアント側」と呼びます。
サーバ側は 192.168.1.0/24、家のほうは 192.168.0.0/24 です。

VPN接続までのおおまかな流れ

サーバ側:

  • WireGuardのインストール
  • 設定ファイルの記述1:予め作成した自分の鍵情報を記述する
  • 設定ファイルの記述2:接続を許可する先(クライアント)の鍵情報などを記述する
  • WireGuard立ち上げ(そして待ち受け)

クライアント側:

  • WireGUardのインストール
  • 設定ファイルの記述1:予め作成した自分の鍵情報を記述する
  • 設定ファイルの記述2:接続先の情報を記述する
  • 接続指示!と確認

構築手順

WireGuardインストール

クライアント側はまずは WSLのUbuntu上にて作業した(後に Windows用アプリを入れてネイティブでも動作確認済)。

サーバ側・クライン側とも

apt install wireguard
鍵ペアの作成

本質的にはサーバ側、クライアント側ともに同じ事をするが、ファイル名を異なるものにしている(べつにファイル名は何でも良いのだけど、なくさないためにファイルに保存し、パーミション制限している。厳密に言えば作ってからパーミッション変更すると、作った瞬間に覗かれる可能性を排除できないので、umaskしたり予めパーミション設定したファイル(touchなど)に鍵を書き込むなどのほうがベスト。)。

サーバ側:

# 秘密鍵を作成
wg genkey | sudo tee /etc/wireguard/server.key
sudo chmod 600 /etc/wireguard/server.key

# 公開鍵を生成
sudo cat /etc/wireguard/server.key | wg pubkey | sudo tee /etc/wireguard/server.pub
sudo chmod 600 /etc/wireguard/server.pub


クライアント側:

# 秘密鍵を作成
wg genkey | sudo tee /etc/wireguard/client.key
sudo chmod 600 /etc/wireguard/client.key

# 公開鍵を生成
sudo cat /etc/wireguard/client.key | wg pubkey | sudo tee /etc/wireguard/client.pub
sudo chmod 600 /etc/wireguard/client.pub
設定ファイルの記述(サーバ側)

サーバ側(接続を待ち受ける側)の設定。[Interface]セクションに自分の(サーバ側の)条件を書き、[Peer]側に接続を許可する相手側の情報を書く

/etc/wireguard/wg0.conf (新規作成する。wg0というインタフェース名にする場合はこのファイル名)

[Interface]
PrivateKey=wOchd2/do5Pe4VRzk3L3pVQHgO4kDUh/3Aec+GBPxFo=
Address=172.16.0.10
ListenPort=51830

[Peer]
PublicKey=bgzIVy3u0A9b+AB5lvknZb5mlfugZ91/9Z0YOBNk460=
AllowedIPs=172.16.0.0/24

InterfaceのPrivateKeyには自分の秘密鍵(server.key)の内容を記述し、
PeerのPublicKeyには接続を許可する相手の公開鍵(client.pub)の内容を記述する。
ここでAddressは、VPN接続に使うインタフェースのアドレス(決める)、
AllowedIPsは、接続を許可する相手のネットワークを記述する。

設定ファイルの記述(クライアント側)

クライアント側(接続をしかける側)の設定。[Interface]セクションに自分の(クライアント側の)条件を書き、[Peer]側に接続先の情報を書く。

[Interface]
PrivateKey = eBXv9XmFgNJYfWxoQH3Z6jBgo4KaX/X22DvTKZKo8lc=
Address = 172.16.0.12/32

[Peer]
PublicKey = aeMvyD+IIlAzphOLS/uyVn9gZdF/pwsT/K6Rs4GtOj4=
AllowedIPs = 172.16.0.0/16, 192.168.1.0/24
Endpoint = XXX.YY.ZZZ.83:51830

InterfaceのPrivateKeyには自分の秘密鍵(client.key)の内容を記述し、
PeerのPublicKeyには接続を許可する相手の公開鍵(server.pub)の内容を記述する。
Addressは、VPN接続に使うインタフェースのアドレス(サーバに設定したのと同じネットワークで、かぶらないもの)。
AllowedIPsは、接続先でアクセスするIPアドレスの範囲。今回はラズパイの裏側にあるネットワーク(今はノートPC1台しかないけど)にもアクセスしたいので、書いておく(実際は裏側にアクセスするためにもう少し設定が必要なので、あとで加えても良いのだけど)。

Endpointは、サーバのIPアドレスと、サーバのwg0.confに記述したポート番号。

接続

サーバ側の待ち受け
sudo systemctl start wg-quick@wg0

(停止したいときは stop、再起動したいときは restart)
設定が正しければ、何のメッセージもなく完了する。誤りがある場合は何か表示される。
このとき wg コマンドで以下の様に表示される。peerに許可先の公開鍵とネットワークが表示されていることがポイント。

$ sudo wg
interface: wg0
  public key: abcdltOAzph/uMvyDZdF/pw+yVSsT/K6Rs4Gn9gOLj4=
  private key: (hidden)
  listening port: 51830

peer: YiKGzKIo6YimEBO5+EsNTuxQymc4VUfC3NgSRJLqryU=
  allowed ips: 172.16.0.0/24
クライアント側からの接続

wg0インタフェースを立ち上げる

sudo wg-quick up wg0

(落とすときは down)
wgコマンドで状態を確認するとこんな感じ。

$ sudo wg
interface: wg0
  public key: gZAB3ku4VlZfbA0m6nb7I9zy30/NZ+vk9u1OYB5bll0=
  private key: (hidden)
  listening port: 50673

peer: tKPslcohiT3Hmsf2mNbvozREpsshlZ+hoAXrBtsc6Bw=
  endpoint: XXX.YY.ZZZ.83:51830
  allowed ips: 172.16.0.0/16, 192.168.1.0/24

正しく設定されていれば、サーバ側RPiへのPingが通るはず。

\> ping 172.16.0.10
172.16.0.10 に ping を送信しています 32 バイトのデータ:
172.16.0.10 からの応答: バイト数 =32 時間 =53ms TTL=64
172.16.0.10 からの応答: バイト数 =32 時間 =17ms TTL=64
172.16.0.10 からの応答: バイト数 =32 時間 =19ms TTL=64
172.16.0.10 からの応答: バイト数 =32 時間 =23ms TTL=64


一度(pingなどで)アクセスがあると、サーバ側ではpeerに接続情報が表示されるようになる。

interface: wg0
  public key: abcdltOAzph/uMvyDZdF/pw+yVSsT/K6Rs4Gn9gOLj4=
  private key: (hidden)
  listening port: 51830

peer: YiKGzKIo6YimEBO5+EsNTuxQymc4VUfC3NgSRJLqryU=
  endpoint: PPP.QQ.RRR.219:26718
  allowed ips: 172.16.0.0/24
  latest handshake: 20 minutes, 33 seconds ago
  transfer: 564 B received, 476 B sent

以上で、家とサーバ側とでVPNが張れました。ただしこの時点では、先方のラズパイとつながっただけです。
なので、最後に「サーバ側で、サーバの裏側のネットワークにもアクセスできるようにする」設定をします。

サーバ側のネットワーク上の機器にアクセスできるようにするための設定

ラズパイ自体の設定

ラズパイ、というか今回使った Ubuntu 24.04 ではネットワーク内の他の機器へのフォワードが許されていないので、まずこれを許可する設定をします。

  • 1. /etc/sysctl.conf に「net.ipv4.ip_forward=1」の行がコメントアウトされているので、有効化する
  • 2. sudo sysctl -p で反映
wg0.confの設定

ip a などでフォーワードしたいネットワークアダプタの名前を確認します(ここでは eth0。無線LANを使っている場合は wlan0とかの場合もあるでしょう)。

[Interface]
(略)
PostUp = iptables -A FORWARD -i wg0 -j ACCEPT; iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
PostDown = iptables -D FORWARD -i wg0 -j ACCEPT; iptables -t nat -D POSTROUTING -o eth0 -j MASQUERADE
アクセス元の wg0.confの設定

ここで wg0.confの [Peer] の AllowedIPsに相手方のアクセスしたネットワーク(ここでは 192.168.1.0/24)を指定するのが流れなのですが、今回は最初に設定してしまったので、とくにここでやることはないです。

あとは、相手側ネットワーク上のPCに向けてアクセスできることを確認すれば良いです。
例:

\> ping 192.168.1.10
192.168.1.10 に ping を送信しています 32 バイトのデータ:
192.168.1.10 からの応答: バイト数 =32 時間 =26ms TTL=127
192.168.1.10 からの応答: バイト数 =32 時間 =23ms TTL=127
192.168.1.10 からの応答: バイト数 =32 時間 =35ms TTL=127
192.168.1.10 からの応答: バイト数 =32 時間 =23ms TTL=127

(おまけ)リモートデスクトップ接続しようとしてハマった件

総仕上げとして、相手側ネットワークにあるWindowsマシンへとリモートデスクトップ接続しようとして、えらくハマりました。私がハマったポイントを。

1. ノートPCをwi-fiに繋ぐ際に「パブリックネットワーク」にしてしまったせいで、全然外からアクセスできなかった
2. セキュリティソフトが「ネットワーク保護」として、外からのアクセスを拒否していた(ファイアウォール
3. そもそもそのノートでリモートデスクトップ許可していなかった

リモートデスクトップ以外の確認方法

あとはpythonで雑にサーバ立てて curlで疎通確認したりもしました。結構便利。

python -m http.server 8080

その他

ここには書いてないけど

  • IPアドレスの固定化
  • デフォルトでRaspPi3のUbuntuがaptでのインストールを許可してくれてなかったのでごにょごにょ設定

など、やっています。


写真はAIが考えたWireGuardのイメージ。

BeelinkのミニPCを買って動かしてみた話

ひとつ前のブログで、このマシンで Ubuntu 24.04 を稼働させた話を書きました。
sakaik.hateblo.jp

このエントリでは、このマシンそのものについての紹介と、自分の設定用メモを書いておきたいと思います。

有機

 昨年の5月の連休の時に、ふと買ってしまった(そして今年の連休になるまで開梱していなかった)ものが1台。そして、今年の連休(昨日)にふと見て安かったのでつい買ってしまったのが1台の計2台あります。

1台目 Beelink EQ12 W11: N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx2, HDMIx2, USB 3+C1、Wi-fi
2台目 Beelink mini S12: N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx1, HDMIx2, USBx4、Wi-fi

1台目:
現在見ると 41,800円ですが、昨年は 3,000円の値引き後で 35,800円での購入でした。

2台目:
公称 29,800円のところ -15%割引のタイムセールで 25,530円でした。

どちらもスペックは似たような感じですが、値段に差があるので、たぶん EQシリーズのほうが少し上位なのでしょう(N100のバージョンとかあまりよく知らない)。EQ 12 のほうは、有線LANが2ポートあるのと、USBのひとつが Type-C になっているところが見かけ上の違いです。

NVMe SSDを換装

 ちょうど部屋に 使っていない2TBのNVMe SSDが転がっていたので、換装することにしました。

1台目(EQ12)を換装しようとフタを開けたら、ちょっとだけ面倒な感じだったので、そっとフタを閉じました(笑)。
EQ12のフタをあけたところの雰囲気は、以下の水野さんの記事で見ることができます。
gihyo.jp

一方の2台目(Mini S12) は非常にシンプルでした。画像で紹介します。なお、どういうタイミングで内部構造が変わるのかは私も知りませんので、あくまでもこのタイミングで買った私のはこういう構造だったという参考程度に。

まず
裏側の4カ所にあるネジを外して裏蓋を取ります。ベロがついていて引っ張りやすくなっているという、この心遣いがニクい。



フタをあけたところ。フラットケーブルでつながっているので、勢いよくフタをひっぱりすぎないように注意。



フタのほうは、2.5インチSSD(HDD)を設置できるようになっています。SATAと電源の端子が、黒い金属カバーの下に隠れていました。金属カバーは2本のネジ(写真中の「A」と「B」の箇所)で留まっています。今回は M.2 SSDを持っていたので換装することにしましたが、普通に 2.5インチSATAを買えば簡単に増設できそうで、良いですね。標準の500GB+増設2TBとかにすれば、かなり色々なことができるようになりそうです。


本体側にはすぐに SSDのスロットが見えるので、換装ラクラクです。そういえばこの形のメモリ(SODIMMでしたっけ?)を見るのもえらく久々のような気がします。
写真は換装後。


フタをしめたら完了。


ちなみにサイズは、少しだけ1台目(EQ)のほうが大きいです。ついでに裏側の端子の状態も紹介。どちらもHDMIが2つ(ただし今回サーバ用途なので接続は確認していません)。EQのほうはLANポートが2つあるのが特長です。あと Type-CのUSBも。


設定など

ここから先は特に、基本的に自分用の記録です。安売りされればなんかそのうちもう一台くらい買いそうな気がするので、その時のために(笑)。
作業は基本的に root にて行っています。
私は Ubuntuは minimum でインストールしているので、色々基本ソフトもインストールされていなかったりします。

sudo su - 
OS (Ubuntu 24.04) のインストール

ひとつ前の記事を参照。 現時点で直接 24.04をインストールできなかったので、22.04インストール後に24.04にupgradeしています。
https://sakaik.hateblo.jp/entry/20240505/ubuntu2404_on_miniPC

upgrade時のサービス起動設定の変更

upgrade処理中にサービスを再起動する際にいちいち尋ねてくるのが鬱陶しいので、勝手に再起動してくれるように設定しておきます。

echo "\$nrconf{restart} = 'a'" >> /etc/needrestart/conf.d/00local.conf
タイムゾーン設定

脳内で9時間を足すのが週間になってしまっているけど、健全ではないので最初にJSTに設定しておこう。脳への負担も少なくなる(はず)です。

timedatectl set-timezone Asia/Tokyo
何はなくとも最新化
apt update
apt upgrade
基本ソフトのインストール

だいたい、私が最初に使いたくなるのはこれくらい。

apt install vim iputils-ping network-manager alsa-utils cron -y
ネットワークの設定

一旦、有線でネットワークを設定しました。最終的には wi-fiにしたいので、ここをすっ飛ばしてもOK。

cd /etc/netplan/
cp 00-installer-config.yaml 10-netconfig.yaml
mv 00-installer-config.yaml 00-installer-config.old
chmod 700 10-netconfig.yaml
vi 10-netconfig.yaml (有線で運用する場合)
---------------------------------------------------
# This is the network config written by 'subiquity'
network:
  ethernets:
    enp1s0:
      dhcp4: no
      optional: true
      addresses: [192.168.0.241/24]
      # gateway4: 192.168.0.1
      routes:
        - to: 0.0.0.0/0
          via: 192.168.0.1
      nameservers:
        addresses: [8.8.8.8] 
    enp2s0:
      optional: true
  version: 2
----------------------------------------------------

→ gateway4指定はdeprecatedなのでroutesを使う

反映。

netplan apply
ホスト名の設定

今回はインストール時に設定したものでOKだけど、インストール時には適当に入れてしまったので変えたいときなど。

--確認
hostname
--設定
nmcli general hostname <<ホスト名>>
wi-fiの設定

ip link や nmcli device status などで確認してwlo1などの無線LANのインタフェースが認識されていることを確認。

vi /etc/netplan/50-wificonfig.yaml
----------------------------------------------------
network:
  version: 2
  wifis:
    wlo1:
      dhcp4: false
      access-points:
        "myhomelan01 ":
          password: "p@sSw0Rd"
      addresses: [192.168.0.211/24]
      routes:
        - to: default
          via: 192.168.0.1
      nameservers:
        addresses: [8.8.8.8]
----------------------------------------------------
netplan apply 

をしても反映されなかったので、

nmcli device wifi connect myhomelan01 password p@sSw0Rd

で手動接続した。ただし、先ほど作成した /etc/netplan/50-wificonfig.yaml と、この処理で自動で作成された設定ファイルがコンフリクトしているせいなのか、再起動後にwi-fiに自動で接続されない状態になってしまった。自動作成されたファイルを削除(拡張子をリネーム)することで正常化したが、これで良かったのかどうかは確証がない。
(そういえばサーバで wi-fiの接続するなんて、今回初めてかもしれない。一度ちゃんと整理しておかないとですね)



以上で、「電源を接続してボタンを押すだけで(sshで入って)遊べるサーバ」ができあがりました。

それラズパイでいいんじゃね?

 実はこういう環境が欲しくてラズパイをいくつか買ったのですが、今回ほどサクサクと作業できなかった(気分が盛り上がらなかった?)という経緯があります。
何が違うのかなと考えたのですが:

  • Beelinkのマシンは、フルサイズのHDMIがそのまま刺さる。ラズパイは変換アダプタが必要で、毎度「どこに行ったかな」と探す手間が鬱陶しかった(整理しておけという話w)
  • Beelinkのマシンは、ACアダプタ付属。ラズパイは自分が持っているUSBケーブルをそのまま使えるのがメリットである一方、適切なケーブル類を自分で用意する必要があるのが一手間面倒。
  • 物理電源スイッチの有無。通電したら起動しちゃうという(ある意味メリットなのですが)のが、今まで自分が使ってきた「コンピュータ」と異なって、なんだか他人行儀というか、違う文化の世界に来てしまった気分で気が休まらない(笑)。


特に電源ケーブルの有無は大きくて、安心してそのまま繋いで使い始められるのか、ケーブル類の選定で一手間あるのか(そして「とりあえず今起動したいだけ」ならそのへんのを使うのだけど、ずっと運用する場合はそのケーブル類が占有されるので、結局最初から専用の電源ケーブルがあったほうが良い)。
ラズパイ専用として色々売られているので、適当なものを買って専用に使えば良いのですが、こんどは「単なるUSB給電だよね? コンセントの挿し口を1台でひとつずつ塞がないで、こう、5台分くらいまとめてケーブル出せるのないの?」などと思ってしまい(いや、そういう風にできるのは分かるんですけど、どの組み合わせがいいかを考えるのが面倒)、やはり「電源ケーブル付属ですぐ使える」ことが私にとっては大きかったのだなぁと感じた次第。

あとは、IPあどれすどうする問題をその都度適当にやっていたのが、今回、家の中でのサーバが使うIPアドレスルールを決めたので、今後ちょっとはラズパイでもやりやすくなってきたかもしれません。
→追記: 電源ボタンについて、↓こちらの記事で「ラズパイ5には待望の電源ボタン」と紹介されていました。自動起動は今まで通りとのことで、私のほしい電源ボタンは「点けるときはボタンを押す、落とすときは shutdownコマンドを実行する(ボタンは押さない)」なので、ちょっと思惑とは異なりました。しかし、あれだ。これなのかぁ(手元のラズパイ5のお尻部分の小さなボタンを見つめながら)。
pc.watch.impress.co.jp

Beelinkの小型PCにUbuntu 24.04 Server

購入してあったものの1年近く放置していた、Beelinkの N100 PCをようやくこの連休に開封しました。Ubuntu 24.04 LTS がリリースされたところだったので最新のサーバ替わりとしてちょうど良いかなとも思い。
最近サーバの設定とか、あまりやっていないし、まぁ色々今回固有のハマり事象もあったので、記録として書いておこうと思います。
必ずしも正確なことではなく、「結果オーライ」「想像」もたくさん含まれていますので、参考にされる方がいたらその辺お含み置きください。

ハードウェアスペック

 Beelink EQ12 W11 というミニPCです。 N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx2, HDMIx2, USB 3+C1、Wi-fi

最初の壁:Ubuntu 24.04 インストールできず

 
Ubuntu 24.04 Server の .isoをUSBに焼いて、EQ12に挿して起動。言語やキーボードを順調に設定して、ミラーサーバの確認をしている画面で Done を押した瞬間、こんなダイアログが。

"Sorry, the installer has encountered an internal error."

いわゆる500エラーですな(違います)。
何度やっても確実に再現する中、この画面に来る直前に起動USBメモリを抜いておくと突破できることを発見。
しかし、数画面続行すると突然 internal error のダイアログが出てくるので、本質的には何の解決にもなっていない模様。

ログを眺めてみたところ、こんなエラーが目についたけど、関係あるのかないのかすら判断つかず。

一応ログを "Send to Canonical" して、インストールはあきらめました。

Ubuntu 22.04 を入れることに

そんなわけで別途、Ubuntu 22.04 Server の .isoをUSBメモリに焼いて、インストール。こちらは問題なくインストール完了。
ただし、Wi-fiのチップが 22.04のカーネルでは(?)対応してないようで、Wi-fiバイス認識せず有線LANにて。
カーネルを上げれば・・・という話も目にしたので上げてみたのですが、慣れない作業でもあり結局適切なドライバにたどり着けず、そちらは諦め。
とりあえず一旦、有線LANで利用できる「My Ubuntuサーバ」ができあがりました。めでたしめでたし

やっぱり 24.04にしてみたい

アップグレードならできるかも、ということで do-release-upgradeを。

# do-release-upgrade -c
Checking for a new Ubuntu release
There is no development version of an LTS available.
To upgrade to the latest non-LTS development release 
set Prompt=normal in /etc/update-manager/release-upgrades.

しかし、まだ来ていなかった。 
https://discourse.ubuntu.com/t/noble-numbat-release-notes/39890 によると、少し経って安定が確認できてからになるらしい。

/etc/update-manager/release-upgrades で、対象をltsではなくnormalにしたところ

# do-release-upgrade -c
Checking for a new Ubuntu release
New release '23.10' available.
Run 'do-release-upgrade' to upgrade to it.

と、非LTSは見つかったけど、24.04にはならない。

余談:22.04では使えないwi-fiが、24.04では使える

 このマシン、22.04ではwi-fiが使えないっぽいです。ということを、インストーラの中でも確認できたので、画像で紹介。
24.04 のインストーラでは、ネットワーク接続設定の画面に wlo1 (wi-fiのインタフェース)が登場していることがわかります。

22.04 → 24.04への救世主登場

まぁしばらく 22.04 でいいかと思い始めたとき、@tmtms さんがこんなリンクを教えてくれました。

tech.buty4649.net

リリース一覧の設定ファイル(アップグレードの確認時に参照されるもの)に最新 nobleの設定を加えたファイルを用意してくだしました。ぶていさん( @buty4649 )ありがとうございます!


書いてあるとおりに /etc/update-manager/meta-release での参照先を変更するよう編集し、do-release-upgrade。 なんか、すぐには出てこなかったのですが、ごちゃごちゃやっているうちに 24.04が出てきて無事アップグレードできました! wi-fiバイスも認識してくれて(この後こまごまと設定をするのですが)、最終的には 電源1本つないで立ち上げるだけで稼働するおうちサーバができあがりました。快適。

もう一台では24.04を見つけてくれず

こんな作業をしているさなか、Amazonさんを開いたら、似たようなスペックのものが 2.5万円程度で買えるのを見てしまいました。気づいたらぽちっと(笑)。
こちらBeelink mini S12 というミニPCで、スペックは N100(3,4GB)、メモリ16GB、SSD(NVMe) 500GB。LANx1, HDMIx2, USBx4、Wi-fi

こちらのマシンにも同様に Ubuntu 22.04 インストール→meta-releaseを書き換えて24.04へのupgradeを試みたのですが、do-release-upgradeしても一向に24.04を見つけてくれない(アップグレード対象はないよ、と出力される)。業を煮やして、ぶていさんが作ってくれた設定ファイル(LTSのほう)の jammyとnobleの部分を手元のファイル( /upgrade_list.txt とか)に書いて、 meta-releaseの ltsの行を file:///upgrade_list.txt を指し示すよう書きかえて、無事認識してもらえました。 キャッシュ的なものが効いちゃってるんですかね。。

そんなわけで

非常に快適な遊びサーバが2台、おうちの中にできあがりました。
24.04のタイミングで作業に取りかかったのが幸いだったなと思うのが、24.04でこのマシンの wi-fiに対応してくれていたこと。 22.04の時に触っていたら有線での運用になっていたので、快適さ半減だったことでしょう。
改めて、Twitter(X)で色々教えてくれたとみたさん、meta-release作成して方法を公開してくれたぶていさん、ありがとうございました!!

マシンの事とか、その後の設定のこととか書こうと思っていたのだけど、長くなったので本エントリは 「Ubuntu 24.04 をインストールできた!」という話題で一旦まとめておきたいと思います。マシンや設定については別の記事を書こう。

Re:mysqlでuserテーブルにダミーデータを10万件ほど入れる方法メモ

たまたま目に入った記事で、「mysqlでuserテーブルにダミーデータを10万件ほど入れる方法メモ」というものを拝読しました。
zenn.dev

そちらでは、ストアド・プロシジャを作成して10万回のループでINSERTを実施する方法を採っていましたが、折角の機会なのでお伝えしたいテクニックがあり、これを書きしたためる次第。

プロシジャってあまり使いたくない

 「実現できない」より「実現できる」ほうが100万倍エラいので、プロシジャを使った解法にたどり着いたことは立派なのですが、「実現できる」から「もっと良く実現できる」に進むと更に楽しい世界が待っています。ということで「もっと良く」を紹介します。

私はこの手の処理にあまりプロシジャ使用を選択したくないのは:

  • 使い捨ての処理なのに、プロシジャというオブジェクトをサーバ上に生成したり、消し忘れてゴミオブジェクトとして残ったりするのがイヤ
  • そもそもプロシジャ作るのが面倒(普段あまり作らないので)。デリミタ一時的に変えるとかからして、無駄な作業をしている感がキライ
  • ぐるぐる系の処理はヤだ(INSERTを 10万回もやるなんて全然DB的発想ではない)


といった理由があります。実際、このプロシジャ版を手元で動かしたところ、4分近くかかりました。

テーブル定義とプロシジャおよび実行方法を https://zenn.dev/mesi/scraps/48b6479d21e00d から引用します:

CREATE TABLE `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50),
  `email` VARCHAR(100),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER $$

CREATE PROCEDURE InsertDummyData()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 100000 DO
    INSERT INTO `user` (`name`, `email`)
    VALUES (CONCAT('User ', i), CONCAT('user', i, '@example.com'));
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;
CALL InsertDummyData();

それ、CTEでできるよ!

こういう処理を行いたいときに便利なのが、CTEです。
白状すると、私もこの構文の書き方をすぐに忘れてしまうので、書く前に毎度検索しています(笑)。

set @@cte_max_recursion_depth=100000

INSERT INTO user (name, email) 
WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<100000)
SELECT CONCAT('User ',n), concat('user',n,'pexample.com')  FROM num;

最初の set文は、cteの実行可能段数を緩和するための指定です。

笑っちゃうと思うんですけど(というか盛大に笑っちゃってほしいんですけど)、先ほど4分かかったこの処理が、どれくらいの時間でできると思います?

手元の環境では、 0.6秒程度でした。


私が若手の頃に、1時間半近くかかっていたクエリ(PL/SQL)が、ちょっと考えて処理の仕方を工夫したらヒトケタ分になったこと(その後更に改善して1分以内になったはず)という、工夫次第でダイナミックに効果が出る体験をしたことがきっかけで、DBMSの世界って面白いなと感じたので、これはそれに匹敵するくらいの差と言えるんじゃないかなと思います。

まとめ

ということで、

  • ぐるぐる系よりガッツリ系
  • CTE知ってると便利
  • 構文覚えてさらさら書けると格好良いけど私は覚えてないw

でした。


MySQL 8.4-LTSがやってきた&native_passwordに注意

お待ちかねの「MySQLのはじめての LTS」、MySQL 8.4.0 がリリースされました!

Note: If you are having trouble connecting to the upgraded MySQL 8.4.0 Server, please read the end of this blog.
(Summary of solutions for "unknown variable 'default-authentication-plugin" error, and "mysql_native_password' is not loaded" error)



status表示に、特に「LTS」をあらわすものはないようです。(variablesを確認していて「お。LTSって書いてあるじゃん!」と思ってよく見たら TLS でした。関係ない)

インストール

手元の Ubuntu 20.04の、既に MySQL 8.3.0が稼働している環境を、今回アップグレードしました。
apt update しても降ってこなかったので、リポジトリ側に 8.4-ltsが存在しているのを確認して(そう、この名前には「lts」がついているのです)、

Origin: MySQL
Label: MySQL
Codename: jammy
Architectures: i386 amd64 source
Components: mysql-apt-config mysql-8.0 mysql-8.4-lts mysql-innovation mysql-cluster-8.0 mysql-cluster-8.4-lts mysql-tools mysql-cluster-innovation mysql-tools-old mysql-tools-preview
Description: Apt repository for Oracle MySQL packages
SignWith: B7B3B788A8D3785C

手動で、/etc/apt/sources.list.d/mysql.list を書き換えちゃいました。

deb [signed-by=/usr/share/keyrings/mysql-apt-config.gpg] http://repo.mysql.com/apt/ubuntu/ jammy mysql-8.4-lts

そして、apt update / apt upgrade。

サーバが起動しない

アップグレードは完了したものの、mysqldサーバが起動しません。

2024-04-30T07:13:31.955229Z 0 [ERROR] [MY-000067] [Server] unknown variable 'default-authentication-plugin=mysql_native_password'.
2024-04-30T07:13:31.955815Z 0 [ERROR] [MY-010119] [Server] Aborting

数ある my.cnf 系設定ファイルを手繰っていき、私は /etc/mysql/mysql.conf.d/default-auth-override.cnf に、この設定があるのを見つけました。これをコメントアウト。サーバ再起動(というか起動)。

# This file is automatically generated by MySQL Maintainer Scripts
[mysqld]
default-authentication-plugin = mysql_native_password

クライアントから接続できない

サーバ起動指示はエラーなく完了したものの、こんどは mysql コマンドでの接続でエラーが発生しました。もうやだ。

ubuntu@vmubuntu:~$ mysql -uroot -p
Enter password: 
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded

救世主登場:


いとうさんありがとう!!!!!

ということで、どこの設定ファイルでも良いのだけど、先ほど設定をコメントアウトしたファイルがちょうどいいや、と(ファイル名も default-auth なので意味は間違ってないし)そのファイルに mysql_native_password=ON の記述を追加。mysqld再起動。
再度 mysql小文字5文字クライアントから接続

ubuntu@vmubuntu:~$ mysql -uroot -p
Password:
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

やったー!!

Conclusion

まとめると、

  • サーバが起動せず「unknown variable 'default-authentication-plugin=mysql_native_password'.」のエラーメッセージが表示されていたら、my.cnf系のどこかの設定ファイルに 「default-authentication-plugin = mysql_native_password」の設定があるので、コメントアウトしろ
  • その後クライアントからもつながらないと思うので、さっきコメントアウトしたファイルあたりにでも、「mysql_native_password=ON」の指定を追加しろ

ということになります。MySQL 8.4.0、いきなり動かなくて焦りました(笑)。


-

After upgrading MySQL 8.4:

  • If the server does not start on error "unknown variable 'default-authentication-plugin=mysql_native_password'.", you shoud comment out the "default-authentication-plugin = mysql_native_password" setting in my.cnf or in other cnf file.
  • After that, you will not able to connect the server by using mysql command-line client on error "ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded", then, you should add "mysql_native_password=ON" to the cnf file.
  • After rebooting mysqld, you can connect the MySQL 8.4.0 LTS! Enjoy it!!

追記

yyamasakiさんからも、この変更に関するリリースノートの記述を補足いただきました。

SQLパズル:離れ島を探せ!

SQLの未解決問題(坂井調べ)。解けたらIQ180! さぁキミはできるかな!?

・・・・とくだらないサイトの煽り文句のような出だしですが、「SQLで書けそうなのにうまく書けない」テーマがあります。ウデに自信がある人も自信がない人も、頭の体操として取り組んでいただき、教えて戴けると、わたしがとっても助かります(笑)。
書けそうなのになぁ・・・なにか私がちょっとしたところで勘違いしちゃっているだけのような気がするんだけどなぁ・・・・

追記:このエントリでは、「面を構成する辺」に注目して書いていますが、実際には(今回の目的である)MOJデータでは辺IDは共有されていないことがわかりました。そのため一番下のサンプルデータでは「面を構成する点」に話を置き換えていますが、本質的には違いはありません。

データ構造に関する説明

基本的な要素

「辺」に囲まれた「面」をあらわすデータです。面にはIDがあり、また、各辺にもそれぞれIDが付けられています。
以下の図は、面1 が、 辺101,102,103,104,105に囲まれて成り立っているものです。

データにするとこんな感じ。

面ID 辺ID
1    101
1    102
1    103
1    104
1    105
面はたくさんある

面は、辺を共有する形で複数(たくさん)存在しています。

先ほどの面1のデータに加えて、こんな感じ。

面ID 辺ID
2    104
2    106
2    107
2    108
4    105
4    110
4    111
4    112
4    109
:

(面3は図の中に辺IDをひとつ書き忘れてしまったので気にしないでください。。面3と面6で共有している線ね)

「島」の存在

さて、上の例(面1~面6までがある状態)のようにひとかたまりになった状態を「島」と呼びます。実はこのデータは、すべてがひとつの「島」になっているわけではなく、多数の「島」に分かれています。以下の図のように。

データは、これまで例示したように淡々と、面IDと辺IDのセットが格納されています。

さて問題です

ここで、出題です! 
本質的には同じ課題のような気がしますが、いくつかの設問にしてみました。すべてSQLで回答してください。

【問1】データはいくつの島から成り立っていますか(いくつの島に分かれていますか)

【問2】任意に「島ID」を決めて、面ID+島IDのリストを返してください。つまり一つの島の中にどの面が含まれているのかを知ることができる結果となります。島IDは構成する面のなかからMAXやMINなどで適当に決めたり、適当に連番にしてみたり等、なんでも構いません。

これはなに

お気づきの方もいるかもしれませんが、この設問、MOJ-XMLデータ(登記所備付地図のデータ)で遊ぼうとしたときにぶち当たってしまった課題でした。できると思っていたのになぁ(まだ言ってるw)。
実際のデータは、この「面」が数億件あります。
今回の「パズル」は課題をシンプル化しましたが、実際には各「面」にはその情報が書かれていた「ファイル」があって、そのファイルごとに「島の数」を求めたいのでした。その辺は、このパズルが解決すれば応用できることでしょう。


ということで、面白おかしく書いていますが、今日1時間くらい考えてこれを解決できなかったことが本気で悔しくてしょうがないので、解決できた方はぜひ披露してください!


サンプルデータ

実際のデータがないと萌えないよ、という人がほとんどだと思うので、用意しました(6月末までの期間限定公開)。5459件のデータです。
データに誤りがありました。というか、このエントリで説明してきた「辺IDが共有されている」は正しくなかったです(同じ辺でも別のIDが振られていた)。
実際に試して、ご指摘くださった皆さんありがとうございました。

ということで、改めてテーマを微修正します。面を構成する辺ではなく、面を構成する「点」に注目することにします。点IDは共有されていることを確認済みです。
(今回のデータでは例えば、F000000007と008で共有点が2つ、F000000006と007で共有点が2つ、などちゃんと重なっています!)


CREATE TABLE と INSERT文ですが、さくっと作成しただけで自分では動作確認していないので、誤りがあったらすいません適当に修正して使ってください(データは間違いないです→データは正しかったのだけどそもそも「辺IDが共有される」という発想自体が間違っていた...)。
surface_idが「面」のID、curve_idが「辺」のIDです。numはその面の中でのその線の順番を表すもので、今回は気にしなくて良いです。

面と点の関係にしたデータダウンロード(データセット2 :益城)ただしMAPPLEビューワの情報と変化している可能性あり
https://www.dropbox.com/scl/fi/9bce437lldnhuhm8o4z53/sql_quiz_moj_data_20240430b.txt?rlkey=6hxui0za4l67iv8iitygqyz35&dl=0

面と点の関係にしたデータダウンロード(データセット3 両国):
https://www.dropbox.com/scl/fi/fm5gl36pu1m356vhai2bx/sql_quiz_moj_data_20240430c.txt?rlkey=j0mi9kepm0lpqc7o4p7g96byn&dl=0


ダウンロードURL: https://www.dropbox.com/scl/fi/zut7jh73lnet1hmkr9uyo/sql_quiz_moj_data_20240430.txt?rlkey=dbxb6v0gwyo6itf5jx5hgdhbm&dl=0

このデータが表す地域は、以下のような島を表しています (MAPPLE法務局地図ビューワより*1)。
ただし、MAPPLEさんのビューワは現時点で 202308データのため、今回の 202404データとID等は異なっている可能性があります。

両国はたぶん変わってない。

なんだこりゃ。SQLの「IS DISTINCT FROM」演算子

発端

ふと、「IS NOT DISTINCT FROM」あるいは「IS DISTINCT FROM」という文字列が含まれるSQL文を目にしました。
SQLの基本的な構文として、

SELECT DISTINCT col1, col2 FROM t1; 

とか書くので、そのDISTINCT と FROM が、、、、とか考えていると混乱します。とりあえず「単にこういう長い名前の記号」と思っておくのがよさそう(笑)。

これはなに

Oracleから入り、その後MySQLをメインとするようになった私の通った道には、こんな構文はなかったわけです。それにしても、SQLiteでさえ(←ひどい言い草)対応しているのにMySQLにないってのは、ちょっと悔しい(笑)。

PostgreSQL 16 で動作を確認

こんなデータを作った。

\pset null (null)
create table t1 (id integer, s varchar(10));

SELECT * FROM t1;
 id |   s    
----+--------
  1 | AA
  2 | CBB
  3 | AA
  4 | CC
  5 | 
  6 | CC
  7 | 
  8 | (null)
  9 | (null)
(9 rows)

単純に結合するとこんな感じ(自分自身との結合を除外)。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.id<>tb.id;
 id |  s  | id |  s  
----+-----+----+-----
  2 | CBB |  1 | AA
  3 | AA  |  1 | AA
  4 | CC  |  1 | AA
 :
  5 |     |  9 | 
  6 | CC  |  9 | 
  7 |     |  9 | 
  8 |     |  9 | 
(72 rows)

値が同じのだけを抽出したい場合は、(今までの私の発想だと)イコールを使う。ここに null のものは現れない。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s = tb.s AND ta.id<>tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
  3 | AA |  1 | AA
  6 | CC |  4 | CC
  7 |    |  5 | 
(6 rows)

IS NOT DISTINCT FROM を使うと、nullも「nullという値だとみなして」一致比較をしてくれる。「nullという値」というとっても気持ち悪いパワーワードですが「みなして」ということで我慢することにします:-)

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<>tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  3 | AA     |  1 | AA
  1 | AA     |  3 | AA
  6 | CC     |  4 | CC
  7 |        |  5 | 
  4 | CC     |  6 | CC
  5 |        |  7 | 
  9 | (null) |  8 | (null)
  8 | (null) |  9 | (null)
(8 rows)

余談

本題ではないのだけど、今回試したクエリで、行きと帰りの一致データが重複して出ているのが気になりますよね。
今回は「IDが一致しないもの」ということで自分自身との結合を除外しましたが、この条件を「IDが自分よりも小さいもの」とだけ比較するようにすると片道切符になります。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)

最初からこちらでやっていたほうが、結果がシンプルになりましたね。


応用例

IS NOT DISTINCT FROM ではなく IS DISTINCT FROM を使うと、不一致のものにマッチさせることができます。
たとえば、値が'AA'のものと、これに一致しないもののIDの対応表を作りたい時に、こんなふうに。
対象としてnullも含まれるところが、新しいところです(べつに新しくないのですが、= とか <> しか知らなかった私には新しい)。

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s='AA' AND ta.s IS DISTINCT FROM tb.s AND ta.id<tb.id;
 id | s  | id |   s    
----+----+----+--------
  1 | AA |  2 | CBB
  1 | AA |  4 | CC
  1 | AA |  5 | 
  1 | AA |  6 | CC
  1 | AA |  7 | 
  1 | AA |  8 | (null)
  1 | AA |  9 | (null)
  3 | AA |  4 | CC
  3 | AA |  5 | 
  3 | AA |  6 | CC
  3 | AA |  7 | 
  3 | AA |  8 | (null)
  3 | AA |  9 | (null)
(13 rows)

まとめ

今回の構文が長いので IS DISTINCT FROM を [コレ] と書くとすると、
A [コレ] B は、AがBと違っているときに成立(true)、
A NOT[コレ] B は、AがBと違ってないとき(笑)、、、つまり同じ時ですね、、に成立。(正確にはNOTの位置は IS NOT の場所になります)

既にSQL構文の中で使われている DISTINCT とか FROM という単語をこういう形でまったく違う用途で使うのって、仕様決めた人はセンスないよなと私は思ってしまうのですが、たぶん私など理解不能なレベルで考えぬいた末に、きっとセンスの塊の成果として決まったものだと思います(思いたい)。

軽く調べてみたのですが、 SQL-92には含まれていなくて、SQL-1999で登場した記法のようです。
近年のMySQL開発はどんどん、なるべく標準に準拠するようにとうことを心がけているように見えるので、そのうちこの構文が導入されたりするのかな。新機能好きなので楽しみに待ちたいと思います。導入されたら「知ってる!知ってる!導入前から注目していたんですよ!」と自慢できるように、このエントリを書いておきました(そんなわけじゃないw)。




参照

Twitter(X)で色々おしえてもらいました。ありがとうございました!

Snowflakeのマニュアル、わかりやすい。
https://docs.snowflake.com/ja/sql-reference/functions/is-distinct-from


追記

要するにこういうことなのか。
何か新しい事ができるようになったというよりは、一種のシンタックスシュガーと捉えても良いのかな。
(ここで「絶対に既存のデータと被らない置換文字列」を決める部分で「絶対」を保証できないので、IS DISTINCT FROM の存在意義が出てくるわけですが)

db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE COALESCE(ta.s,'*+;:')=COALESCE(tb.s,'*+;:') AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s=tb.s AND ta.id<tb.id;
 id | s  | id | s  
----+----+----+----
  1 | AA |  3 | AA
  4 | CC |  6 | CC
  5 |    |  7 | 
(3 rows)
db=# SELECT ta.*, tb.* FROM t1 ta, t1 tb WHERE ta.s IS NOT DISTINCT FROM tb.s AND ta.id<tb.id;
 id |   s    | id |   s    
----+--------+----+--------
  1 | AA     |  3 | AA
  4 | CC     |  6 | CC
  5 |        |  7 | 
  8 | (null) |  9 | (null)
(4 rows)