问题
t=2022-11-23T00:15:15+0000 lvl=eror msg="Failed getting data source" logger=sqlstore err="database is locked" uid=j9nlG_wnk id=0 name= orgId=1
分析
使用chart方式安装的Grafana使用sqlite3来存储用户、密码、session等信息，数据存储在pvc云盘中，库老被锁，性能差，得换成mysql。
操作步骤
- 安装sqlite3-to-mysql
pip install sqlite3-to-mysql
-
替换此文档里目标数据的IP信息
-
创建过database --- grafana 进入net容器
mysql -h xxx.xxx.xxx.xxx -u root --password=Native_123
drop database if exists grafana;
create database grafana default character set utf8mb4;
create user grafana@'%' identified by 'Native_123';
grant all on grafana.* to grafana@'%';
- 拷贝grafana.db 到本地
出现错误“error: unexpected EOF”，就重试
- 导入到grafana数据库中
sqlite3mysql -f ./grafana.db -ugrafana -p -hxxx.xxx.xxx.xxx -P 3306 -d grafana --mysql-integer-type bigint --mysql-text-type mediumtext
- net容器中导出 grafana.sql
mysqldump -h xxx.xxx.xxx.xxx -P 3306 -u root --password=Native_123 --no-create-info --skip-extended-insert --databases grafana > grafana.sql
--no-create-info
tells mysqldump to suppress CREATE statements from the output, so that the dump file contains only table data.
--skip-extended-insert
默认mysqldump会将多条插入语句导出成一条insert语句格式，生成多条insert语句在mysqldump时加上参数--skip-extended-insert。
- 删除database，重新创建database --- grafana
mysql -h xxx.xxx.xxx.xxx -u root --password=Native_123
drop database if exists grafana;
create database grafana default character set utf8mb4;
grant all on grafana.* to grafana@'%';
- 配置grafana.ini
删除容器prometheus-grafana（滚动不了，记得删deploy），grafana 自动初始化数据库grafana的表结构
- 清空所有表的数据
mysql -h xxx.xxx.xxx.xxx -u root --password=Native_123
use grafana;
truncate table alert;
truncate table alert_configuration;
truncate table alert_instance;
truncate table alert_notification;
truncate table alert_notification_state;
truncate table alert_rule;
truncate table alert_rule_tag;
truncate table alert_rule_version;
truncate table annotation;
truncate table annotation_tag;
truncate table api_key;
truncate table builtin_role;
truncate table cache_data;
truncate table dashboard;
truncate table dashboard_acl;
truncate table dashboard_provisioning;
truncate table dashboard_snapshot;
truncate table dashboard_tag;
truncate table dashboard_version;
truncate table data_keys;
truncate table data_source;
truncate table kv_store;
truncate table library_element;
truncate table library_element_connection;
truncate table login_attempt;
truncate table migration_log;
truncate table ngalert_configuration;
truncate table org;
truncate table org_user;
truncate table permission;
truncate table playlist;
truncate table playlist_item;
truncate table plugin_setting;
truncate table preferences;
truncate table quota;
truncate table role;
truncate table seed_assignment;
truncate table server_lock;
truncate table session;
truncate table short_url;
truncate table star;
truncate table tag;
truncate table team;
truncate table team_member;
truncate table team_role;
truncate table temp_user;
truncate table test_data;
truncate table user;
truncate table user_auth;
truncate table user_auth_token;
truncate table user_role;
上面的truncate table 通过这个命令得到 mysql -h xxx.xxx.xxx.xxx -u grafana --password=Native_123 -e "SELECT CONCAT('truncate table ',TABLE_NAME,';') AS a FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'grafana' ;"
- 使用mysql导入grafana.sql mysql -h xxx.xxx.xxx.xxx -u root -pNative_123 --default-character-set=utf8mb4 grafana < grafana.sql
这里可能报冲突“ERROR 1062 (23000) at line 8132: Duplicate entry '1' for key 'PRIMARY'”，这是因为虽然上面一步我们清空了表数据，但是grafana又给写进去了。我们可以在argocd上删除grafana的deploy，然后导入数据后在sync出grafana的pod。
