Ambari组件状态从MySQL同步到SQLServer或者MySQL
2023-09-11 14:22:11 时间
目录
1.2.hostcomponentstate(主机组件状态表)
一、Ambari表信息
1.1.hoststate(主机状态表)
mysql> desc hoststate;
+-------------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-------------------+--------------+------+-----+---------+
| agent_version | varchar(255) | NO | | NULL | agent版本
| available_mem | bigint(20) | NO | | NULL | 可用内存
| current_state | varchar(255) | NO | | NULL | 当前状态
| health_status | varchar(255) | YES | | NULL | 健康状态
| host_id | bigint(20) | NO | PRI | NULL | 主机id
| time_in_state | bigint(20) | NO | | NULL |
| maintenance_state | varchar(512) | YES | | NULL | 维护模式状态
+-------------------+--------------+------+-----+---------+
1.2.hostcomponentstate(主机组件状态表)
mysql> desc hostcomponentstate;
+-----------------+--------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------+--------------+------+-----+---------+
| id | bigint(20) | NO | PRI | NULL |
| cluster_id | bigint(20) | NO | | NULL |
| component_name | varchar(100) | NO | MUL | NULL | 组件名称
| version | varchar(32) | NO | | UNKNOWN | 版本
| current_state | varchar(255) | NO | | NULL |
| last_live_state | varchar(255) | NO | | UNKNOWN | 上一次活跃状态
| host_id | bigint(20) | NO | MUL | NULL |
| service_name | varchar(100) | NO | | NULL | 组件对应服务名称
| upgrade_state | varchar(32) | NO | | NONE |
+-----------------+--------------+------+-----+---------+
1.3. hosts(主机表)
mysql> desc hosts;
+------------------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------------------+---------------+------+-----+---------+
| host_id | bigint(20) | NO | PRI | NULL |
| host_name | varchar(255) | NO | UNI | NULL | 主机名称
| cpu_count | int(11) | NO | | NULL | cpu数量
| cpu_info | varchar(255) | NO | | NULL | cpu信息
| discovery_status | varchar(2000) | NO | | NULL |
| host_attributes | longtext | NO | | NULL | 主机属性
| ipv4 | varchar(255) | YES | | NULL | IP地址
| ipv6 | varchar(255) | YES | | NULL | IP地址
| last_registration_time | bigint(20) | NO | | NULL | 最后登记时间
| os_arch | varchar(255) | NO | | NULL | 操作系统架构
| os_info | varchar(1000) | NO | | NULL |
| os_type | varchar(255) | NO | | NULL |
| ph_cpu_count | int(11) | YES | | NULL |
| public_host_name | varchar(255) | YES | | NULL | 主机名
| rack_info | varchar(255) | NO | | NULL | 机架信息
| total_mem | bigint(20) | NO | | NULL | 总内存
+------------------------+---------------+------+-----+---------+
1.4.clusters(集群表)
+-----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| cluster_id | bigint(20) | NO | PRI | NULL | |
| resource_id | bigint(20) | NO | MUL | NULL | | 资源id
| upgrade_id | bigint(20) | YES | MUL | NULL | |
| cluster_info | varchar(255) | NO | | NULL | |
| cluster_name | varchar(100) | NO | UNI | NULL | | 集群名称
| provisioning_state | varchar(255) | NO | | INIT | | 配置状态
| security_type | varchar(32) | NO | | NONE | | 安全策略类型
| desired_cluster_state | varchar(255) | NO | | NULL | | 预期集群状态
| desired_stack_id | bigint(20) | NO | MUL | NULL | | 预期库id
+-----------------------+--------------+------+-----+---------+-------+
1.5.表的关联查询
SELECT cluster_name,host_name,ipv4,service_name,a.current_state,component_name
FROM hostcomponentstate a
INNER JOIN hoststate b
on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED'
查询出的信息我们将同步到 SQLServer或者MySQL 表,说明一点,上面这个SQL语句查询出来是 STARTED 的状态,也就是组件的最新状态,当第一次查询时,我们要将组件全部最新状态同步到 要查询的数据库中,如果哪个组件停掉,上面的 SQL的 就不会查询出来,查询出来的还是 STARTED 状态,我们只需要 将 最新状态的临时表和 目标表关联 ,将关联不到的 STATED的 状态的组件 状态更新为 STOPED 即可,就可以实现集群监控信息 的同步。
二、Ambari组件信息同步实现
将MySQL中Ambari 中 4个表的信息关联查询出的信息同步到SqlServer,任务调度频次5分钟一趟,发现问题就告警
2.1.编写table.ini 的数据库配置文件
xxx 是不同的客户不同的数据库 可以读取多个数据库的配置写到不同的SQLServer数据库
[kangll@client ~]$ cat /hadoop/datadir/script/hadoop/table.ini
[xxx_CONNECT]
url=xxx
port=1433
username=PCS.Support
password=321@win#
dbname=HDP_TEST
customer=xxx_
2.2.Shell 实现表信息的同步到SQLServer
set -x
HOSTNAME="xxx"
USER="root"
PASSWD="@001"
PORT="3306"
DBNAME="ambari"
function ReadConnect(){
ReadINI=`awk -F '=' '/\['$2'\]/{a=1}a==1&&$1~/^'$3'$/{print $2;exit}' $1`
}
batchCustomer=xxx_
table_ini=/hadoop/datadir/script/hadoop/ipvaSum/table.ini
ReadConnect $table_ini "${batchCustomer}CONNECT" url
server=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" port
port=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" dbname
database=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" username
user=$ReadINI
ReadConnect $table_ini "${batchCustomer}CONNECT" password
paw=$ReadINI
ambari_tmp_file=/hadoop/datadir/temp/monitor/component_tmp_file.txt
mysql_cmd="mysql -h${HOSTNAME} -P${PORT} -u${USER} -p${PASSWD} ${DBNAME} -e"
sqlserver_cmd="/opt/mssql-tools/bin/sqlcmd -S $server -U $user -P $paw -d ${database} -Q "
datebatch=`date +'%Y-%m-%d %H:%M:%S'`
# AmbariServer监控
ambariServerCount=`ps -ef | grep AmbariServer | grep -v "grep" | wc -l`
if [ 0 == $ambariServerCount ];then
${sqlserver_cmd} "INSERT into task_monitor (flowId,taskId,status,startTime,endTime) VALUES(DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()),'ambari-server-heartbeat',90,GETDATE(),GETDATE())"
else
${sqlserver_cmd} "INSERT into task_monitor (flowId,taskId,status,startTime,endTime) VALUES(DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()),'ambari-server-heartbeat',80,GETDATE(),GETDATE())"
fi
#查询组件状态
select_ambari_sql="SELECT cluster_name , host_name , ipv4 , service_name ,a. current_state,component_name FROM hostcomponentstate a INNER JOIN hoststate b on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED' into outfile \"${ambari_tmp_file}\" fields terminated by \",\" ;"
# meger
task_merge_sql="MERGE ${database}.[dbo].[component_monitor] AS a USING (SELECT cluster_name,host_name,ipv4,service_name,current_state,component_name FROM ${database}.[dbo].[component_monitor_tmp]) AS b ON a.host_name = b.host_name AND a.service_name=b.service_name AND a.component_name = b.component_name WHEN MATCHED THEN UPDATE SET a.current_state = b.current_state ,a.modifyTime='${datebatch}' WHEN NOT MATCHED THEN INSERT (cluster_name , host_name , ipv4 , service_name ,current_state , component_name,modifyTime,createTime) VALUES(b.cluster_name , b.host_name , b.ipv4 , b.service_name ,b.current_state , b.component_name,'${datebatch}','${datebatch}');"
update_stop_sql="UPDATE ${database}.[dbo].[component_monitor] SET current_state = 'STOPED' where modifyTime < (select max(modifyTime) from ${database}.[dbo].[component_monitor]);"
#执行SQL 运行成功和失败的任务 写入文件
rm -rf ${azkaban_exec_tmp_file}
${mysql_cmd} "${select_ambari_sql}"
if [ -f ${azkaban_exec_tmp_file} ];then
${sqlserver_cmd} "truncate table ${database}.[dbo].[component_monitor_tmp]"
/opt/mssql-tools/bin/bcp ${database}.dbo.component_monitor_tmp in ${azkaban_exec_tmp_file} -S${server} -U${user} -P${paw} -c -t, -r'\n' -b 1000
${sqlserver_cmd} "${task_merge_sql}"
${sqlserver_cmd} "${update_stop_sql}"
else
echo file ${azkaban_exec_tmp_file} not exist!
fi
2.3.Shell 实现表信息的同步到MySQL
两个MySQL 是不同的数据库 或者不同的服务器上的数据库。
set -x
# Ambari 元数据库
HOSTNAME="xxx"
USER="root"
PASSWD="@001"
PORT="3306"
DBNAME="ambari"
# 告警目标库
WARN_HOSTNAME="xxx"
WARN_USER="root"
WARN_PASSWD="@001"
WARN_PORT="3306"
WARN_DBNAME="kangll"
# 查询组件状态写入到本地的临时文件
hdp_tmp_file=/hadoop/datadir/temp/monitor/component_tmp_file.txt
mysql_cmd="mysql -h${HOSTNAME} -P${PORT} -u${USER} -p${PASSWD} ${DBNAME} -e "
mysql_cmd_two="mysql -h${WARN_HOSTNAME} -P${WARN_PORT} -u${WARN_USER} -p${WARN_PASSWD} ${WARN_DBNAME} -e "
#查询组件状态,查询出来是 STATED 状态的组件,如果组件停掉则查不到
select_ambari_sql="SELECT cluster_name , host_name , ipv4 , service_name ,a. current_state,component_name FROM hostcomponentstate a INNER JOIN hoststate b on a.host_id = b.host_id
INNER JOIN hosts c
on a.host_id = c.host_id
INNER JOIN clusters d
ON d.cluster_id = a.cluster_id
WHERE a.current_state != 'INSTALLED' into outfile \"${hdp_tmp_file}\" fields terminated by \",\" ;"
# 状态字段的更新,组件停掉则临时表查不到,临时表和目标表关联,如果哪个组件停掉则,目标表会多一条,则更新STARED 状态的组件
update_state_sql="UPDATE component_monitor cm LEFT JOIN component_monitor_tmp t ON
cm.host_name = t.host_name AND cm.service_name = t.service_name AND cm.component_name = t.component_name
SET cm.current_state = if(t.component_name is null, 'STOPED',t.current_state);"
# 查词插入目标表数据检查
insert_check_sql="select count(*) as cnt from component_monitor;"
# 首次查询集群组件状态
select_insert_sql="insert into component_monitor(cluster_name,host_name,ipv4,service_name,current_state,component_name) select cluster_name,host_name,ipv4,service_name,current_state,component_name from component_monitor_tmp"
# load文件到临时表
load_file_sql="load data local infile \"${hdp_tmp_file}\" into table component_monitor_tmp fields terminated by \",\" (cluster_name,host_name,ipv4,service_name,current_state,component_name)"
# 删除临时文件
rm -rf ${hdp_tmp_file}
# 查询组件状态写入临时文件
${mysql_cmd} "${select_ambari_sql}"
# 判断临时文件是否存在
if [ -f ${hdp_tmp_file} ];then
${mysql_cmd_two} "truncate table component_monitor_tmp"
# load 本地数据到临时表
${mysql_cmd_two} "${load_file_sql}"
# 判断首次目标表数据为 0,则查询插入,否则直接更新状态
dataCount=$(mysql -h${WARN_HOSTNAME} -P${WARN_PORT} -u${WARN_USER} -p${WARN_PASSWD} ${WARN_DBNAME} --skip-column-names -e "${insert_check_sql}")
echo $dataCount
if [ 0 -eq $dataCount ];then
# 集群状态首次写入表
${mysql_cmd_two} "${select_insert_sql}"
else
# 集群组件状态更新
${mysql_cmd_two} "${update_state_sql}"
fi
else
echo ${hdp_tmp_file}"not exist"!
fi
相关文章
- Error connecting to database [Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)]
- Mysql:is not allowed to connect to this MySQL server
- 【Mysql 学习】mysql 字符集
- 【C/C++学院】(23)Mysql数据库编程--C语言编程实现mysql客户端
- mysql-介绍、MySQL部署、数据类型、存储引擎
- Windows 7安装解压版MySQL 5.6(不包含配置文件优化)
- 【MySQL】Semisynchronous Replication 配置和参数说明
- 【MySQL】解决mysql的 1594 错误
- navcat导入mysql.sql出现:2006, 'MySQL server has gone away'
- 第二百八十九节,MySQL数据库-ORM之sqlalchemy模块操作数据库
- mysql启动时报错:Starting MySQL... ERROR! The server quit without updating PID file (/opt/mysql/data/mysql.pid) 的解决方法
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
- MYSQL查看和修改存储引擎
- mymysql与go-mysql-driver性能比较
- 大叔经验分享(94)jdbc连接mysql、sqlserver、oracle
- 【问题解决方案】MySQL安装后无法启动-net start mysql服务名无效
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- 【收藏】windows下 Mysql 错误 Can‘t open and lock privilege tables: Table ‘mysql.user‘ doesn‘t exist
- 【高可用MySQL解决方案】centos7配置mysql主从复制
- mysql sql语句大全(MySQL语句 整理一)
- MYSQL提权之反弹SHELL——数据库提权属于webshell到管理员的纵向提权,本质还是利用udf提权,无非是在mysql自定义函数中使用了反弹shell而已
- Groonga开源搜索引擎——列存储做聚合,没有内建分布式,分片和副本是随mysql或者postgreSQL作为存储引擎由MySQL自身来做分片和副本的
- MySQL-mysql 8.0.11安装教程 windows
- Mysql基础篇之事务隔离---03
- MySQL最后一次查询耗时查询
- MySQL-第十章-MHA高可用技术
- 【Mysql异构实时同步Oracle】OGG12异构同步mysql到oracle(windows mysql实时同步数据到linux oracle)详细文档