使用k8s部署连接外部数据库和redis,执行sql备份sql语句报错

firehalt commented 5 months ago

重现步骤

使用k8s部署的，连接的外部数据库和redis 1.已经修改goinception里面关于数据库的信息 backup_port = 3306 backup_host = "xxx.mysql.rds.aliyuncs.com" backup_user = "public_admin" backup_password = "xxx"

预期外的结果

可以使用k8s部署

日志文本

使用k8s部署的，172.16.0.38是我k8s的node ip，这个一看就有问题
goinception错误日志：

time="2024/05/07 15:45:50.383" level=error msg="con:3 Error 1044: Access denied for user 'public_admin'@'%' to database 'xxxxx_mysql_rds_aliyuncs_com_3306_elnkpro_vc'" file=session_backup.go func=mysqlCreateBackupTable line=357
time="2024/05/07 15:45:50.384" level=error msg="Error 1142: INSERT command denied to user 'public_admin'@'172.16.0.38' for table '$_$inception_backup_information$_$'" file=session_backup.go func=flushBackupRecord line=120

版本

1.10.0

部署方式

K8S

是否还有其他可以辅助定位问题的信息？比如数据库版本等

k8s部署的，

LeoQuote commented 5 months ago

goinception 的pod 重启了吗? 里面的配置更新到位了吗?

firehalt commented 5 months ago

goinception 的pod 重启了吗? 里面的配置更新到位了吗?

已经重启并且更新到位

LeoQuote commented 5 months ago

看一下你archery 后台的备份库有没有写对, 还有代码可以用 master 分支的试一下

firehalt commented 5 months ago

看一下你archery 后台的备份库有没有写对, 还有代码可以用 master 分支的试一下 rchery 后台的备份库没有问题，点旁边的测试连接是可以正常连接的

官方提供的k8s部署文件有问题，如下： chart

LeoQuote commented 5 months ago

你好, 更新了仓库的 readme, 请阅读 wiki 获取最新的部署指引

firehalt commented 5 months ago

你好, 更新了仓库的 readme, 请阅读 wiki 获取最新的部署指引

还是不对，我就是用wiki里面的k8s方式部署的 1.部署命令helm install archery douban/archery -f archery-values.yaml -n archery

2.archery-values.yaml文件如下

image:
  repository: hhyo/archery # 如果你有二次开发, 改成你自己的 repo
  tag: v1.9.1 # 改到最新版, 或者你想要的版本
ingress:
  enabled: true # 启用 ingress
  className: "nginx"
  paths:
    - /
  servicePort: 9123
  hosts:
    - archery.xxxx.com  # 你的ingress 域名
redis:
  embedded: false
  url: "redis://xxxx.redis.rds.aliyuncs.com:6379/0?PASSWORD=MvUk7fCY*2EF"
mysql:
  embedded: false
  url: "mysql://xxx:xxxx@xxxxxx.mysql.rds.aliyuncs.com:3306/archery"
configMap:
  enabled: true
  superuser:
    username: admin
    password: xxxx  # 请尽快修改
    email: "fixxx@126.com"
  data:
    local_settings.py: |-
        # -*- coding: UTF-8 -*-
        # override your configs here
    soar.yaml: |-
        # 是否允许测试环境与线上环境配置相同
        allow-online-as-test: false
        # 是否清理测试时产生的临时文件
        drop-test-temporary: true
        # 语法检查小工具
        only-syntax-check: false
        sampling-data-factor: 100
        sampling: false
        sampling-statistic-target: 100
        profiling: false
        trace: false
        # 日志级别，[0:Emergency, 1:Alert, 2:Critical, 3:Error, 4:Warning, 5:Notice, 6:Informational, 7:Debug]
        log-level: 3
        log-output: /opt/archery/logs/soar.log
        # 优化建议输出格式
        report-type: markdown
        ignore-rules:
        - ""
        # 启发式算法相关配置
        max-join-table-count: 5
        max-group-by-cols-count: 5
        max-distinct-count: 5
        max-index-cols-count: 5
        max-total-rows: 9999999
        spaghetti-query-length: 2048
        allow-drop-index: false
        # EXPLAIN相关配置
        explain-sql-report-type: pretty
        explain-type: extended
        explain-format: traditional
        explain-warn-select-type:
        - ""
        explain-warn-access-type:
        - ALL
        explain-max-keys: 3
        explain-min-keys: 0
        explain-max-rows: 10000
        explain-warn-extra:
        - ""
        explain-max-filtered: 100
        explain-warn-scalability:
        - O(n)
        query: ""
        list-heuristic-rules: false
        list-test-sqls: false
        verbose: true
    analysis_slow_query.sh: |-
        #!/bin/bash
        DIR="$( cd "$( dirname "$0"  )" && pwd  )"
        cd $DIR
        #配置archery数据库的连接地址
        monitor_db_host="xxxx.mysql.rds.aliyuncs.com"
        monitor_db_port=3306
        monitor_db_user="xxxx"
        monitor_db_password="xxxx"
        monitor_db_database="archery"
        #实例慢日志位置
        slowquery_file="/home/mysql/log_slow.log"
        pt_query_digest="/usr/bin/pt-query-digest"
        #实例连接信息
        hostname="mysql_host:mysql_port" # 和archery实例配置内容保持一致，用于archery做筛选
        #获取上次分析时间，初始化时请删除last_analysis_time_$hostname文件，可分析全部日志数据
        if [ -s last_analysis_time_$hostname ]; then
            last_analysis_time=`cat last_analysis_time_$hostname`
        else
            last_analysis_time='1000-01-01 00:00:00'
        fi
        #收集日志
        #RDS需要增加--no-version-check选项
        $pt_query_digest \
        --user=$monitor_db_user --password=$monitor_db_password --port=$monitor_db_port \
        --review h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review  \
        --history h=$monitor_db_host,D=$monitor_db_database,t=mysql_slow_query_review_history  \
        --no-report --limit=100% --charset=utf8 \
        --since "$last_analysis_time" \
        --filter="\$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$hostname\"  and \$event->{client}=\$event->{ip} " \
        $slowquery_file > /tmp/analysis_slow_query.log
        echo `date +"%Y-%m-%d %H:%M:%S"`>last_analysis_time_$hostname

3.使用sql上线错误如下错误333

ime="2024/05/07 17:20:30.859" level=error msg="con:98, config: user: 'public_admin'\npassword: 'xxxxxx'\nhost: 'xxxxx.mysql.rds.aliyuncs.com'\nport: 3306\nexecute: 1\nignorewarnings: 1\nbackup: 1\nsleep: 200\nsleeprows: 100\n, parsed: map[string]interface {}{\"backup\":1, \"execute\":1, \"host\":\"xxxxxx.mysql.rds.aliyuncs.com\", \"ignorewarnings\":1, \"password\":\"xxxxxx\", \"port\":3306, \"sleep\":200, \"sleeprows\":100, \"user\":\"public_admin\"} (err: con:98 dial tcp: lookup archery-mysql on 10.100.0.10:53: no such host)" file=session_inception.go func=parseOptions line=2153

LeoQuote commented 5 months ago

不好意思这确实是 chart 的代码和说明不够清晰导致的, 请使用最新的 helm chart , 最新的 goinception chart 新增了环境变量配置备份数据库的功能, 另外比较建议你使用 master 分支的代码进行测试, 你使用的版本比较老, 不保证能和 k8s 兼容

https://github.com/douban/charts/blob/bffc88bc08a2e58bb3d53e2af260d862315048df/charts/goinception/values.yaml#L25-L32

有什么改进的点欢迎pr

firehalt commented 5 months ago

不好意思这确实是 chart 的代码和说明不够清晰导致的, 请使用最新的 helm chart , 最新的 goinception chart 新增了环境变量配置备份数据库的功能, 另外比较建议你使用 master 分支的代码进行测试, 你使用的版本比较老, 不保证能和 k8s 兼容

https://github.com/douban/charts/blob/bffc88bc08a2e58bb3d53e2af260d862315048df/charts/goinception/values.yaml#L25-L32

有什么改进的点欢迎pr

你这个还是有问题，archery/values.yaml里面下面的变量还是替换不了goinception/values.yaml的值， initEnv:

name: BACKUP_HOST value: ""
name: BACKUP_PORT value: ""
mysql superuser
name: BACKUP_USER value: ""
name: BACKUP_PASSWORD value: ""

LeoQuote commented 5 months ago

goinception 是 archery的依赖，你得写

goinception:
  initEnv:

firehalt commented 5 months ago

goinception 是 archery的依赖，你得写
goinception:
  initEnv:
1.我现在的部署命令 helm install archery douban/archery -f archery-values.yaml -n archery

2.上面的命令会自动启动archery ，archery-goinception两个pod，然后archery-values.yaml里面的关于goinception数据库配置没有生效

我写了的错误666

LeoQuote commented 5 months ago

你能确认下chart 版本吗刚更新的版本 0.3.3 https://github.com/douban/charts/blob/bffc88bc08a2e58bb3d53e2af260d862315048df/charts/archery/Chart.yaml#L5

firehalt commented 5 months ago

你能确认下chart 版本吗刚更新的版本 0.3.3 https://github.com/douban/charts/blob/bffc88bc08a2e58bb3d53e2af260d862315048df/charts/archery/Chart.yaml#L5

现在已经升级到最新版本了，现在又是不同的错误备份数据库连接信息

执行sql上线错误

一会是这种： Backup: Access denied for user 'public_admin'@'%!'(MISSING) to database 'xxxx_mysql_rds_aliyuncs_com_3306_elnkpro_vc'. INSERT command denied to user 'publicadmin'@'172.16.0.38' for table '$$inception_backupinformation$$'. INSERT command denied to user 'public_admin'@'172.16.0.38' for table 'elnkpro_vc'.

一会是这种： (2005, "Unknown MySQL server host 'archery-goinception' (2)") : Traceback (most recent call last): File "/opt/venv4archery/lib/python3.9/site-packages/django_q/cluster.py", line 432, in worker res = f(*task["args"], task["kwargs"]) File "/opt/archery/sql/utils/execute_sql.py", line 44, in execute return execute_engine.execute_workflow(workflow=workflow_detail) File "/opt/archery/sql/engines/mysql.py", line 646, in execute_workflow return self.inc_engine.execute(workflow) File "/opt/archery/sql/engines/goinception.py", line 118, in execute inception_result = self.query(sql=sql_execute) File "/opt/archery/sql/engines/goinception.py", line 151, in query conn = self.get_connection() File "/opt/archery/sql/engines/goinception.py", line 39, in get_connection self.conn = MySQLdb.connect( File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/init.py", line 121, in Connect return Connection(*args, *kwargs) File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/connections.py", line 193, in init super().init(args, kwargs2) MySQLdb.OperationalError: (2005, "Unknown MySQL server host 'archery-goinception' (2)")

版本信息如下 hhyo/archery:v1.10.0 hanchuanchuan/goinception:latest

LeoQuote commented 5 months ago

先看第一个问题哈，第一个问题你能确认IP解析是否正确吗？

他说access denied 那应该就是权限不够呀，你试试多授权一些，可以看看wiki备份库需要什么权限

LeoQuote commented 5 months ago

第二个问题应该是你解析的问题，你可以试一下写全域名，比如 archery-goinception.namespace 或者 archery-goinception.namespace.k8s……

firehalt commented 5 months ago

先看第一个问题哈，第一个问题你能确认IP解析是否正确吗？我改成svc的地址了还是一样的错误 (2003, "Can't connect to MySQL server on '10.100.15.213' (110)") : Traceback (most recent call last): File "/opt/venv4archery/lib/python3.9/site-packages/django_q/cluster.py", line 432, in worker res = f(*task["args"], task["kwargs"]) File "/opt/archery/sql/utils/execute_sql.py", line 44, in execute return execute_engine.execute_workflow(workflow=workflow_detail) File "/opt/archery/sql/engines/mysql.py", line 646, in execute_workflow return self.inc_engine.execute(workflow) File "/opt/archery/sql/engines/goinception.py", line 118, in execute inception_result = self.query(sql=sql_execute) File "/opt/archery/sql/engines/goinception.py", line 151, in query conn = self.get_connection() File "/opt/archery/sql/engines/goinception.py", line 39, in get_connection self.conn = MySQLdb.connect( File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/init.py", line 121, in Connect return Connection(*args, *kwargs) File "/opt/venv4archery/lib/python3.9/site-packages/MySQLdb/connections.py", line 193, in init super().init(args, kwargs2) MySQLdb.OperationalError: (2003, "Can't connect to MySQL server on '10.100.15.213' (110)")