innotop MySql tools.

Description:innotop monitors MySQL servers. Each of its modes shows you a different
aspect of what's happening in the server. For example, there's a mode for
monitoring replication, one for queries, and one for transac-tions.
innotop refreshes its data periodically, so you see an updating view.
innotop has lots of features for power users, but you can start and run it with virtually no configuration. If you're just getting started, see "QUICK-START". Press '?' at any time while running innotop for context-sensitive help.
How to install the innotop:
1. 下載 innotop 1.7
2. 解開，並執行
tar zxpf innotop-1.7.0.tar.gz
cd innotop-1.7.0
perl Makefile.PL
make
make install
3. 如果看到以下的message, 請到step 4
Warning: prerequisite Term::ReadKey 2.1 not found.
4. 安裝 perl 的相關套件，請執行以下指令，也請確保你的網路是通的..它需要下載套件下來安裝，並且gcc有安裝:
perl -MCPAN -eshell
install Term::ReadKey
install DBI
install DBD::mysql
5. 若沒有安裝default mysql package 會發現 install DBD::mysql 會失敗，這時請執行以下的方法安裝:

cd ~/.cpan/build/DBD-mysql-4.013

perl Makefile.PL --with-mysql=/opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.0.52-linux-x86_64-glibc23 --mysql_config=/opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.0.52-linux-x86_64-glibc23/bin/mysql_config

make
make test
make install

若是使用redhat 可以安裝rpm 檔
rpm -ivh perl-DBI-1.52-1.fc6.x86_64.rpm perl-DBD-MySQL-3.0007-1.fc6.x86_64.rpm mysql-5.0.22-2.1.x86_64.rpm
6. 若安裝完Step 4，請再執行Step2
7. 執行innotop
innotop --password admin --socket /opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.0.52-linux-x86_64-glibc23/mysql.sock

[root@localhost innotop-1.7.2]# innotop --password admin --socket /opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.0.52-linux-x86_64-glibc23/mysql.sock

http://crazytoon.com/2008/05/21/mysql-how-do-you-install-innotop-to-monitor-innodb-in-real-time/
http://crazytoon.com/2007/05/21/perl-how-do-i-install-perl-modules-eg-installing-lwp-module-for-perl/
http://www.lamp-tips.com/man-pages/innotop/

check the DB lock

mysql> use itms;
mysql> show engine innodb status;
mysql> tee innodb.txt
mysql> show engine innodb status;
mysql> tee off;
mysql>exit

vi innodb.txt

mysql> show innodb status;

mysql command

mysql> select apId, mac from zdap where deviceId=78 and state=1 and visible=1 and model='zf7942';

select * from zdap where mac='00:a3:00:00:01:60';

select * from zdap where deviceId=78 and state=1 and visible=1;
select count(*) from zdap where visible=1 and state=0;
mysql> select count(*) from zdap where visible=1;
mysql> select count(*) from zdap where visible=1 and meshAPType='RAP'
mysql> select count(*) from zdap where visible=1 and meshAPType='MAP';
mysql> select count(*) from zdap where visible=1 and meshAPType='LAP';
mysql> select count(*) from zdap where visible=1 and meshAPType=NULL;
mysql> select count(*) from zdap where state=1; =>Approval
mysql> select count(*) from zdap where state=2; => Approval Pending

how to knwo how many devices register to FM from MySql.

SQLyou commend:

select i.* from device d, inventory i where d.inventoryid= i.inventoryid and d.connect = 0
select i.* from device d, inventory i where d.inventoryid= i.inventoryid and d.connect = 1
select d.* from device d, inventory i where d.inventoryid= i.inventoryid and d.connect = 1

0 = disconnect
1= connect

select * from hotzone_associate where check_time > '2009-06-18 16:00:00' and check_time < '2009-06-18 17:00:00' select * from inventory where serialNumber like '%159' select * from hotzone_associate_client where hotzone_associateId= 21543 select * from device where deviceId = 273 select * from hotzone_associate where ap_count > 100 and deviceId = 273
select count(*) from deviceattr

select * from device order by deviceId asc limit 20;

mysql command

path:
/opt/FlexMaster/3rdparty/mysql/mysql-enterprise-5.0.52-linux-i686/bin/
Get into the mysql command:
[root@localhost bin]# ./mysql -uroot -p --socket=../mysql.sock
./bin/mysql -u root -p --socket=../mysql.sock

the mysql connamd:
mysql> grant all privileges on *.* to root@"%";
mysql> grant all privileges on itms.* to root@'%' identified by 'admin';
mysql> select * from task;
mysql> Select * from task_attrs where taskId=135;

sqlyog:
grant all privileges on *.* to root@"%" identified by 'ruckus' with grant option;
Check the task:
mysql> select * from task_device where taskId=734 and isCompleted=0;
Count the event amount:
mysql>use itms;
mysql>select count(*) from event;

Delete entries:
delete from connectivity;
delete from status_msg;
delete from hotzone_associate;
delete from event;
delete from deviceattr;
delete from devicemethods;
delete from deviceparams;
delete from task_attrs;
delete from task_device;
delete from task;
delete from device;
delete from auto_config_rule;
delete from inventory_comments;
delete from inventory;