Notes on SkySQL usage and migration¶
Notes cover: - mainly testing DB1 replication to SkySQL test cluster - how to connect in Windows using Heidi / DBeaver
Table of contents¶
[[TOC]]
How to connect to SkySQL¶
There are several options for connecting to SkySQL instances: - using Private Service Connect - programatically - using CLI or GUI tools
If using a direct access (not via PSC) please note the instance has a IP whitelist, that can be managed via TF.
Using Private Service Connect¶
One option is to use Google's Private Service Connect endpoint, that is available in a given zone (currently testing a PSC endpoint in europe-west3). The PSC endpoints are available for VM's in devops or legacy Google projects.
To create a new endpoint, follow SkySQL docs [3]
Using programming languages¶
This is an example of connecting via PHP:
<?php
$host = 'dbpgf36222258.sysp0000.db2.skysql.com';
$port = 3306;
$db = 'ziskejucet';
$user = '';
$pass = '';
$options = array(
PDO::MYSQL_ATTR_SSL_CAPATH = '/etc/ssl/certs', // this should be actually set-up as default in FPM pool
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_PERSISTENT => false
);
try {
$dsn = "mysql:host={$host};dbname={$db};port={$port}";
$conn = new PDO($dsn, $user, $pass, $options);
echo "Connected successfully";
var_dump($conn->query("SHOW STATUS LIKE 'Ssl_cipher';")->fetchAll());
var_dump($conn->query("SHOW STATUS LIKE 'Ssl_version';")->fetchAll());
$conn = null;
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
NOTE: If using PSC to connect, don't verify the server certificate as otherwise the connection would fail
Using CLI / GUI tools¶
Example of connecting from the command line:
mariadb --host iadutlqeauxsasch.sysp0000.db2.skysql.com --port 3306 --user SOME_USER -p --ssl-verify-server-cert
DBeaver note: (25/102024)
Since devs do not get SHOW DATABASES privilege in SkySQL, DBeaver has a problem connecting. When opening a connection to a server, it tries to see all other databases, ignoring the setup, where its instructed to just connect to one particular DB. Thus the connection fails. This is documented in the issue: https://github.com/dbeaver/dbeaver/issues/23231. See the link for a workaround.
Setting up a DB1 replica in SkySQL¶
Note, 07.10.2024
These notes are preliminary notes and will be modified once we create a real production-ready replica on DB1 on SkySQL. So far this replica is running on SkySQL instance called ftmo-db1-test-replica and is a beta version.
Prerequisities¶
- A running SkySQl db1-replica instance (via TF in Projects)
- !!IMPORTANT!! In SkySQL dashboard applied
db1_replica_settings_rwconfiguration to db1-replica instance. - !!IMPORTANT!! db1-replica instance needs to have automatic disk scaling enabled. ATM not possible to do via Terraform and needs to be done by hand.
- SSH access to DB2 and DB1 servers
Prepare replication¶
- SSH to DB2, start / enter a screen (very important) and
sudo suto root cd /root/scripts- edit the SkySQL credentials in
skysql_db1_test_replica.cnf. Credentials can be found in SkySQL dashboard. So far use the default user. - run
./skysql_slave.shand follow the prompts - this will:
-
- Ask if you want to drop
ziskejuceton the replica. Not needed if this is a fresh replica.
- Ask if you want to drop
-
- Use
pt-show-grantsto export users from DB1 and import users to the replica
- Use
-
- Ask to disable the plugin
simple_password_checkon the replica. Do this just one time per replica.
- Ask to disable the plugin
-
-
- Ask if you want to use GCP bucket method for dump & import (its 10% faster)
-
- Ask to create a new database dump on DB1 or use a local copy of the dump. This is an option used when something fails and you want to import na existing dump. If you create a new replica after some time, dont use the local dump.
- 5.0 The script will output a mysqldump command to be run on DB1. SSH to DB1 and enter a screen
- 5.1 Run the command. As password use the backup_usr pwd in the
/root/script/sql_backup.shon DB1:echo "SET FOREIGN_KEY_CHECKS = 0;" > /data/temp/latest_dump.sql; time mysqldump -u backup_usr -p --all-databases --ignore-database=mysql --routines --triggers --skip-lock-tables --single-transaction --quick --hex-blob --master-data=2 >> /data/temp/latest_dump.sql; echo "SET FOREIGN_KEY_CHECKS = 1;" >> /data/temp/latest_dump.sql(this is just an example, please follow the prompt, as the prompt might have a updated version of this command) - 5.2 (optional) While the dump runs, change SSH settings to allow root@db2 to SSH to root@db1. This entails: adding user
rootto AllowUsers list on DB1 and also enabling root login. Both settings are in/etc/ssh/sshd_configfile. Restart sshd after modification - 5.3 The
./skysql_slave.shscript will wait until you press Enter that the DB1 dump is ready. Wait for DB1 dump to finish (~30m) - 5.4 (optional) Once the DB1 dump is done, press Enter. The script will transfer the dump from DB1 to DB2 (another ~30m)
- 5.5 (optional) While the transfer is running, change the SSH settings back and restart sshd. Restart of sshd will not close the existing connections.
-
- The script will import DB1 dump to SkySQL instance. This takes around ~24H so take a rest.
-
- After import the script will try to import existing grants using
pt-show-grants. This will output a lot of errors because of dangling GRANTS on DB1. Some grants are not changed when we delete a table. Historical GRANTS mention tables that are non-existent and SkySQL will complain - however we are using the--forceparameter to continue with import despite errors.
- After import the script will try to import existing grants using
- Once the import is done, you can proceed to the next section - Starting replication.
Starting replication¶
In this section we will follow the steps outlined in [2]. Please check the docs for more context.
Once the import of DB1 is finished, check the console output to get the logfile name and position and connect as admin to SkySQL instance. The output will look like this:
Importing latest dump..
real 1790m36.908s
user 38m20.827s
sys 4m11.211s
Dumping current DB1 users ..
Importing DB1 user GRANTS to SkySQL ..
(some errors here)
real 0m0.072s
user 0m0.021s
sys 0m0.017s
Deleting DB1 user dump ..
Use these details to start a slave:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-mixed-log.029770', MASTER_LOG_POS=1042412733;
For more details see: https://gitlab.fftrader.cz/devops/docs/-/blob/main/skysql-notes.md
Done.
- From the above we see that
MASTER_LOG_FILE='mysql-mixed-log.029770'andMASTER_LOG_POS=1042412733 - According to [2],connect to the replica and setup the logpos and logname with this command:
-
The command will return a GRANT statement that needs to be executed on DB1:
CALL sky.change_external_primary('db1.ftmo.com', 3306, 'mysql-mixed-log.029770', 1042412733, false); +----------------------------------------------------------------------------------------------------------------------------+ | Run_this_grant_on_your_external_primary | +----------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'skysql_replication_dbpgf03272882'@'%' IDENTIFIED BY '******'; | +----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.091 sec) Query OK, 5 rows affected (0.091 sec) -
The command needs to be adjusted to the IP of the SkySQL instance. Usually the IP is
34.159.133.209. Adjust the command accordingly: - Before running the command, delete the former "skysql_replication" users (if any):
select user, host from mysql.user where user like 'skysql%';
+----------------------------------+----------------+
| User | Host |
+----------------------------------+----------------+
| skysql_replication_dbpgf03272882 | 34.159.133.209 |
| skysql_replication_dbpgf03272882 | 35.198.144.151 |
+----------------------------------+----------------+
2 rows in set (0.449 sec)
drop user `skysql_replication_dbpgf03272882`@`34.159.133.209`;
Query OK, 0 rows affected (0.029 sec)
drop user `skysql_replication_dbpgf03272882`@`35.198.144.151`;
Query OK, 0 rows affected (0.004 sec)
flush privileges;
Query OK, 0 rows affected (0.022 sec)
-
Run the GRANT command:
-
After applying the grant statement you are ready to start replication:
-
If you will see a message like this:
-
Check the replication status and search for a error message like this:
-
If the SKYSQL_IP_ADDRESS is different than
34.159.133.209, you will need to delete the user again from DB1 and create the final user with the correct IP . -
Afterwards start replication again using
CALL sky.start_replication(). It might happen that replication initially decalres an error like this:MariaDB [(none)]> CALL sky.start_replication(); +---------------------------------------------------------------------------------------------------+ | Message | +---------------------------------------------------------------------------------------------------+ | External replication has encountered an error. CALL sky.replication_status()\G; to see the error. | +---------------------------------------------------------------------------------------------------+ 1 row in set (2.005 sec) Query OK, 3 rows affected (2.005 sec) - This however doesnt have to mean all is bad. Check the replication status with
CALL sky.replication_status()\G. The output should look like this:
MariaDB [(none)]> CALL sky.replication_status()\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db1.ftmo.com
Master_User: skysql_replication_dbpgf03272882
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-mixed-log.030038
Read_Master_Log_Pos: 335101137
Relay_Log_File: mariadb-relay-bin-ext_repl.000562
Relay_Log_Pos: 881156709
Relay_Master_Log_File: mysql-mixed-log.029957
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: 881156404
Relay_Log_Space: 88389608420
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 94165
.. and so on...
```
In the output you can see that
1. `Slave_IO_Running` and `Slave_SQL_Running` are OK
2. `Seconds_Behind_Master` is decreasing, after you run the status command several times.
The `Seconds_Behind_Master` can be initially up to 200000 seconds behind so again, take a rest, the synchronization usually takes ~2 days to finish. Check it from time to time.
## Post setup
### Configure correct access for scripts
Correct passwords and instance names need to be set on `skysql-sanitizer-vm`.
1. Change `SRC_SERVICE_ID` and `DST_SERVICE_ID` in [`skysql_api.conf`](https://gitlab.fftrader.cz/devops/database-sanitization/-/blob/main/config/skysql_api.orig).
- `SRC_SERVICE_ID` is the ID of the replica
- `DST_SERVICE_ID` is the ID of the `ftmo-beta-data-new` instance.
2. Change access data in `/root/scripts/database-sanitization/config/ziskejucet_skysql_beta_new.cnf`. This config file is used by the `skysql_beta_ziskejucet.sh` script. Please note that the password of `'sanitizer'@'34.141.78.68'` can be reset on DB1. Once you reset it there, the changes will propagate to the replica. Here it is enough to just change the host, since the user and pass remain the same.
```bash
[client]
user="sanitizer"
password=""
host=""
ssl-verify-server-cert
-
Change host in
/root/scripts/database-sanitization/config/ziskejucet_new.ini. This file is read by the python sanitization script sanitize_database.py. -
In case you changed the DB1 pass of
'sanitizer'@'34.141.78.68', change it also in/root/scripts/database-sanitization/config/ziskejucet_prod.cnf.
Correct grants¶
The script doesnt transfer all grants 100% to the replica, for various issues. Important for the sanitization process is that 'sanitizer'@'34.141.78.68' has these grants:
+---------------------------------------------------------------------------------------------------------------------+
| Grants for sanitizer@34.141.78.68 |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sanitizer`@`34.141.78.68` IDENTIFIED BY PASSWORD 'XXX' |
| GRANT SELECT, UPDATE, DROP, LOCK TABLES, SHOW VIEW, TRIGGER ON `ziskejucet`.* TO `sanitizer`@`34.141.78.68` |
| GRANT SHOW VIEW ON `ziskejucet`.`funded_trader_contracts_view` TO `sanitizer`@`34.141.78.68` |
| GRANT SELECT ON `mysql`.`proc` TO `sanitizer`@`34.141.78.68` |
| GRANT SHOW VIEW ON `ziskejucet`.`clients_blacklist` TO `sanitizer`@`34.141.78.68` |
+---------------------------------------------------------------------------------------------------------------------+
Especially this line is important:
| GRANT SELECT, UPDATE, DROP, LOCK TABLES, SHOW VIEW, TRIGGER ON `ziskejucet`.* TO `sanitizer`@`34.141.78.68` |
Deactivate default backup schedule¶
Since we want to trigger out own backups + restores, we dont need the default backup schedule of the replica. It has to be deactivated. Start by exploring backup schedules of the new instance via the API. For this you will need a API_KEY env variable with your SkySQL API key:
curl -s --location "https://api.skysql.com/skybackup/v1/backups/schedules?service_id=dbpgf03272882" --header 'Accept: application/json' --header "X-API-Key: $API_KEY"|jq
This gives you roughly such output:
{
"schedules": [
{
"id": 558,
"service_id": "dbpgf03272882",
"service_name": "ftmo-db1-test-replica",
"cloud_provider": "gcp",
"schedule": "0 0 * * *",
"type": "full",
"status": "Succeeded",
"created_at": "2024-08-26T20:56:12Z",
"updated_at": "2024-09-05T14:42:57Z",
"scheduled_at": "2024-09-03T00:00:00Z",
"message": "Backup has succeeded!",
"labels": {
"skysql/managed-by": true
}
}
],
"schedules_count": 1,
"pages_count": 1
}
We see that the default schedule has ID 558. Now we can try deleting this schedule:
curl -s --request DELETE --location "https://api.skysql.com/skybackup/v1/backups/schedules/558" --header 'Accept: application/json' --header "X-API-Key: $API_KEY"|jq
{
"errors": [
{
"error": "BACKUP_SCHEDULE_DELETE_NOT_ALLOWED",
"message": "cannot delete default backup schedule"
}
],
"exception": "Exception",
"path": "/",
"code": 400,
"timestamp": 1725547664449,
"trace_id": "trace-id"
}
Oh well. Since it can't be deleted, lets give it some weird time that never occurs. Like the 30th of February:
curl --location --request PATCH 'https://api.skysql.com/skybackup/v1/backups/schedules/558' --header 'Content-Type: application/json' --header 'Accept: application/json' --header "X-API-Key: $API_KEY" --data '{ "schedule": "0 10 30 2 *" }'
Thus, we deactivated the default backup schedule and can schedule our own cronjob that does the complete backup -> restore -> sanitize pipeline.
Setting up a GCP bucket to dump and load DB1 to SkySQL¶
This is a experimental feature and is only being tested for feasibility.
Setting up bucket and service account¶
- create a bucket called
db1-to-skysql-dump-and-restore-test - create a service account
db1-to-skysql-dump-and-restore-test-saand give it no permissions - add that service account as principal with the role
Storage Legacy Bucket OwnerandStorage Legacy Object Ownerto the bucket - create a json key for the service account and save it on DB1 to the file
/root/scripts/google-service-account-db1-to-skysql-dump.json - run these commands on DB1:
- verify that you can list the bucket:
gsutil ls gs://db1-to-skysql-dump-and-restore-test
Dumping data straight to the bucket¶
Dump DB1 like this:
mysqldump -u backup_usr -p --single-transaction --skip-lock-tables --skip-set-charset --routines --triggers --databases ziskejucet --ignore-table=ziskejucet.backup_* --ignore-table=ziskejucet.communication --ignore-table=ziskejucet.smazat_* --ignore-table=ziskejucet.tags_latest --ignore-table=ziskejucet.trading_accounts_trades | gzip | gsutil cp – gs://db1-to-skysql-dump-and-restore-test/db1_0709_2024.gz
For precise arguments check either the sanitization scripts or the skysql_slave.sh script.
Loading data into SkySQL¶
- encode the
google-service-account-db1-to-skysql-dump.jsoninto base64 without line breaks - identify the service id into which you want to restore
- use the name of the dump file created in the bucket
- get your SkySQL API key and put it into API_KEY env variable
- then do this API call:
curl --location 'https://api.skysql.com/skybackup/v1/restores' --header 'Content-Type: application/json' --header 'Accept: application/json' --header "X-API-Key: $API_KEY" --data '{ "service_id": "dbpgf12557367", "id": "db1_0709_2024.gz", "external_source": { "bucket": "gs://db1-to-skysql-dump-and-restore-test/", "method": "mysqldump", "credentials": "base64-encoded-credentials" } }'
TODO¶
- ~use PSC when importing data to the replica~
- ~use innodb_flush_log_at_trx_commit = 0 when importing data to the replica~
- ~scale down the replica to just one node when importing~
- ~dump DB1 to a bucket and import from there. See details in https://app.asana.com/0/1171939180356341/1208225482766282~
- drop privileges of sanitizer in DB1 and do sanitization via admin user
Sources¶
[1] https://skysqlinc.github.io/skysql-docs/Data%20loading,%20Migration/Migrating%20your%20existing%20Production%20DB/ [2] https://skysqlinc.github.io/skysql-docs/Data%20loading%2C%20Migration/Replicating%20data%20from%20external%20DB/ [3] https://skysqlinc.github.io/skysql-docs/Using%20AWS%20GCP%20private%20VPC%20connections/Setting%20up%20GCP%20Private%20Service%20Connect/]