MySQL related usefull tips and commands

MySQL_usefull_commands

# To kill sleeping connecions in MySQL #   :

mysqladmin -u root --password=xxxx  proc | grep Sleep | awk '{print $2}' | while read LINE; do mysqladmin -u root --password=xxxxx kill $LINE; done

#  Every 1 second run this command to see overall Queries/Threads Connected / and running queries at this moment

mysqladmin ext -i1 -u root -p12345 | awk '/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

######################################################################
mysql -e "SHOW PROCESSLIST\G" -u root -p12345 | grep State: | sort | uniq -c | sort -rn
Warning: Using a password on the command line interface can be insecure.
     15   State: Waiting for an event from Coordinator
      1   State: Waiting for master to send event
      1   State: Slave has read all relay log; waiting for the slave I/O thread to update it
      1   State: init
######################################################################

# To monitor traffic for 3306 port #       :

ngrep -q -t src remote_host and dst local_host and tcp port 3306 > /home/output.txt