azure 如何正确连接MySQL数据库与C#应用程序?

1cosmwyk  于 2023-02-09  发布在  Mysql
关注(0)|答案(7)|浏览(158)

我正在尝试将我的MySQL数据库与C#连接,但无法连接。
我在用

static string connstring = @"server=my.live.ip;userid=user;password=123;database=db_name;port=3306";

但我还是
使用方法'mysql_native_password'对用户'user'的主机'my. live. ip'进行身份验证失败,并显示消息:用户"user'@" 202.xxx.xxx.xxx"的访问被拒绝(使用密码:否)'
我已经搜索过了,但没有找到任何合适的解决方案。

    • P. S:**我使用的live IP属于azure,即MySQL数据库通过xampp托管在Azure服务器上

任何帮助都将不胜感激

wqsoz72f

wqsoz72f1#

(using password: NO)表示未提供密码。我不熟悉C#的连接语法,但我怀疑

static string connstring =
     @"server=my.live.ip;userid=user;password=123;database=db_name;port=3306";

或者可能没有使用connstring
还要检查MySQL端。执行SHOW GRANTS FOR 'user'@'202%'SHOW GRANTS FOR 'user'@'202.xxx.xxx.xxx',这取决于您使用的是'202%'还是'202.xxx.xxx.xxx'作为“主机”。
你应该回到

GRANT ... ON dbname.* TO 'user'@'202%' WITH AUTHENTICATION 'mysql_native_password';

注:主机名与IP地址可能存在问题。

ygya80vv

ygya80vv2#

有几种可能可以尝试-
1.将连接字符串中的服务器名称与Azure门户上的设置进行匹配。
1.看起来你提到的用户名和密码是假的(可以理解)。检查你的实际用户名或密码是否有任何特殊字符,你将不得不对它们进行适当的编码以转义。
例如,如果您的密码是my〉Pass,则连接字符串应如下所示。static string connstring = @"server=servername;userid=user;password=my>Pass;database=db_name;port=3306";
如果您想探索一下,这里有一个full list

brc7rcf0

brc7rcf03#

using System;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace AzureMySqlExample
{
class MySqlCreate
{
    static async Task Main(string[] args)
    {
        var builder = new MySqlConnectionStringBuilder
        {
            Server = "YOUR-SERVER.mysql.database.azure.com",
            Database = "YOUR-DATABASE",
            UserID = "USER@YOUR-SERVER",
            Password = "PASSWORD",
            SslMode = MySqlSslMode.Required,
        };

        using (var conn = new MySqlConnection(builder.ConnectionString))
        {
            Console.WriteLine("Opening connection");
            await conn.OpenAsync();

            using (var command = conn.CreateCommand())
            {
                command.CommandText = "DROP TABLE IF EXISTS inventory;";
                await command.ExecuteNonQueryAsync();
                Console.WriteLine("Finished dropping table (if existed)");

                command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                await command.ExecuteNonQueryAsync();
                Console.WriteLine("Finished creating table");

                command.CommandText = @"INSERT INTO inventory (name, quantity) VALUES (@name1, @quantity1),
                    (@name2, @quantity2), (@name3, @quantity3);";
                command.Parameters.AddWithValue("@name1", "banana");
                command.Parameters.AddWithValue("@quantity1", 150);
                command.Parameters.AddWithValue("@name2", "orange");
                command.Parameters.AddWithValue("@quantity2", 154);
                command.Parameters.AddWithValue("@name3", "apple");
                command.Parameters.AddWithValue("@quantity3", 100);

                int rowCount = await command.ExecuteNonQueryAsync();
                Console.WriteLine(String.Format("Number of rows inserted={0}", rowCount));
            }

            // connection will be closed by the 'using' block
            Console.WriteLine("Closing connection");
        }

        Console.WriteLine("Press RETURN to exit");
        Console.ReadLine();
    }
}

}

dgtucam1

dgtucam14#

你可以查一下,但这里有代码,无论如何,我发现:
将所有端口号、用户名、密码、数据库名称和服务器IP替换为您需要的内容。
代码:

using System;
using System.Windows;
using MySql.Data.MySqlClient;

namespace Deportes_WPF
{

public partial class Login : Window
{
private MySqlConnection connection;
private string server;
private string database;
private string user;
private string password;
private string port;
private string connectionString;
private string sslM;

public Login()
{
    InitializeComponent();

    server = "server_name";
    database = "database_name";
    user = "user_id";
    password = "password";
    port = "3306";
    sslM = "none";

    connectionString = String.Format("server={0};port={1};user id={2}; password={3}; database={4}; SslMode={5}", server, port, user, password, database, sslM);

    connection = new MySqlConnection(connectionString);
}

private void conexion()
{
    try
    {
        connection.Open();

        MessageBox.Show("successful connection");

        connection.Close();
    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.Message + connectionString);
    }
}

private void btn1_Click(object sender, RoutedEventArgs e)
{
    conexion();
}
}

}

我希望这个代码对你有用。
它也看起来像你给我的用户名或密码无效的错误。

rpppsulh

rpppsulh5#

using System;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace AzureMySqlExample
{
    class MySqlCreate
    {
        static async Task Main(string[] args)
        {
            var builder = new MySqlConnectionStringBuilder
            {
                Server = "YOUR-SERVER.mysql.database.azure.com",
                Database = "YOUR-DATABASE",
                UserID = "USER@YOUR-SERVER",
                Password = "PASSWORD",
                SslMode = MySqlSslMode.Required,
            };

            using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "DROP TABLE IF EXISTS inventory;";
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Finished dropping table (if existed)");

                    command.CommandText = "CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);";
                    await command.ExecuteNonQueryAsync();
                    Console.WriteLine("Finished creating table");

                    command.CommandText = @"INSERT INTO inventory (name, quantity) VALUES (@name1, @quantity1),
                        (@name2, @quantity2), (@name3, @quantity3);";
                    command.Parameters.AddWithValue("@name1", "banana");
                    command.Parameters.AddWithValue("@quantity1", 150);
                    command.Parameters.AddWithValue("@name2", "orange");
                    command.Parameters.AddWithValue("@quantity2", 154);
                    command.Parameters.AddWithValue("@name3", "apple");
                    command.Parameters.AddWithValue("@quantity3", 100);

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows inserted={0}", rowCount));
                }

                // connection will be closed by the 'using' block
                Console.WriteLine("Closing connection");
            }

            Console.WriteLine("Press RETURN to exit");
            Console.ReadLine();
        }
    }
}

你可以参考下面的链接来使用c# https://learn.microsoft.com/en-us/azure/mysql/connect-csharp创建和访问mysql数据库

gtlvzcf8

gtlvzcf86#

您的密码是否以#或类似字符开头,并且您将其存储在ini之类的配置文件中
由于一些配置类型将一些特殊字符视为注解或其他内容,因此密码变为null
为了验证,将密码更改为字母数字一次,然后测试。我在使用password=#strongP时遇到了这个问题

siotufzp

siotufzp7#

试试这个:

const string connStr = "Server=my.live.ip; User=user; Database=db_name; Password=123; port=3306; 
    Allow Zero Datetime=true; Convert Zero Datetime=true;";

相关问题