Connect to MySQL semi-sync cluster

A MySQL semi-sync cluster can be connected to:

• through the mysql terminal client;
• graphical tools for database management: phpMyAdmin, MySQL Workbench, Sequel Pro and others;
• program code.

SSL and non-SSL connections are available for all methods.

Specify the port and address when connecting.

The number of connections for a cluster is limited by the amount of RAM.

Connection ports

Use port 3306 to connect to the cluster.

Addresses for connection

The address to connect to depends on the cluster subnet and where you are connecting from. You can choose an address depending on one of the scenarios:

• connecting to a cluster on a public subnet;
• connect from a private subnet to a cluster on a private subnet;
• connecting from the Internet to a cluster on a private subnet.

Connecting to a cluster on a public subnet

If the cluster is on a public subnet, the nodes can be connected to by DNS address or IP address from the public subnet.

We recommend connecting by DNS address. For DNS addresses in the cluster, the master discovery mechanism is used — the address is bound to the node role, not to the node itself. If the master is unavailable, one of the replicas becomes the new master and the address is transferred to the new node along with the role.

When connecting using an IP address from a public subnet, the master discovery mechanism is not used. If one of the replicas becomes the new master, the master IP address will change and the connection to the old IP address will not work.

You can view the address to connect to in the control panel.

Connecting from a private subnet to a cluster on a private subnet

If you are connecting from a private subnet to a cluster on a private subnet, you can use a DNS address or a private IP address.

We recommend connecting by DNS address. For DNS addresses in the cluster, the master discovery mechanism is used — the address is bound to the node role, not to the node itself. If the master is unavailable, one of the replicas becomes the new master and the address is transferred to the new node along with the role.

When connecting by private IP address, the master discovery mechanism is not used. If one of the replicas becomes the new master, the master IP address will change and the connection using the old IP address will not work.

To connect from another private subnet, first connect both private subnets to the cloud router.

You can view the address to connect to in the control panel.

Connecting from the Internet to a cluster on a private subnet

Если вы подключаетесь к кластеру в приватной подсети из интернета, можно использовать только публичный IP-адрес (Floating IP). Приватная подсеть должна соответствовать требованиям. Если подсеть не соответствует требованиям, подготовьте ее для подключения публичного IP-адреса (Floating IP).

For public IP addresses (Floating IPs) the master discovery mechanism is used — the address is bound to the role of the node, not to the node itself. If the master is unavailable, one of the replicas becomes the new master and the address is transferred to the new node along with the role.

You can view the address to connect to in the control panel.

View the address for connection

1. In the Dashboard, on the top menu, click Products and select Cloud Databases.
2. Open the Active tab.
3. Open the Database Cluster page → Connection tab.
4. 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 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 desired amount of RAM.

Connect with SSL

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

Bash

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

2. Connect to the cluster:

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

   Specify:

   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <database_name> — database name.

PowerShell

1. In the control panel, click Download Certificate to download the root certificate and place it in the %APPDATA% \mysql\ folder.

2. Connect to the cluster:

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

   Specify:

   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <database_name> — database name.

Python

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

2. Install the PyMySQL library:

   pip3 install PyMySQL

3. Use the connection example:

   import pymysql.cursors
   
   connection = pymysql.connect(
       host='<host>',
       user='<database_user_name>',
       password='<password>',
       database='<database_name>',
       ssl_ca='<path>',
       ssl_verify_cert=True,
       port=<port>,
       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 or public IP address (Floating IP) of the node;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name;
   • <path> — the full path to the root certificate;
   • <port> — port for connection.

PHP

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

2. Install the mysqli library:

   apt install php-mysqli

3. 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, "<path>", NULL, NULL);
   
   if (
       !$mysqli->real_connect(
           "<host>",
           "<database_user_name>",
           "<password>",
           "<database_name>",
           <port>
       )
   ) {
       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:

   • <path> — the full path to the root certificate;
   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name;
   • <port> — port for connection.

Go

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

2. 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("<path>")
       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:<port>)/%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:

   • <path> — the full path to the root certificate;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <database_name> — database name.

Node.js

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

2. Install the mysql2 library:

   npm install mysql2

3. Use the connection example:

   const fs = require('fs');
   const mysql = require('mysql2');
   
   const config = {
     host: '<host>',
     port: <port>,
     database: '<database_name>',
     user: '<database_user_name>',
     password: '<password>',
     ssl: {
       rejectUnauthorized: true,
       ca: fs.readFileSync('<path>').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 or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <path> — the full path to the root certificate.

Connect without SSL

Bash

1. Open the CLI.

2. Connect to the cluster:

   mysql --host=<host> \
         --port=<port> \
         --user=<database_user_name> \
         --password \
         --database=<database_name>

   Specify:

   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <database_name> — database name.

PowerShell

1. Open the CLI.

2. Connect to the cluster:

   mysql --host=<host> `
         --port=<port> `
         --user=<database_user_name> `
         --password `
         --database=<database_name>

   Specify:

   • <host> — DNS address or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <database_name> — database name.

Python

1. Install the PyMySQL library:

   pip3 install PyMySQL

2. Use the connection example:

   import pymysql.cursors
   
   connection = pymysql.connect(
       host='<host>',
       user='<database_user_name>',
       password='<password>',
       database='<database_name>',
       port=<port>,
       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 or public IP address (Floating IP) of the node;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name;
   • <port> — port for connection.

PHP

1. Install the mysqli library:

   apt install php-mysqli

2. Use the connection example:

   <?php
   $conn = new mysqli(
       "<host>:<port>",
       "<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 or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name.

Go

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:<port>)/%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:

• <port> — port for connection;
• <database_user_name> — database user name;
• <password> — user password;
• <host> — DNS address or public IP address (Floating IP) of the node;
• <database_name> — database name.

Node.js

1. Install the mysql2 library:

   npm install mysql2

2. Use the connection example:

   const mysql = require('mysql2');
   
   const config = {
     host: '<host>',
     port: <port>,
     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 or public IP address (Floating IP) of the node;
   • <port> — port for connection;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password.

Connect via phpMyAdmin

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

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

Specify:

• <host> — DNS address or public IP address (Floating IP) of the node;
• <port> — port for connection.