MySQL Tips : Verify database Objects

Verify database Objects:

1. Verify table.
a. SHOW TABLE STATUS LIKE '%user%'\G
b. SHOW TABLE STATUS from forum LIKE '%user%' \G

2. It will show you all tables have MyISAM engine.
a. SELECT TABLE_SCHEMA, table_name, table_type, engine From information_schema.tables where engine='MyISAM';

3. To know rows per table, you can use a query like this:
a. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'forum';

4. Verify you procedure / function.
a. SHOW procedure/FUNCTION STATUS LIKE ´hello´G
b. show create procedure/function hello\G

5. Routines
a. select routine_name from information_schema.routines; // list of all the routines in the system
b. select routine_name, routine_schema, routine_type from information_schema.routines; // This lists all of the routines in the system. With additional Information like the database the routines belongs too and also distinct between the different routines.
c. select routine_name, routine_schema, routine_type from information_schema.routines where routine_schema = database() // This time we can see just the routines for the currently selected database.


6. Triggers
a. select TRIGGER_SCHEMA,TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS;
b. select TRIGGER_NAME from INFORMATION_SCHEMA.TRIGGERS where TRIGGER_SCHEMA = database();

MySQL Tips : Calculate database and table size

Few MySQL Tips:

a. calculate databases size :
-> It will show database size

SELECT s.schema_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in("mysql","information_schema","test") GROUP BY s.schema_name order by Data_size DESC;

-> It will show database size along with table size.

SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in ("mysql","information_schema") GROUP BY s.schema_name,t.table_name,TABLE_ROW_FORMAT,ENGINE ORDER BY TABLE_ROWS DESC,data_size DESC,index_size DESC;

Temporarily stop/start a process in linux

Some time we have requirement that, particular job should stop for certain period of time and start again.

Most of us familiar with KILL command, but here is another feature of KILL command , which saves your life :-

#kill -STOP 10067 (where 10067 is process id)

#kill -CONT 10067 (where 10067 is process id)

have a fun :)

Extract single db/table from dump file

using sed to extract single database from the dumpfile:
=========================================
sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile > dbname.sql 2>error

eg:
sed -n '/^-- Current Database: `blogs`/,/^-- Current Database: `/p' dump.sql > blogs.sql 2>error

we can also use "awk" for the same.

extract single table from the dumpfile:-

nohup : commands keep executing even you exit from a shell prompt

nohup command if added in front of any command will continue running the command or process even if you shut down your terminal or close your session to machine

nohup command-name &

Where,

* command-name : is name of shell script or command name. You can pass argument to command or a shell script.
* & : nohup does not automatically put the command it runs in the background; you must do that explicitly, by ending the command line with an & symbol.

examples:

# nohup mysql -q -uUSER1 -pPASS1 < dump.sql > dump.log 2> error.log &


source:
http://www.idevelopment.info/data/Unix/General_UNIX/GENERAL_RunningUNIXCommandsImmunetoHangups_nohup.shtml

http://www.cyberciti.biz/tips/nohup-execute-commands-after-you-exit-from-a-shell-prompt.html

Reset your MySQL Table Auto Increment Value

Recently,I created tables using auto increment number as the Primary Key. As name say, the number will increase every time I insert new record into the table. At some point I wanted to delete the whole dummy data I inserted.

ALTER TABLE tablename AUTO_INCREMENT = value;

eg.
If I had a items table and I created a few new items and then deleted them, to set the auto increment value back to ‘100′ i would simply:

ALTER TABLE items AUTO_INCREMENT = 100;

MySQL log file rotation

Rotating MySQL Log Files on Linux.
A. Check logrotate must on daily cron
#less /etc/cron.daily/logrotate

B. Check on logrotate.d there must be mysql
#less /etc/logrotate.d/mysql
Its default Content is:

# by setting the variable "err-log"
# in the [safe_mysqld] section as follows:
#
# [safe_mysqld]
# err-log=/var/lib/mysql/mysqld.log
#
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password =
# user= root
#
# where "" is the password.
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !

/var/lib/mysql/mysqld.log {
# create 600 mysql mysql
notifempty
daily
rotate 3
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &>/dev/null
then
/usr/bin/mysqladmin flush-logs
fi
endscript
}

Read uses of variable
My file :

# This logname can be set in /etc/my.cnf
# by setting the variable "err-log"
# in the [safe_mysqld] section as follows:
#
# [safe_mysqld]
# err-log=/var/lib/mysql/mysqld.log
#
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password =
# user= root
#
# where "" is the password.
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !

# - I put everything in one block and added sharedscripts, so that mysql gets
# flush-logs'd only once.
# Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
daily
rotate 7
missingok
create 640 mysql adm
compress
sharedscripts
postrotate
test -x /usr/bin/mysqladmin || exit 0

# If this fails, check debian.conf!
export HOME=/etc/mysql/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
# Really no mysqld or rather a missing debian-sys-maint user?
# If this occurs and is not a error please report a bug.
if ps cax | grep -q mysqld; then
exit 1
fi
else
$MYADMIN flush-logs
fi
endscript

C. Create a file /root/.my.cnf.
#vi /root/.my.cnf (# ATTENTION: This /root/.my.cnf should be readable ONLY)

Its contain ;

[mysqladmin]
password = xxxx
user= root

Run manually ;

#logrotate -f /path/to/some/logrotate.conf
if you want to force a rotatation which is uncalled for in the config files, you need to give it a "-f":