Access a database instance over the MySQL protocol by using secure access proxy - Data Management

After enabling Secure Access Proxy for a database instance and obtaining proxy endpoint authorization, connect to the instance using standard MySQL tools, program code, or SQL clients.

Prerequisites

Before you begin, ensure that you have:

Enabled Secure Access Proxy for the database instance. See Enable the secure access proxy feature.
Authorization to access the database instance by using proxy endpoints. See Apply for the permissions to access a database instance by using proxy endpoints.

Limitations

The idle timeout of your MySQL client cannot exceed 900 seconds.
If you use a connection pool, set the failure detection interval to 750 seconds or less (the hard limit is 900 seconds) to avoid unexpected disconnections.

Usage notes

Instances managed in Security Collaboration mode are subject to security rules, which limit the number of rows returned per query. The default limit is 100,000 rows.

To change the limit, log on to the Data Management (DMS) console and choose Security and Specifications > Security Rules in the top navigation bar. Find the MySQL engine type, click Edit in the Actions column, and then choose Security and Specifications > Secure Access Proxy in the left-side navigation pane. Modify the maximum number of rows returned per query.

Note

If you do not want security rules applied, connect using the connection address provided by the database console, or contact DMS technical support to evaluate your scenario. For instances not managed in Security Collaboration mode, the limit is fixed at 3,000 rows and cannot be customized.

Get your connection parameters

Before connecting, collect the following parameters from the DMS console.

Parameter	Where to find it
`host`	On the Secure Access Proxy details page of the instance, find the public or internal proxy endpoint for MySQL protocol access. The domain name in the endpoint is the host.
`port`	On the same Secure Access Proxy details page, find the port number in the proxy endpoint (for example, `3306`).
`user`	In the Authorization Information section on the Secure Access Proxy details page, find your AccessKey ID. Use this as the username.
`password`	In the Authorization Information section on the same page, find your AccessKey secret. Use this as the password.
`database`	The name of the database you want to access.

Important

Your username is your AccessKey ID — not your Alibaba Cloud account name. Your password is your AccessKey secret.

Connect to your database instance

Use MySQL commands

# Each flag maps directly to a connection parameter:
#   -h  host      — domain name from the proxy endpoint
#   -P  port      — port from the proxy endpoint (e.g. 3306)
#   -u  user      — your AccessKey ID (not your account name)
#   -p  password  — your AccessKey secret
#       database  — name of the database to access
mysql -h<host> -P<port> -u<user> -p<password> <database> -e '<sql_statement>'

Example:

mysql -hdpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com -P3306 -uAccessID -pAccessSecret Schema -e 'SHOW DATABASES'

Use program code

All examples connect to the proxy endpoint using standard MySQL drivers. The user field takes your AccessKey ID and password takes your AccessKey secret.

Java

// dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com:3306 — proxy endpoint host and port
// schema — name of the database to access
String url = "jdbc:mysql://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com:3306/schema";
Properties properties = new Properties();
// AccessID — your AccessKey ID (used as the username)
properties.setProperty("user", "AccessID");
// AccessSecret — your AccessKey secret (used as the password)
properties.setProperty("password", "AccessSecret");
try (Connection connection = DriverManager.getConnection(url, properties)) {
    try (Statement statement = connection.createStatement()) {
        statement.execute("SHOW DATABASES");
        ResultSet resultSet = statement.getResultSet();
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

Python

Note

This example uses Python 2 and PyMySQL.

import pymysql

try:
    conn = pymysql.connect(
        host='dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com',  # proxy endpoint host
        port=3306,                                        # proxy endpoint port
        user='AccessID',                                  # your AccessKey ID
        password='AccessSecret',                          # your AccessKey secret
        database='schema'                                 # database name
    )
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.execute('SHOW DATABASES')
    rs = cur.fetchall()
    print rs
finally:
    cur.close()
    conn.close()

Node.js

var mysql = require('mysql');

var connection = mysql.createConnection({
    host:     'dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com',  // proxy endpoint host
    port:     '3306',                                      // proxy endpoint port
    user:     'AccessID',                                  // your AccessKey ID
    password: 'AccessSecret',                              // your AccessKey secret
    database: 'schema'                                     // database name
});

connection.connect();
connection.query('SHOW DATABASES', function(err, result) {
    console.log(result);
});
connection.end();

Use an SQL client

This example uses Navicat. Enter the following fields when creating a new connection:

Host: the domain name from your proxy endpoint
Port: the port number from your proxy endpoint
User Name: your AccessKey ID
Password: your AccessKey secret