使用 VB.NET 2010 连接到远程 MySQL 数据库

Connect to remote MySQL database using VB.NET 2010

提问by nirosha rathnayaka

My connection string as follows.

我的连接字符串如下。

conn.ConnectionString = "Server=192.248.***.***; Port=3036; User id=admin; password=***; Database=abc; Connect Timeout=60;"

But it returns error "Error connecting to database: Unable to connect to any of the specified MySQL hosts."

但它返回错误“连接到数据库时出错：无法连接到任何指定的 MySQL 主机。”

But i can log in to the same using PHPMyAdmin.

但我可以使用 PHPMyAdmin 登录。

//192.248.***.***/phpmyadmin

Also i can log in to local MySQL database using:

我也可以使用以下方法登录到本地 MySQL 数据库：

    conn.ConnectionString = "server=" & "localhost" & ";" & "user id=" & "admin" & ";" & "password=" & "" & ";" & "database=abc"

What is the wrong in this code. I want to connect to the remote database since it is the requirement of the system. Any help please.

这段代码有什么问题。我想连接到远程数据库，因为这是系统的要求。请任何帮助。

Update:In immediate window it shows:

更新：在即时窗口中显示：

    PassbookPrinter.vshost.exe Error: 0 : Unable to connect to any of the specified MySQL hosts.
A first chance exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

回答by Simbo

Connect to remote MySQL Database Using VB.Net

使用 VB.Net 连接到远程 MySQL 数据库

To connect vb.net to remote MySql database ; No matter what VB.Net version you might be using, just go throw the following steps.

将 vb.net 连接到远程 MySql 数据库；无论您使用的是什么 VB.Net 版本，只需执行以下步骤即可。

1) Download Mysql Connector/Net from the url (https://dev.mysql.com/downloads/connector/net/)

1) 从 url ( https://dev.mysql.com/downloads/connector/net/)下载 Mysql Connector/Net

2) Install the connector; by default the connector will be installed in the path (C:\Program Files\MySQL\Connector Net 6.9.6) that's the version i have installed.

2) 安装连接器；默认情况下，连接器将安装在我安装的版本的路径 (C:\Program Files\MySQL\Connector Net 6.9.6) 中。

3) Open VB.Net IDE and start the new project.

3) 打开 VB.Net IDE 并启动新项目。

4) Add the "Mysql.Data.dll" as a reference to your project, which you can find it in the path (C:\Program Files\MySQL\Connector Net 6.9.6\Assemblies\v4.5);

4）添加“Mysql.Data.dll”作为你项目的引用，你可以在路径（C:\Program Files\MySQL\Connector Net 6.9.6\Assemblies\v4.5）中找到它；

5) Prepare your connection form as shown in this image;

5) 准备您的连接表格，如图所示；

6) Create the class named "Database" and write in the following code.

6）创建名为“Database”的类，写入如下代码。

Database class code

数据库类代码

Imports MySql.Data.MySqlClient

导入 MySql.Data.MySqlClient

Public Class Database

公共类数据库

Private _connection As New MySqlConnection
Private _errormessge As String
Private _servername As String
Private _databasename As String
Private _userid As String
Private _password As String

Public WriteOnly Property ServerName() As String
    Set(ByVal value As String)
        _servername = value
    End Set
End Property

Public WriteOnly Property DatabaseName() As String
    Set(ByVal value As String)
        _databasename = value
    End Set
End Property

Public WriteOnly Property UserID() As String
    Set(ByVal value As String)
        _userid = value
    End Set
End Property

Public WriteOnly Property Password() As String
    Set(ByVal value As String)
        _password = value
    End Set
End Property

Public ReadOnly Property ErrorMessage() As String
    Get
        Return _errormessge
    End Get
End Property

Public Function Connection() As Boolean
    Try
        _connection.ConnectionString = "Server=" & _servername & ";Port=3306;Database=" & _databasename & ";User ID=" & _userid & ";Password=" & _password & ""
        _connection.Open()
        If _connection.State = ConnectionState.Open Then
            _connection.Close()
            Return True
        End If
    Catch ex As Exception
        _errormessge = ex.Message
        Return False
    End Try
End Function

End Class

结束类

Form Class Code

表单类代码

Public Class Frm_Main Private Sub btn_connect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_connect.Click

公共类 Frm_Main Private Sub btn_connect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) 处理 btn_connect.Click

'Object declaration and instantiation

'对象声明和实例化

Dim data As New Database

将数据变暗为新数据库

    With data
        'Assing the object property values
        .ServerName = txt_server.Text
        .DatabaseName = txt_database.Text
        .UserID = txt_uid.Text
        .Password = txt_pwd.Text

        'Connection testing
        If .Connection Then
            MessageBox.Show("Database Conneted.")
        Else
            MessageBox.Show(.ErrorMessage)
        End If
    End With
End Sub

Private Sub btn_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_exit.Click
    Close()
End Sub

End Class

结束类

7) Run the project and try the connection; if the connection is successfully then your luck; and if the connection is not successfully with the following error message worry not just keep reading more;

7）运行项目，尝试连接；如果连接成功，那么你的运气；如果连接不成功并显示以下错误消息，请不要继续阅读更多内容；

8) Note the ip address on error message after @ (thats your ip) and add it to your domain cpanel "remote mysql access" the image bellow illustrates how the remote mysql access looks like(they are the same bu they may defer in colors); Don't forget to press "add hosts" button. This settings can work daily for those who are in static ip.

8) 记下@ 之后错误消息中的 ip 地址（那是您的 ip）并将其添加到您的域 cpanel“远程 mysql 访问”中，下图说明了远程 mysql 访问的样子（它们是相同的，但它们可能会延迟颜色); 不要忘记按“添加主机”按钮。对于那些使用静态 ip 的人来说，此设置每天都可以使用。

See the message of success after the above steps;

完成以上步骤后看到成功的信息；

But if the error message persists try to leave the password text blank and connect again if you had no password in your remote database; if the error comes again except YES is changed to be NO then you have to check if your in DHCP;

但是，如果错误消息仍然存在，请尝试将密码文本留空并在远程数据库中没有密码的情况下再次连接；如果错误再次出现，除了 YES 更改为 NO ，那么您必须检查您是否在 DHCP 中；

9) If your in DHCP which means the ip is changing in every new Internet connection. If your using modem probably your in DHCP. If your in dynamic ips then check what is changing in the ip's 4 blocks. If the first ip was 197.250.3.201 in the first connection and the next ip is 197.250.60.70 and the next next ip is 197.250.80.24; you have to add 197.250.% in your cpanel access hosts for your connection to be stable.

9) 如果您使用的是 DHCP，这意味着 IP 在每个新的 Internet 连接中都会发生变化。如果您使用调制解调器可能是您在 DHCP。如果您使用的是动态 ip，则检查 ip 的 4 个块中发生了什么变化。如果第一次连接的第一个ip是197.250.3.201，下一个ip是197.250.60.70，下一个ip是197.250.80.24；您必须在 cpanel 访问主机中添加 197.250.% 才能使连接稳定。

10) Note: As the percent symbol (wild card) flows to the left side of the ip address the more the door of security becomes open. On new error please contact your domain provider there might be some other security issues in the domain. Thanks!

10) 注意：随着百分比符号（通配符）流向 IP 地址的左侧，安全门越开越大。如果出现新错误，请联系您的域提供商，域中可能存在其他一些安全问题。谢谢！

回答by Prappo Prince

just try this , it will work

试试这个，它会起作用

con.ConnectionString ="Persist Security Info=False;datasource=site.com;port=3306;username=username;password=password;database=database name"

You must add

你必须添加

Persist Security

持久安全

in your code . to access your mysql database of your server of site . go cpanel > Remote MySQL , and add your ip address

在您的代码中。访问您的站点服务器的 mysql 数据库。转到 cpanel > Remote MySQL ，并添加您的 IP 地址

see this image >>> https://www.dropbox.com/s/ytsz57spanwdpkz/cpanel.PNG

看这张图片 >>> https://www.dropbox.com/s/ytsz57spanwdpkz/cpanel.PNG

now enjoy :p

现在享受：p

回答by PeterJ

The default port for MySQL is 3306. If you can connect from phpmyadmin using the default settings it sounds like a typo and you should have:

MySQL 的默认端口是 3306。如果您可以使用默认设置从 phpmyadmin 连接，这听起来像是一个错字，您应该：

conn.ConnectionString = "Server=192.248.***.***; Port=3306; User id=admin; password=***; Database=abc; Connect Timeout=60;"

Additionally remote access to MySQL Server is disabled by default. For example if you're running MySQL on a Debian Linux distro you might need to take additional steps such as the following to allow access from another machine:

此外，默认情况下禁用对 MySQL 服务器的远程访问。例如，如果您在 Debian Linux 发行版上运行 MySQL，您可能需要执行以下额外步骤以允许从另一台机器访问：

http://www.debianhelp.co.uk/remotemysql.htm

http://www.debianhelp.co.uk/remotemysql.htm

For a Windows host the following may prove useful:

对于 Windows 主机，以下内容可能有用：

http://techminded.net/blog/allow-remote-connections-for-mysql-on-windows.html

http://tech mind.net/blog/allow-remote-connections-for-mysql-on-windows.html

回答by Gerry85

Iv previously had the same issue, i was the same as it was running on a different server.

我以前有同样的问题，我和它在不同的服务器上运行一样。

Try to ping the server from where the application is running and see if you can connect. that's how i found out and needed a firewall change.

尝试从应用程序运行的地方 ping 服务器，看看是否可以连接。这就是我发现并需要更改防火墙的方式。

回答by flexaiosys

try changing port to 3307 on .ini config of mysql then bind-address=server's ip or hostname on .ini config of mysql also

尝试在 mysql 的 .ini 配置上将端口更改为 3307 然后在 mysql 的 .ini 配置上绑定地址=服务器的 ip 或主机名