Sign inSign up

jonhartley/percona-server

By jonhartley

•Updated over 5 years ago

Percona Server 5.7 that allows creation of schemas and content and config variables

Image
0

1.5K

jonhartley/percona-server repository overview

⁠Percona 5.7 Server

⁠Basic Info
  • This is Percona's version of MySQL
  • Includes the ability to start and create content and pass config parameters
  • Start from backup and create master <=> master setup
  • Autotuning config settings for bufferpool or connections
  • Custom SSL config
⁠ENV Variables
  • CONFIG="max_allowed_packet=1G,innodb_buffer_pool_size=500M,max_connections=1000"
  • MYSQL_ROOT_PASSWORD=password -- root password
  • BINLOG="true" -- create a binlog
  • SLAVE="true" -- be a slave of another cluster
  • REPLUSER="replication:password" -- replication credentials
  • MASTER_HOST="mysql1" -- where your master is
  • STARTFROMBACKUP="true" -- restore backup before starting (only if datadir is empty)
  • BACKUPDIR="/var/master/backup" -- location of latest backup
  • SERVERID=1 -- Static server_id for replication (ip is used if this is not set)
  • CPU_LIMIT -- use Downward API to push limits.cpu
  • MEM_LIMIT -- use Downward API to push limits.memory
  • AUTOTUNE="connections|bufferpool" -- tune configuration based on CPU_LIMIT | MEM_LIMIT
  • SSL="client|all" -- both option require ssl-cert,ssl-key and ssl-ca in CONFIG and certs mounted on container
  • RLIMIT="5M" -- limit extraction of backups, default is 5M, done as nor to overwelm IO
  • AUDIT="true" -- use servr_audit.so to audite the server
  • AUDITCONFIG="server_audit_file_path='/tmp/audit.log" -- comma separated values to be added/replaced in audit config
⁠Content Creation
  • On init the directory /var/lib/sql will be checked for .sql files, if any are found these will be executed before the server comes up
  • This allows you to create schemas/tables/users and add data, you can use a mysqldump file

docker run \
--name mysql \
--hostname mysql \
-e MYSQL_ROOT_PASSWORD=password \
-p 33306:3306 \
-v /tmp/sql:/var/lib/sql \
-d jonhartley/percona-server
⁠Master-Master Setup
  • We can use CONFIG and the slave variables to create a GTID master <==> master setup
  • First spin up a standalone container fill with data required and run the backup script
docker run --net percona \
--name percona \
--hostname percona \
-e CONFIG="max_allowed_packet=1G,innodb_buffer_pool_size=500M,max_connections=500" \
-e MYSQL_ROOT_PASSWORD=password \
-e SCHEMA=sysbench \
-e EXTUSER=sysbench:sysbench \
-e BACKUPDIR=/backup \
-e BINLOG=true \
-v /tmp/percona:/backup \
-d jonhartley/percona-server
  • Then we can share this backup with both containers to force master master replcation to be set up
docker network create percona

docker run --net percona \
--name percona1 \
--hostname percona1 \
-e CONFIG="max_allowed_packet=1G,innodb_buffer_pool_size=500M,max_connections=500;auto_increment_increment=2;auto_increment_offset=1" \
-e MYSQL_ROOT_PASSWORD=password \
-e SCHEMA=sysbench \
-e EXTUSER=sysbench:sysbench \
-e BACKUPDIR=/backup \
-e STARTFROMBACKUP=true \
-e REPLUSER=replication:password \
-e BINLOG=true \
-e SLAVE=true \
-e MASTER_HOST=percona2 \
-v /tmp/percona:/backup \
-d jonhartley/percona-server

docker run --net percona \
--name percona2 \
--hostname percona2 \
-e CONFIG="max_allowed_packet=1G,innodb_buffer_pool_size=500M,max_connections=500;auto_increment_increment=2;auto_increment_offset=2" \
-e MYSQL_ROOT_PASSWORD=password \
-e SCHEMA=sysbench \
-e EXTUSER=sysbench:sysbench \
-e BACKUPDIR=/backup \
-e STARTFROMBACKUP=true \
-e REPLUSER=replication:password \
-e BINLOG=true \
-e SLAVE=true \
-e MASTER_HOST=percona1 \
-v /tmp/percona:/backup \
-d jonhartley/percona-server
⁠Kubernetes Master<=>Master setup
  • Use stateful sets to provide endpoint dns for replication (ips will change on re scheduling etc)
  • Create service to cover both sets for HA.
apiVersion: v1
kind: PersistentVolume
metadata:
  name: perconanfs
  namespace: data
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteMany
  nfs:
    server: 192.168.3.250
    path: "/volume1/kubernetes"
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: perconanfs
  namespace: data
spec:
  accessModes:
    - ReadWriteMany
  storageClassName: ""
  resources:
    requests:
     storage: 10Gi
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: percona-telegraf
  namespace: data
  labels:
    name: percona-telegraf
data:
  telegraf.conf: |-
    [global_tags]

    [agent]
        interval = "20s"
        round_interval = true
        metric_batch_size = 1000
        metric_buffer_limit = 10000
        collection_jitter = "0s"
        flush_interval = "10s"
        flush_jitter = "0s"
        precision = ""
        debug = false
        quiet = false
        logfile = ""
        hostname = ""
        omit_hostname = false
    [[inputs.disk]]
      mount_points = ["/var/lib/mysql","/var/backup"]
    [[inputs.mysql]]
      servers = ["monitor:9087345jhveoihj34095tu5@tcp(127.0.0.1:3306)/"]
      gather_user_statistics              = false
      gather_table_schema                 = false
      gather_info_schema_auto_inc         = false
      gather_innodb_metrics               = true
      gather_process_list                 = true
      gather_slave_status                 = true
      gather_binary_logs                  = true
      gather_table_io_waits               = true
      gather_table_lock_waits             = true
      gather_index_io_waits               = false
      gather_event_waits                  = false
      gather_file_events_stats            = false
      gather_perf_events_statements       = false
      interval_slow                       = "30m"
    [[outputs.prometheus_client]]
        listen = ":9126"
---
apiVersion: v1
kind: Service
metadata:
  name: percona1
  namespace: data
  labels:
    app: percona1
spec:
  ports:
    - port: 3306
      name: mysql
  clusterIP: "None"
  selector:
    app: percona1
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: percona1
  namespace: data
spec:
  serviceName: percona1
  replicas: 0
  selector:
    matchLabels:
      app: percona1
  template:
    metadata:
      annotations:
        prometheus.io/port: "9126"
        prometheus.io/scrape: "true"
      labels:
        app: percona1
        type: percona
    spec:
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchExpressions:
                  - key: "type"
                    operator: In
                    values: 
                    - percona
              topologyKey: "kubernetes.io/hostname"
      containers:
      - name: percona-server
        image: jonhartley/percona-server
        resources:
          limits:
            memory: "4000Mi"
            cpu: "2"
          requests:
            memory: "4000Mi"
            cpu: "2"
        imagePullPolicy: Always
        volumeMounts:
         - name: datadir
           mountPath: "/var/lib/mysql"
         - name: perconanfs
           mountPath: "/var/backup"
           subPath: perconabackup
        ports:
          - containerPort: 3306
        readinessProbe:
          tcpSocket:
            port: 3306
          initialDelaySeconds: 30
          periodSeconds: 10
        livenessProbe:
          exec:
            command:
            - /usr/bin/livecheck.sh
          initialDelaySeconds: 60
          timeoutSeconds: 5
          periodSeconds: 10
        env:
          - name: CONFIG
            value: "max_allowed_packet=1G,innodb_buffer_pool_size=512M,max_connections=500,innodb_log_file_size=256M;auto_increment_increment=2;auto_increment_offset=1"
          - name: MYSQL_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                name: pxc-root-creds
                key: password
          - name: MEM_LIMIT
            valueFrom:
              resourceFieldRef:
                containerName: percona-server
                resource: limits.memory
          - name: MY_CPU_LIMIT
            valueFrom:
              resourceFieldRef:
                containerName: percona-server
                resource: limits.cpu
          - name: BACKUPDIR
            value: "/var/backup"
          - name: STARTFROMBACKUP
            value: "true"
          - name: BINLOG
            value: "true"
          - name: SERVERID
            value: "1"
          - name: MASTER_HOST
            value: "percona2.data"
          - name: AUTOTUNE
            value: "bufferpool"
          - name: SSL
            value: "false"
      - name: telegraf
        image: telegraf:1.9.3-alpine
        resources:
          limits:
            memory: "20Mi"
            cpu: "20m"
          requests:
            memory: "20Mi"
            cpu: "20m"
        imagePullPolicy: Always
        volumeMounts:
         - name: config
           mountPath: /etc/telegraf
           readOnly: true
        ports:
          - containerPort: 9126
      volumes:
      - name: config
        configMap:
          name: percona-telegraf
      - name: perconanfs
        persistentVolumeClaim:
          claimName: perconanfs
  volumeClaimTemplates:
  - metadata:
      name: datadir
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: "cinder"
      resources:
        requests:
          storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: percona2
  namespace: data
  labels:
    app: percona2
spec:
  ports:
    - port: 3306
      name: mysql
  clusterIP: "None"
  selector:
    app: percona2
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: percona2
  namespace: data
spec:
  serviceName: percona2
  replicas: 0
  selector:
    matchLabels:
      app: percona2
  template:
    metadata:
      annotations:
        prometheus.io/port: "9126"
        prometheus.io/scrape: "true"
      labels:
        app: percona2
        type: percona
    spec:
      affinity:
        podAntiAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            - labelSelector:
                matchExpressions:
                  - key: "type"
                    operator: In
                    values: 
                    - percona
              topologyKey: "kubernetes.io/hostname"
      containers:
      - name: percona-server
        image: jonhartley/percona-server
        resources:
          limits:
            memory: "4000Mi"
            cpu: "2"
          requests:
            memory: "4000Mi"
            cpu: "2"
        imagePullPolicy: Always
        volumeMounts:
         - name: datadir
           mountPath: "/var/lib/mysql"
         - name: perconanfs
           mountPath: "/var/backup"
           subPath: perconabackup
        ports:
          - containerPort: 3306
        readinessProbe:
          tcpSocket:
            port: 3306
          initialDelaySeconds: 30
          periodSeconds: 10
        livenessProbe:
          tcpSocket:
            port: 3306
          initialDelaySeconds: 45
          periodSeconds: 20
        env:
          - name: CONFIG
            value: "max_allowed_packet=1G,innodb_buffer_pool_size=512M,max_connections=500,innodb_log_file_size=256M;auto_increment_increment=2;auto_increment_offset=2"
          - name: MYSQL_ROOT_PASSWORD
            valueFrom:
              secretKeyRef:
                name: pxc-root-creds
                key: password
          - name: MEM_LIMIT
            valueFrom:
              resourceFieldRef:
                containerName: percona-server
                resource: limits.memory
          - name: MY_CPU_LIMIT
            valueFrom:
              resourceFieldRef:
                containerName: percona-server
                resource: limits.cpu
          - name: BACKUPDIR
            value: "/var/backup"
          - name: STARTFROMBACKUP
            value: "true"
          - name: BINLOG
            value: "true"
          - name: SERVERID
            value: "2"
          - name: MASTER_HOST
            value: "percona1.data"
          - name: AUTOTUNE
            value: "bufferpool"
          - name: SSL
            value: "false"
      - name: telegraf
        image: telegraf:1.9.1-alpine
        resources:
          limits:
            memory: "20Mi"
            cpu: "20m"
          requests:
            memory: "20Mi"
            cpu: "20m"
        imagePullPolicy: Always
        volumeMounts:
         - name: config
           mountPath: /etc/telegraf
           readOnly: true
         - name: datadir
           mountPath: "/var/lib/mysql"
           readOnly: true
         - name: perconanfs
           mountPath: "/var/backup"
           subPath: perconanfs
           readOnly: true
        ports:
          - containerPort: 9126
      volumes:
      - name: config
        configMap:
          name: percona-telegraf
      - name: perconanfs
        persistentVolumeClaim:
          claimName: perconanfs
  volumeClaimTemplates:
  - metadata:
      name: datadir
    spec:
      accessModes: [ "ReadWriteOnce" ]
      storageClassName: "cinder"
      resources:
        requests:
          storage: 10Gi
---
apiVersion: v1
kind: Service
metadata:
  name: percona
  namespace: data
  labels:
    type: percona
spec:
  ports:
    - port: 3306
      name: mysql
  selector:
    type: percona

Tag summary

Content type

Image

Digest

Size

232 MB

Last updated

over 5 years ago

docker pull jonhartley/percona-server