About the Author: Shane

By ,

SkySQL offers a robust platform for managing databases in the cloud, but setting up an effective disaster recovery (DR) site requires careful planning and execution. In this guide, we’ll explore how to automate the creation and restoration of SkySQL database services for disaster recovery purposes using the SkySQL API.

We are going to use SkySQL’s REST CloudOps API as shown in the main swagger documentation and the backup swagger documentation.

Understanding the backup service

The SkySQL Backup service offers a comprehensive suite of features designed to safeguard data integrity and facilitate efficient disaster recovery. With automated nightly backups, users benefit from regular full backups of their databases without impacting performance. Additionally, the service supports on-demand or scheduled backups, full and incremental backup options, as well as logical backups for flexible data restoration. Advanced features include Bring Your Own Bucket (BYOB) support for custom storage solutions, binlog backups for replication setup, and point-in-time recovery capabilities. SkySQL ensures data security with role-based access control for backup and restore operations, providing a robust backup solution tailored to users’ needs.

Defining requirements for your DR site

This blog overviews a easily automated hot DR site setup in SkySQL but defining your specific business requirements is a must/ Consider the following to start determining your need.

RPO & RTO: Define data loss and downtime tolerances.
Geographic Location: Evaluate proximity and regional stability.
DR Configuration: Choose between hot, warm, or cold setups based on budget and performance needs.

Setting Up Disaster Recovery Site

Prerequisites

  • SkySQL Account: You need access to SkySQL and an API token.
  • Basic knowledge of SkySQL API usage.
  • Bash Environment: Ensure you have an environment to execute commands via CLI.

Execution Steps

Step 1: Set your token for use in api commands

This session variable will be used for each subsequent command.

JWT_TOKEN=$(cat path/to/your/api-key)

Step 2: Create your new DR service in SkySQL

Here we will create our service (you can also create via portal) with deployment options that can be adjusted for your needs.

curl --request POST '' \\
> --header "Authorization: Bearer $JWT_TOKEN" \\
> --header "Content-Type: application/json" \\
> --data-raw '{
> "topology": "es-single",
> "provider": "aws",
> "region": "us-east-2",
> "name": "dr-site",
> "version": "10.11.6-1-2",
> "architecture": "amd64",
> "nodes": '1',
> "size": "sky-2x4",
> "volume_type": "gp3",
> "storage": 100,
> "ssl_enabled": true
> }' | jq '.'
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1118 100 845 100 273 3176 1026 --:--:-- --:--:-- --:--:-- 4203
{
"id": "dbtwf13631302",
"name": "dr-site",

Step 3: Initiate Restore to DR (backup if needed)

After setting up the new database service, you can initiate a restore operation from an existing backup. If you need a backup first, it’s easy to initiate one.

##using my existing service dbtwf02420137
###take a full backup
skydba@MrLenovo:~/skysql-tools/local-scripts$ curl --request POST '' \\
> --header "Authorization: Bearer $JWT_TOKEN" \\
> --header "Content-Type: application/json" \\
> --data-raw '{
"backup_type": "full",
> "backup_type": "full",
> "schedule": "once",
> "service_id": "dbtwf02420137"
> }' | jq '.'
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 229 100 147 100 82 176 98 --:--:-- --:--:-- --:--:-- 274
{
"id": 368,
"service_id": "dbtwf02420137",
"service_name": "bb33",
"schedule": "once",
"type": "full",
"status": "Scheduled",
"message": "Backup is scheduled."
}

###retrieve information of last backups
skydba@MrLenovo:~/skysql-tools/local-scripts$ curl -s --request GET "" \\
> --header "Authorization:Bearer $JWT_TOKEN" | jq '.'
{
"backups": [
{
"id": "defd8a1e42ec0d189bd9bb50bdba1033:dbtwf02420137:bb33-mdb-ms-1",
"service_id": "dbtwf02420137",
"service_name": "bb33",
"type": "full",
"method": "skybucket",
"server_pod": "bb33-mdb-ms-1",
"backup_size": 5335734,
"binlog_file": "mariadb-bin.000002",
"binlog_position": "3263",
"binlog_gtid_position": "13700-13700-199",
"reference_full_backup": "",
"point_in_time": "2024-04-15 20:57:16",
"start_time": "2024-04-15T20:57:49Z",
"end_time": "2024-04-15T20:57:53Z",
"status": "Succeeded"
}
],
"backups_count": 1,
"pages_count": 1
}

##restore to the new service
skydba@MrLenovo:~/skysql-tools/local-scripts$ curl -s --request POST '' \\
> --header "Authorization: Bearer $JWT_TOKEN" \\
> --header 'accept: application/json' \\
> --header 'Content-Type: application/json' \\
> --data-raw '{
> "key": "defd8a1e42ec0d189bd9bb50bdba1033:dbtwf02420137:bb33-mdb-ms-1",
> "service_id": "dbtwf13631302"
> }' | jq '.'
{
"id": 21,
"service_id": "dbtwf13631302",
"service_name": "dr-site",
"bucket": "s3://skysql-backup-447603875694-us-east-2-5b9a4e52e7d440/org27bgg",
"key": "defd8a1e42ec0d189bd9bb50bdba1033:dbtwf02420137:bb33-mdb-ms-1",
"type": "physical",
"status": "Scheduled",
"message": "Restore is scheduled."
}

Step 4: Setup replication via skysql procs on the DR instance

This information is in the SkySQL documentation pages shown here and our above backup information from step 3 will allow us to set the proper start position for the replication stream. Make sure to create a user as indicated in the SkySQL documentation.

#
## "binlog_file": "mariadb-bin.000002",
## "binlog_position": "3263",

CALL sky.change_external_primary('dbtwf02420137.syst0000.test1.skysql.com', 3306, 'mariadb-bin.000002', 3263, false);
CALL sky.start_replication();
CALL sky.replication_status();

Step 5: Verification

Once the setup and restoration processes are complete, verify the status of the new database service in the SkySQL console. Ensure that the restoration was successful and that the service is replicating for use post failover in case of a disaster.

Conclusion

Automating these steps of provisioning a disaster recovery site for SkySQL empowers organizations to streamline the process and customization it around business needs. By leveraging these scripts, you can efficiently provision resources, restore data, and initiate backup restoration, thereby enhancing resilience and minimizing downtime in critical scenarios.

Incorporating these practices into your disaster recovery strategy to fortify your SkySQL deployments to safeguard your data against potential disruptions. Remember to periodically review and update your DR procedures to adapt to evolving business needs and technology advancements.