Connect to the cluster | Документация Selectel

Connect to MySQL semi-sync cluster

The MySQL semi-sync cloud database cluster can be connected to by DNS address, private IP address, and public IP address.

We recommend connecting by DNS address because the DNS addresses correspond to the node roles and lead to the actual IP addresses of the master and replicas. IP addresses correspond to specific nodes. If the master is unavailable, one of the replicas will assume its role, the master's IP address will change, and the IP connection will stop working.

If the cluster is connected to a private subnet and you want to work with it via DNS, connect cloud router-to-external-network to the cluster subnet.

If the cluster is connected to a private network and you need to configure access to the node from the Internet, connect public IP address.

Ports⁠

Use port 3306 to connect to the cluster.

Ways to connect⁠

through the mysql terminal client;
via graphical database management tools: phpMyAdmin, MySQL Workbench, Sequel Pro, and others;
from the with SSL and without SSL program code.

Connection with SSL certificate is available for all methods.

View the address to connect⁠

In Control Panel, go to Cloud Platform → Databases.
Open the Database Cluster page → Connect tab.
In the Addresses to connect block, look up the address.

Number of connections⁠

The number of connections for a MySQL semi-sync cluster is determined by the amount of RAM for the database cluster nodes. For every 1 GB of RAM, 50 connections are available. For example, for a cluster with 4 GB RAM, 200 connections are available; for a cluster with 6 GB RAM, 300 connections are available.

To increase the number of connections, scale the cluster to the desired amount of RAM.

Connect with SSL⁠

Connecting using TLS/SSL encryption provides a secure connection between your server and the database cluster.

Download the root certificate and place it in the ~/.mysql/ folder:

mkdir -p ~/.mysql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.mysql/root.crt
chmod 0600 ~/.mysql/root.crt

Connect to the cluster:

mysql --host=<host> \
    --port=3306 \
    --user=<database_user_name> \
    --password \
    --database=<database_name> \
    --ssl-ca=~/.mysql/root.crt \
    --ssl-mode=verify_ca

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<database_name> is the name of the database.

In control panel, click Download Certificate to download the root certificate and place it in the ~/.mysql/ folder

Connect to the cluster:

mysql --host=<host> \
    --port=3306 \
    --user=<database_user_name> \
    --password \
    --database=<database_name> \
    --ssl-ca=%APPDATA%\mysql\CA.pem\
    --ssl-mode=verify_ca

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<database_name> is the name of the database.

Download the root certificate and place it in the ~/.mysql/ folder:

mkdir -p ~/.mysql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.mysql/root.crt
chmod 0600 ~/.mysql/root.crt

Install the PyMySQL library:
```
pip3 install PyMySQL
```

Use the connection example:

import pymysql.cursors

connection = pymysql.connect(host='<host>',
                                user='<database_user_name>',
                                password='<password>',
                                database='<database_name>',
                                ssl_ca='<full_path_to_root_certificate>',
                                ssl_verify_cert=True,
                                port=3306,
                                cursorclass=pymysql.cursors.DictCursor)

with connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT 40 + 2 AS sum")
            result = cursor.fetchone()
            print(result)

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<password> is the user's password;
<database_name> is the name of the database;
<full_path_to_root_certificate> is the full path to the root certificate.

Download the root certificate and place it in the ~/.mysql/ folder:

mkdir -p ~/.mysql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.mysql/root.crt
chmod 0600 ~/.mysql/root.crt

Install the mysqli library:
```
apt install php-mysqli
```

Use the connection example:

<?php
$mysqli = mysqli_init();
if (!$mysqli)
{
        die("mysqli_init failed");
}
$mysqli->options(MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$mysqli->ssl_set(NULL, NULL, "<full_path_to_root_certificate>", NULL, NULL);

if (!$mysqli->real_connect("<host>", "<database_user_name>", "<password>", "<database_name>", 3306)))
{
        die("Connect Error: " . mysqli_connect_error());
}

$result = $mysqli->query("SELECT 40 + 2");

$row = $result->fetch_row();

echo "Result: $row[0]";

$result->close();
$mysqli->close();

?">

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<password> is the user's password;
<database_name> is the name of the database;
<full_path_to_root_certificate> is the full path to the root certificate.

Download the root certificate and place it in the ~/.mysql/ folder:

mkdir -p ~/.mysql
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.mysql/root.crt
chmod 0600 ~/.mysql/root.crt

Use the connection example:

package main

import (
        "crypto/tls"
        "crypto/x509"
        "database/sql"
        "fmt"
        "github.com/go-sql-driver/mysql"
        "io/ioutil"
)

func main() {
        rootCertPool := x509.NewCertPool()
        pem, err := ioutil.ReadFile("<full_path_to_root_certificate>")
        if err !"= nil {
            panic(err)
        }
        if ok := rootCertPool.AppendCertsFromPEM(pem); !"ok {
            panic("Failed to append PEM.")
        }
        mysql.RegisterTLSConfig("custom", &tls.Config{
            RootCAs: rootCertPool,
        })

        connectionString := fmt.Sprintf("%s: :%s@tcp(%s:3306)/%s?tls=custom",
            "<database_user_name>",
            "<password>",
            "<host>",
            "<database_name>",
    )

        db, err := sql.Open("mysql", connectionString)
        if err != nil {
            panic(err)
        }
        defer db.Close()

        var sum int64
        err = db.QueryRow("SELECT 40+2").Scan(&sum)
        if err != nil {
            panic(err)
        }
        fmt.Println(sum)

}

Specify:

<full_path_to_root_certificate> is the full path to the root certificate;
<database_user_name> is the database username;
<password> is the user's password;
<host> — DNS address of the node;
<database_name> is the name of the database.

Download the root certificate and place it in the ~/.mysql/ folder:

mkdir -p ~/.mysql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.mysql/root.crt
chmod 0600 ~/.mysql/root.crt

Install the mysql2 library:
```
npm install mysql2
```

Use the connection example:

const fs = require('fs');
const mysql = require('mysql2');

const config = {
    host: '<host>',
    port: 3306,
    database: '<database_name>',
    user: '<database_user_name>',
    password: '<password>',
    ssl: {
      rejectUnauthorized: true,
      ca: fs.readFileSync('<full_path_to_root_certificate>').toString(),
    },
};

const connection = mysql.createConnection(config);
};

connection.query('SELECT 40 + 2 AS sum', (error, res) => {
    if (error) throw error;
    console.log(res);
    connection.end();
});

Specify:

<host> — DNS address of the node;
<database_name> is the name of the database;
<database_user_name> is the database username;
<password> is the user's password;
<full_path_to_root_certificate> is the full path to the root certificate.

Connect without SSL⁠

Open the CLI.
Connect to the cluster:
```
mysql --host=<host> \
    --port=3306 \
    --user=<database_user_name> \
    --password \
    --database=<database_name>
```
Specify:
- <host> — DNS address of the node;
- <database_user_name> is the database username;
- <database_name> is the name of the database.

Open the CLI.
Connect to the cluster:
```
mysql --host=<host> \
    --port=3306 \
    --user=<database_user_name> \
    --password \
    --database=<database_name>
```
Specify:
- <host> — DNS address of the node;
- <database_user_name> is the database username;
- <database_name> is the name of the database.

Install the PyMySQL library:
```
pip3 install PyMySQL
```

Use the connection example:

import pymysql.cursors

connection = pymysql.connect(host='<host>',
                                user='<database_user_name>',
                                password='<password>',
                                database='<database_name>',
                                port=3306,
                                cursorclass=pymysql.cursors.DictCursor)

with connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT 40 + 2 AS sum")
            result = cursor.fetchone()
            print(result)

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<password> is the user's password;
<database_name> is the name of the database.

Install the mysqli library:
```
apt install php-mysqli
```

Use the connection example:

<?php
$conn = new mysqli("<host>:3306", "<database_user_name>", "<password>", "<database_name>");

if ($conn->connect_error)
{
        die("ERROR: Unable to connect: " . $conn->connect_error);
}

$result = $conn->query("SELECT 40 + 2");

$row = $result->fetch_row();

echo "Result: $row[0]";

$result->close();
$conn->close();
?">

Specify:

<host> — DNS address of the node;
<database_user_name> is the database username;
<password> is the user's password;
<database_name> is the name of the database.

Use the connection example:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    connectionString := fmt.Sprintf("%s:%s@tcp(%s:3306)/%s",
        "<database_user_name>",
        "<password>",
        "<host>",
        "<database_name>",
    )

    db, err := sql.Open("mysql", connectionString)
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    var sum int64
    err = db.QueryRow("SELECT 40+2").Scan(&sum)
    if err != nil {
        panic(err.Error()))
    }
    fmt.Println(sum)

}

Specify:

<database_user_name> is the database username;
<password> is the user's password;
<host> — DNS address of the node;
<database_name> is the name of the database.

Install the mysql2 library:
```
npm install mysql2
```

Use the connection example:

const mysql = require('mysql2');

const config = {
    host: '<host>',
    port: 3306,
    database: '<database_name>',
    user: '<database_user_name>',
    password: '<password>',
};

const connection = mysql.createConnection(config);

connection.query('SELECT 40 + 2 AS sum', (error, res) => {
    if (error) throw error;
    console.log(res);
    connection.end();
});

Specify:

<host> — DNS address of the node;
<database_name> is the name of the database;
<database_user_name> is the database username;
<password> is the user's password.

Connect via phpMyAdmin⁠

Add lines to the /etc/phpmyadmin/config.inc.php configuration file:

$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['host'] = '<host>';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Specify <host> — the DNS address of the node.

Connect to MySQL semi-sync cluster

Ports⁠​

Ways to connect⁠​

View the address to connect⁠​

Number of connections⁠​

Connect with SSL⁠​

Connect without SSL⁠​

Connect via phpMyAdmin⁠​

Ports⁠

Ways to connect⁠

View the address to connect⁠

Number of connections⁠

Connect with SSL⁠

Connect without SSL⁠

Connect via phpMyAdmin⁠