Connect to a MySQL semi-sync cluster

You can connect to a MySQL semi-sync cluster:

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

For all methods, you can connect with SSL and without SSL.

When connecting, specify the port and address.

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

Connection ports

Use port 3306 to connect to the cluster.

Connection addresses

You can select a connection address based on one of the following scenarios:

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

You can view the connection address in the Control Panel.

Connecting to a cluster in a public subnet

If the cluster is in a public subnet, you can connect to the nodes using a DNS address or an IP address from the public subnet.

We recommend connecting via a DNS address. The cluster uses the master discovery mechanism for DNS addresses: the address is linked to the node role, not to the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

When connecting via 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 via the old IP address will stop working.

Connecting from a private subnet to a cluster in a private subnet

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

We recommend connecting via a DNS address. The cluster uses the master discovery mechanism for DNS addresses: the address is linked to the node role, not to the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

When connecting via a 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 via the old IP address will stop working.

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

Connecting from the Internet to a cluster in a private subnet

If you are connecting to a cluster on a private subnet from the Internet, you can only use a public IP address (Floating IP). The private subnet must meet the requirements. If the subnet does not meet the requirements, prepare it for a public IP address (Floating IP).

The master discovery mechanism is used for public IP addresses (Floating IPs): the address is linked to the node role, not to the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

View the connection address

1. In the Control Panel, click Products in the top menu and select Managed Databases.
2. Open the Active tab.
3. Open the database cluster page → Connection tab.
4. In the Connection addresses block, view 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. 50 connections are available per 1 GB of RAM. For example, for a cluster with 4 GB of RAM, 200 connections are available; for 6 GB of RAM, 300 connections are available.

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

Connect via SSL

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

Bash

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

   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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_user_name> — the database user name; ;
   • <database_name> — the database name. .

PowerShell

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

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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_user_name> — the database user name; ;
   • <database_name> — the database name. .

Python

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

   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 following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <database_user_name> — the database user name; ;
   • <password> — the user password; ;
   • <database_name> — the database name; ;
   • <path> — the full path to the root certificate; ;
   • <port> — connection port.

PHP

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

   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 following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <database_user_name> — the database user name; ;
   • <password> — the user password; ;
   • <database_name> — the database name; ;
   • <port> — connection port.

Go

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

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

2. Use the following 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> — connection port;;
   • <database_user_name> — the database user name; ;
   • <password> — the user password; ;
   • <host> — the DNS address or public IP address (Floating IP) of the node; ;
   • <database_name> — the database name.

Node.js

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

   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 following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_name> — the database name; ;
   • <database_user_name> — the database user name; ;
   • <password> — the 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_user_name> — the database user name; ;
   • <database_name> — the 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_user_name> — the database user name; ;
   • <database_name> — the database name.

Python

1. Install the PyMySQL library:

   pip3 install PyMySQL

2. Use the following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <database_user_name> — the database user name; ;
   • <password> — the user password; ;
   • <database_name> — the database name; ;
   • <port> — connection port.

PHP

1. Install the mysqli library:

   apt install php-mysqli

2. Use the following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_user_name> — the database user name; ;
   • <password> — the user password; ;
   • <database_name> — the database name.

Go

Use the following 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> — connection port;;
• <database_user_name> — the database user name; ;
• <password> — the user password; ;
• <host> — the DNS address or public IP address (Floating IP) of the node; ;
• <database_name> — the database name.

Node.js

1. Install the mysql2 library:

   npm install mysql2

2. Use the following 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> — the DNS address or public IP address (Floating IP) of the node; ;
   • <port> — connection port;;
   • <database_name> — the database name; ;
   • <database_user_name> — the database user name; ;
   • <password> — the user password.

Connect via phpMyAdmin

Add the following 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'] = '<port>';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Specify:

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