Access an instance over the MySQL protocol-Data Management-阿里云帮助中心

Prerequisites

Secure Access Proxy is enabled for the instance. For more information, see Enable Secure Access Proxy.
You have been granted permissions to access the instance by using proxy endpoints. For more information, see Apply for permissions to access a database instance by using proxy endpoints.

Usage notes

For an instance in Security Collaboration mode, connections through Secure Access Proxy are subject to security rules. These rules limit the number of rows a query can return, with a configurable maximum of 100,000.

Set the maximum number of rows returned per query

In the DMS console, navigate to Security and Specifications > Security Rules > Secure Access Proxy to set the maximum number of rows returned per query.

In the rule list, find the Maximum number of rows returned per query rule. Its current value is 5000. You can modify this value as needed.

Note

To bypass security rules, connect to the database using the endpoint provided in the database console, or contact DMS technical support to evaluate your use case.
For instances that are not in Security Collaboration mode, you cannot customize the maximum number of rows returned per query. The default limit is 3,000 rows.

Limitations

The idle timeout for your MySQL client cannot exceed 900 seconds.
If you use a connection pool, the keep-alive interval cannot exceed 900 seconds.

Note

We recommend using a connection pool and setting the keep-alive interval to 750 seconds.

Connection examples

You can connect to a target instance that has Secure Access Proxy enabled using the MySQL CLI, an SQL client, or program code.

Use the MySQL CLI

Use the following format:

mysql -h<host> -P<port> -u<user_name> -p<password> <database> -e '<sql_statements>'

Parameters:

Parameter	Description
host	The proxy endpoint for the target instance. You can find this on the Secure Access Proxy page, listed as the MySQL protocol address for either the internal or public proxy endpoint.
port	The port of the target instance, such as 3306. This port is part of the MySQL protocol address shown on the Secure Access Proxy page.
user_name	Your AccessKey ID. DMS assigns this ID after authorization. You can find it in the authorized user list on the Secure Access Proxy page.
password	Your AccessKey secret. DMS assigns this secret after authorization. You can find it in the authorized user list on the Secure Access Proxy page.
database	The name of the database in the target instance.
sql_statements	The SQL command to execute, such as SHOW DATABASES.

Example:

mysql -hdpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com -P3306 -uYOUR_ACCESS_KEY_ID -pYOUR_ACCESS_KEY_SECRET Schema -e 'SHOW DATABASES'

Use program code

Note

The Python example uses Python 2.

Java

// dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com:3306 is the proxy endpoint and port of the target instance. 
// You can find the endpoint and port in the MySQL protocol address on the Secure Access Proxy page.
// schema is the name of the database in the target instance.
String url = "jdbc:mysql://dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com:3306/schema";
Properties properties = new Properties();
// Use your AccessKey ID. You can find it in the authorized user list on the Secure Access Proxy page.
properties.setProperty("user", "YOUR_ACCESS_KEY_ID");
// Use your AccessKey secret. You can find it in the authorized user list on the Secure Access Proxy page.
properties.setProperty("password", "YOUR_ACCESS_KEY_SECRET");
try (Connection connection = DriverManager.getConnection(url, properties)) {
    try (Statement statement = connection.createStatement()) {
        // Use the execute method to run an SQL statement. This example uses SHOW DATABASES. You can replace it with other SQL statements.
        statement.execute("SHOW DATABASES");
        ResultSet resultSet = statement.getResultSet();
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

Python

import pymysql
try:
    # host: The proxy endpoint of the target instance.
    # port: The port of the target instance.
    # user: Your AccessKey ID. You can find it in the authorized user list on the Secure Access Proxy page.
    # password: Your AccessKey secret. You can find it in the authorized user list on the Secure Access Proxy page.
    # database: The name of the database in the target instance.
    conn = pymysql.connect(host='dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com', port=3306, user='YOUR_ACCESS_KEY_ID', password="YOUR_ACCESS_KEY_SECRET",database ='schema')  
    cur = conn.cursor(pymysql.cursors.DictCursor)
    # Use the execute method to run an SQL statement. This example uses SHOW DATABASES. You can replace it with other SQL statements.
    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: The proxy endpoint of the target instance. 
    host     : 'dpxxxx-xxxxxxxx.proxy.dms.aliyuncs.com',  
    // user: Your AccessKey ID. You can find it in the authorized user list on the Secure Access Proxy page.     
    user     : 'YOUR_ACCESS_KEY_ID', 
    // password: Your AccessKey secret. You can find it in the authorized user list on the Secure Access Proxy page.             
    password : 'YOUR_ACCESS_KEY_SECRET', 
    // port: The port of the target instance.      
    port     : '3306',  
    // database: The name of the database in the target instance.                 
    database : 'schema' 
}); 
connection.connect();
// Use the execute method to run an SQL statement. This example uses SHOW DATABASES. You can replace it with other SQL statements. 
connection.query('SHOW DATABASES', function(err, result) {
    console.log(result);
});
connection.end();

Use an SQL client

This example uses Navicat. Configure the following fields:

Host: The proxy endpoint of the target instance.
Port: The port of the target instance.
User name: Your AccessKey ID.
Password: Your AccessKey secret.