MySQL « ross beazley

Archive for the ‘MySQL’ Category

MySQL Tips : Calculate database and table size

Tuesday, December 29th, 2009

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Warning: preg_replace() [function.preg-replace]: Compilation failed: unknown option bit(s) set at offset 0 in /var/www/wordpress/wp-includes/shortcodes.php on line 227

Original Post by Prabhat Kumar

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;

-> It will show table size of engine 'MEMORY'.

SELECT concat(table_schema,'.',table_name) as Database_Tablename, table_rows as Rows, concat(round(data_length/(1024*1024),2),'M') DATA, concat(round(index_length/(1024*1024),2),'M') idx, concat(round((data_length+index_length)/(1024*1024),2),'M') total_size, round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where ENGINE='MEMORY';

-> It will show 5 top tables using space.

SELECT concat(table_schema,'.',table_name) as Database_Tablename,
table_rows as Rows, concat(round(data_length/(1024*1024),2),'M') DATA,
concat(round(index_length/(1024*1024),2),'M') idx, concat(round((data_length+index_length)/(1024*1024),2),'M') total_size,
round(index_length/data_length,2) idxfrac FROM information_schema.TABLES where ORDER BY data_length+index_length DESC limit 5;

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

Extract single db/table from dump file

Wednesday, November 4th, 2009

Original Post by Prabhat Kumar

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:-

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

Reset your MySQL Table Auto Increment Value

Thursday, October 8th, 2009

Original Post by Prabhat Kumar

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;

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

MySQL log file rotation

Sunday, September 13th, 2009

Original Post by Prabhat Kumar

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":

Prabhat Kumar original post

Posted in Linux, MySQL, Open Source | No Comments »

MySQL: Analyze slow query log using mysqldumpslow

Wednesday, September 9th, 2009

Original Post by Prabhat Kumar

First enable slow query logging, then generate a slow query and finally look at the slow query log.

mysqldumpslow
This program parses and summarizes a 'slow query log'.
-v verbose
-d debug
-s=WORD
what to sort by (t, at, l, al, r, ar etc)
-r reverse the sort order (largest last instead of first)
-t=NUMBER
just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n=NUMBER
abstract numbers with at least n digits within names
-g=WORD
grep: only consider stmts that include this string
-h=WORD
hostname of db server for *-slow.log filename (can be wildcard)
-i=WORD
name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
eg.

1. mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_query.txt
It will put top ten slow query in file /tmp/top_ten_slow_query.txt
2. mysqldumpslow -a -s c -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_repeat_slow_query.txt
It will put top ten repeat slow query in file top_ten_repeat_slow_query.txt

Prabhat Kumar original post

Posted in Linux, MySQL, Open Source | No Comments »

MySQL Replication : Purged binary logs

Wednesday, July 29th, 2009

Original Post by Prabhat Kumar

Yesterday I have found that there is no space left on server of MySQL master and on Slave. Once I debugged I have come to know that there is GB's of bin-log files on Master and relay-log on Slave.

Its due to I have forget to add a expire_logs_days Variable in my.cnf during the configuration of replication server.

# expire_logs_days = 7

It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log switch.

Or, You can also delete bin-log manually using command :

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Prabhat Kumar original post

Posted in MySQL, Open Source | No Comments »

MYSQL: slow queries log

Thursday, July 2nd, 2009

Original Post by Prabhat Kumar

MySQL has built-in functionality that allows you to log SQL queries to a file , You can enable the full SQL queries logs to a file or only slow running queries log. It is easy for us to troubleshoot/ debug the sql statement if SQL queries log enable , The slow query log is used to find queries that take a long time to execute and are therefore candidates for optimization.

To enable you just need to add some lines to your my.cnf file, and restart. Add the following:

* To enable slow Query Log only

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

After enabling slow query, mysqld writes a statement to the slow query log file and it consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld only log after SQL statements has been executed and after all locks have been released, so log order might be different from execution order. The minimum and default values of long_query_time are 1 and 10, respectively.

* To enable full Log Query

log=/var/log/mysqldquery.log

The above will log all queries to the log file.

Selecting Queries to Optmize
• The slow query log
– Logs all queries that take longer than long_query_time
– Can also log all querie s that don’t use indexes with
--log-queries-not-using-indexes
– To log slow administatve commands use
--log-slow-admin-statements
– To analyze the contents of the slow log use
mysqldumpslow

Prabhat Kumar original post

Posted in Linux, MySQL, Open Source | No Comments »

ross beazley

Archive for the ‘MySQL’ Category

MySQL Tips : Calculate database and table size

Extract single db/table from dump file

Reset your MySQL Table Auto Increment Value

MySQL log file rotation

MySQL: Analyze slow query log using mysqldumpslow

MySQL Replication : Purged binary logs

MYSQL: slow queries log

recent posts

Archives

Archive for the ‘MySQL’ Category

recent posts

Tag Cloud

Archives