Use Yearning for MYSQL audit docker+K8S deployment
Download percona-toolkit (optional) working directory / apps/work/docker/sql-audit download percona-toolkit suite wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/tarball/percona-toolkit-3.0.13_x86_64.tar.gztar-xvf percona-toolkit-3.0.13_x86_64.tar.gzcd percona-toolkit-3.0.13mv. / bin / apps/work/docker/sql-audit/ptbin Load Yearning-gowget https://github.com/cookieY/Yearning/releases/download/v2.1.1/Yearning-2.1.1.linux-amd64.ziptar-xvf Yearning-2.1.1.linux-amd64.zipcd Yearning-2.1.1.linux-amd64/Yearning-go# to delete the useless file rm # README .DS _ Store# copy the file to the / apps/work/docker/sql-audit/ directory cp-pdr Yearning dist conf.toml / apps/work/docker/ Sql-audit/# Yearning executable permissions chmod + x Yearning modify configuration and modify dockerfilecd / apps/work/docker/sql-audit/# to generate 16-bit SecretKey
< /dev/urandom tr -dc A-Z-a-z|head -c ${1:-16};echo [root@jenkins ~]# < /dev/urandom tr -dc A-Z-a-z|head -c ${1:-16};echovMEUDgvGIJqapfPQvi conf.toml[Mysql]Db = "Yearning"Host = "sql-audit-mysql"Port = "3306"Password = "kUgUS6mYbL"User = "root"[General]SecretKey = "vMEUDgvGIJqapfPQ"#cp 一份时区文件cp /usr/share/zoneinfo/Hongkong ./localtime# 修改dockerfilevi DockerfileARG ARCH="amd64"ARG OS="linux"FROM alpine:latestLABEL maintainer="The audit sql Authors "ENV VERSION 2.1.1COPY Yearning /opt/YearningCOPY dist /opt/distCOPY conf.toml /opt/conf.tomlCOPY ptbin/ /binCOPY localtime /etc/localtimeRUN sed -i 's/dl-cdn.alpinelinux.org/mirrors.aliyun.com/g' /etc/apk/repositoriesRUN mkdir /lib64 && ln -s /lib/libc.musl-x86_64.so.1 /lib64/ld-linux-x86-64.so.2RUN apk add --no-cache ca-certificates \ perl-dbi perl-dbd-mysql \ perl-time-hires perl-io-socket-ssl \ && rm -rf /tmp/* \ && rm -rf /var/cache/apk/*EXPOSE 8000WORKDIR /optENTRYPOINT ["/opt/Yearning"]CMD ["-m", "-s"]制作docker imagesdocker build -t mysql-audit:2.1.1 .# docker 标签上传仓库 docker tag mysql-audit:2.1.1 docker.xxxx.com/library/mysql-audit:2.1.1 docker push docker.xxxx.com/library/mysql-audit:2.1.1docker run 方式运行# mysql版本必须5.7及以上版本,请事先自行安装完毕且创建Yearning库,字符集应为UTF-8/UTF8mb4 (仅Yearning所需mysql版本)docker run -tid --name sql-audit-mysql \ -e MYSQL_ROOT_PASSWORD="kUgUS6mYbL" \ -p "3306:3306" \ -v/apps/mysqldb:/var/lib/mysql \ mysql:5.7.26 \ --character-set-server=utf8mb4 \ --collation-server=utf8mb4_unicode_ci \ --max_allowed_packet=256M \ --transaction-isolation=READ-COMMITTED# 等待mysql 启动完成使用Navicat 或者mysql 客户端工具连接登陆mysql 数据库 创建Yearning库CREATE DATABASE `Yearning` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;# 启动Yearningdocker run -tid -p8000:8000 --link sql-audit-mysql:sql-audit-mysql mysql-audit:2.1.1 #http://ip:8000 端口访问#Yearning 基于1080p分辨率开发仅支持1080p及以上显示器访问#由于使用较多新的前端技术栈,请使用Chrome最新版本(不包括360等其他魔改版本)默认密码:admin/Yearning_admin或者docker logs 容器ID k8s 方式运行# 创建命名空间 sql-auditkubectl create namespace sql-audit# helm 方式部署 mysql helm install -n sql-audit --name-template sql-audit --set metrics.enabled=true,metrics.serviceMonitor.enabled=true,persistence.size=50Gi stable/mysql# 查看mysql 部署root 密码kubectl get secret --namespace sql-audit sql-audit-mysql -o jsonpath="{.data.mysql-root-password}" | base64 --decode; echo[root@]~]#kubectl get secret --namespace sql-audit sql-audit-mysql -o jsonpath="{.data.mysql-root-password}" | base64 --decode; echoFo7FZg8FuA# 我这里内网环境与K8S pod 及k8s 自身网络已经打通可以远程直接访问mysql -hsql-audit-mysql.sql-audit.svc.cluster.local -u root -p# kubectl get pod --namespace sql-audit[root@]~]#kubectl get pod --namespace sql-auditNAME READY STATUS RESTARTS AGEsql-audit-mysql-6f6bcbf4bc-27d 2/2 Running 4 14dkubectl exec -ti sql-audit-mysql-6f6bcbf4bc-27d /bin/bash -n sql-audit[root@]~]#kubectl exec -ti sql-audit-mysql-6f6bcbf4bc-27d /bin/bash -n sql-auditDefaulting container name to sql-audit-mysql.Use 'kubectl describe pod/sql-audit-mysql-6f6bcbf4bc-27d -n sql-audit' to see all of the containers in this pod.root@sql-audit-mysql-6f6bcbf4bc-27d:/#root@sql-audit-mysql-6f6bcbf4bc-27d:/#root@sql-audit-mysql-6f6bcbf4bc-27d:/#root@sql-audit-mysql-6f6bcbf4bc-27d:/#root@sql-audit-mysql-6f6bcbf4bc-27d:/# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7504Server version: 5.7.26-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>