Find Table Size :
mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES               WHERE TABLE_SCHEMA = 'mrsqa4'; +-----------------+ | SUM(TABLE_ROWS) | +-----------------+ |           10655 | +-----------------+ 1 row in set (0.29 sec)
 pc_auditlog               |          1 | | pc_dbobject               |        475 | | pc_dbrconfig              |          2 | | pc_iclass_sig             |       1660 | | pc_namespace              |          8 | | pc_namespace_sequence     |          0 | | pc_ormstate           …

               Capturing MySQL Queries

I know there is mytop, mtop, and innodbtop out there, just google them, but it can be very hard to Freeze The Frame to get every query.  So, here’s a quick and dirty script to catch the queries and see what you users are doing.

#!/bin/sh #./run_show_full_processlist.ksh > SHOW_PROCCESSLIST.doc &

x=1

while [ $x -le 3600 ]                      #Loop for 1 Hour (60sec * 60 = 3600)

do

echo "START TIME"

date

echo "show full processlist;" | mysql –h HOSTNAME -uUSERNAME -pPASSWD

  x=$(( $x + 1 ))

echo "END DATE"

date

sleep …

Display All CPU(s) With the top Command

As you know, the top command displays All CPU(s) so it is difficult to identify if All CPU(s) are pegged.  Interactively, you can toggle top by pressing the Number 1 on the keyboard, however that toggle cannot be scripted.

The work around is to create a .toprc file in your home directory by pressing the W key (capital W) while top is running.  If you are paying attention, you will see a message that the .toprc file was just created.  From there on out, typing top will display All CPU(s).

Below is a script I run to capture top activity during peak loads.  Make sure you have enough disk space!

vi run_top.sh

#!/bin/sh

#Syntax Below

#./run_top.sh > TOP_UNDER_LOAD.doc

x=1

while [ $x -le 4000 ]        #Run 4000 Times

…

Locking User Accounts in MySQL

For SOX Compliance when an employee, contractor, or vendor leaves your company their account needs to be locked, expired, or removed.  Some of these people may return, and their Account may have many complicated permissions.  In these cases, it would be better to “Lock” their Account while they are no longer doing business with your company. 
However, MySQL does not have the ability to “Lock” or “Expire” a User Account.   The following is a simple procedure to “Lock” and “Unlock” a MySQL Account.

Typically, the one-way encrypted hash value of a users password is stored in a password field  in an user account table.  MySQL supports three builtin one-way hashed functions, PASSWORD(), MD5() and SHA1().  It is recommended not to directly use the PASSWORD() function. Reason, is that prior to PHP 4.1 version, the …

MySQL – Script to Kill A User Session

I googled around and found some code that could be used with a few tweaks to kill all users EXCEPT the User(s) you specify.

#vi kill_all_users.sh

#!/bin/bash

connections=$(mysql -h$1 -u$2 -p$3 -e "SHOW PROCESSLIST" |grep -v wmayall| grep -v Id | grep -v root|awk '{print $1}' )

for id in $connections

do

    mysql -h$1 -u$2 -p$3 -e "KILL $id"

done

#chmod 700 kill_all_users.sh

The syntax is:

./kill_all_users.sh  –host –user –password:

Below is an example:

./kill_all_users.sh localhost root my_password

The grep –v excludes the User you do NOT want to kill their session.  
If the User is connected through ODBC, a new …

How to Setup MySQL Replication
Configure the Master Database :
First you will need to configure the my.cnf file.  The my.cnf file is not located in the same place on all servers, so you should use the ps command to list how mysql was started.
[root@hostname repl_setup]$ ps -ef | grep mysql
mysql 6290 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf --pid-file=/apps/mysql/mysql.pidmysql 6318 6290 0 May18 ? 00:48:49 /usr/sbin/mysqld --defaults-file=/apps/mysql/my.cnf --basedir=/usr --datadir=/apps/mysql --pid-file=/apps/mysql/mysql.pid --skip-external-locking --port=3300 --socket=/apps/mysql/mysql.sockmysql 6473 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps2/mysql/my.cnf --pid-file=/apps2/mysql/mysql.pidmysql 6501 6473 0 May18 ? 00:19:18 /usr/sbin/mysqld --defaults-file=/apps2/mysql/my.cnf --basedir=/usr --datadir=/apps2/mysql …

MySQL – When an Optimize Fails
When using the MyISAM engine, you may need to run an “optimize” to regain space and increase performance.  What do you need to know BEFORE you start the “optimize” command.

The first thing to know is the “optimize” creates a Temporary file in the database datadir, (see mr.TMD).  The problem is there is only 16GB of free space with the mr.MYD and mr.MYI are almost 40GB.  There is one more thing you need to know.  MySQL uses tmpdir at the end of the “optimize” which can be a problem if there is not enough space.

SERVER1:/db/mysql/data/stats>ls -lt kh*                                                                   -rw-rw---- 1 pm pmapp 18163175424 Mar 23 …

Scripts I Use to Isolate MySQL Performance Problems

#!/bin/sh
#./run_iostat.sh > REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START RUN TIME"
date
/usr/bin/iostat -d 2 6
date
  x=$(( $x + 1 ))
date
echo "END RUN TIME"
sleep 60
done


#!/bin/sh
#./run_mysqlreport_sleep60.sh > REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START TIME"
date
mysqlreport –user USER --password PASSWD --host HOST --port 3306
  x=$(( $x + 1 ))
echo "END DATE"
sleep 60
done


#!/bin/sh
#./run_iostat.sh > VMSTAT_REPORT.doc &
x=1
while [ $x -le 60 ]
do
echo "START TIME"
date
/usr/bin/vmstat
/usr/bin/vmstat -d
  x=$(( $x + 1 ))
echo "END DATE" …

=================================================MySQL Configuration Changes

 =================================================This document is intended to explain how MySQL creates Temporary Tables and how to tune the MySQL Configuration file to improve query performance for Power Users.  MySQL will create a Temporary Table when a user issues a Group By or Order By command.  
MySQL will first create the Temporary Table in Memory, if the query exceeds the value of certain parameters in the my.cnf file, MySQL will use the MyISAM Storage Engine creating *.MYI and *.MYD files in the /tmp directory.  When MySQL has to create the *.MYI and *.MTD files the query performance will be slower than creating the Temporary Tables in Memory.

I created a Test on dl4dev to illustrate how MySQL creates Temporary Tables and how changing the MySQL Configuration file will improve performance.  My plan was …

MySQL 8 features invisible indexes. But what good is an index if you can see it? br>Is It Really InvisibleYou probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help.

mysql> CREATE TABLE t2 (
    ->   i INT NOT NULL,
    ->   j INT NOT NULL,
    ->   UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8);
Query OK, 4 rows affected (0.00 sec)

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | …