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
220 1f4fc60b-3093-48be-8b32-d5712c8ca7ca

 

 

 

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