実践Linux
RockyLinux8        RockyLinux8 目次へ  TOP(HOME)へ


SSH+MariaDB+Excel          2021年7月

ここではSSHトンネル(あるいはVPNトンネル)を使って、遠隔地のWindowsのExcelからサーバーのデーターベスを操作してみます。とても便利です。
サーバー側では、すでにMariaDBとSSH(あるいはVPN)の設定が終わっているとします。

VPNの場合は、openVPN参照。ここでは、少しトンネル設定が面倒なSSHについて取り上げてみます。

WindowsのSSHクライアント(Putty)
●Windows SSHクライアントPuTTY-ranvisの導入
入手 PuTTYrv (PuTTY-ranvis) - Ranvis software
puttygen.exe(秘密キー、公開キーの生成・変換ツール)、pageant.exe(認証エージェント)も同梱されている。

●鍵セットの作成(windowsクライアント側で作成する方法)
puttygen.exeを実行。
RSAが選択されていることを確認して、Generateボタンを押す。
途中でランダムシード作成のためマウスを動かすように促されるのでマウスを動かし続ける。
パスフレーズを入力する。(2カ所同じパスワードを入力)
「公開鍵を保存」を押して、公開キーを保存する。(分かりやすくkeyフォルダを作成して保存とよい。)
「秘密鍵を保存」を押して、秘密キーを保存する。
ここではputty_rsa.pub、secret.ppk名で保存したとする。

●linuxサーバーへの公開キー登録
公開キーputty_rsa.pubをlinuxサーバーに移動。
これをopenSSHが使用可能な形式に変換する。(ここではputty.pub名で変換するとする。)
# ssh-keygen -i -f putty_rsa.pub > putty.pub
変換した公開キーをSSH接続したいユーザーの~/.ssh/authorized_keysファイルに登録する。(当然このユーザーはbashなどのログインシェルが必要。nologin等ではssh接続できない。)
ここではuser01に登録。
$ mkdir /home/user01/.ssh
$ cat putty.pub >> /home/user01/.ssh/authorized_keys
$ chmod 644 /home/user01/.ssh/authorized_keys
authorized_keysファイルには複数の公開キーを登録することができる。

●通常のSSH接続をやってみる
puttyjp.exeを実行。
セッション  接続先のIPアドレス user01@333.333.333.210 ポート10055(SSHサーバー側で22から変更済) プロトコルSSH
接続→SSH→認証  プライベート鍵の場所を指定(ここではsecret.ppk)
ウィンドウ→変換  文字コードの設定 UTF-8を指定
セッションに戻り以上の設定を保存しておくと次回接続から便利。
「開く」で接続開始。
鍵のパスフレーズの入力。
サーバーのターミナルが開く。

●トンネルの設定(MariaDBサーバーにトンネル接続)
上記「通常のSSH接続」に次の設定を加えてやる。
接続→SSH→トンネル
 受け側ポート  1111
 送り先  127.0.0.1:3306 
       SSHサーバーから見たMariaDBサーバーを指定。ここではSSHサーバーと接続先ホストが同一なので、localhost:3306または127.0.0.1:3306
 「ローカル」がチェックされていることを確認。
 追加ボタン。
この設定もセッションに戻り保存しておくとよい。

「開く」でトンネル接続開始。
鍵のパスフレーズの入力。
サーバーのターミナルが開く。

複数の別サーバー(例えばSambaサーバーなど)へ接続したい場合には受け側ポートを変えて行う(1112、1113 使われていないポートを選ぶ)。

●ExcelなどWindowsアプリケーション
MariaDBサーバーから見ると、ユーザーは SSHサーバーからアクセスしているように見えるが、一方、アクセスするアプリケーション側は、自分のマシンをMariaDBサーバーとして設定する必要がある。つまり、アプリケーションにはアクセスするMariaDBサーバーとして自分自身localhost:1111を指定する=自分自身をMariaDBサーバーと見立ててポート1111に送るとSSHトンネルを通して本物のMariaDBサーバー(ポート3306)に届くことになる。
つまり、SERVER=localhost;PORT=1111を指定(自分自身を指定)。

1.MariaDBクライアントは自分自身にアクセスをする
2.SSHのトンネルを通り、SSHサーバへアクセス
3.SSHからMariaDBへアクセス
4.MariaDBがSSHサーバーへレスポンス
5.SSHのトンネルを通り、クライアントへ帰る
6.自分自身からレスポンスが返ってくる


WindowsのExcel+ADOでデータベースにアクセスする

●MyODBCのインストール(Windoes10)
データベースをExcelから操作するには、データベースとExcelなどのアプリケーションソフトウェアとを結ぶ「ODBCドライバ」が必要。
MySQL専用のODBCドライバをWindowsにインストールする。
https://dev.mysql.com/downloads/より、
Connector/ODBC → 「Microsoft Windows」を選択して、「Go to Download Page」
Windows (x86, 32-bit), MSI Installer 8.0.21 24.5Mをダウンロード(2020/7現在)
インストール
serverは必要なければ、外す。

インストール完了後はコントロールパネルの管理ツールのODBC data sources(32bit)を開く。
システムDSNタブの追加ボタンを押す。
MySQL ODBC 8.0 Unicode Driverを選択し、Data Source Nameを適当に。あとはそのままでかまわない。

Windows7
コントロールパネルからデータソースを起動してもMySQLのODBCは表示されない。
この場合は、コントロールパネルから起動せず、\Windows\SysWOW64\にある「odbcad32.exe」を起動する。


●マクロの作成例
マクロを実行する際は、その前に必ず、Puttyでサーバーに接続しておきます。
データの読み込み(取得型)マクロ

Sub データ取込()

Dim myCon As New ADODB.Connection
Dim myRS As New ADODB.Recordset
Dim myStr As String

Set myCon = New ADODB.Connection
myCon.Open "Driver={MySQL ODBC 8.0 Unicode DRIVER}; SERVER=localhost; PORT=1111; DATABASE=mydb; USER=mydb; PASSWORD=12345;"
  ← Driver={MySQL ODBC 8.0 Unicode DRIVER}の部分はインストールしたODBCドライバに合わせる。12345はデータベースmydbに対するパスワード。

myStr = "SQL文;"

Set myRS = New ADODB.Recordset

With myRS
  ActiveConnection = myCon
  Source = myStr
  Open
End With

Cells(10, 1).CopyFromRecordset myRS

myRS.Close
Set myRS = Nothing

myCon.Close
Set myCon = Nothing

End Sub

データの更新や挿入(実行型)マクロ
Sub データ登録()

Dim myCon As ADODB.Connection
Dim myStr As String

Set myCon = New ADODB.Connection
myCon.Open "Driver={MySQL ODBC 8.0 Unicode DRIVER}; SERVER=localhost; PORT=1111; DATABASE=mydb; USER=mydb; PASSWORD=12345;"

myStr = "SQL文"

myCon.Execute myStr

myCon.Close
Set myCon = Nothing

End Sub

サンプルのダウンロードはこちらから


TOP(HOME)へ

目 次
HOME
 全体のシステム構成&目次


RockyLinux

RockyLinux8.4のインストール
旧サーバーからのデータ移行

ネットワークの設定ほか
ダイレクトルールを使ったfirewallの強化
DNS(BIND)サーバー
DHCPサーバー
FTPサーバー
FTPS(FTP over SSL/TLS)
Webサーバー
  アクセス解析ツールAwstats
Mail(Dovecot&Postfix)サーバー
MariaDB(MySQL)サーバー
SSHサーバー
SSH+MariaDB+Exel
Sambaサーバー
openVPNサーバー
VNCサーバー(リモートディスクトップ)

systemdとfirewalldについて


CentOS8
CentOS7
CentOS5〜6

SELinux

Cプログラミング


●その他
クラウド Amazon EC2
Raspberry Piで遊ぶ
印刷機関連開発 刷版絵柄面積率測定
数独をExcelで解く


●趣味の世界
相対性理論
量子力学
群論
熱力学・統計力学
解析力学
物理でつかう数学
超弦理論(ノート作成中)