Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
firehalt opened this issue May 7, 2024 · 18 comments
Open

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

firehalt opened this issue May 7, 2024 · 18 comments

Comments

@firehalt
Copy link

firehalt commented May 7, 2024

重现步骤

使用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
Copy link
Collaborator

LeoQuote commented May 7, 2024

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

@firehalt
Copy link
Author

firehalt commented May 7, 2024

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

已经重启并且更新到位

@LeoQuote
Copy link
Collaborator

LeoQuote commented May 7, 2024

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

@firehalt
Copy link
Author

firehalt commented May 7, 2024

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

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

@LeoQuote LeoQuote mentioned this issue May 7, 2024
Merged
@LeoQuote
Copy link
Collaborator

LeoQuote commented May 7, 2024

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

@firehalt
Copy link
Author

firehalt commented May 7, 2024 •
edited by LeoQuote

你好, 更新了仓库的 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
Copy link
Collaborator

LeoQuote commented May 7, 2024

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

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

有什么改进的点欢迎pr

@firehalt
Copy link
Author

firehalt commented May 7, 2024

不好意思这确实是 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
Copy link
Collaborator

LeoQuote commented May 7, 2024 •
edited

goinception 是 archery的依赖，你得写

goinception:
  initEnv:

@firehalt
Copy link
Author

firehalt commented May 7, 2024

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
Copy link
Collaborator

LeoQuote commented May 7, 2024

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

@firehalt
Copy link
Author

firehalt commented May 8, 2024 •
edited

你能确认下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 'public_admin'@'172.16.0.38' for table '$$inception_backup_information$$'.
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
Copy link
Collaborator

LeoQuote commented May 8, 2024 •
edited

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

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

@LeoQuote
Copy link
Collaborator

LeoQuote commented May 8, 2024

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

@firehalt
Copy link
Author

firehalt commented May 8, 2024

先看第一个问题哈，第一个问题你能确认IP解析是否正确吗？
我改成svc的地址了还是一样的错误
改成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)")

邮箱firehalt@126.com 能否邮件指导一下，谢谢

他说access denied 那应该就是权限不够呀，你试试多授权一些，可以看看wiki备份库需要什么权限
wiki没有说需要什么权限，还是出现上面两个的一样的错误

@LeoQuote
Copy link
Collaborator

LeoQuote commented May 8, 2024

https://hanchuanchuan.github.io/goInception/zh/permission.html#%E5%AE%A1%E6%A0%B8%E5%8A%9F%E8%83%BD 这是goinception 需要的权限说明，请参考一下

@firehalt
Copy link
Author

firehalt commented May 9, 2024

https://hanchuanchuan.github.io/goInception/zh/permission.html#%E5%AE%A1%E6%A0%B8%E5%8A%9F%E8%83%BD 这是goinception 需要的权限说明，请参考一下

goinception 备份库已经给了SUPER权限，现在是提交SQL上线，提示排队中
345666

@LeoQuote
Copy link
Collaborator

LeoQuote commented May 9, 2024

https://github.com/hhyo/Archery/wiki/faq#%E5%B7%A5%E5%8D%95%E7%8A%B6%E6%80%81%E4%B8%80%E7%9B%B4%E6%98%AF%E6%8E%92%E9%98%9F%E4%B8%AD 请参考下这里的手册, 里面有对于你说的问题的排查方案

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@LeoQuote @firehalt