Sign inSign up

johanmarx/table-archiver

By johanmarx

•Updated 2 months ago

Azure MySQL Tabler Archiver

Image
0

1.2K

johanmarx/table-archiver repository overview

⁠table-archiver

https://hub.docker.com/r/johanmarx/table-archiver⁠

An image used to archive data from a working source table to an archive destination table

Alternate name?

  • The Stormlight Archiver

⁠How it works

Bash script located at: /scripts/archive.sh

Run it manually or with Kubernetes CronJob

  1. Uses mysqldump⁠ to get the table structure of the source table
  2. Uses sed⁠ to the replace table name with a current date⁠ modified name and add "IF NOT EXISTS" to enable repeated runs
  3. Uses sed⁠ to the remove FOREIGN KEY CONSTRAINTs
  4. Create the new table in the destination using the mysql cli⁠
  5. Use pt-archiver⁠ from the Percona Toolkit to archive the data

⁠Environment Variables

Passed to the script for configuration.

VARDESCRIPTION
Source Setup
SRC_HOSTHost for source
SRC_DBDatabase for source
SRC_USERUser for source
SRC_PASSWORDPassword for source
SRC_SSL<Default: "FALSE"> To use Azure CA certificate⁠ and SSL for source connection, set to string "TRUE"
SRC_TABLETable for source, must exist
SRC_WHERE<Default: 1=1> Where clause for source table, defaults to all rows. Eg. Id < 420
Destination Setup
DEST_HOSTHost for destination
DEST_DBDatabase for destination
DEST_USERUser for destination
DEST_PASSWORDPassword for destination
DEST_SSL<Default: "FALSE"> To use Azure CA certificate⁠ and SSL for destination connection, set to string "TRUE"
Date Setup
DATE_FORMAT<Default: %Y_%m> Used to create the destination table from the source table. Eg. a value of %Y_%m will prefix the current year and month. auditlog will become 2024_07_auditlog date formats⁠
DATE_MODIFIER<Default: unset> Optional argument passed to date -d when creating the destination table name. Example: 7 days ago will produce a table name based on the date from one week ago. If unset, the current date is used.
Other
BATCH_SIZE<Default: 1000> Used to limit the number of rows archived from a table in a single batch

⁠Example K8s CronJob

apiVersion: batch/v1
kind: CronJob
metadata:
    name: cronjob-archiver-database-table
spec:
    schedule: "0 * * * *" # Run every hour
    suspend: false
    jobTemplate:
        spec:
            suspend: false
            activeDeadlineSeconds: 3300 # Limit run to 55 minutes
            template:
                spec:
                    containers:
                        - name: archiver-database-table-container
                          image: archiver
                          imagePullPolicy: IfNotPresent
                          command:
                              - bash
                              - /scripts/archive.sh
                          envFrom:
                              - configMapRef:
                                    name: archiver-database-table-configmap
                    restartPolicy: Never

⁠Links

Tag summary

Content type

Image

Digest

sha256:f37e85a7f…

Size

129.7 MB

Last updated

almost 2 years ago

docker pull johanmarx/table-archiver