# merge-sh-dbs

**Repository Path**: cncf/merge-sh-dbs

## Basic Information

- **Project Name**: merge-sh-dbs
- **Description**: 🔀🗄🎩Tool to merge Sorting Hat databases
- **Primary Language**: Unknown
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-07-25
- **Last Updated**: 2025-09-23

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# merge-sh-dbs

Tool to merge Sorting Hat databases


# Get database dumps

You need to have `cncf/json2hat-helm` cloned and its access secrets configured. You can find database details for both `development` and `staging` environments in AWS console.

Copy secretes from `json2hat-helm` to `secrets` directory, example (depending on `json2hat-helm` path): `cp ../json2hat-helm/json2hat-helm/secrets/*.secret secrets/`.

Development environment:

- Shell into MariaDB pod: `` devk.sh run -it --rm --image=mariadb --restart=Never mariadb --env="SH_HOST=`cat secrets/SH_HOST.dev.secret`" --env="SH_USER=`cat secrets/SH_USER.secret`" --env="SH_PASS=`cat secrets/SH_PASS.dev.secret`" --env="SH_DB=`cat secrets/SH_DB.secret`" -- /bin/bash ``.
- Dump database into file: `mysqldump --single-transaction -h$SH_HOST -u$SH_USER -p$SH_PASS $SH_DB > dump.sql`.
- Dump database structure into file: `mysqldump --single-transaction -d -h$SH_HOST -u$SH_USER -p$SH_PASS $SH_DB > struct.sql`.
- Using another terminal copy dump from the K8s pod: `devk.sh cp mariadb:dump.sql dump_dev.sql`.
- Using another terminal copy structure dump from the K8s pod: `devk.sh cp mariadb:struct.sql dump_struct.sql`.
- Logout from the mariadb pod shell.


Staging environment:

- Shell into MariaDB pod: `` stgk.sh run -it --rm --image=mariadb --restart=Never mariadb --env="SH_HOST=`cat secrets/SH_HOST.stg.secret`" --env="SH_USER=`cat secrets/SH_USER.secret`" --env="SH_PASS=`cat secrets/SH_PASS.stg.secret`" --env="SH_DB=`cat secrets/SH_DB.secret`" -- /bin/bash ``.
- Dump database into file: `mysqldump --single-transaction -h$SH_HOST -u$SH_USER -p$SH_PASS $SH_DB > dump.sql`.
- Using another terminal copy dump from the K8s pod: `stgk.sh cp mariadb:dump.sql dump_staging.sql`.
- Logout from the mariadb pod shell.


# Restore dumps

Restore dev and staging dumps, for example locally:

- `mysql`: `create database dev`, `create database staging`, `mysql dev < dump_dev.sql`, `mysql staging < dump_staging.sql`, `create database merged`, `mysql merged < dump_struct.sql`.


# Merge databases

There are 3 prefixes: `SH1_`, `SH2_` and `SH_`. `SH1_` is used for the first input DB, `SH2_` is used for the second input DB, `SH_` is used for the output DB. program will merge two input databases into output database.

Database with `SH1_` has a higher priority than `SH2_` when resolving conflicts (but only when we cannot solve conflict using newer record).

Setting Sorting Hat database parameters: you can either provide full database connect string/dsn via `SH_DSN=...` or provide all or some paramaters individually, via `SH_*` environment variables. `SH_DSN=..` has a higher priority and no `SH_*` parameters are used if `SH_DSN` is provided. When using `SH_*` parameters, only `SH_PASS` is required, all other parameters have default values.

Sorting Hat database connection parameters (example with prefix `SH_`, you can replace with `SH1_` or `SH2_`):

- `SH_DSN` - provides full database connect string, for example: `SH_DSN='shuser:shpassword@tcp(shhost:shport)/shdb?charset=utf8'`
- `SH_USER` - user name, defaults to `shuser`.
- `SH_PASS` - password - required.
- `SH_PROTO` - protocol, defaults to `tcp`.
- `SH_HOST` - host, defaults to `localhost`.
- `SH_PORT` - port, defaults to `3306`.
- `SH_DB` - database name, defaults to `shdb`.
- `SH_PARAMS` - additional parameters that can be specified via `?param1=value1&param2=value2&...&paramN=valueN`, defaults to `?charset=utf8`. You can use `SH_PARAMS='-'` to specify empty params.


# Running merge

Many possible connect strings:

- You can prepend with `DEBUG=1` to have more verbose output.
- Using TCP: `SH1_USER=root SH2_USER=root SH_USER=root SH1_PASS=... SH2_PASS=... SH_PASS=... SH1_DB=dev SH2_DB=staging SH_DB=merged ./merge-sh-dbs`.
- Using unix sockets without passwords (fastest local option): `SH1_DSN='root@unix(/var/run/mysqld/mysqld.sock)/dev?charset=utf8&parseTime=true' SH2_DSN='root@unix(/var/run/mysqld/mysqld.sock)/staging?charset=utf8&parseTime=true' SH_DSN='root@unix(/var/run/mysqld/mysqld.sock)/merged?charset=utf8&parseTime=true' ./merge-sh-dbs`.

# Dump merged database

Dump merged database into a SQL file: `mysqldump --single-transaction merged > merged.sql`.