Connect to a database - PolarDB - Alibaba Cloud Documentation Center

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

Log on to the PolarDB for Xscale console.
In the top navigation bar, select the region where the target instance is located.
On the Instances page, click the PolarDB-X 2.0 tab.
Find the target instance and click its ID.
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.
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'
```

Run the following MySQL command to connect to your database:

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


Parameter	Description	Example
`-h`	The internal endpoint or public endpoint that is used to connect to the instance.	`pxc-******************.public.polarx.rds.aliyuncs.com`
`-P`	The number of the port that is used to connect to the instance. If you connect to the instance over an internal network, specify the internal port number for the instance. If you connect to the instance over the Internet, specify the public port number for the instance. Note Take note that the letter p in `-P` must be in uppercase. The default port number is 3306.	`3306`
`-u`	The username of the account that is used to connect to the instance. For more information about how to create a database account, see Create an account.	`testuser`
`-p`	The password of the account that is used to connect to the instance. Note To ensure password security, we recommend that you do not specify the password at the end of `-p`. After the command is run, you are prompted to enter the password. After you enter the password, press Enter to connect to the database. If you want to specify the password in the command, do not include spaces between `-p` and the password.	`passWord123`
`-D`	The name of the database to which you want to connect. Note This parameter is optional. If you need to specify a database, you can enter only the database name without `-D`.	`mysql`

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>