IT業界のすみっこ暮らし

ふと気がついたときの記録

C#:秘密鍵を使ってSSH接続をしてからリモート先のMySQLに接続して操作を行う

やりたいこと

  • C#でコンソールアプリを作成
  • SSHに秘密鍵を使って外部のLinuxサーバー(VPS)にアクセスする
  • リモート先のMySQL(リモートから見ればLocalhostにあるやつ)にアクセスして操作したい

前提

SSHの秘密鍵はビルド先の直下にある

ex) \bin\Debug\openssh.key もしくは \bin\Release\openssh.key

※既存のid_rsaファイルをコピーして名前変更するだけ。

必要なパッケージ

SSH.NET NuGet Gallery | SSH.NET 2016.1.0

MySql.Data NuGet Gallery | MySql.Data 8.0.12

MySql.Dataのバージョンは各自の状況に合わせる。
(下記のサンプルを検証した際のMySql.Dataのバージョンは6.9.11.0)

サンプル

using Renci.SshNet;
using MySql.Data.MySqlClient;
using System.Data;


public const string LocalHostAddress = "localhost";

// 空いてるポートなら何でも可。(開発端末/実行端末基準の空きポート)
public const uint LocalHostPort = 13306;   

// ipもしくはホスト名
public const string RelayHostAddress = "dev.test"; 

// sshのポート。(RelayHostAddressでsshのportがどれに充てられているかによる(22だったり10022だったり))
public const int RelayHostPort = 22;  

// sshでアクセスするユーザー名
public const string SshUser = "sshuser"; 

// RelayHostAddressにSshUserの値でユーザーが登録されている且つ公開鍵が登録されていることが前提
public const string SshPrivateKeyFileName = "openssh.key";

// SshPrivateKeyFileName(ssh鍵)を作る際に指定したパスワード
public const string SshPassword = "pass"; 

// リモート先のRelayHostAddressに存在するDBなので"127.0.0.1"を設定
public const string DbHostAddress = "127.0.0.1";

// MySQLのポート。 
public const uint DbHostPort = 3306;    

public const string MySqlUser = "root"; 
// パスワード未指定の場合は""
public const string MySqlPassword = "";

// dbスキーマ名 
public const string DbName = "test"; 

// 検証用に使うテーブル名
public const string TableName = "tbl_test"; 


public void AccessSshAndMySql()
{
    // Setup Credentials and Server Information
    // Key Based Authentication (using keys in OpenSSH Format)
    var ConnNfo = new ConnectionInfo(RelayHostAddress, RelayHostPort, SshUser,
        new AuthenticationMethod[]{
            new PrivateKeyAuthenticationMethod(SshUser,new PrivateKeyFile[]{
                new PrivateKeyFile(SshPrivateKeyFileName, SshPassword)
            }),
        }
    );

    using (var client = new SshClient(ConnNfo))
    {
        client.Connect();
        try
        {
            if (!client.IsConnected)
            {
                Console.WriteLine("[NG] SSH Connection failed!!");
            }
            // SSHを経由してアクセス元(開発端末)の空きポートからリモート先のMySQLにアクセスできるようにする。
            using (var forwardedPortLocal = new ForwardedPortLocal(LocalHostAddress, LocalHostPort, DbHostAddress, DbHostPort))
            {
                client.AddForwardedPort(forwardedPortLocal);
                try
                {
                    forwardedPortLocal.Start();
                    if (!forwardedPortLocal.IsStarted)
                    {
                        Console.WriteLine("[NG] forwardedPortLocal Connection failed!!");
                    }

                    // MySQL Start (接続先がフォワードポート)
                    var connStr = string.Format(
                        "server={0};port={1};user={2};password={3};database={4};Pooling=False",
                        forwardedPortLocal.BoundHost, forwardedPortLocal.BoundPort, MySqlUser, MySqlPassword, DbName);
                    var sql = "SELECT * FROM " + TableName;

                    using (var connection = new MySqlConnection(connStr))
                    using (var com = new MySqlCommand(sql, connection))
                    {
                        connection.Open();
                        try
                        {
                            var datatable = new DataTable();
                            var adapter = new MySqlDataAdapter(sql, connection);
                            adapter.Fill(datatable);
                        }
                        catch (Exception) { /*Ignore SQL Error*/ }
                        connection.Close();
                    }
                    // MySQL End
                }
                finally
                {
                    client.RemoveForwardedPort(forwardedPortLocal);
                }
            }
        }
        finally
        {
            client.Disconnect();
        }
    }
}

参考

のぶろぐ SSHホストを介してMySQLへ接続

c# - Connection to MySQL from .NET using SSH.NET Library - Stack Overflow

win10 + php5 + nginxローカル環境設定

参照

Windows10(64Bit版)にApache2.4、PHP5.6をインストールする方法

↑はPHPのインストールのみ参照

Windows10にnginx+PHP環境の構築(初心者の復習用)

1. phpダウンロード

https://windows.php.net/downloads/releases/archives/

2. PHPをダウンロードして、適当にzipファイルを解凍した後

f:id:papamau:20180720190348p:plain

3. フォルダ名を[php5]に変更後、[C:\Program Files (x86)]にコピー。(C:\Program Files (x86)\php5)

f:id:papamau:20180720190409p:plain

4. 環境変数[path]に[C:\Program Files (x86)\php5]追加

f:id:papamau:20180720190426p:plain

5. [php -v]でインストール確認

f:id:papamau:20180720190459p:plain

6. php.ini ファイル作成

[C:\Program Files (x86)\php5\php.ini-development]をコピーして名前を[php.ini]に変更する。

以下、最小限の変更箇所

; extension_dir = "ext"
↓
extension_dir = "C:\Program Files (x86)\php5\ext"

※コメント解除

;extension=php_gd2.dll
;extension=php_mbstring.dll
;extension=php_mysql.dll
;extension=php_mysqli.dll
↓
extension=php_gd2.dll
extension=php_mbstring.dll
extension=php_mysql.dll
extension=php_mysqli.dll

7. [nginx/Windows-1.10.3 ]リンクからダウンロード

nginx: download

8. zipファイルを解凍して下記に格納。(どこでもいい)

C:\server\nginx-1.10.3

f:id:papamau:20180720190702p:plain

8-1. ファイアウォールの通信を許可(不要かもしれないが私はやった)

f:id:papamau:20180720190754p:plain

9. [C:\server\nginx-1.10.3\conf\nginx.conf]を修正

#user  nobody;
worker_processes  1;

error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;

#pid        logs/nginx.pid;


events {
    worker_connections  1024;
}


http {
    include       mime.types;
    default_type  application/octet-stream;

    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                      '$status $body_bytes_sent "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

    access_log  logs/access.log  main;

    sendfile        on;
    #tcp_nopush     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    #gzip  on;

    server {
        listen       80;
        server_name  localhost;

        #charset koi8-r;

        access_log  logs/host.access.log  main;

        location / {
            root   html;
            index  index.html index.htm;
        }

        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        location ~ \.php$ {
            #root           html;
            fastcgi_pass   127.0.0.1:9000;
            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
            include        fastcgi_params;
        }

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;

    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;

    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;

    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}

}

10. [localhost]にアクセスしてnginxの画面が表示されれば一応nginxはok.

nginx起動(nginx.exeが格納されているフォルダ内で実行)

f:id:papamau:20180720190903p:plain

START nginx.exe

リクエスト処理後停止

f:id:papamau:20180720190919p:plain

nginx.exe -s quit

強制停止

f:id:papamau:20180720190936p:plain

nginx.exe -s stop

f:id:papamau:20180720190944p:plain

11. [php-cgi]を起動してphpファイルが正常に動作するか確認。

php-cgi起動

START php-cgi.exe -b 127.0.0.1:9000 -c C:\php\php.ini

※-cコマンドで参照するconfigを指定可能。指定しない場合、[Loaded Configuration File]に[(none)]が表示され、拡張モジュール(extension)の読み込みなどが出来ないので、下記のtest.phpでphpの設定を確認する。

test.php

<?php
  phpinfo();
?>

php-cgi停止

Taskkill /im php-cgi.exe

f:id:papamau:20180720191044p:plain

開発するならもっともりもり設定を見なきゃだけど、一応最低限ここまででhello worldはできるようになった。

追記

PHPに[mysqli]の拡張モジュールがインストール済みなのにMySQLにアクセスできない場合、下記の[mysqli.default_socket]の値の設定漏れの場合があるので調べながら頑張る。 f:id:papamau:20180724111404p:plain

Windows10環境でSSHを使って外部サーバーアクセスするときの手順

1、Gitをインストール

最新版をダウンロードして、デフォルト値のままインストールする。

2、SSHコマンド : OpenSSH 64bit インストール

sshをWindowsコマンドプロンプトで実行する手順 | 無停電電源装置(UPS) | イートン

ダウンロード先

Releases · PowerShell/Win32-OpenSSH · GitHub

3、[ssh-keygen]コマンドと[ssh]コマンドが実行できるのか確認

環境変数は2のインストール後、[OpenSSH-Win64]のみ設定した状態。 gitはデフォルトで問題なければgit\cmdが環境変数に追加されるはず(ない場合は自分で設定)

f:id:papamau:20180719181348p:plain

[HOME]変数に[C:\Users\xxxx]が設定されていることを確認。 f:id:papamau:20180719181433p:plain

4、WindowsでSSHの鍵を作る

WindowsでSSHの鍵を作る

ssh用のフォルダを作る。

f:id:papamau:20180719181630p:plain

鍵を生成する。

f:id:papamau:20180719181704p:plain

キーの生成確認

sshのキー(id_rsaとid_rsa.pub)がc:/Users/XXXXX/.ssh 以下にあるかどうか確認する。

5、公開鍵[id_rsa.pub]を持って外部サーバーにアカウント名を指定してアクセスできるよう設定する。

※注意:'-'(ハイフン)混用の場合、ログインできない

※使用可能文字

  • 数字、アルファベット、アンダーバーを使用可能。
  • 大文字は使用不可
  • 先頭文字は数字は使用不可

SSHを使ってアクセスする

1、CMD + SSH

f:id:papamau:20180719182054p:plain

2、CMD + SSH + config

[C:\Users\xxxx.ssh]に[config]ファイルを作成(txtファイルを作成後、拡張子なしで[config]に名前保存)

[C:\Users\xxxx.ssh\config]

Host testhost
    HostName host.xxx
    Port 10022
    User username
    IdentityFile ~\.ssh\id_rsa

[ssh + configに設定したhost名(ここでは[testhost])]で短いコマンドでアクセス可能。

f:id:papamau:20180719182359p:plain

※ディレクトリ単位のファイル送受信に使う[scp]コマンドを使う場合は2が一番適切。

3、Tera Term

最初表示される接続画面は閉じて[設定-SSH認証]を選択。

ユーザー名と秘密鍵のパスを設定。

f:id:papamau:20180719182551p:plain

[ファイル-新しい接続]でホストとTCPポート設定。サービスが[SSH]であることを確認。

f:id:papamau:20180719182616p:plain

[SSH認証]画面でパスワード入力。

f:id:papamau:20180719182650p:plain

ファイルの受信

scpコマンドでファイルの送受信(ここではディレクトリ単位の受信のみ)を行う(CMD + SSH + config前提)。

C:\Users\username\.ssh>scp -r testhost:/var/www/vhosts/domain ../../../domain

※[../../../domain] = [c:\domain]

※ローカルパスに[c:\~]を指定しても認識できなかった。

MySQLでRollbackが効かないケースについて

経緯

下記のようなエラーメッセージが表示され、外部サーバーにあるMySQLでロールバックが効かない。

1196 Some non-transactional changed tables couldn't be rolled back

原因

非トランザクションである MyISAM テーブルとしてテーブルが作成されているので、ロールバックが出来なかった。

下記のステートメントで該当テーブルが使っているエンジンの確認ができる。

SHOW TABLE STATUS LIKE 'table_name';

スキーマ単位の確認の場合

SELECT TABLE_NAME,
 ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'schema_name' and ENGINE = 'myISAM'

EnginにMyISAMが設定されている

f:id:papamau:20180726171709p:plain

ちなみにRollback可能な場合はこうなっている

f:id:papamau:20180726171638p:plain

参考サイト

MySQL :: MySQL 5.6 リファレンスマニュアル :: B.5.5.5 非トランザクションテーブルのロールバックの失敗

運用視点なMyISAMとInnoDBと。 – LexTech

kinsta.com

qiita.com

shindolog.hatenablog.com

C# : 整数のN位で四捨五入

C# : 整数のN位で四捨五入

/// <summary>
/// 整数のN位で四捨五入
/// Math.Roundは銀行丸めなのでエクセルと同様の四捨五入をする場合はMidpointRounding.AwayFromZeroを設定
/// ex) GetExcelRound(13405, 1) // return 13410
/// ex) GetExcelRound(13405, 2) // return 13400
/// </summary>
/// <param name="value"></param>
/// <param name="digit"></param>
/// <returns></returns>
public static double GetExcelRound(double value, int digit)
{
    var val1 = double.Parse((0.01 * Math.Pow(0.1, digit - 1)).ToString(), System.Globalization.NumberStyles.Float);
    var val2 = 100 * Math.Pow(10, digit - 1);
    
    return Math.Round(value * val1, 1, MidpointRounding.AwayFromZero) * val2;
}


プライバシーポリシー