Connect to the cluster | Selectel Documentation

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 DNS addresses correspond to 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 the cluster subnet to a cloud router with access to the external network. Use the following instructions Set up internet access via cloud router.

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

Ports⁠

Use port 3306 to connect to the cluster.

Ways of connection⁠

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

Connection with SSL certificate is available for all methods.

View the address for connection⁠

В control panels go to Cloud platform → Databases.
Open the Database Cluster page → tab Connection.
In the block Addresses for connection check out 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 each 1 GB of RAM, 50 connections are available. For example, 200 connections are available for a cluster with 4 GB RAM, and 300 connections are available for a cluster with 6 GB RAM.

To increase the number of connections, scale the cluster to the right 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 folder ~/.mysql/:

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> — database user name;
<database_name> — database name.

В control panels click Download the certificate to download the root certificate and place it in the folder ~/.mysql/

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> — database user name;
<database_name> — database name.

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

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> — database user name;
<password> — user password;
<database_name> — database name;
<full_path_to_root_certificate> — the full path to the root certificate.

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

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> — database user name;
<password> — user password;
<database_name> — database name;
<full_path_to_root_certificate> — the full path to the root certificate.

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

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> — the full path to the root certificate;
<database_user_name> — database user name;
<password> — user password;
<host> — DNS address of the node;
<database_name> — database name.

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

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> — database name;
<database_user_name> — database user name;
<password> — user password;
<full_path_to_root_certificate> — 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> — database user name;
- <database_name> — database name.

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> — database user name;
- <database_name> — database name.

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> — database user name;
<password> — user password;
<database_name> — database name.

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> — database user name;
<password> — user password;
<database_name> — database name.

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> — database user name;
<password> — user password;
<host> — DNS address of the node;
<database_name> — database name.

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> — database name;
<database_user_name> — database user name;
<password> — user password.

Connect via phpMyAdmin⁠

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

$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> — DNS address of the node.

Connect to MySQL semi-sync cluster

Ports⁠​

Ways of connection⁠​

View the address for connection⁠​

Number of connections⁠​

Connect with SSL⁠​

Connect without SSL⁠​

Connect via phpMyAdmin⁠​

Ports⁠

Ways of connection⁠

View the address for connection⁠

Number of connections⁠

Connect with SSL⁠

Connect without SSL⁠

Connect via phpMyAdmin⁠