Connecting to a database in a MySQL cluster
You can connect to Managed Service for MySQL cluster hosts:
-
Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.
-
From Yandex Cloud virtual machines located in the same cloud network. If there is no public access to a host, using SSL for connections from such virtual machines is not required.
Warning
If only some cluster hosts have public access configured, the master may not be accessible from the internet when it changes automatically.
The maximum number of connections is defined by the Max connections setting that depends on the host class.
For more information, see Network and clusters in Managed Service for MySQL.
Configuring security groups
To connect to a cluster, security groups must include rules allowing traffic from certain ports, IP addresses, or from other security groups.
Rule settings depend on the connection method you select:
Configure all security groups in the cluster to allow incoming traffic on port 3306 from any IP address. To do this, create the following rule for incoming traffic:
- Port range:
3306
- Protocol:
TCP
- Source:
CIDR
- CIDR blocks:
0.0.0.0/0
-
Configure all security groups in the cluster to enable incoming traffic from the security group where your VM is located on port 3306. To do this, create the following rule for incoming traffic in these groups:
- Port range:
3306
- Protocol:
TCP
- Source:
Security group
- Security group: If a cluster and a VM are in the same security group, select
Current
(Self
) as the value. Otherwise, specify the VM security group.
- Port range:
-
Configure the security group where the VM is located to enable connections to the VM and traffic between the VM and the cluster hosts.
For example, you can set the following rules for a VM:
-
For incoming traffic:
- Port range:
22
- Protocol:
TCP
- Source:
CIDR
- CIDR blocks:
0.0.0.0/0
This rule allows you to connect to the VM over SSH.
- Port range:
-
For outgoing traffic:
- Port range:
0-65535
- Protocol:
Any
(Any
) - Destination name:
CIDR
- CIDR blocks:
0.0.0.0/0
This rule allows all outgoing traffic, which enables you to both connect to the cluster and install the certificates and utilities the VMs need to connect to the cluster.
- Port range:
-
Note
You can set more detailed rules for security groups, such as allowing traffic in only specific subnets.
Security groups must be configured correctly for all subnets that will include cluster hosts. If the security group settings are incomplete or incorrect, you might lose access the cluster.
For more information about security groups, see Security groups.
Getting an SSL certificate
MySQL hosts with public access only support encrypted connections. To use them, get an SSL certificate:
mkdir -p ~/.mysql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
--output-document ~/.mysql/root.crt && \
chmod 0600 ~/.mysql/root.crt
The certificate is saved to the ~/.mysql/root.crt
file.
mkdir $HOME\.mysql; curl.exe -o $HOME\.mysql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem
The certificate is saved to the $HOME\.mysql\root.crt
file.
To use graphical IDEs, save a certificate
MySQL host FQDN
To connect to a host, you need its fully qualified domain name (FQDN). You can obtain it in one of the following ways:
-
In the management console
, copy the command for connecting to the cluster. This command contains the host FQDN. To get the command, go to the cluster page and click Connect. -
Look up the FQDN in the management console:
- Go to the cluster page.
- Go to Hosts.
- Copy the Host FQDN column value.
Cluster hosts also use special FQDNs.
Special FQDNs
Alongside regular FQDNs, Managed Service for MySQL provides several special FQDNs, which can also be used when connecting to a cluster.
Warning
If, when the master host is changed automatically, a host with no public access becomes a new master or the most recent replica, you will not be able to access them from the internet. To avoid this, enable public access for all cluster hosts.
Current master
Such FQDN as c-<cluster_ID>.rw.mdb.yandexcloud.net
always points to the current cluster master host. You can get the cluster ID with a list of clusters in the folder.
When connecting to this FQDN, both read and write operations are allowed.
Here is an example of connecting to a master host for a cluster with the c9qash3nb1v9********
ID:
mysql --host=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net \
--port=3306 \
--ssl-ca=~/.mysql/root.crt \
--ssl-mode=VERIFY_IDENTITY \
--user=<username> \
--password \
<DB_name>
Most recent replica
Such FQDN as c-<cluster_ID>.ro.mdb.yandexcloud.net
points to the most recent replica, i.e., the one most up-to-date with the master host. You can request the cluster ID with a list of clusters in the folder.
Specifics:
- When connecting to this FQDN, only read operations are allowed.
- If there are no active replicas in a cluster, you cannot connect to this FQDN, as the respective DNS CNAME record will point to a
null
object.
Here is an example of connecting to the most recent replica for a cluster with the c9qash3nb1v9********
ID:
mysql --host=c-c9qash3nb1v9********.ro.mdb.yandexcloud.net \
--port=3306 \
--ssl-ca=~/.mysql/root.crt \
--ssl-mode=VERIFY_IDENTITY \
--user=<username> \
--password \
<DB_name>
Connecting from graphical IDEs
Connections were tested in the following environment:
- Ubuntu 20.04, DBeaver:
22.2.4
- MacOS Monterey 12.7
- JetBrains DataGrip:
2023.3.4
- DBeaver Community:
24.0.0
- JetBrains DataGrip:
- Windows 10 Pro 21H1 with SQL Server Management Studio
18.9.1
You can only use graphical IDEs to connect to public cluster hosts using SSL certificates.
To avoid connection errors, save the certificate
- Create a data source:
- Select File → New → Data Source → MySQL.
- On the General tab:
- Specify the connection parameters:
- Host: Any MySQL host FQDN or a special FQDN.
- Port:
3306
. - User, Password: DB user's name and password.
- Database: Name of the DB to connect to.
- Click Download to download the connection driver.
- Specify the connection parameters:
- On the SSH/SSL tab:
- Enable the Use SSL setting.
- In the CA file field, specify the path to the file with an SSL certificate for the connection.
- To test the connection, click Test Connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
- Click OK to save the data source.
- Create a new DB connection:
- In the Database menu, select New connection.
- Select MySQL from the DB list.
- Click Next.
- Specify the connection parameters on the Main tab:
- Server: FQDN of any MySQL host or a special FQDN.
- Port:
3306
. - Database: DB to connect to.
- Username, Password: DB username and password.
- On the SSL tab:
- Enable Use SSL.
- In the Root certificate field, specify the path to the saved SSL certificate file.
- Under Advanced:
- Enable Require SSL.
- Enable Verify server certificate.
- Click Test connection ... to test the connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
- Click Ready to save the database connection settings.
Connecting to a cluster from your browser
Use Yandex WebSQL to connect to MySQL clusters in Yandex Cloud.
Note
This feature is in the Preview stage.
Warning
You can establish connections to managed DB clusters from your Yandex Cloud organization's clouds and folders. If you switch to a different organization, a set of clusters from other clouds and folders will be available to you.
- Make sure the Access from the management console option is enabled in the PostgreSQL, MySQL, or ClickHouse® cluster settings.
- In Yandex WebSQL, go to the Connection manager section and click
- Specify the connection parameters:
- Cloud: Select a cloud.
- Folder: Select the folder where you created the database cluster.
- Cluster: Select the cluster.
- Click Connect to connect to the selected cluster.
Imported connections will be available while you are working in the current Yandex Cloud organization.
To build a query:
-
In the Connection manager, select a database, table, or image.
-
In the data panel, you will see a form with the default query:
SELECT * FROM <table_name> LIMIT 10;
-
Use this query or edit it. The interface will suggest relevant parts of the SQL query and highlight errors.
-
Click Run.
In the results panel, you will see a table with the query results.
When editing or running a query, you can use the following keyboard shortcuts (click
to see the full list):- Show suggestions: Ctrl + I , Ctrl + Space.
- Comment out a line: Ctrl + /.
- Run a query: Ctrl + Enter.
- Show suggestions: ⌘ + I , ⌃ + Space.
- Comment out a line: ⌘ + /.
- Run a query: ⌘ + ⏎.
Click Open Command Palette (
Before you connect from a Docker container
To connect to a Managed Service for MySQL cluster from a Docker container, add the following lines to the Dockerfile:
RUN apt-get update && \
apt-get install mysql-client --yes
RUN apt-get update && \
apt-get install wget mysql-client --yes && \
mkdir --parents ~/.mysql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
--output-document ~/.mysql/root.crt && \
chmod 0600 ~/.mysql/root.crt
Sample connection strings
The Linux examples were tested in the following environment:
- Yandex Cloud virtual machine running Ubuntu 20.04 LTS.
- Bash:
5.0.16
. - Python:
3.8.2
; pip3:20.0.2
. - PHP:
7.4.3
. - OpenJDK:
11.0.8
; Maven:3.6.3
. - Node.JS:
10.19.0
, npm:6.14.4
. - Go:
1.13.8
. - Ruby:
2.7.0p0
. - unixODBC:
2.3.6
.
The Windows examples were tested in the following environment:
- A local machine with Windows 10 Pro build
19042.1052
. - PowerShell:
5.1.19041
. - cURL:
7.55.1 WinSSL
.
You can only connect to publicly accessible MySQL hosts using an SSL certificate.
Bash
Before connecting, install the mysql
utility:
sudo apt update && sudo apt install --yes mysql-client
mysql --host=<MySQL_host_name>.mdb.yandexcloud.net \
--port=3306 \
--ssl-ca=~/.mysql/root.crt \
--ssl-mode=VERIFY_IDENTITY \
--user=<username> \
--password \
<DB_name>
mysql --host=<FQDN_of_any_MySQL_host> \
--port=3306 \
--ssl-mode=DISABLED \
--user=<username> \
--password \
<DB_name>
To learn how to get a host FQDN, see this guide.
When running any command, enter the DB user password.
Once connected to the DBMS, run SELECT @@version;
.
If the connection to the cluster and the test query are successful, the MySQL version is output.
Go
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes golang git && \
go get github.com/go-sql-driver/mysql
connect.go
package main
import (
"io/ioutil"
"crypto/tls"
"crypto/x509"
"database/sql"
"fmt"
"github.com/go-sql-driver/mysql"
)
const (
host = "<FQDN_of_any_MySQL_host>"
port = 3306
user = "<username>"
password = "<user_password>"
dbname = "<DB_name>"
)
func main() {
rootCertPool := x509.NewCertPool()
pem, err := ioutil.ReadFile("/home/<home_directory>/.mysql/root.crt")
if err != nil {
panic(err)
}
if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
panic("Failed to append PEM.")
}
mysql.RegisterTLSConfig("custom", &tls.Config{
RootCAs: rootCertPool,
})
mysqlInfo := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?tls=custom",
user, password, host, port, dbname)
conn, err := sql.Open("mysql", mysqlInfo)
if err != nil {
panic(err)
}
defer conn.Close()
q, err := conn.Query("SELECT version()")
if err != nil {
panic(err)
}
var result string
for q.Next() {
q.Scan(&result)
fmt.Println(result)
}
}
For this connection method, the code must include the full path to the root.crt
certificate for MySQL in the ca
variable.
connect.go
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
const (
host = "<FQDN_of_any_MySQL_host>"
port = 3306
user = "<username>"
password = "<user_password>"
dbname = "<DB_name>"
)
func main() {
mysqlInfo := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",
user, password, host, port, dbname)
conn, err := sql.Open("mysql", mysqlInfo)
if err != nil {
panic(err)
}
defer conn.Close()
q, err := conn.Query("SELECT version()")
if err != nil {
panic(err)
}
var result string
for q.Next() {
q.Scan(&result)
fmt.Println(result)
}
}
To learn how to get a host FQDN, see this guide.
Connecting:
go run connect.go
If the connection to the cluster and the test query are successful, the MySQL version is output.
Java
Before connecting:
-
Install the dependencies:
sudo apt update && sudo apt install --yes default-jdk maven
-
Add the SSL certificate to the Java trusted certificate store (Java Key Store) so that the MySQL driver can use this certificate for secure connections to the cluster hosts. Make sure to set the password for additional storage security using the
-storepass
parameter:cd ~/.mysql && \ sudo keytool -importcert \ -alias YandexCA \ -file root.crt \ -keystore YATrustStore \ -storepass <certificate_store_password> \ --noprompt
Where
storepass
is the certificate store password, at least 6 characters long. -
Create a folder for the Maven project:
cd ~/ && mkdir -p project/src/java/com/example && cd project/
-
Create a configuration file for Maven:
pom.xml<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.example</groupId> <artifactId>app</artifactId> <packaging>jar</packaging> <version>0.1.0</version> <properties> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.21</version> </dependency> </dependencies> <build> <finalName>${project.artifactId}-${project.version}</finalName> <sourceDirectory>src</sourceDirectory> <resources> <resource> <directory>src</directory> </resource> </resources> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-assembly-plugin</artifactId> <executions> <execution> <goals> <goal>attached</goal> </goals> <phase>package</phase> <configuration> <descriptorRefs> <descriptorRef>jar-with-dependencies</descriptorRef> </descriptorRefs> <archive> <manifest> <mainClass>com.example.App</mainClass> </manifest> </archive> </configuration> </execution> </executions> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-jar-plugin</artifactId> <version>3.1.0</version> <configuration> <archive> <manifest> <mainClass>com.example.App</mainClass> </manifest> </archive> </configuration> </plugin> </plugins> </build> </project>
You can check the current
mysql-connector-java
version on the project page in the Maven repository .
src/java/com/example/App.java
package com.example;
import java.sql.*;
public class App {
public static void main(String[] args) {
String DB_URL = "jdbc:mysql://<FQDN_of_any_MySQL_host>:3306/<DB_name>?useSSL=true";
String DB_USER = "<username>";
String DB_PASS = "<user_password>";
System.setProperty("javax.net.ssl.trustStore", "/home/<home_directory>/.mysql/YATrustStore");
System.setProperty("javax.net.ssl.trustStorePassword", "<certificate_store_password>");
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
ResultSet q = conn.createStatement().executeQuery("SELECT version()");
if(q.next()) {System.out.println(q.getString(1));}
conn.close();
}
catch(Exception ex) {ex.printStackTrace();}
}
}
This code must specify the full path to the YATrustStore
certificate store for the MySQL driver in the javax.net.ssl.trustStore
property.
src/java/com/example/App.java
package com.example;
import java.sql.*;
public class App {
public static void main(String[] args) {
String DB_URL = "jdbc:mysql://<FQDN_of_any_MySQL_host>:3306/<DB_name>?useSSL=false";
String DB_USER = "<username>";
String DB_PASS = "<user_password>";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
ResultSet q = conn.createStatement().executeQuery("SELECT version()");
if(q.next()) {System.out.println(q.getString(1));}
conn.close();
}
catch(Exception ex) {ex.printStackTrace();}
}
}
To learn how to get a host FQDN, see this guide.
Connecting:
mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar
If the connection to the cluster and the test query are successful, the MySQL version is output.
Node.js
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes nodejs npm && \
npm install mysql2
app.js
"use strict"
const fs = require('fs');
const mysql = require('mysql2');
const config = {
host : '<FQDN_of_any_MySQL_host>',
port : 3306,
user : '<username>',
password : '<user_password>',
database : '<DB_name>',
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync('/home/<home_directory>/.mysql/root.crt').toString(),
},
}
const conn = mysql.createConnection(config)
conn.connect(err => {if (err) throw err})
conn.query('SELECT version()', (err, result, fields) => {
if (err) throw err
console.log(result[0])
conn.end()
})
For this connection method, the code must include the full path to the root.crt
certificate for MySQL in the ca
variable.
app.js
"use strict"
const mysql = require('mysql2');
const config = {
host : '<FQDN_of_any_MySQL_host>',
port : 3306,
user : '<username>',
password : '<user_password>',
database : '<DB_name>',
}
const conn = mysql.createConnection(config)
conn.connect(err => {if (err) throw err})
conn.query('SELECT version()', (err, result, fields) => {
if (err) throw err
console.log(result[0])
conn.end()
})
To learn how to get a host FQDN, see this guide.
Connecting:
node app.js
If the connection to the cluster and the test query are successful, the MySQL version is output.
ODBC
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes unixodbc && \
wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.21-1ubuntu20.04_amd64.deb && \
sudo dpkg -i mysql-connector-odbc_8.0.21-1ubuntu20.04_amd64.deb
The MySQL Connector/ODBC driver will be registered automatically in /etc/odbcinst.ini
. Current version of the driver: mysql-connector-odbc
Set the connection parameters in the /etc/odbc.ini
file.
odbc.ini
[mysql]
Driver=MySQL ODBC 8.0 Unicode Driver
SERVER=<FQDN_of_any_MySQL_host>
UID=<username>
PWD=<user_password>
DATABASE=<DB_name>
PORT=3306
SSLCA=/home/<home_directory>/.mysql/root.crt
SSLVERIFY=1
For this connection method, the /etc/odbc.ini
file must include the full path to the root.crt
certificate for MySQL in the SSLCA
parameter.
odbc.ini
[mysql]
Driver=MySQL ODBC 8.0 Unicode Driver
SERVER=<FQDN_of_any_MySQL_host>
UID=<username>
PWD=<user_password>
DATABASE=<DB_name>
PORT=3306
To learn how to get a host FQDN, see this guide.
Connecting:
isql -v mysql
Once connected to the DBMS, run SELECT @@version;
.
If the connection to the cluster and the test query are successful, the MySQL version is output.
PHP
Before connecting, install the dependencies:
sudo apt update && apt install --yes php php-mysql
connect.php
<?php
$conn = mysqli_init();
$conn->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$conn->ssl_set(NULL, NULL, '/home/<home_directory>/.mysql/root.crt', NULL, NULL);
$conn->real_connect('<FQDN_of_any_MySQL_host>', '<username>', '<user_password>', '<DB_name>', 3306, NULL, MYSQLI_CLIENT_SSL);
$q = $conn->query('SELECT version()');
$result = $q->fetch_row();
echo($result[0]);
$q->close();
$conn->close();
?>
For this connection method, the code must include the full path to the root.crt
certificate for MySQL in the ssl_set
method.
connect.php
<?php
$conn = mysqli_init();
$conn->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, false);
$conn->real_connect('<FQDN_of_any_MySQL_host>', '<username>', '<user_password>', '<DB_name>', 3306, NULL, NULL);
$q = $conn->query('SELECT version()');
$result = $q->fetch_row();
echo($result[0]);
$q->close();
$conn->close();
?>
To learn how to get a host FQDN, see this guide.
Connecting:
php connect.php
If the connection to the cluster and the test query are successful, the MySQL version is output.
PowerShell
Before connecting, downloadMySQL Shell
utility.
mysqlsh --host=<FQDN_of_any_MySQL_host> `
--port=3306 `
--ssl-ca=<absolute_path_to_certificate_file> `
--ssl-mode=VERIFY_IDENTITY `
--user=<username> `
--password `
--database=<DB_name> `
--sql
mysqlsh --host=<FQDN_of_any_MySQL_host> `
--port=3306 `
--ssl-mode=DISABLED `
--user=<username> `
--password `
--database=<DB_name>
To learn how to get a host FQDN, see this guide.
When running any command, enter the DB user password.
Once connected to the DBMS, run SELECT @@version;
.
If the connection to the cluster and the test query are successful, the MySQL version is output.
Python
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes python3 python3-pip libmysqlclient-dev && \
pip3 install mysqlclient
connect.py
import MySQLdb
conn = MySQLdb.connect(
host="<FQDN_of_any_MySQL_host>",
port=3306,
db="<DB_name>",
user="<username>",
passwd="<user_password>",
ssl={'ca': '~/.mysql/root.crt'})
cur = conn.cursor()
cur.execute('SELECT version()')
print(cur.fetchone()[0])
conn.close()
connect.py
import MySQLdb
conn = MySQLdb.connect(
host="<FQDN_of_any_MySQL_host>",
port=3306,
db="<DB_name>",
user="<username>",
passwd="<user_password>")
cur = conn.cursor()
cur.execute('SELECT version()')
print(cur.fetchone()[0])
conn.close()
To learn how to get a host FQDN, see this guide.
Connecting:
python3 connect.py
If the connection to the cluster and the test query are successful, the MySQL version is output.
Ruby
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes ruby ruby-mysql2
connect.rb
require "mysql2"
conn = Mysql2::Client.new(
:host => "<FQDN_of_any_MySQL_host>",
:port => 3306,
:database => "<DB_name>",
:username => "<username>",
:password => "<user_password>",
:ssl_mode => "verify_identity",
:sslca => "~/.mysql/root.crt")
q = conn.query("SELECT version()")
q.each do |result|
puts result["version()"]
end
conn.close()
connect.rb
require "mysql2"
conn = Mysql2::Client.new(
:host => "<FQDN_of_any_MySQL_host>",
:port => 3306,
:database => "<DB_name>",
:username => "<username>",
:password => "<user_password>")
q = conn.query("SELECT version()")
q.each do |result|
puts result["version()"]
end
conn.close()
To learn how to get a host FQDN, see this guide.
Connecting:
ruby connect.rb
If the connection to the cluster and the test query are successful, the MySQL version is output.
ClickHouse® is a registered trademark of ClickHouse, Inc