DB1-small usage and setup¶

DB1-small is a VM living in Devops GCP project. Like other VMs placed in Devops project it is created using Terraform in the devops-infra repository.

DB1-small is working replica of the the main SkySQL production cluster sky-db1-new. The purpose of the VM is to be a fully functional replica in the case of some unforeseen catastrophy, which would make SkySQL instances / services unavailable.

The machine is also running regular backups of the database, whoch are also stored encrypted locally in the office.

Last purpose is that it runs a custom SQL Exporter that reports on user connection counts in SkySQL. The data is available in Grafana dashboard.

Table of contents¶

[[TOC]]

Setup replication from SkySQL¶

Overview¶

Setting up replication entails configuring the replica, dumping and importing database from sky-db1-new, capturing the correct GTID, setting up a replication user in sky-db1-new and re-syncing the replica with the main.

Step by step¶

For a fast dump and import you can use the existing script in /root/scripts/rapid_slave.sh:

# /root/scripts/rapid_slave.sh
Use /data/temp/skydb1_full_latest as latest dump ? [y/N]n
02/02/26 20:24: Removing skydb1_full_latest
02/02/26 20:24: Dumping SkyDB1 to /data/temp/skydb1_full_latest ..

real    117m21.422s
user    24m46.267s
sys 6m56.324s
02/02/26 22:21: Dropping replica on localhost..
02/02/26 22:21: Starting import of SkyDB1 dump to localhost

real    687m39.480s
user    33m18.545s
sys 2m38.062s
02/03/26 09:49: Restarting MariaDB on localhost..
02/03/26 09:49: Use these details to start a slave:
-- The deferred gtid setting for slave corresponding to the master-data CHANGE-MASTER follows
-- SET GLOBAL gtid_slave_pos='225800-225800-40357452289,533000-533000-5192499221';
For more details see: https://docs.skysql.com/Data%20offloading/Replicating%20data%20from%20SkySQL%20to%20external%20database/
Done.

After the script finishes (roughly in around 13-14 hours, 2026), you can follow SkySQL documentation. A condensed step-by-step follows:

Obtain and set GTID position in replica

Login into the replica:

mysql -u root -p

Set a global variable called gtid_slave_pos using the GTID from the rapid_slave.sh script:

SET GLOBAL gtid_slave_pos='225800-225800-40357452289,533000-533000-5192499221';

Dont close the session and stay logged in.

Prepare a user for replication in sky-db1-new

Login into sky-db1-new and check if such user exists:

MariaDB [(none)]> select user, host from mysql.user where user like 'db1_rep%';
+-----------------+---------------+
| User            | Host          |
+-----------------+---------------+
| db1_replication | 35.242.234.91 |
+-----------------+---------------+
1 rows in set (0.081 sec)

If not, create the user like this:

CREATE USER 'db1_replication'@'35.242.234.91' IDENTIFIED BY 'SOME_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO ‘db1_replication’@'35.242.234.91';

Start the replication

Get back to your session onthe replica. Run the following command:

CHANGE MASTER TO
   MASTER_HOST='SKY-DB1-NEW-FQDN',
   MASTER_PORT=3307,
   MASTER_USER='db1_replication',
   MASTER_PASSWORD='SOME_PASSWORD',
   MASTER_SSL=1,
   MASTER_USE_GTID=slave_pos;

Replace SKY-DB1-NEW-FQDN with sky-db1-new secondary endpoint, and SOME_PASSWORD with the correct password for db1_replication user.

Start the replication:

MariaDB [(none)]> start replica;
Query OK, 0 rows affected (0.006 sec)

Verify the replication is working

MariaDB [(none)]> show replica status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: lsvdqnfvlkzpyciy.sysp0000.db2.skysql.com
                   Master_User: db1_replication
                   Master_Port: 3307
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.035650
           Read_Master_Log_Pos: 375261011
                Relay_Log_File: mysqld-relay-bin.000554
                 Relay_Log_Pos: 166166199
         Relay_Master_Log_File: mariadb-bin.035650
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 375261011
               Relay_Log_Space: 642141708
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 141354
 Master_SSL_Verify_Server_Cert: Yes
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 533001
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: Slave_Pos
                   Gtid_IO_Pos: 225800-225800-40357452289,533000-533000-5211378476
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 9
Slave_Non_Transactional_Groups: 5
    Slave_Transactional_Groups: 18911303
          Replicate_Rewrite_DB:
1 row in set (0.000 sec)

Check for errors and especially the value of Seconds_Behind_Master. The number must go down :)

DB1-small usage and setup¶

Table of contents¶

Setup replication from SkySQL¶

Overview¶

Step by step¶

Setup monitoring scripts¶

Setup backups¶

Setup custom mysql exporter¶