自宅からXServerのMySQLへ接続
[Home]

 MySQLに外部接続

 最初に

MySQL は何かと言えば、言わずと知れた 無料のデータベースであり、外部接続とは何かと言えば自宅などからレンタルサーバーのデータベースに接続する事です。
そんなのは出来て当たり前だと思うでしょうが、近年はセキュリティ上の理由から、SSH を使って暗号化通信をしないと接続が出来なくなっております。
XServer画像
一般の方が自宅からレンタルサーバーのデータベースをアクセスする事は無いでしょう。 ここで言うアクセスとは、リアルタイムで膨大なデータをレンタルサーバーのデータベースにアップロードする事を言います。 私は競馬予想を行っておりますので、過去２０年間程度の競馬データをデータベースに蓄積しております。 競馬は毎週の土日の開催は当然ですが、祭日でも開催される事がありますので、データは溜まる一方です。

勿論、最近はHDDも大容量化されましたし、SSD も安価になって自宅にデータベースを構築しても、さほど大きな問題はありませんが、（私の場合）今年の１１月から借りた、XServer と言うレンタルサーバーでは 200GB と言う気が遠くなりそうな容量が使えますので、どの程度の実用性があるのかのテストも兼ねて自宅から接続する事にしました。

レンタルサーバーのデータベースにアクセスするには、phpMyAdmin と言うブラウザ経由でアクセスする方法もありますが、極小規模のデータベースならまだしも、２００万件を超えるデータを扱うには処理の遅さが目立ち、セキュリティ上の問題点も指摘されておりますので、会社では使用している所は無いでしょう。

個人の使用でも、他人の個人データを登録するような用途には使用するべきでは無いです。私も他人のデータ（名前や住所、電話番号、メールアドレス）などの情報は外部のデータベースには一切登録しません。
自分の情報を登録する場合でも、データを登録するのは、どうしても必要な場合のみにしております。

データベースの用途は限られるでしょうが、データベースのバックアップとしてなら役に立ちそうです。
競馬データベースの場合は、それを利用して WEB 上で競馬予想を行わせる事も可能ですが、実用的な競馬予想を行うとなると別の難しさがあります。


（XServer の社内案内に掲載されていた画像を一部加工して作成）



 MySQL の設定方法（サーバー側）

MySQL の設定を行うには、まずサーバー側での設定を行います。XServer の場合は、 サーバーパネル を呼び出します。 そして、以下のように「MySQL設定」を選択します。
ssh0画像

次に、接続する MySQL のデータベースを作成します。
mysql1画像

私の場合は、excelumachan_uma と言う名前のデータベースを作成しました。 レンタルサーバーですので、名前も任意な名前には出来ません。
ちなみに、他のデータベースが２つありますが、excelumachan_co5 と言うデータベースは、concrete5 用のデータベースですし、excelumachan_wp1 と言うデータベースは、WordPress で使用しているデータベースです。 どちらも、インストールをしてみただけで、運用はしておりません。
concrete5 も WordPress も、大勢の人が WEB 作成に関与している環境に向いているだけで、個人が使用するのは面倒な上、画面の表示がワンテンポ以上遅くなります。
データベースの作成も「サーバーパネル」を使えば「超楽ちん」で、これでも作成出来ないなら自分の能力の低さ（幼稚園児以下）を嘆いてください。
concrete5 のインストールも文化系の人には（多分）難しいでしょうが、理工系の人なら難しさを感じないでしょう。（どうでも良い事ですが）


 MySQL Workbench

データベースの接続には、MySQL Workbench を使用するのが一般的なようです。他にもありましたが、このソフトは使用する人が多いので解説サイトも数多く存在しています。
供給サイトは、https://www-jp.mysql.com/products/workbench/になります。 「ダウンロードはこちら」のボタンを押して以下の最新ファイルをダウンロードします。

mysql4画像
最初に32ビット版をダウンロードしようとしましたが、案内には32ビット版（x86）も書いてありますが、64ビット版しかダウンロード出来ませんでしたので、64ビット版でWindows7の64ビットにインストールしました。 32ビット版はバージョンが古い物ならありましたが、変なトラブルに合う場合もありますので、64ビット版にしたものです。
インストールは問題になりそうな部分はありませんので、解説は省略（手抜き）させていただきます。XServer への接続設定（Connect to Database）は以下の通りです。

mysql3画像
最上部の赤丸で囲った「Standard TCP/IP over SSH を左の逆三角形で選択すると、以下の入力画面になります。
タブには、SSL とか Advanced もありますが、一切手を触れる必要はありません。
上部のStored Connection は空白でも可能でしたが、接続確認が出来たと言うだけの段階ですので、実際の使用上の細かい事は時間が取れれば追記したいと思います。

この設定は、私の環境での設定内容（サーバー番号等は適当）ですが、設定方法は（多分）合っております。
①は XServer から設定完了通知を受け取った時に書いてある「サーバー番号」を記載します。後ろに「:10022」を追加記載するのを忘れないようにしてください。
サーバーパネルにある「アカウント情報」の「サーバー情報」のホスト名にも同じ内容が記載されています。
②は XServer から支給された「サーバーID」になりますが、元々は自分が決めたドメインになっていると思います。
③は XServer から支給されたサーバーパスワードを記入してください。
④は「秘密鍵」のリンク先です。供給されたキーのままで構いません。
「秘密鍵」の供給を初めて受ける場合は、 XServerをSSH化 を先に行ってください。「秘密鍵」を再発行した場合は、以前の「秘密鍵」で動作していたソフトは動かなくなります。
⑤は MySQL のサーバーアドレスを記載します。 サーバーパネルの「MySQL設定」の下部の MySQL5.7 情報の「MySQL5.7 ホスト名」の内容を記載します。
⑥は 作成したデータベース（私の場合は excelumachan_uma）のアクセス権所有ユーザ（私の場合は excelumachan_co5）を記載します。
⑦は MySQL データベースのパスワードを記載します。（まさか、パスワードを空白にした人は居ないでしょうね）
⑧は 起動時に接続するデータベースを記載します。 アクセス権所有ユーザは、多くのデータベースを管理しますので、指定した方が便利でしょう。（空白での動作は未確認）
お断りしておきますが、記載している説明は正しいとは限りません。正確な情報を求められる場合は、 XServer にお問い合わせください。

以下が接続が完了した時の画像になります。 日本語版は旧バージョン用しかありません。 それを改造する方法も出ておりますが、面倒で止めた方が利口です。
mysql5画像
どうやら接続は上手く行ったようですが、MySQL Workbench の使い方は（私の場合）勉強しなければなりません。
レンタルサーバーのデータベースですので、転送量の制限などもあるようで、運用は注意して行いたいと思います。


 コマンドラインで接続

MySQL Workbench 使えるので MySQL の操作は楽になりましたが、リアルタイムでデータを更新するには無理があります。
最初 Tera Term Pro で SSH の接続を試みたのですが、XServer に繋がらなくて、PUTTY （パティ）をインストールしてSSH 接続をやってみたら、あっさりと接続出来ました。

PuTTY は、Simon Tatham氏が開発した、オープンソースの端末エミュレータソフトウェアで、sshプロトコルだけでなく、telnetやrlogin、シリアルポート通信などにも対応しています。 但し、オリジナルバージョンでは欧文文字しか表示できませんので、日本語表示を可能にしたバージョンが有志によって公開されています。
日本語化された PuTTY は、 PuTTY-ranvis からダウンロードできます。（最新バージョンの 0.70 にも対応しております）

起動時の画面は以下のようになります。
mysql6画像
XServer の接続の参考例ですが、「接続」の 「SSH」 の「認証」を選んで「秘密鍵」のパスを指定すれば接続できます。
「秘密鍵」のタイプは、ppk タイプが必要でしたが、「WinSCP」をインストールした時にも ppk タイプが必要で、変換を行っておりましたので、それを流用しました。
変換が必要な場合は、同梱ソフトの「puttygen.exe」を使うと変換できるらしいですが、私は試しておりません。
解説によると、変換方法は以下の通りです。
（１）puttygen.exeを起動
（２）[Load] をクリックしてid_rsa （XServer の場合は、****.key と表示）をロード
（３）ロードできたら、[Save private key] をクリックしてppkファイルを保存
XServer から支給された RSA形式の「秘密鍵」を ppk タイプに変更する方法は、探せばいくらでも見つかると思います。


 Tera Termのマクロ接続

試行錯誤して Tera Term でも「秘密鍵」を使用して接続が可能になったのですが、いちいちパスワードを入力するのが煩わしく、パスワードの入力を自動化したいと思っていたら Tera Term にはマクロ機能があって自動的に接続が可能でした。
しかも、SSH を使用してマクロでの接続例の プログラムを公開 してくださっている方が居りましたので、利用させていただく事にしました。
様々な接続例が記載されておりましたが、私の場合は「秘密鍵」を使用してメインサーバーに接続した後に、そこから MySQL サーバーに接続しようとしております。
プログラムは以下のようになります。

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
username = 'umachan'
hostname = 'sv7777.xserver.jp'
keyfile = 'C:\Users\user\umachan.ppk'
passwdfile = 'C:\Users\user\umachan.dat'
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

msg = 'Enter password for user '
strconcat msg username
passwdkey = username
strconcat passwdkey '@'
strconcat passwdkey hostname
getpassword passwdfile passwdkey password

msg = hostname
strconcat msg ':10022 /ssh /auth=publickey /user='
strconcat msg username
strconcat msg ' /keyfile='
strconcat msg keyfile
strconcat msg ' /passwd="'
strconcat msg password
strconcat msg '"'
connect msg

ファイル名は、XServer.ttl にしてディスクトップに置いてダブルクリックすると自動的にメインサーバーには繋がって、コマンド入力待ちになりました。

【注記】
拡張子 ttl は、Tera Term が独自に設定しておりますので、関連付けをしないと動作しません。
関連付けを行う方法が分からない方は、再インストールをして、「ttl に関連付け」の項目にチェックを入れるのが確実なやり方です。

そこから XServer の MySQL サーバーにも自動的に繋ぐ場合は、Tera Term の「設定」から 「SSH転送」を選択します。
mysql13.png画像

「追加」ボタンを押します。
mysql14.png画像

以下のように記入してから「OK」ボタンを押します。
mysql15.png画像
①はローカル側のポート番号です。
②は、XServer 側の MySQL のデータベース番号(データベースアドレス）を記入します。
③は、XServer 側のポート番号です。

追加されているのを確認して「OK」ボタンを押します。
mysql16.png画像

「設定の保存」を行って、作成した内容を保存します。
mysql17.png画像
これで、Tera Termのマクロ接続を行った場合は、XServer のMySQL に接続した状態のコマンドプロンプトになります。
この状態を維持しておけば、MySQL ODBC との接続も可能になります。


 PowerShellで接続

PowerShell でも XServer に SSH 接続をしてみました。なんで、そんな事をしたのかと言うと、あるサイトで以下のポートフォワードツールを片っ端から試したそうです。
1. putty
2. poderosa
3. TeraTerm
4. xshell
5. Win32-OpenSSH
そうしましたら、Win32-OpenSSH 以外は、データの転送が激しいと堕ちてしまったそうです。
それじゃ、PowerShell でも XServer に接続出来るようにしてみようと考えて接続させました。

マイクロソフトの OpenSSH のダウンロード先は、 https://github.com/PowerShell/Win32-OpenSSH/releases になります。
通常はシンボルなしの「OpenSSH-Win32.zip」または「OpenSSH-Win64.zip」のどちらかを選んで、Cドライブのルートフォルダーに「OpenSSH」というフォルダ（C:\OpenSSH）を作り、そこにインストールするのが無難だそうです。
Program Files や Program Files(x86) フォルダには、書き込み制限がかけられていて、かつ過去のアプリケーションとの互換性のためにフォルダ仮想化の仕組みが働くため、後々面倒になる場合が多いとの事です。 ここまで書いて気が付きましたが、私がくどくど説明するよりも、 こちらのサイト をご覧いただいた方が時間を無駄にせずに済みます。

と、他所のサイトに丸投げしましたが、そのサイトが知らない間に消えている事もありますので、一応私の設定例も記載しておきます。
ただ、私の場合は、Windows7 64bit の例ですが、Windows8 や Windows10 では、コマンドが異なったり、設定する画面が異なったりします。

数サイトの解説を拝見しましたが、微妙に設定方法が異なっており、私はかなり戸惑いました。
私の場合は、PowerShell を使って設定を行いました。 まず、Set-ExecutionPolicy Unrestricted と入力して、スクリプトの実行禁止を解除します。
こうしなくても、-ExecutionPolicy Bypass -File と頭で宣言すれば良いようですが、スクリプトの実行禁止を解除しても、終わったら元に戻せば良いので、こちらの方が楽でしょう。 次に、netsh advfirewall firewall add rule name="sshd" dir=in action=allow protocol=TCP localport=10022 profile=private と入力しましたが、この例の場合は localport=10022 に穴を開けるためです。 ただ、これは　Windows7 や Windows10 の場合ではのようで、通常は New-NetFirewallRule -DisplayName "sshd" -Direction Inbound -Protocol TCP -LocalPort 10022 -Action Allow とするそうです。（どちらが良いのか、あるいはどちらでも良いのか、分かりません）

次に、sshd と ssh-agent サービスのインストールを以下のコマンドで行います。
install-sshd.ps1
mysql22.png画像
ここには記載しておりませんが、公開鍵ペアを作成する場合は ssh-keygen.exe -A と入力します。 ほとんどのサイトではやっておりますので、私も実際は行っております。
あるサイトでは、その後に reg add HKLM\System\CurrentControlSet\Control\Lsa /v "Authentication Packages" /t REG_MULTI_SZ /d msv1_0\0ssh-lsa.dll -f としてから copy .\ssh-lsa.dll %SystemRoot%\system32\ の記載がありましたが、（必要性とセキュリティ上どうなのかが不明で）そこまでは私はやっておりません。

又、手動で OpenSSH フォルダ内にある sshd_config.txt を開いて、以下の変更を行いました。
Port 10022 （コメントアウトを外して、番号を 22 から 10022 に変更）
Subsystem sftp C:\OpenSSH\sftp-server.exe （sftp-server.exeのフルパスを記載）

次に SSHD の自動起動の設定と起動を行います。 これもほとんどの解説サイトで行っています。
Set-Service sshd -StartupType Automatic
Set-Service ssh-agent -StartupType Automatic
net start sshd
環境変数でリンクが OPenSSH に張ってあれば、設定時にエラーが出る事は無いでしょう。
後は Set-ExecutionPolicy Restricted コマンドでスクリプトの実行禁止を解除したのを元に戻せば完了です。

XServer の設定コマンドは、ssh.exe -i c:\users\User\umachan.key -p 10022 umachan@sv7777.xserver.jp としました。
勿論、これはセキュリティ上の都合もあって、固有の内容はアレンジして記載しております。 これで最初に接続した時の画像を以下に示します。
powershell1画像
説明する程の内容ではありませんが、c:\users\User\umachan.key が XServer から支給された「秘密鍵」のフルパスになります。
-p 10022 が接続するポート番号です。 XServer では、SSH のポートが、22 では無くて 10022 なのはご存じの通りです。
umachan@sv7777.xserver.jp が、「サーバーID」と「サーバー番号」を＠でつないでいるだけです。

上記は Windows7 64bit で接続した時の画像ですが、Windows7 32bit での接続も行ってみました。（環境設定ファイルを作成済の場合です）
XServer の設定コマンドは、ssh.exe -i c:\users\User\umachan.key -p 10022 umachan@sv7777.xserver.jp で全く同じです。
powershell2画像
パスワードの入力が２回あります。 ①は「秘密鍵」のパスワード入力で、②はMySQL のパスワード入力です。
コマンドラインで MySQL を操作するのを面倒で分かり難いと嫌う人は多いですが、それは素人の人達だけで、玄人は phpMyAdmin などは決して使いません。
もし、そんな人が居たら、単なる自称プロであって、あんな危なっかしくて、かったるいソフトなど、私のような素人でも使いませんよ。

余談は兎も角、この状態で MySQL ODBC を使った接続も出来る筈なのですが、やってみたらコネクションが切断されておりました。
OpenSSH フォルダに sshd_config.txt と言うそれらしき設定ファイルがありましたので、ServerAliveInterval のコメントアウトを外して、設定値を 0 から 60 変更しました。
コネクションの切断は起きなくなったようですが、PowerShell を使って MySQL ODBC で接続する方法が掴めておりません。
他の方法で接続が出来ますので不便ではありませんが、接続ができるコマンドを試行錯誤して探しております。

PowerShell で MySQL ODBC に接続する方法を探している過程で、PowerShell でエイリアスの登録が出来る事を知りました。
エイリアスを知らない幼稚園児向けに解説すると、ssh.exe -i c:\users\User\umachan.key -p 10022 umachan@sv7777.xserver.jp をコピペ入力していたものを、例えば xserver と入力するだけで済むようになります。 x の一文字だけの設定にも出来るのですが、短かければ良いと言うものでも無いでしょう。
尚、幼稚園児レベルの人は手を出さない方が良いです。 設定に失敗して OS の再インストールをするはめにでもなったら、当サイトの悪評を世間に振り撒かれるだけですから。

エイリアスファイルを置く場所は決まっておりますが、存在しておりませんので、以下のコマンドで新規にフォルダを作成します。
New-Item -path $profile -type file -force
mysql18画像

PowerShell でメモ帳を開いて以下のように記載します。（当然ですが、当サイトの場合の設定例です）
notepad $profile
メモ帳には以下の内容を記載します。 # はコメント記号です。

#xserver
function xserver {
	ssh.exe -i C:\Users\User\umachan.key -p 10022 umachan@sv7777.xserver.jp
}

mysql20画像
同様の書式で、何個でも追加する事が出来ます。 頻繁に接続先を変える場合は便利です。

PowerShell を再起動すると以下のようなエラーが表示されますので、続けて　Set-ExecutionPolicy RemoteSigned コマンドを実行します。
mysql19画像

PowerShell を再起動すると、今度はエラー表示しなくなりましたので、設定した xserver を入力してみます。
mysql21画像
エイリアス機能が動作しているのが確認できます。
「秘密鍵」のパスワード入力を避ける方法もありますが、幼稚園児向けですので止めた方が良いです。
そんな事をしたい人は、何のためにセキュリティを向上させているのか分かっておりません。

そんな事は兎も角、ようやく、PowerShell から XServer の MySQL に MySQL ODBC で接続するコマンドを発見しました。
ssh -i C:\Users\User\umachan.key -o ServerAliveInterval=60 -p 10022 umachan@sv7777.xserver.jp -L 3306:mysql1111.xserver.jp:3306
上記のコマンドを打つと、XServer の MySQL にトンネルが掘られますから、MySQL ODBC で接続が出来る状態になります。
記載されていたのは外国のサイトでしたが、そこでは接続例として以下のように記載されておりました。
ssh -v -N -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -i /Users/eugene/Downloads/aws-sandbox.pem -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 ec2-user@xx.xx.xx.xx -L 51141/127.0.0.1/3306
実に細かい設定ですね。 日本のサイトには、ここまで細かく接続例が書いてあるのを見た事がありません。
これを見る事が出来たあなたは幸せ者です。（とっくの昔に知っていたって？ 嘘こけ！）

[Home]