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

The connection address depends on the cluster subnet and where you are connecting from. You can choose an address based on one of the 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.

Connecting to a cluster in a public subnet

If a 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 using a DNS address. DNS addresses use the master discovery mechanism in the cluster—the address is tied to the node role rather than the node itself. If a master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

The master discovery mechanism is not used when connecting via an IP address from a public subnet. If one of the replicas becomes the new master, the master's IP address will change, and the connection using the old IP address will stop working.

You can view the connection address in the Dashboard.

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 using a DNS address. DNS addresses use the master discovery mechanism in the cluster—the address is tied to the node role rather than the node itself. If a master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

The master discovery mechanism is not used when connecting via a private IP address. If one of the replicas becomes the new master, the master's IP address will change, and the connection using the old IP address will stop working.

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

You can view the connection address in the Dashboard.

Connecting from the Internet to a cluster in a private subnet

If you are connecting to a cluster in 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).

Public IP addresses (Floating IPs) use the master discovery mechanism—the address is tied to the node role rather than the node itself. If a master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.

You can view the connection address in the Dashboard.

View the connection address

1. In the Dashboard, in the top menu click Products 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.