Highlighted
3 Argentum

Useful commands for MySQL

The following are some useful commands when searching the MYSQL DB:

Useful commands for MySQL

Get variable id# from the data property flat for a specific metric: select id,device,part,parttype,name from data_property_flat where device =”<DeviceName>” and name =”<MetricName>”;

Get all the metric values from a given cache group table: select * from cache_group_<TimeAggregate>_<timestamp> where variable = <Variable ID#>;

Get the variable’s name: select * from data_variable where id=<variable id#>;

Get the full size of the database: select concat (format(sum(data_length + index_length)/ power(1024,2),2), ' Mb') as`Size` from information_schema.tables where tables.table_type = 'base table';

Get the metric count: select count(*) from data_property where name=”<DeviceName>”;

Get the metric count by device: select distinct source, devtype, count(*) as ct_metrics from data_property_flat group by source, devtype order by source, devtype;

Get a count of inactive variables: select count(*) from data_variable where id in (select id from data_property_flat where vstatus='inactive');

Get a count of inactive properties: select count(*) from data_property where variableid in (select id from data_property_flat where vstatus='inactive');

Find the last X variables that where created: select * from data_variable order by id desc limit <X>;

Get a count of metrics per data group: select datagrp,count(*) as total from data_property_flat group by datagrp order by total desc;

Getting a list of inactive device in a text file for review: select data_variable.name,data_property_flat.device from data_property_flat left join data_variable on data_variable.id = data_property_flat.id and data_property_flat.vstatus=”inactive” into outfile “/”;

Manually (re)creating the admin account in the master db: insert into users.master (id, username, password, rolename, disabled) values (“1”, “admin”, “changeme”, “admin”, “0”);

Manually changing the admin password: update master.users set password = “<NewPassword>” where username=“admin”;

Reply