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. A master discovery mechanism is used for DNS addresses in the cluster—the address is linked to the node role, not 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. A master discovery mechanism is used for DNS addresses in the cluster—the address is linked to the node role, not 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 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) connection.

For public IP addresses (Floating IPs), a master discovery mechanism is used—the address is linked to the node role, not 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 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> — the DNS address or public IP address (Floating IP) 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> — the DNS address or public IP address (Floating IP) 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> — the 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> — 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> — the 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> — 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> — the 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 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> — 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> — the DNS address or public IP address (Floating IP) 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> — the DNS address or public IP address (Floating IP) 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> — the 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> — 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> — 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> — the 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 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> — 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> — the DNS address or public IP address (Floating IP) of the node;
• <port> — connection port.