{"id":1790,"date":"2023-04-15T22:58:24","date_gmt":"2023-04-15T13:58:24","guid":{"rendered":"https:\/\/www.typea.info\/blog\/?p=1790"},"modified":"2023-04-15T22:58:24","modified_gmt":"2023-04-15T13:58:24","slug":"ubuntu_lightsail_multipass_mysql_connect_via_ssh","status":"publish","type":"post","link":"https:\/\/www.typea.info\/blog\/index.php\/2023\/04\/15\/ubuntu_lightsail_multipass_mysql_connect_via_ssh\/","title":{"rendered":"Ubuntu(Lightsail\u304a\u3088\u3073multipass\u4eee\u60f3\u74b0\u5883)\u306eMySQL\u306b\u30ed\u30fc\u30ab\u30eb\u304b\u3089SSH\u7d4c\u7531\u3067\u63a5\u7d9a"},"content":{"rendered":"

Ubuntu(Lightsail\u304a\u3088\u3073multipass\u4eee\u60f3) \u306b Blazor WebAssembly\u304a\u3088\u3073ASP.NET API \u30a2\u30d7\u30ea\u3092\u69cb\u7bc9\u3057\u3066\u30c7\u30d7\u30ed\u30a4<\/a><\/p>\n

\u00a0<\/p>\n

\u30c7\u30d7\u30ed\u30a4\u3059\u308b\u30a2\u30d7\u30ea\u306fMySQL\u3068\u540c\u3058\u30b5\u30fc\u30d0\u30fc\u306b\u7f6e\u304f\u3064\u3082\u308a\u306a\u306e\u3067\u554f\u984c\u306a\u3044\u306e\u3060\u3051\u308c\u3069\u3001\u30ed\u30fc\u30ab\u30ebPC\u3067\u52d5\u304b\u3059\u306b\u3042\u305f\u3063\u3066\u306f\u3001multipass\u4e0a\u306e\u4eee\u60f3\u30b5\u30fc\u30d0\u30fc\u3084Lightsail\u306e\u4e2d\u306eMySQL\u306b\u30a2\u30af\u30bb\u30b9\u3055\u305b\u305f\u3044\u3002<\/p>\n

\u3068\u306f\u3044\u3048\u3001MySQL\u7528\u306e\u30dd\u30fc\u30c8\u3084\u3089\u3092\u516c\u958b\u306f\u3057\u305f\u304f\u306a\u3044\u3002\u306e\u3067\u3001\u30d7\u30ed\u30b0\u30e9\u30e0\u304b\u3089\u3082\u30ed\u30fc\u30ab\u30eb\u958b\u767a\u6642\u306fSSH\u7d4c\u7531\u3067MySQL\u306b\u63a5\u7d9a\u3055\u305b\u308b\u3002<\/p>\n

https:\/\/mysqlconnector.net\/tutorials\/connect-ssh\/<\/a><\/p>\n

\u3092\u53c2\u8003\u306b\u3001SSH\u7d4c\u7531\u3067MySQL\u3067\u63a5\u7d9a\u3059\u308b\u30b5\u30f3\u30d7\u30eb\u3002<\/p>\n

SSH.NET<\/a>\u00a0\u306e\u53c2\u7167\u8a2d\u5b9a\u3092\u884c\u3046\u3002<\/p>\n

using Renci.SshNet;\n\nnamespace SetlistShare.Dao;\n\n\/\/\/ <summary>\n\/\/\/ \n\/\/\/ <\/summary>\npublic class ConnectionHelper\n{\n\n    \/\/\/<summary>SSH\u63a5\u7d9a<\/summary>\n    \/\/\/ <param name=\"host\">\u30db\u30b9\u30c8\u540d<\/param>\n    \/\/\/ <param name=\"username\">\u30e6\u30fc\u30b6\u30fc\u540d<\/param>\n    \/\/\/ <param name=\"password\">\u30d1\u30b9\u30ef\u30fc\u30c9<\/param>\n    \/\/\/ <param name=\"keyFile\">\u79d8\u5bc6\u9375\u30d5\u30a1\u30a4\u30eb<\/param>\n    \/\/\/ <param name=\"passPhrase\">\u79d8\u5bc6\u9375\u306e\u30d1\u30b9\u30d5\u30ec\u30fc\u30ba<\/param>\n    \/\/\/ <param name=\"port\">\u30dd\u30fc\u30c8\u756a\u53f7<\/param>\n    public static (SshClient sshClient, int Port) ConnectSsh(\n        string host, \n        string username, \n        string? password = null,\n        string? keyFile = null,\n        string? passPhrase = null,\n        int port = 22)\n    {\n        if (string.IsNullOrEmpty(password) && string.IsNullOrEmpty(keyFile))\n        {\n            throw new ArgumentException(\"Either password or keyFile must be specified.\");\n        }\n\n        var authMethods = new List<AuthenticationMethod>();\n        if (!string.IsNullOrEmpty(keyFile))\n        {   \n            var key = new PrivateKeyFile(keyFile, passPhrase);\n            authMethods.Add(new PrivateKeyAuthenticationMethod(username, key));\n        }\n        if (!string.IsNullOrEmpty(password))\n        {\n            authMethods.Add(new PasswordAuthenticationMethod(username, password));\n        }\n\n        var connectionInfo = new ConnectionInfo(host, port, username, authMethods.ToArray());\n        var sshClient = new SshClient(connectionInfo);\n\n        sshClient.Connect();\n\n        return (sshClient, port);\n    }\n\n    \/\/\/<summary>SSH\u63a5\u7d9a<\/summary>\n    \/\/\/ <param name=\"host\">\u30db\u30b9\u30c8\u540d<\/param>\n    \/\/\/ <param name=\"username\">\u30e6\u30fc\u30b6\u30fc\u540d<\/param>\n    \/\/\/ <param name=\"password\">\u30d1\u30b9\u30ef\u30fc\u30c9<\/param>\n    \/\/\/ <param name=\"keyFile\">\u79d8\u5bc6\u9375\u30d5\u30a1\u30a4\u30eb<\/param>\n    \/\/\/ <param name=\"passPhrase\">\u79d8\u5bc6\u9375\u306e\u30d1\u30b9\u30d5\u30ec\u30fc\u30ba<\/param>\n    \/\/\/ <param name=\"port\">\u30dd\u30fc\u30c8\u756a\u53f7<\/param>\n    \/\/\/ <param name=\"dbHost\">\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30db\u30b9\u30c8\u540d<\/param>\n    \/\/\/ <param name=\"dbPort\">\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30dd\u30fc\u30c8\u756a\u53f7<\/param>\n    public static (SshClient sshClient, int Port) ConnectDatabaseViaSsh(\n        string host, \n        string username, \n        string? password = null,\n        string? keyFile = null,\n        string? passPhrase = null,\n        int port = 22,\n        string? dbHost = \"localhost\",\n        int dbPort = 3306)\n    {\n\n        var (sshClient, sshPort) = ConnectSsh(host, username, password, keyFile, passPhrase, port);\n\n        var forwardPort = new ForwardedPortLocal(\"127.0.0.1\", dbHost, (uint)dbPort);\n        sshClient.AddForwardedPort(forwardPort);\n        forwardPort.Start();\n\n        return (sshClient, (int) forwardPort.BoundPort);\n    }\n}<\/pre>\n
\u63a5\u7d9a\u306e\u78ba\u8a8d<\/p>\n
using MySql.Data.MySqlClient;\nusing SetlistShare.Dao;\nnamespace SetlistShare.Test;\n\npublic class ConnectionHelperTest\n{\n    [Fact]\n    public void ConnectSshTest()\n    {\n        var (sshclient, port) =  ConnectionHelper.ConnectDatabaseViaSsh (\n            \"lightsail.local\", \n            \"SSH\u30e6\u30fc\u30b6\u30fcID\", \n            \"SSH\u3071\u3059\u308f\u30fc\u3069\");\n        using (sshclient)\n        {\n\n            var builder = new MySqlConnectionStringBuilder \n            {\n                Server = \"127.0.0.1\",\n                Port = (uint)port,\n                UserID = \"DB\u30e6\u30fc\u30b6\u30fcID\",\n                Password = \"DB\u30d1\u30b9\u30ef\u30fc\u30c9\"\n            };\n\n            using (var conn = new MySqlConnection(builder.ConnectionString))\n            {\n                conn.Open();\n                using (var cmd = new MySqlCommand(\"SELECT * FROM setlistshare.Person\", conn))\n                {\n                    using (var reader = cmd.ExecuteReader())\n                    {\n                        var users = new List<dynamic>();\n                        while (reader.Read())\n                        {\n                            users.Add(\n                                new {\n                                    Id = reader.GetInt32(0),\n                                    Name = reader.GetString(1),\n                                    Email = reader.GetString(2)}\n                            );\n                        }\n                        users.ForEach(user => Console.WriteLine($\"{user}\"));\n                    }\n                }\n            }\n        }\n    }\n}<\/pre>\n
\u7121\u4e8b\u30c7\u30fc\u30bf\u53d6\u5f97\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
Ubuntu(Lightsail\u304a\u3088\u3073multipass\u4eee\u60f3) \u306b Blazor WebAssembly\u304a\u3088\u3073ASP.NET API \u30a2\u30d7\u30ea\u3092\u69cb\u7bc9\u3057\u3066\u30c7\u30d7\u30ed\u30a4 \u00a0 \u30c7\u30d7\u30ed\u30a4\u3059\u308b\u30a2\u30d7\u30ea\u306fMySQL\u3068\u540c\u3058\u30b5\u30fc\u30d0\u30fc\u306b\u7f6e\u304f\u3064\u3082\u308a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"sns_share_botton_hide":"","vkExUnit_sns_title":"","_vk_print_noindex":"","sitemap_hide":"","_veu_custom_css":"","veu_display_promotion_alert":"","vkexunit_cta_each_option":"","footnotes":""},"categories":[175,144,59],"tags":[145,166,178],"class_list":["post-1790","post","type-post","status-publish","format-standard","hentry","category-lightsail","category-multipass","category-mysql","tag-multipass","tag-mysql","tag-ssh"],"veu_head_title_object":{"title":"","add_site_title":""},"_links":{"self":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1790"}],"version-history":[{"count":1,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1790\/revisions"}],"predecessor-version":[{"id":1791,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1790\/revisions\/1791"}],"wp:attachment":[{"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1790"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.typea.info\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}