Documentation Home
MySQL Connector/NET Developer Guide
Related Documentation Download this Manual

MySQL Connector/NET Developer Guide  /  Connector/NET Connections  /  Creating a Connector/NET Connection String

4.1 Creating a Connector/NET Connection String

The MySqlConnection object is configured using a connection string. A connection string contains several key-value pairs, separated by semicolons. In each key-value pair, the option name and its corresponding value are joined by an equal sign. For the list of option names to use in the connection string, see Section 4.5, “Connector/NET 8.0 Connection Options Reference”.

The following is a sample connection string:

"server=127.0.0.1;uid=root;pwd=12345;database=test"

In this example, the MySqlConnection object is configured to connect to a MySQL server at 127.0.0.1, with a user name of root and a password of 12345. The default database for all statements will be the test database.

Note

Using the '@' symbol for parameters is now the preferred approach, although the old pattern of using '?' is still supported. To avoid conflicts when using the '@' symbol in combination with user variables, see the Allow User Variables connection string option in Section 4.5, “Connector/NET 8.0 Connection Options Reference”. The Old Syntax connection string option has now been deprecated.

Connector/NET supports several connection models:

Opening a Connection to a Single Server

After you have created a connection string it can be used to open a connection to the MySQL server.

The following code is used to create a MySqlConnection object, assign the connection string, and open the connection.

MySQL Connector/NET can also connect using the native Windows authentication plugin. See Section 5.3, “Using the Windows Native Authentication Plugin” for details.

You can further extend the authentication mechanism by writing your own authentication plugin. See Section 5.4, “Writing a Custom Authentication Plugin” for details.

Visual Basic Example

Dim conn As New MySql.Data.MySqlClient.MySqlConnection
Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
            & "uid=root;" _
            & "pwd=12345;" _
            & "database=test"

Try
  conn.ConnectionString = myConnectionString
  conn.Open()

Catch ex As MySql.Data.MySqlClient.MySqlException
  MessageBox.Show(ex.Message)
End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection();
    conn.ConnectionString = myConnectionString;
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

You can also pass the connection string to the constructor of the MySqlConnection class:

Visual Basic Example

Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
              & "uid=root;" _
              & "pwd=12345;" _
              & "database=test"

Try
    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
    conn.Open()
Catch ex As MySql.Data.MySqlClient.MySqlException
   MessageBox.Show(ex.Message)
End Try

C# Example

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

After the connection is open, it can be used by the other Connector/NET classes to communicate with the MySQL server.

Opening a Connection for Multiple Hosts with Failover

Data used by applications can be stored on multiple MySQL servers to provide high availability. Connector/NET provides a simple way to specify multiple hosts in a connection string for cases in which multiple MySQL servers are configured for replication and you are not concerned about the precise server your application connects to in the set. For an example of how to configure multiple hosts with replication, see Using Replication & Load balancing.

Starting in Connector/NET 8.0.19, both classic MySQL protocol and X Protocol connections permit the use of multiple host names and multiple endpoints (a host:port pair) in a connection string or URI scheme. For example:

// classic protocol example
"server=10.10.10.10:3306,192.101.10.2:3305,localhost:3306;uid=test;password=xxxx"

// X Protocol example
mysqlx://test:test@[192.1.10.10:3305,127.0.0.1:3306]

An updated failover approach selects the target for connection first by priority order, if provided, or random order when no priority is specified. If the attempted connection to a selected target is unsuccessful, Connector/NET selects a new target from the list until no more hosts are available. If enabled, Connector/NET uses connection pooling to manage unsuccessful connections (see Section 4.3, “Managing a Connection Pool in Connector/NET”).

Opening a Connection Using a Single DNS Domain

Important

To enable DNS-SRV in your .NET Framework project, avoid downloading the MySql.Data.dll package from the NuGet gallery. The package omits some libraries required by .NET Framework for this feature. Instead, download the no-install version of MySQL Connector/NET (mysql-connector-net-8.0.19.msi) from https://dev.mysql.com/downloads/connector/net/ and then add v4.5.2\MySql.Data.dll as a reference to your project. No other references are required if all items remain in the same location.

.NET Core projects can use the NuGet package directly to enable the DNS-SRV feature.

When multiple MySQL instances can provide the same service in your installation, such as with a cluster of servers, you can apply DNS Service (SRV) records to provide failover, load balancing, and replication services. DNS SRV records remove the need for clients to identify each possible host in the connection string, or for connections to be handled by an additional software component. They can also be updated centrally by administrators when servers are added or removed from the configuration or when their host names are changed. DNS SRV records can be used in combination with connection pooling, in which case connections to hosts that are no longer in the current list of SRV records are removed from the pool when they become idle.

A service record is a specification of data managed by your domain name system that defines the location (host name and port number) of servers for the specified services. The record format defines the priority, weight, port, and target for the service as defined in the RFC 2782 specification (see https://tools.ietf.org/html/rfc2782). In the following SRV record example with four server targets (for _mysql._tcp.foo.abc.com.), Connector/NET uses the server selection order of foo2, foo1, foo3, and foo4.

Record                    TTL   Class   Priority Weight Port  Target
_mysql._tcp.foo.abc.com. 86400 IN SRV   0        5      3306  foo1.abc.com
_mysql._tcp.foo.abc.com. 86400 IN SRV   0        10     3306  foo2.abc.com
_mysql._tcp.foo.abc.com. 86400 IN SRV   10       5      3306  foo3.abc.com
_mysql._tcp.foo.abc.com. 86400 IN SRV   20       5      3306  foo4.abc.com

To open a connection using DNS SRV records, add the dns-srv connection option to your connection string. For example:

C# Example

var conn = new MySqlConnection("server=_mysql._tcp.foo.abc.com.;dns-srv=true;" +
             "user id=user;password=****;database=test");

For additional usage examples and restrictions for both classic MySQL protocol and X Protocol, see Options for Both Classic MySQL Protocol and X Protocol.