Connect to a MySQL sync cluster

You can connect to a MySQL 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 or without SSL.

When connecting, specify the port and address.

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

Connection ports

Use port 6033 to connect to ProxySQL.

ProxySQL automatically distributes requests between cluster nodes.

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 bound to the node role rather than 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's IP address will change, and the connection using 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 bound to the node role rather than 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'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.

Connecting from the Internet to a cluster in a private subnet

If you connect to a cluster in a private subnet from the internet, you can only use a public floating IP address (Floating IP).

For public floating IP addresses (Floating IPs), the master discovery mechanism is used — the address is bound to the node's 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, in the top menu, click Products and select Cloud Databases.
2. Open the Active tab.
3. Go to the database cluster page → Connection tab.
4. In the Connection addresses block, check the address.

Number of connections

The number of connections for a MySQL 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, and for 6 GB of RAM, 300 connections.

To increase the number of connections, scale the 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/ 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 floating IP address of the node;
   • <port> — connection port;
   • <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 floating IP address of the node;
   • <port> — connection port;
   • <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 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> — DNS address or public floating IP address of the node;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name;
   • <path> — full path to the root certificate;
   • <port> — connection port.

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 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(
           "master.77c6916d-8f64-45d0-81fb-a5343bb2ff1c.c.dbaas.selcloud.ru",
           "<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> — full path to the root certificate;
   • <host> — DNS address or public floating IP address of the node;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <database_name> — database name;
   • <port> — connection port.

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