Connect to MySQL sync cluster

Last update: April 01 2024

Connect to MySQL sync cluster

You can connect to the MySQL sync cloud database cluster by DNS address, private IP address, and public IP address.

We recommend connecting by DNS address because the DNS addresses correspond to the node roles and lead to the actual IP addresses of the master and replicas. IP addresses correspond to specific nodes. If the master is unavailable, one of the replicas will assume its role, the master's IP address will change, and the IP connection will stop working.

If the cluster is connected to a private subnet and you want to work with it via DNS, connect cloud router-to-external-network to the cluster subnet.

If the cluster is connected to a private network and you need to configure access to the node from the Internet, connect public IP address.

Ports⁠

Use port 6033 to connect to ProxySQL.

ProxySQL automatically distributes queries among the nodes in the cluster.

Ways to connect⁠

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

Connection with SSL certificate is available for all methods.

View the address to connect⁠

1. In Control Panel, go to Cloud Platform → Databases.
2. Open the Database Cluster page → Connect tab.
3. In the Addresses to connect block, look up the address.

Number of connections⁠

The number of connections for the MySQL sync cluster is determined by the amount of RAM for the database cluster nodes. For every 1 GB of RAM, 50 connections are available. For example, for a cluster with 4 GB RAM, 200 connections are available; for a cluster with 6 GB RAM, 300 connections are available.

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=6033 \
     --user=<database_user_name> \
     --password \
     --database=<database_name> \
     --ssl-ca=~/.mysql/root.crt \
     --ssl-mode=verify_ca

   Specify:

   • <host> — DNS address of the node;
   • <database_user_name> is the database username;
   • <database_name> is the name of the database.

PowerShell

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

2. Connect to the cluster:

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

   Specify:

   • <host> — DNS address of the node;
   • <database_user_name> is the database username;
   • <database_name> is the name of the database.

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='<full_path_to_root_certificate>',
       ssl_verify_cert=True,
       port=6033,
       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 of the node;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <database_name> is the name of the database;
   • <full_path_to_root_certificate> is the full path to the root certificate.

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

   • <host> — DNS address of the node;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <database_name> is the name of the database;
   • <full_path_to_root_certificate> is the full path to the root certificate.

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("<full_path_to_root_certificate>")
       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:6033)/%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:

   • <full_path_to_root_certificate> is the full path to the root certificate;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <host> — DNS address of the node;
   • <database_name> is the name of the database.

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: 6033,
     database: '<database_name>',
     user: '<database_user_name>',
     password: '<password>',
     ssl: {
       rejectUnauthorized: true,
       ca: fs.readFileSync('<full_path_to_root_certificate>').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 of the node;
   • <database_name> is the name of the database;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <full_path_to_root_certificate> is the full path to the root certificate.

Connect without SSL⁠

Bash

1. Open the CLI.

2. Connect to the cluster:

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

   Specify:

   • <host> — DNS address of the node;
   • <database_user_name> is the database username;
   • <database_name> is the name of the database.

PowerShell

1. Open the CLI.

2. Connect to the cluster:

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

   Specify:

   • <host> — DNS address of the node;
   • <database_user_name> is the database username;
   • <database_name> is the name of the database.

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=6033,
       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 of the node;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <database_name> is the name of the database.

PHP

1. Install the mysqli library:

   apt install php-mysqli

2. Use the connection example:

   <?php
   $conn = new mysqli("<host>:6033", "<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 of the node;
   • <database_user_name> is the database username;
   • <password> is the user's password;
   • <database_name> is the name of the database.

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

• <database_user_name> is the database username;
• <password> is the user's password;
• <host> — DNS address of the node;
• <database_name> is the name of the database.

Node.js

1. Install the mysql2 library:

   npm install mysql2

2. Use the connection example:

   const mysql = require('mysql2');
   
   const config = {
       host: '<host>',
       port: 6033,
       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 of the node;
   • <database_name> is the name of the database;
   • <database_user_name> is the database username;
   • <password> is the user's password.

Connect via phpMyAdmin⁠

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

Specify <host> — the DNS address of the node.