mysql agent_mysql 语句

修改最大连接数

临时生效：

SHOW VARIABLES LIKE "max_connections"; 查看

SET GLOBAL max_connections=9000; 修改

永久生效：

修改 /etc/my.cnf

修改/添加 max_connections=9000 #修改最大连接数为9000

重启服务

SHOW VARIABLES LIKE "max_connections"; 查看是否生效

KVM 计算节点离线统计：

SELECT

t_agent.uuid,

t_agent.`host`,

t_agent.ip_addr,

t_agent.agent_type,

t_agent.os_type,

t_agent.`status`,

t_agent.last_seen_time,

t_agent.create_time

FROM

t_agent

WHERE

last_seen_time < "2020-01-15 20:00:00"

AND agent_type = "32"

KVM 任务统计

SELECT

t_kvm_backupjobs.job_name AS `任务名`,

t_kvm_backupjobs.vm_uuid,

t_kvm_backupjobs.backup_type,

t_policy.policy_name AS `策略名`,

t_policy.description,

t_kvm_lcm.versions AS `保留版本`,

t_policy.`year`,

t_policy.`month`,

t_policy.`day`,

t_policy.`week`,

t_policy.day_of_week,

t_policy.`hour`,

t_policy.`minute`,

t_policy.`second`

FROM

t_kvm_backupjobs

LEFT JOIN t_policy ON t_kvm_backupjobs.backup_policy_id = t_policy.id

INNER JOIN t_kvm_lcm ON t_kvm_backupjobs.vm_uuid = t_kvm_lcm.vm_uuid

WHERE t_kvm_backupjobs.vm_uuid like "%%"

查虚机任务policy_id

SELECT

t_kvm_backupjobs.vm_uuid,

t_kvm_backupjobs.backup_policy_id,

t_kvm_backupjobs.backup_type

FROM

t_kvm_backupjobs

WHERE

t_kvm_backupjobs.vm_uuid LIKE "%4d8fce07-3213-4140-8de3-11fbe38508a6%"

AND t_kvm_backupjobs.backup_type LIKE "%1%"

查无版本虚机

SELECT t_kvm_backups.vm_uuid FROM t_kvm_backups WHERE t_kvm_backups.del_flag = 0 AND t_kvm_backups.vm_uuid NOT IN (SELECT vm_uuid FROM t_kvm_backupjobruns WHERE backup_type = "1" GROUP BY vm_uuid) GROUP BY vm_uuid;

http://10.180.49.37

KVM 任务运行情况统计

SELECT

t_agent.ip_addr,

t_kvm_backupjobruns.pm_uuid,

t_kvm_backupjobruns.vm_uuid AS `虚机UUID`,

t_kvm_backupjobruns.backup_type AS `备份类型`,

t_kvm_backupjobruns.`status` AS `状态`,

t_kvm_backupjobruns.backup_log AS `日志`,

t_kvm_backupjobruns.start_time AS `发起时间`,

t_kvm_backupjobruns.stop_time AS `结束时间`

FROM

t_kvm_backupjobruns

LEFT JOIN t_agent ON t_kvm_backupjobruns.pm_uuid = t_agent.uuid

WHERE

t_kvm_backupjobruns.start_time > "2020-3-1 00:00:00"

AND t_kvm_backupjobruns.`status` LIKE "%%"

AND t_kvm_backupjobruns.backup_type LIKE "%%"

AND t_kvm_backupjobruns.backup_log LIKE "%%"

AND t_kvm_backupjobruns.vm_uuid LIKE "%%"

ORDER BY

`发起时间` DESC

SELECT

t_kvm_backupjobruns.vm_uuid AS `虚机UUID`,

t_kvm_backupjobruns.backup_log AS `日志`

FROM

t_kvm_backupjobruns

LEFT JOIN t_agent ON t_kvm_backupjobruns.pm_uuid = t_agent.uuid

WHERE

t_kvm_backupjobruns.start_time > "2020-3-18 00:00:00"

AND t_kvm_backupjobruns.`status` LIKE "%444%"

AND t_kvm_backupjobruns.backup_type LIKE "%%"

AND t_kvm_backupjobruns.backup_log LIKE "%%"

AND t_kvm_backupjobruns.vm_uuid LIKE "%%"

group by vm_uuid

SELECT t_agent.ip_addr, t_kvm_backupjobruns.pm_uuid, t_kvm_backupjobruns.vm_uuid AS `虚机UUID`, t_kvm_backupjobruns.backup_type AS `备份类型`, t_kvm_backupjobruns.`status` AS `状态`, t_kvm_backupjobruns.backup_log AS `日志`, t_kvm_backupjobruns.start_time AS `发起时间`, t_kvm_backupjobruns.stop_time AS `结束时间` FROM t_kvm_backupjobruns LEFT JOIN t_agent ON t_kvm_backupjobruns.pm_uuid = t_agent.uuid WHERE t_kvm_backupjobruns.start_time > "%%" AND t_kvm_backupjobruns.`status` LIKE "%%" AND t_kvm_backupjobruns.backup_type LIKE "%%" AND t_kvm_backupjobruns.backup_log LIKE "%%" AND t_kvm_backupjobruns.vm_uuid LIKE "%%" ORDER BY `发起时间` DESC;

目前计算节点全部安装完成，虚机任务、策略也已配置完毕

未完成的工作：

备份虚机很多失败的，目前正在排查错误，

网关没有安装系统，需要安装

SELECT

t_agent.ip_addr,

t_kvm_backupjobruns.pm_uuid,

t_kvm_backupjobruns.vm_uuid AS `虚机UUID`,

t_kvm_backupjobruns.backup_type AS `备份类型`,

t_kvm_backupjobruns.`status` AS `状态`,

t_kvm_backupjobruns.backup_log AS `日志`,

t_kvm_backupjobruns.start_time AS `发起时间`

FROM

t_kvm_backupjobruns

LEFT JOIN t_agent ON t_kvm_backupjobruns.pm_uuid = t_agent.uuid

WHERE

t_kvm_backupjobruns.start_time > "2020-01-09 00:00:00" AND

t_kvm_backupjobruns.backup_log LIKE "%%" AND

t_kvm_backupjobruns.`status` LIKE "%444%"

ORDER BY

`发起时间` DESC

yum install s3fs-fuse -y

chmod 600 /etc/s3cred

echo "admin:admin123" > /etc/s3cred

s3fs test001 /opt/webhooks -o passwd_file=/etc/s3cred,use_path_request_style,url=http://192.168.3.193:9000

Oracle 任务运行情况：

只看成功的

SELECT

t_oracle_jobruns.agent_id,

t_oracle_jobruns.agent_name,

t_oracle_jobruns.abs_path,

t_oracle_jobruns.backup_log,

t_oracle_jobruns.start_time

FROM

t_oracle_jobruns

WHERE

t_oracle_jobruns.backup_type = "1"

AND t_oracle_jobruns.`status` = "666"

ORDER BY

t_oracle_jobruns.start_time DESC

·

SELECT

t_agent.ip_addr,

t_oracle_jobruns.job_name,

t_oracle_jobruns.backup_type,

t_oracle_jobruns.abs_path,

t_oracle_jobruns.backup_log,

t_oracle_jobruns.`status`,

t_oracle_jobruns.start_time

FROM

t_oracle_jobruns

LEFT JOIN t_agent ON t_oracle_jobruns.agent_id = t_agent.uuid

WHERE

t_oracle_jobruns.backup_type LIKE "%%" AND

t_oracle_jobruns.start_time > "2020-04-02" AND

t_agent.ip_addr LIKE "%%" AND

t_oracle_jobruns.`status` LIKE "%444%"

ORDER BY

t_oracle_jobruns.start_time ASC\G;

SELECT

t_agent.ip_addr,

t_oracle_backupjobruns.agent_name,

t_oracle_backupjobruns.job_name,

t_oracle_backupjobruns.backup_type,

t_oracle_backupjobruns.abs_path,

t_oracle_backupjobruns.backup_log,

t_oracle_backupjobruns.`status`,

t_oracle_backupjobruns.start_time

FROM

t_oracle_backupjobruns

LEFT JOIN t_agent ON t_oracle_backupjobruns.agent_id = t_agent.uuid

WHERE

t_oracle_backupjobruns.backup_type LIKE "%"

AND t_oracle_backupjobruns.start_time > "2020-04-2"

AND t_agent.ip_addr LIKE "%%"

AND t_oracle_backupjobruns.`status` LIKE "%444%"

ORDER BY

t_oracle_backupjobruns.start_time DESC

Oracle资产统计

SELECT

t_agent.ip_addr AS `Oracle数据库ip`,

t_oracle_backupjobs.agent_name AS `Oracle数据库主机名`,

t_oracle_backupjobs.agent_id,

t_oracle_instance.database_name AS `数据库名`,

t_oracle_instance.instance_name AS `实例名`,

t_oracle_instance.sys_passwd,

t_oracle_instance.listen_port AS `监听端口`,

t_agent.create_time AS `客户端创建时间`,

t_oracle_instance.`status` AS `实例状态`,

t_agent.agent_type,

t_agent.os_type

FROM

t_oracle_backupjobs

LEFT JOIN t_agent ON t_oracle_backupjobs.agent_id = t_agent.uuid

LEFT JOIN t_oracle_instance ON t_oracle_instance.id = t_oracle_backupjobs.instance_id

GROUP BY

t_oracle_backupjobs.agent_id

ORDER BY

t_agent.ip_addr ASC

Oracle 任务统计

SELECT

t_oracle_jobs.agent_name AS "Oracle数据库主机名",

t_agent.ip_addr AS "数据库服务器IP",

t_storage.host AS `存储节点主机名`,

t_storage.mgr_ip AS `存储节点管理IP`,

t_storage.data_ip,

t_storage.data_dir AS `存储位置`,

t_oracle_instance.instance_name AS `备份实例`,

t_oracle_jobs.job_name AS `备份任务名`,

t_oracle_jobs.`status` AS `任务状态`,

t_policy.backup_type AS `备份类型`,

t_oracle_jobs.policy_id AS `策略id`,

t_policy.policy_name AS `策略名`,

t_oracle_jobs.versions AS `保留版本`,

t_policy.description,

t_policy.year,

t_policy.month,

t_policy.day,

t_policy.week,

t_policy.day_of_week,

t_policy.hour,

t_policy.minute,

t_policy.second

FROM

t_oracle_jobs

LEFT JOIN t_agent ON t_oracle_jobs.agent_id = t_agent.uuid

LEFT JOIN t_storage ON t_oracle_jobs.storage_uuid = t_storage.uuid

LEFT JOIN t_oracle_instance ON t_oracle_instance.id = t_oracle_jobs.instance_id

LEFT JOIN t_policy ON t_oracle_jobs.policy_id = t_policy.id

where t_storage.uuid like "%%"

ORDER BY

t_agent.ip_addr ASC

SELECT

t_oracle_backupjobs.agent_name AS `Oracle数据库主机名`,

t_agent.ip_addr AS `数据库服务器IP`,

t_storage.`host` AS `存储节点主机名`,

t_storage.mgr_ip AS `存储节点管理IP`,

t_storage.data_ip,

t_storage.data_dir AS `存储位置`,

t_oracle_instance.instance_name AS `备份实例`,

t_oracle_backupjobs.job_name AS `备份任务名`,

t_oracle_backupjobs.`status` AS `任务状态`,

t_policy.backup_type AS `备份类型`,

t_oracle_backupjobs.policy_id AS `策略id`,

t_policy.policy_name AS `策略名`,

t_oracle_backupjobs.versions AS `保留版本`,

t_policy.description,

t_policy.`year`,

t_policy.`month`,

t_policy.`day`,

t_policy.`week`,

t_policy.day_of_week,

t_policy.`hour`,

t_policy.`minute`

FROM

t_oracle_backupjobs

LEFT JOIN t_agent ON t_oracle_backupjobs.agent_id = t_agent.uuid

LEFT JOIN t_storage ON t_oracle_backupjobs.storage_uuid = t_storage.uuid

LEFT JOIN t_oracle_instance ON t_oracle_instance.id = t_oracle_backupjobs.instance_id

LEFT JOIN t_policy ON t_oracle_backupjobs.policy_id = t_policy.id

ORDER BY

`数据库服务器IP` ASC

mysql资产统计

SELECT

t_agent.ip_addr AS `mysql客户端ip`,

t_mysql_jobs.agent_name AS `主机名`,

t_mysql_jobs.agent_id,

t_mysql_instance.username AS `用户名`,

t_mysql_instance.`password` AS `密码`,

t_mysql_instance.instance_name AS `实例名`,

t_storage.mgr_ip AS `存储管理ip`,

t_storage.data_ip AS `存储数据ip`,

t_storage.`host` AS `存储主机名`,

t_mysql_jobs.storage_uuid AS `存储uuid`,

t_storage.data_dir AS `数据存放目录`,

t_storage.username AS `存储用户名`,

t_storage.`password` AS `存储密码`,

t_storage.storage_type,

t_storage.service_type,

t_agent.agent_type,

t_agent.os_type

FROM

t_mysql_jobs

LEFT JOIN t_mysql_instance ON t_mysql_jobs.instance_id = t_mysql_instance.id

LEFT JOIN t_storage ON t_mysql_jobs.storage_uuid = t_storage.uuid

LEFT JOIN t_policy ON t_mysql_jobs.policy_id = t_policy.id

LEFT JOIN t_agent ON t_agent.uuid = t_mysql_jobs.agent_id

GROUP BY

t_mysql_jobs.agent_id

ORDER BY

`mysql客户端ip` ASC

MySQL 任务统计

SELECT

t_agent.ip_addr AS `mysql客户端ip`,

t_mysql_jobs.agent_name AS `主机名`,

t_mysql_jobs.agent_id,

t_mysql_instance.username AS `用户名`,

t_mysql_instance.`password` AS `密码`,

t_mysql_jobs.job_name AS `任务名`,

t_mysql_jobs.backup_type AS `备份类型`,

t_mysql_jobs.policy_id AS `策略id`,

t_policy.description AS `策略描述`,

t_mysql_jobs.`day`,

t_mysql_jobs.`week`,

t_mysql_jobs.day_of_week,

t_mysql_jobs.`hour`,

t_mysql_jobs.`minute`,

t_mysql_jobs.`second`,

t_mysql_jobs.versions AS `保留版本`,

t_mysql_instance.instance_name AS `实例名`,

t_storage.mgr_ip AS `存储管理ip`,

t_storage.data_ip AS `存储数据ip`,

t_storage.`host` AS `存储主机名`,

t_mysql_jobs.storage_uuid AS `存储uuid`,

t_storage.data_dir AS `数据存放目录`,

t_storage.username AS `存储用户名`,

t_storage.`password` AS `存储密码`,

t_storage.storage_type,

t_storage.service_type

FROM

t_mysql_jobs

LEFT JOIN t_mysql_instance ON t_mysql_jobs.instance_id = t_mysql_instance.id

LEFT JOIN t_storage ON t_mysql_jobs.storage_uuid = t_storage.uuid

LEFT JOIN t_policy ON t_mysql_jobs.policy_id = t_policy.id

LEFT JOIN t_agent ON t_agent.uuid = t_mysql_jobs.agent_id

ORDER BY

`mysql客户端ip` ASC

文件备份资产统计：

SELECT

t_agent.ip_addr AS `文件备份客户端IP`,

t_file_backupjobs.agent_name AS `主机名`,

t_file_backupjobs.agent_id,

t_agent.agent_type,

t_agent.os_type,

t_storage.storage_type,

t_storage.service_type

FROM

t_file_backupjobs

LEFT JOIN t_agent ON t_file_backupjobs.agent_id = t_agent.uuid

LEFT JOIN t_storage ON t_file_backupjobs.storage_uuid = t_storage.uuid

GROUP BY

t_file_backupjobs.agent_id

ORDER BY

t_agent.ip_addr ASC

文件备份任务统计

SELECT

t_agent.ip_addr AS `文件备份客户端IP`,

t_file_backupjobs.agent_name AS `主机名`,

t_file_backupjobs.agent_id,

t_file_backupjobs.backup_type AS `备份类型`,

t_policy.description AS `策略描述`,

t_file_backupjobs.`day`,

t_file_backupjobs.`week`,

t_file_backupjobs.day_of_week,

t_file_backupjobs.`hour`,

t_file_backupjobs.`minute`,

t_file_backupjobs.`second`,

t_file_backupjobs.versions AS `保留版本`,

t_policy.id AS `策略ID`,

t_storage.mgr_ip AS `存储管理ip`,

t_storage.data_ip AS `存储数据ip`,

t_storage.`host` AS `存储主机名`,

t_storage.data_dir AS `存储目录`,

t_storage.username AS `存储用户名`,

t_storage.`password` AS `存储密码`,

t_agent.agent_type,

t_agent.os_type,

t_storage.storage_type,

t_storage.service_type

FROM

t_file_backupjobs

LEFT JOIN t_agent ON t_file_backupjobs.agent_id = t_agent.uuid

LEFT JOIN t_storage ON t_file_backupjobs.storage_uuid = t_storage.uuid

LEFT JOIN t_policy ON t_file_backupjobs.policy_id = t_policy.id

ORDER BY

t_agent.ip_addr ASC

systemctl status nbp-agent

SELECT

t_agent.ip_addr AS `文件备份客户端IP`,

t_file_jobs.agent_name AS `主机名`,

t_file_jobs.agent_id,

t_file_jobs.backup_type AS `备份类型`,

t_policy.description AS `策略描述`,

t_file_jobs.`day`,

t_file_jobs.`week`,

t_file_jobs.day_of_week,

t_file_jobs.`hour`,

t_file_jobs.`minute`,

t_file_jobs.`second`,

t_file_jobs.versions AS `保留版本`,

t_policy.id AS `策略ID`,

t_storage.mgr_ip AS `存储管理ip`,

t_storage.data_ip AS `存储数据ip`,

t_storage.`host` AS `存储主机名`,

t_storage.data_dir AS `存储目录`,

t_storage.username AS `存储用户名`,

t_storage.`password` AS `存储密码`,

t_agent.agent_type,

t_agent.os_type,

t_storage.storage_type,

t_storage.service_type

FROM

t_file_jobs

LEFT JOIN t_agent ON t_file_jobs.agent_id = t_agent.uuid

LEFT JOIN t_storage ON t_file_jobs.storage_uuid = t_storage.uuid

LEFT JOIN t_policy ON t_file_jobs.policy_id = t_policy.id

ORDER BY

t_agent.ip_addr ASC

文件任务运行

SELECT

t_agent.ip_addr,

t_agent.`host`,

t_file_jobruns.backup_type,

t_file_jobruns.`status`,

t_file_jobruns.backup_log,

t_file_jobruns.start_time

FROM

t_file_jobruns

LEFT JOIN t_agent ON t_agent.uuid = t_file_jobruns.agent_id

WHERE

t_file_jobruns.start_time > "2020-03-01"

ORDER BY

t_file_jobruns.start_time DESC

nfv任务运行情况

SELECT

t_agent.ip_addr,

t_agent.host,

t_nfv_backupjobruns.job_name,

t_nfv_backupjobruns.status,

t_nfv_backupjobruns.backup_type,

t_nfv_backupjobruns.backup_log,

t_nfv_backupjobruns.start_time,

t_nfv_backupjobruns.stop_time,

t_storage.uuid

FROM

t_nfv_backupjobruns

LEFT JOIN t_agent ON t_agent.uuid = t_nfv_backupjobruns.agent_id

LEFT JOIN t_storage ON t_storage.uuid=t_nfv_backupjobruns.storage_uuid

WHERE

t_nfv_backupjobruns. STATUS like "%444%"

AND t_nfv_backupjobruns.start_time > "2020-04-15"

AND t_agent.ip_addr like "%10.50.118.70%"

ORDER BY

start_time ASC\G;

mysqldump -uroot -p123456 nbp > /root/liwofu.sql

vi config.py

SELECT

t_agent.ip_addr,

t_nfv_backupjobruns.agent_name,

t_nfv_backupjobruns.job_name,

t_nfv_backupjobruns.storage_uuid,

t_nfv_backupjobruns.abs_path,

t_nfv_backupjobruns.backup_log,

t_nfv_backupjobruns.`status`,

t_nfv_backupjobruns.start_time

FROM

t_nfv_backupjobruns

LEFT JOIN t_agent ON t_agent.uuid = t_nfv_backupjobruns.agent_id

WHERE

t_nfv_backupjobruns. STATUS like "%444%"

AND t_nfv_backupjobruns.start_time > "2020-03-1"

AND t_nfv_backupjobruns.job_name like "%DC1-Director-1%"

ORDER BY

start_time ASC\G;

nfv备份集

SELECT

t_agent.ip_addr,

t_nfv_backups.job_name,

t_nfv_backups.agent_name,

t_nfv_backups.backup_type,

t_nfv_backups.abs_path,

t_nfv_backups.create_time,

t_nfv_backups.`status`,

t_storage.mgr_ip

FROM

t_nfv_backups

LEFT JOIN t_storage ON t_nfv_backups.storage_uuid = t_storage.uuid

LEFT JOIN t_agent ON t_nfv_backups.agent_id = t_agent.uuid

WHERE

t_agent.ip_addr LIKE "%10.50.118.70%"

AND t_nfv_backups.create_time > "2020-04-12"

ORDER BY

t_nfv_backups.create_time ASC\G;

SELECT

t_agent.ip_addr,

t_nfv_backups.job_name,

t_nfv_backups.agent_name,

t_nfv_backups.backup_type,

t_nfv_backups.abs_path,

t_nfv_backups.create_time,

t_nfv_backups.`status`,

t_storage.mgr_ip

FROM

t_nfv_backups

LEFT JOIN t_storage ON t_nfv_backups.storage_uuid = t_storage.uuid

LEFT JOIN t_agent ON t_nfv_backups.agent_id = t_agent.uuid

WHERE

t_agent.ip_addr LIKE "%10.50.118.70%"

AND t_nfv_backups.create_time > "2020-04-01"

ORDER BY

t_nfv_backups.create_time ASC;

NFV任务统计详情

SELECT

t_agent.ip_addr AS `NFV计算节点IP`,

t_nfv_backupjobs.agent_name AS `NFV计算节点主机名`,

t_nfv_backupjobs.job_name AS `任务名`,

t_nfv_backupjobs.backup_type AS `备份类型`,

t_nfv_backupjobs.policy_id AS `策略ID`,

t_nfv_backupjobs.versions AS `保留版本`,

t_nfv_backupjobs.`year`,

t_nfv_backupjobs.`month`,

t_nfv_backupjobs.`day`,

t_nfv_backupjobs.`week`,

t_nfv_backupjobs.day_of_week,

t_nfv_backupjobs.`hour`,

t_nfv_backupjobs.`minute`,

t_nfv_backupjobs.`second`,

t_storage.`host` AS `存储节点主机名`,

t_storage.mgr_ip AS `存储节点IP`,

t_storage.data_dir AS `备份文件存储路径`

FROM

t_nfv_backupjobs

LEFT JOIN t_agent ON t_agent.uuid = t_nfv_backupjobs.agent_id

LEFT JOIN backup_policy ON t_nfv_backupjobs.policy_id = backup_policy.id

LEFT JOIN t_storage ON t_nfv_backupjobs.storage_uuid = t_storage.uuid

where t_nfv_backupjobs.job_name="DC2-12-05-06"

ORDER BY

t_nfv_backupjobs.agent_name ASC\G

update t_nfv_backupjobs set t_nfv_backupjobs.hour="04",minute="00" where t_nfv_backupjobs.job_name="DC2-12-05-06";

ORACLE

51 DED38B12-38BD-44DE-AF57-B81BAAD5AEE7

103 fb71c686-823a-43cd-b606-6e051864f299

2201f4fc60b-3093-48be-8b32-d5712c8ca7ca

cd /opt/NDP/silk && python setup.py develop && cd /opt/NDP/nbp && python setup.py develop