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.

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 groupsConfiguring 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:

For more information about security groups, see Security groups.

Getting an SSL certificateGetting 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

mkdir $HOME\.mysql; curl.exe -o $HOME\.mysql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

To use graphical IDEs, save a certificate to a local folder and specify the path to it in the connection settings.

MySQL® host FQDNMySQL® 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:

• Request a list of cluster hosts.

• 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:

  1. Go to the cluster page.
  2. Go to Hosts.
  3. Copy the Host FQDN column value.

Cluster hosts also use special FQDNs.

Special FQDNsSpecial FQDNs

Alongside regular FQDNs, Managed Service for MySQL® provides several special FQDNs, which can also be used when connecting to a cluster.

Current masterCurrent 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 replicaMost 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 IDEsConnecting 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

You can only use graphical IDEs to connect to public cluster hosts using SSL certificates.

To avoid connection errors, save the certificate to a local folder that does not require administrator rights to access.

Connecting from Yandex WebSQLConnecting from Yandex WebSQL

You can send SQL queries to databases in a Managed Service for MySQL® cluster using Yandex WebSQL.

WebSQL is a Yandex Cloud service that allows you to connect to managed database clusters, work with DBs, tables, and schemas, and run SQL queries. It is a web-based tool that requires no additional authorization and simplifies working with SQL commands by suggesting prompts.

To connect from WebSQL to a Managed Service for MySQL® cluster, create a connection:

1. Go to the folder page and select Managed Service for MySQL.
2. Click the cluster name.
3. Enable the WebSQL access option in the cluster settings if it is not enabled yet.
4. Select the WebSQL tab.
5. Click Create connection and specify the connection parameters:
   • Connection name.
   • Database type: MySQL®.
   • Cluster: Defaults to your current MySQL® cluster.
   • Username you will use to connect to the database in the cluster.
   • User password.
   • Databases you want to connect to. You can only connect to the databases that exist in this cluster. The user you specified must have access to them configured.
6. Click Create.

To open the SQL editor, click the created connection on the WebSQL tab.

For more information about working with WebSQL, see the service documentation.

Before you connect from a Docker containerBefore 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

Examples of connection stringsExamples of 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 public MySQL® hosts using an SSL certificate.

BashBash

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.

GoGo

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes golang git && \
go get github.com/go-sql-driver/mysql

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)
  }
}

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.

JavaJava

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install --yes default-jdk maven
   

2. 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.

3. Create a folder for the Maven project:

   cd ~/ && mkdir -p project/src/java/com/example && cd project/
   

4. 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.

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();}
  }
}

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.jsNode.js

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes nodejs npm && \
npm install mysql2

"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()
  })

"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.

ODBCODBC

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.

[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

[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.

PHPPHP

Before connecting, install the dependencies:

sudo apt update && apt install --yes php php-mysql

<?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();
?>

<?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.

PowerShellPowerShell

Before connecting, download and install the MySQL 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.

PythonPython

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes python3 python3-pip libmysqlclient-dev && \
pip3 install mysqlclient

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()

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.

RubyRuby

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes ruby ruby-mysql2

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()

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.