mysqldump

Transferring data between two tables/two databases can be achieved by

insert into table1 select * deom database2.table2;

But they are not on the same host you can use following :

$ mysqldump -h host_name -u user_name -p database table_name "--where= date = 20111121" > temp

We can use "-t" option which will exclude table creation definition from the output.

Finding latest modified file/directory

Another useful command

$ find . -exec stat \{} --printf="%y %n\n" \; | sort -n -r | head -1

$ for i in `ls`; do echo -n $i" "; find $i -exec stat \{} --printf="%y %n\n" \; | sort -n -r | head -1;done


Mysql Tricks

• How to get current time

select now();
select curdate();
select curtime();

• How to get date in standard format

select date from table;

will give answer as "2011-08-15"
But

select date+0 from table;

will give answer as "20110815"

• To use group by with a summary.

select col from .... group by col with rollup;

This will give a summary(addition) in the last row.

• When performing string operations

Shell "*" => mysql "%"
Shell "?" => mysql "_"

• Conditional statements in mysql

select (sum(case when shares>0 then shares else - end) from table;

• Case-sensetive comparison

select 'A' like 'a';
1
select 'A' like binary 'a';
0

• Using variables

set @total=0;select (@total := 2+@total) as total from table;

• Checking table sizes

select table_schema "Database name", sum(data_length _ index_length)/1024/1024 "size", sum (data_free)/1024/1024 "Free space " from information_schema.TABLES group by table_schema;

• Command line execution and output
  

bash# mysql -e "select * from table";
mysql> select * into outfile "/tmp/hpatil" from ...;

But watchout for mysqld host and users when searching for outfile on commandline.

Linux/Unix NFS bug

Sometimes it seems that we are not able to 'touch' the file becaue of error "already exists", but we try to delete it we get an error "file does not exists".

To solve this issue,
We need to create a temp file.
mv to original file
and then delete this file.

Now, we will be able to create a new file in same location.

Finding database size through mysql query


SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;


My bashrc

This is a initial setup. You can work on things similar to these.





PATH=$PATH:.:~/hpatil/0/
export EDITOR=/usr/bin/vim

PROMPT_COMMAND='echo -ne "\033]0;${USER}@${HOSTNAME%%.*}:${PWD/#$HOME/~}"; echo -ne "\007"'
PS1="[\t \u@\h:\w]$ "
export HISTSIZE=10000
export HISTCONTROL=erasedups
shopt -s histappend

BC_ENV_ARGS=~/.bcrc
export BC_ENV_ARGS
export INPUTRC=~/.inputrc

#alias vim="vim -c 'set nopaste'"
alias ll="ls -l"
alias rl="readlink -f"
alias vim='vim -c"set nopaste" '
alias vr='vim -MR -c"set nopaste" '
alias li='less -niFX'


alias grep_dir='_(){ bd;grep -a "mt=j" app.$1.tlog | grep -av "not active" | li;}; _'

Setting autofs timeout option

This is the time after which autofs will unmount the volume if kept idle. It can be set in file /etc/sysconfig/autofs.

OPTIONS="--timeout=86400"


Compression bzip2 command

Good and simple compression command

find ./04 -type f | grep -v bz2 | xargs bzip2