Go to Qiita Advent Calendar 2024 Top
LoginSignup
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ringtail003in株式会社カルテットコミュニケーションズ

ターミナルでリモートサーバーのMySQLに接続するシェルスクリプト

Last updated at Posted at 2024-05-06

日ごろ、ステージングサーバー、本番サーバー、社内サーバーと複数サーバーのデータベースにSSH接続することがあります。

知識人にポートフォワーディングのやり方を教えてもらい自作のシェルスクリプトで接続していたのですが、何回もサーバーに接続すると前回接続したポートが解放されないことがありました。

コマンド実行中だけSSHポートフォワーディングを有効化する を読んでこれだ！と思ったので、ブラッシュアップした自作シェルスクリプトを紹介します。

対象読者

シェルスクリプトあまりよく知らないし、詳しい解説よりもコピペで簡単に接続できる即効性を求めてます、という自分と同じ人。

概要

構造はこんな感じです。サーバーと踏み台が複数あります。

overview.png

サーバー1に接続したつもりで、うっかりサーバー2に対してUPDATEとかDELETEすると事故るので「本番サーバーに接続する」ことや「本番サーバーのデータベースを操作している」ことがわかると嬉しい感じです。

＜シェルスクリプトを動かす環境＞

  • SSH
    • 公開鍵認証でサーバーにアクセスできることを前提とします。
  • ZSH
    • 接続先の候補選択にファイル補完を利用します。
  • MySQLクライアントツール
    • サジェストなど表示する mycli を使っていますが、要らない場合はMySQLコマンドに読み替えてください。

＜デモ画面＞

sdb- をタイプして、タブキーで接続先サーバーを選択します。

1.gif

本番サーバーに接続する時はうっかり操作を防ぐため戒めのメッセージを表示し、確認の y をタイプして接続します。パスワードはサーバーのデータベースが要求するものです。

2.gif

踏み台サーバーを設定する

SSHの設定ファイルに踏み台サーバーを記述します。

~/.ssh/config
Host fumidai1
  Hostname 192.168.1.100
  User user1
  Port 22
  StrictHostKeyChecking=no

Host fumidai2
  Hostname 192.168.1.200
  User user1
  Port 22
  StrictHostKeyChecking=no

シェルスクリプトを作成する

任意のフォルダを作り sdb-{サーバー名} という名前のシェルスクリプトを作成します。「SSHでDB接続」の安易なネーミングなのでプレフィクスは適当に変更してください。

mkdir ~/sdb && cd $_
touch sdb-foo

シェルスクリプトの中身を転記します。

sdb-foo
#!/bin/bash

PROMPT=foo # mycliのプロンプト、操作中のデータベースやサーバーを判断する任意のテキスト
REMOTE_PORT=4000 # SSH接続のポート、空いているローカルポートならなんでも良い
LOCAL_PORT=3306 # mycliのポート
DB_NAME=foo # 接続するデータベース
REMOTE_HOST=192.168.1.101 # 本番サーバーのアドレス
USER_NAME=user1 # データベースのログインユーザー

# 戒めのメッセージ
echo -e "(esc)[37;41m === CONNECTS TO PRODUCTION SERVER ===(esc)[m"
echo -e "(esc)[31m If the process is aborted, kill port ${REMOTE_PORT} manually.(esc)[m"

# 確認キーのyを要求
read -p "(esc)[31m Do you want to continue? (y/n): (esc)[m" CONFIRM
if [ "${CONFIRM}" != "y" ]; then
  echo "Goodbye!"
  exit 1
fi

# SSH接続
ssh -NL $REMOTE_PORT:$REMOTE_HOST:$LOCAL_PORT fumidai1 &

# バックグラウンド実行のプロセスIDを格納
PID=$!

# mycliでデータベースに接続、タイムラグがないとうまく接続できなかったので1秒待機
sleep 1
mycli -u $USER_NAME -D $DB_NAME -h 127.0.0.1 -P $REMOTE_PORT --prompt="(esc)[31m $PROMPT \R:\m > (esc)[m"

# mycliを抜けたらポートを解放
kill $PID

(esc) は実際にはbash上で制御キー（エスケープ）が入力されています。コピーして使う時は入力しなおしてください。viのINSERTモードで Ctrl + Q に続けて esc をタイプし、続けて esc で抜けると入力できます。
escape

ファイルをコピーして設定値を変更すれば、他のサーバー用に展開できます。

sdb-foo-stage
PROMPT=foo-stage # プロンプトを変更
REMOTE_PORT=4001 # ポート番号をずらしたほうが同時接続する時に便利
LOCAL_PORT=3306
DB_NAME=foo
REMOTE_HOST=192.168.1.201 # サーバーのアドレスを変更
USER_NAME=user1

戒めは接続先サーバーに合わせてゆるめにアレンジできます。

sdb-foo-stage
# 黄色の文字
echo -e "(esc)[33m === CONNECTS TO STAGE SERVER ===(esc)[m"
echo -e "(esc)[33m If the process is aborted, kill port ${REMOTE_PORT} manually.(esc)[m"

# 確認キーのyを要求しない
# read -p "(esc)[31m Do you want to continue? (y/n): (esc)[m" CONFIRM
# if [ "${CONFIRM}" != "y" ]; then
#   echo "Goodbye!"
#   exit 1
# fi

踏み台が異なる時はsshコマンドの指定を変更します。

# fumidai2を使う
ssh -NL $REMOTE_PORT:$REMOTE_HOST:$LOCAL_PORT fumidai2 &

シェルスクリプトのパスを通す

実行権限を付与します。

chmod +x ./sdb-* 

ls -la
> -rwxr-xr-x   1 ringtail003  staff  555  5  6 sdb-bar
> -rwxr-xr-x   1 ringtail003  staff  669  5  6 sdb-foo
> -rwxr-xr-x   1 ringtail003  staff  669  5  6 sdb-foo-stage

ZSHがシェルスクリプト群を探せるよう、フォルダのパスを通します。

~/.zshrc
export PATH="$PATH:$HOME/sdb"

ZSHの設定を再読み込みします。

source ~/.zshrc
rehash

データベースに接続する

ターミナルで sdb- とタイプして、接続するサーバーを選択します。

sdb-

> sdb-foo
> sdb-foo-stage
> sdb-bar

なにかSQLを実行してみましょう。

foo 17:30 > select * from user;
+---------+-------------------------+
| id      | email                   |
+---------+-------------------------+
| 1000000 | testuser1@example.com   |
| 1000001 | testuser2@example.com   |
+---------+-------------------------+

接続中に lsof コマンドを叩くと、同じポートを使用するプロセスがいくつか起動しています。上2行がSSH接続、下2行はmycliのプロセスを示しています。

> === CONNECTS TO STAGE SERVER ===
> If the process is aborted, kill port 4001 manually.

lsof -i:4001

> COMMAND   PID        USER   FD   TYPE     DEVICE SIZE/OFF NODE NAME
> ssh     11111 ringtail003    5u  IPv6 0xaabb1111      0t0  TCP localhost:terabase (LISTEN)
> ssh     11111 ringtail003    6u  IPv4 0xaabb1111      0t0  TCP localhost:terabase (LISTEN)
> ssh     11111 ringtail003    7u  IPv4 0xaabb1111      0t0  TCP localhost:terabase->localhost:51114 (ESTABLISHED)
> Python  22222 ringtail003    4u  IPv4 0xaabb1111      0t0  TCP localhost:51114->localhost:terabase (ESTABLISHED)

exit でmycliを抜けます。

foo 17:30 > exit;
Goodbye!

mycliを抜けるとSSH接続のポートが解放されます。

lsof -i:4001

シェルスクリプトのシンタックスエラーなどで異常終了するとポートが解放されないので、そんな時は自力でポートを解放します。

lsof -i:4001

> COMMAND   PID        USER   FD   TYPE     DEVICE SIZE/OFF NODE NAME
> ssh     11111 ringtail003    5u  IPv6 0xaabb1111      0t0  TCP localhost:terabase (LISTEN)

kill 11111

おわりに

セッション切れるのが早かったりとまだ改良の余地はありますが ssh -fNL foo.example.com && mycli... とアナログにコマンド叩いていた頃に比べると便利になりました。

良ければコピペでお使いください。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?