Connect to a database

You can connect to PolarDB-X databases by using Data Management (DMS), the MySQL command-line client mysql, third-party clients, and third-party drivers for MySQL. This topic describes how to use mysql to connect to a PolarDB-X database.

Prerequisites

The first time you use PolarDB-X, perform the operations described in Get started. For example, you must create a PolarDB-X instance, create an account, and create a database.

If mysql is not installed on your server, go to the MySQL website to download and install the MySQL client.

Use mysql to connect to a database

1. Log on to the PolarDB for Xscale console.

2. In the top navigation bar, select the region where the target instance is located.

3. On the Instances page, click the PolarDB-X 2.0 tab.

4. Find the target instance and click its ID.

5. In the Connection information section, find the private and public endpoints of the instance. Note If you need to access the instance over the Internet for a test, use the External address and Extranet Port to connect to the instance. To ensure database security, we recommend that you use the private endpoint to access the instance during your use of PolarDB-X.

6. After you obtain the connection information in the Connection information section, run the following command to obtain the local public IP address. Then, add the IP address to a whitelist of the instance. For more information about how to configure a whitelist, see Configure whitelists.

    curl 'https://api.ipify.org'
   

7. Run the following MySQL command to connect to your database:

   mysql -h<Endpoint> -P<Port number> -u<Username> -p -D<Database name>
   

Use a third-party client to connect to a database

You can connect to PolarDB-X databases by using the following clients. You can go to the corresponding official websites to download the clients.

• MySQL Workbench (Recommended)

• SQLyog

• Sequel Pro

• Navicat for MySQL

Note On a third-party GUI client, you can perform basic database operations. For example, you can insert, delete, modify, and query data and perform DDL operations. PolarDB-X may not support the advanced features of the client.

Use third-party drivers for MySQL to connect to a database

You can connect to PolarDB-X databases by using the following third-party drivers for MySQL:

• JDBC Driver for MySQL (Connector/J)

• Python Driver for MySQL (Connector/Python)

• C++ Driver for MySQL (Connector/C++)

• C Driver for MySQL (Connector/C)

• ADO.NET Driver for MySQL (Connector/NET)

• ODBC Driver for MySQL (Connector/ODBC)

• PHP Drivers for MySQL (mysqli, PDO_MYSQL, PHP_MYSQLND)

• Perl Driver for MySQL (DBD::mysql)

• Ruby Driver for MySQL (ruby-mysql)

The following code provides an example on how to use JDBC Driver for MySQL (Connector/J) to connect to a PolarDB-X database:

//JDBC
Class.forName("com.mysql.jdbc.Driver"); 
Connection conn = DriverManager.getConnection("jdbc:mysql://pxc-******************.public.polarx.rds.aliyuncs.com:3306/doc_test","doc_test","doc_test_password");
//...
conn.close();

The following code provides an example on how to use a connection pool to connect to a PolarDB-X database from your application.

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> 
<property name="url" value="jdbc:mysql://pxc-******************.public.polarx.rds.aliyuncs.com:3306/doc_test" />
<property name="username" value="doc_test" />
<property name="password" value="doc_test_password" />
<property name="filters" value="stat" />
<property name="maxActive" value="100" />
<property name="initialSize" value="20" />
<property name="maxWait" value="60000" />
<property name="minIdle" value="1" />
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="20" />
<property name="asyncInit" value="true" />
</bean>

Note We recommend that you use the Druid connection pool to connect to PolarDB-X databases. For more information about Druid, see Druid in GitHub.

The best practice to set a timeout period for database queries

Your application may access databases by using common ORM frameworks, such as Spring Boot, MyBatis, and JDBC Driver. These ORM frameworks allow you to specify the client-side timeout period for SQL queries from multiple dimensions, such as transactions, SQL statements, and network packet forwarding. For example, you can use transaction_timeout for Spring Boot, statement_timeout for Mybatis, and query_timeout for JDBC MySQL Driver.

Most of these clients implement the timeout settings by executing Kill statements to interrupt queries. In distributed databases, a large number of resources are consumed when Kill statements are frequently executed. Therefore, we recommend that you do not use the preceding timeout settings frequently when you connect to a database from your application.

The best practice is to use the socket_timeout parameter in JDBC URL to specify the timeout period for SQL queries. The timeout period specified by this parameter is implemented based on the timeout mechanism of the TCP protocol. Therefore, the Kill statement is not executed when query timeout occurs. The following example shows how to use the socket_timeout parameter to specify a timeout period:

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  <property name="url" value="jdbc:mysql://pxc-*****.public.polarx.rds.aliyuncs.com:3306/doc_test?socketTimeout=60000" />
  ...
  <property name="asyncInit" value="true" />
</bean>