show mysql database all tables

mysql> show table status from pubccc;

perl install UserAgent.pm error message "Connection timed out"

1, perl -MCPAN -e shell


2, install LWP::UserAgent
Connection timed out

Solution:
Firewall block the connection, so need to stop iptables

ubuntu static ip

vim /etc/network/interfaces

auto lo
iface lo inet loopback
auto eth0
#iface eth0 inet dhcp
iface eth0 inet static
address 192.168.1.6
netmask 255.255.255.0
network 192.168.1.0
broadcast 192.168.1.255
gateway 192.168.1.99

gunzip: aaa.zip: unknown suffix -- ignored

I used 7z to zip this file under Windows, try to unzip it under linux

[ang@walker temp]$ gunzip 2011.sdf.zip
gunzip: 2011.sdf.zip: unknown suffix -- ignored


Try to us unzip, instead of gunzip

[chang@walker temp]$ unzip 2011.sdf.zip
Archive: 2011.sdf.zip
inflating: 2011.sdf

LVM add harddisk

1 ifconfig
2 fdisk -l
3 aptitude update && sudo aptitude safe-upgrade
4 apt-get update
5 apt-get upgrade
6 fdisk -l
7 pvcreate /dev/sdb1
8 fdisk /dev/sdb -l
9 vgdisplay
10 df
11 vgscan
12 vgdisplay
13 vgextend PubChem /dev/sdb1
14 df
15 lvextend -L128G /dev/mapper/PubChem-root
16 lvextend -L500G /dev/mapper/PubChem-root
17 lvextend -L480G /dev/mapper/PubChem-root
18 lvextend -L400G /dev/mapper/PubChem-root
19 df
20 resize2fs /dev/mapper/PubChem-root
21 df
22 shutdown now
23 df
24 sudo -s
25 history

ubuntu add second disk

1, sudo fdisk -l

root@ubuntu:~/pubchem/process_sdf# fdisk -l

Disk /dev/sda: 250.1 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders, total 488397168 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000b6fa8

Device Boot Start End Blocks Id System
/dev/sda1 * 2048 499711 248832 83 Linux
/dev/sda2 501758 488396799 243947521 5 Extended
/dev/sda5 501760 488396799 243947520 8e Linux LVM

Disk /dev/sdb: 250.0 GB, 250000000000 bytes
255 heads, 63 sectors/track, 30394 cylinders, total 488281250 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x41ab2316

Device Boot Start End Blocks Id System
/dev/sdb1 63 80324 40131 12 Compaq diagnostics
/dev/sdb2 * 80325 481949999 240934837+ fd Linux raid autodetect
/dev/sdb3 481966065 488263544 3148740 fd Linux raid autodetect
/dev/sdb4 481950061 481966064 8002 5 Extended
/dev/sdb5 481950063 481966064 8001 8e Linux LVM

Partition table entries are not in disk order

Disk /dev/sdc: 250.1 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders, total 488397168 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00055dc9

Device Boot Start End Blocks Id System
/dev/sdc1 * 63 488392064 244196001 8e Linux LVM

mysql add AUTO_INCREMENT

1, if the table no primary key, you need add primary key first

mysql> alter table blacklist add primary key (alarmID) ;

2, mysql> alter table blacklist modify alarmID int(10) unsigned AUTO_INCREMENT, AUTO_INCREMENT=50000;

mysqldump with where condition

mysqldump -u user -ppasswd yourDB yourTable --where="service_category_id in (261, 262)"> signatures_blacklist.sql

line terminator

The line terminator expected for each file format is:

unix LF only (each line ends with an LF character).
dos CRLF (each line ends with two characters, CR then LF).
mac CR only (each line ends with a CR character).
CR is carriage return (return cursor to left margin), which is Ctrl-M or ^M or hex 0D.
LF is linefeed (move cursor down), which is Ctrl-J or ^J or hex 0A. Sometimes, LF is written as NL (newline).

Mac OS version 9 and earlier use mac line endings, while Mac OS X and later use unix line endings.


Carriage Return (^M or \r).

LF:    Line Feed, U+000A
 VT:    Vertical Tab, U+000B
 FF:    Form Feed, U+000C
 CR:    Carriage Return, U+000D
 CR+LF: CR (U+000D) followed by LF (U+000A)
 NEL:   Next Line, U+0085
 LS:    Line Separator, U+2028
 PS:    Paragraph Separator, U+2029

mysql concat string

update compounds set link=concat("http://www.chemmol.com/chemmol/", mol_id , ".html") ;

Remove blank lines

Type the following command:
$ sed '/^$/d' input.txt > output.txt
Task: Remove blank lines using grep

$ grep -v '^$' input.txt > output.txt

alter table defalut value

alter table kid_profiles alter head_circumference_for_age set default 0;

How to create duplicate table in MySQL?

mysql> create table signatures_20110921 select * from signatures;

mysql remove table name space

You should be able to reference that table using `backticks` or `Acute` , not single quote

mysql>rename table `CDC_Stature_for_age_charts_2 _20` to CDC_Stature_for_age_charts_2_20;


Here is reference:
http://www.grassrootsdesign.com/intro/char.php

Google PageRank Historical Analysis

Cool tool:
Check your website Historical Google PageRank
http://3wtemplates.com/tools/pagerank_checker/pr_report.php

redhat apache issue: caught SIGTERM, shutting down

issue:
========================================
[Thu Jul 28 11:48:34 2011] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jul 28 11:48:34 2011] [notice] Digest: generating secret for digest authentication ...
[Thu Jul 28 11:48:34 2011] [notice] Digest: done
[Thu Jul 28 11:48:34 2011] [notice] Apache/2.2.3 (Red Hat) configured -- resuming normal operations
[Thu Jul 28 11:52:34 2011] [notice] caught SIGTERM, shutting down
[Thu Jul 28 11:52:37 2011] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jul 28 11:52:37 2011] [notice] Digest: generating secret for digest authentication ...
[Thu Jul 28 11:52:37 2011] [notice] Digest: done
[Thu Jul 28 11:52:37 2011] [notice] Apache/2.2.3 (Red Hat) configured -- resuming normal operations
[Thu Jul 28 11:58:45 2011] [notice] caught SIGTERM, shutting down
[Thu Jul 28 11:58:48 2011] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jul 28 11:58:48 2011] [notice] Digest: generating secret for digest authentication ...
[Thu Jul 28 11:58:48 2011] [notice] Digest: done
[Thu Jul 28 11:58:48 2011] [notice] Apache/2.2.3 (Red Hat) configured -- resuming normal operations
[Thu Jul 28 12:07:43 2011] [notice] caught SIGTERM, shutting down
[Thu Jul 28 12:13:07 2011] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jul 28 12:13:07 2011] [notice] Digest: generating secret for digest authentication ...
[Thu Jul 28 12:13:07 2011] [notice] Digest: done
[Thu Jul 28 12:13:07 2011] [notice] Apache/2.2.3 (Red Hat) configured -- resuming normal operations
[Thu Jul 28 12:18:07 2011] [notice] caught SIGTERM, shutting down
[Thu Jul 28 12:18:12 2011] [notice] suEXEC mechanism enabled (wrapper: /usr/sbin/suexec)
[Thu Jul 28 12:18:12 2011] [notice] Digest: generating secret for digest authentication ...
[Thu Jul 28 12:18:12 2011] [notice] Digest: done
[Thu Jul 28 12:18:12 2011] [notice] Apache/2.2.3 (Red Hat) configured -- resuming normal operations

==================================
solution:
I forgot to open port 80 in firewall

[root@aaa var]# /sbin/service iptables stop

open port 80:
[root@localhost ~]# vim /etc/sysconfig/iptables
# add this line
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT

[root@aaa var]# /sbin/service iptables start

how to add linkedin share button

link:

http://www.linkedin.com/publishers

how to add twitter to your website

LINK:
http://twitter.com/about/resources/tweetbutton

how to add facebook like button

1, login to your facebook account

2, open this page
http://developers.facebook.com/docs/reference/plugins/like/

jpgraph: The function imageantialias() is not available

error message:

Fatal error: Call to undefined function imageantialias() in /var/www/vhosts/bar.php on line 13

The function imageantialias() is not available in your php installation. Use the GD version that comes with PHP and not the standalone version.

SOLUTION:
Open the file jpgraph.x.x.x/src/gd_image.inc.php, find the function SetAntiAliasing and comment this line (about line 110 in the version jpgraph-3.5.0b1):

else {
//JpGraphError::RaiseL(25128);//('The function imageantialias() is not available in your PHP installation. Use the GD version that comes with PHP and not the standalone version.')
}

wget folder

wget -m http://www.chemmol.com/images/

duplicate mysql table

mysql> CREATE TABLE products_excel_20110711 select * from products_excel;

protocol.rb:212:in `initialize': No such file or directory - /tmp/mysql.sock

Ruby mysql error

===================
chang@cm-test:/aost$ ruby gen_pot.rb
/usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql/protocol.rb:212:in `initialize': No such file or directory - /tmp/mysql.sock (Errno::ENOENT)
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql/protocol.rb:212:in `new'
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql/protocol.rb:212:in `block in initialize'
from /usr/local/lib/ruby/1.9.1/timeout.rb:44:in `timeout'
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql/protocol.rb:209:in `initialize'
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql.rb:110:in `new'
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql.rb:110:in `connect'
from /usr/local/lib/ruby/gems/1.9.1/gems/ruby-mysql-2.9.4/lib/mysql.rb:42:in `new'
from gen_blogspot.rb:9:in `

'
chang@cm-test:/opt/pubchem/ruby/autopost$ ^C


solution:
1, install
sudo apt-get install libmysqlclient15-dev

2
reinstall mysql gem
sudo gem install mysql

check tomcat version on linux

[root@localhost ~]# /opt/tomcat/bin/version.sh
Using CATALINA_BASE: /opt/tomcat
Using CATALINA_HOME: /opt/tomcat
Using CATALINA_TMPDIR: /opt/tomcat/temp
Using JRE_HOME: /usr/java/default
Using CLASSPATH: /opt/tomcat/bin/bootstrap.jar
Server version: Apache Tomcat/6.0.32
Server built: February 2 2011 2003
Server number: 6.0.32.0
OS Name: Linux
OS Version: 2.6.18-194.el5PAE
Architecture: i386
JVM Version: 1.6.0_21-b06
JVM Vendor: Sun Microsystems Inc.
[root@localhost ~]#

show qpid user list

sasldblistusers2 -f /etc/qpid/qpidd.sasldb

sasldblistusers2 -f /var/lib/qpidd/qpidd.sasldb

# saslpasswd2 -f /var/lib/qpidd/qpidd.sasldb -u realm new_user_name

To list the users in the SASL database, use sasldblistusers2:

# sasldblistusers2 -f /var/lib/qpidd/qpidd.sasldb

shell script get yesterday yyyymmdd format

#!/bin/bash

yesterday=`/bin/date --date="-1 days" +%Y%m%d`

echo $yesterday

One_month_ago=`/bin/date --date="-30 days" +%Y%m%d`

echo $One_month_ago

disable selinux - centos

1,
echo 0 > /selinux/enforce

2,[root@localhost ~]# less /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted - Only targeted network daemons are protected.
# strict - Full SELinux protection.
SELINUXTYPE=targeted

# SETLOCALDEFS= Check local definition changes
SETLOCALDEFS=0

Install locate and updatedb on CentOS

yum install mlocate

use namp to detect remote box port

root@test:~# nmap -sU -p1434 -PO -sS 175.x.2.x

Starting Nmap 5.00 ( http://nmap.org ) at 2011-06-15 16:57 EDT
Interesting ports on 175.x.2.x:
PORT STATE SERVICE
1434/tcp filtered ms-sql-m
1434/udp open|filtered ms-sql-m

linux script loop and math

#!/bin/bash

for a in 0 1 2 3 4 5 6 7 8 9 10 11
do
let limit=$a*40000
api="select concat('\"',CAS,'\"',',','\"',Names,'\"') from webcas.cas_name where CAS REGEXP '[0-9]{2,7}-[0-9]{1,2}-[0-9]{1}' limit $limit, 40000;"
echo "$api"
mysql -u chem -ppw webcas -e "$api" > api.$a.csv
done

linux zip a folder

zip -9 -r amadis.zip images

mysql show stored procedure

mysql> show create procedure truncate_all_data;
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-------------------+----------+------------------------------------------------------+----------------------+----------------------+--------------------+
| truncate_all_data | | CREATE DEFINER=`amp_db_user`@`localhost` PROCEDURE `truncate_all_data`()
BEGIN
truncate table file_capture_events;
truncate table capture_files;

call sp_audit('truncate_all_data','Call truncate_all_data store procedure');

END | latin1 | latin1_swedish_ci | latin1_swedish_ci |
+-------------------+----------+------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

cronjob every hr

* * * * * command to be executed
- - - - -
| | | | |
| | | | |
| | | | +----- day of week (0 - 7) (Sunday=0 or 7)
| | | +---------- month (1 - 12)
| | +--------------- day of month (1 - 31)
| +-------------------- hour (0 - 23)
+------------------------- min (0 - 59)

ruby date format yyyymmdd

[root@nt cronjobs]# irb
irb(main):001:0> today = Time.now.strftime("%Y%m%d").to_i
=> 20110513

mysql ERROR No. 1030: Got error 28 from storage engine

Disk is out of space

[root@reyotest stat]# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda3 231825440 219859768 0 100% /
/dev/sda1 101086 22394 73473 24% /boot
tmpfs 1037048 0 1037048 0% /dev/shm

perl error: Can't locate LWP/UserAgent.pm in @INC

need to install libwww-perl

1, download package from cpan:
wget http://www.cpan.org/modules/by-module/LWP/libwww-perl-5.837.tar.gz

2, unzip it
tar xvzf libwww-perl-5.837.tar.gz

3, install pkg
cd libwww-perl-5.837
perl Makefile.PL
make
make install

Unable to open env file: /etc/default/locale

To fix
"Unable to open env file: /etc/default/locale: No such file or directory"
in /var/log/auth.log, run:

update-locale

vim: convert the ^M linebreak to 'normal' linebreak

^M is ctrl+v and ctrl+M
:%s/^M/\r/g

mysql group by and having

mysql> select CASNumber, count(CASNumber) as ccas from compounds group by CASNumber having ccas=1 order by ccas ;

sed: -e expression #1, char 24: unknown option to `s'

When you call sed in ruby, you may get this error message

here is the ruby code

def update_server (conf_file_path, config )
puts "update #{conf_file_path}"
%x[sed "s/drs_db.*\/drs_db/drs_db mmmmmmmm \/drs_db/g" #{conf_file_path}]
end

correct:
def update_server (conf_file_path, config )
puts "update #{conf_file_path}"
%x[sed "s/drs_db.*\\/drs_db/drs_db mmmmmmmm \\/drs_db/g" #{conf_file_path}]
end

add a line in a file

add a line after line: [mysqld]
sed -i '/\[mysqld\]/ a\bind-address = 6.31.83.19' test.cnf

how to check mysql version

mysql> SELECT version();
+--------------------+
| version() |
+--------------------+
| 5.0.51a-3ubuntu5.7 |
+--------------------+
1 row in set (0.00 sec)

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 153 days 19 hours 36 min 16 sec

enable Telnet client in Windows Vista

1. Open the Programs and Features Control Panel
2. Select "Turn Windows features on or off."
3. Select the Telnet Client option and click OK.

vim find replace regex

Try to remove \n before the last "
Update:
"B160102","4-Nitropicolinaldehyde","108338-19-8
"
To:
"B160102","4-Nitropicolinaldehyde","108338-19-8"

:s%/-[0-9]\zs\n"\ze

\zs and \ze to set the start and end of a pattern.

mysql change root password

$ mysqladmin -u root password NEWPASSWORD

How to find the total number of files in a folder

root@209:/temp# ls /var/htdocs/temp | wc
19767 19767 212692

Disabling root SSH logins.

CentOS:

1,
Edit the /etc/ssh/sshd_config file and set the PermitRootLogin parameter to no.

[root@sor ~]# vim /etc/ssh/sshd_config
PermitRootLogin no


2, restart ssh deamon
[root@sor ~]# /etc/init.d/sshd restart

remove all cronjob using linux command

crontab /dev/null

Using vim as a hex editor - replace windows chars

For example: <99> <B0>

:%s/\%x99//g

:%s/\%xB0//g

Switch to hex mode
Open a file in vim, hit escape and type:
:%!xxd

to exit from hex mode
hit escape again and type:
:%!xxd -r

Search Hex using vim
\%xff or [\xff]

Search 44
\%x34\%x34 or [\x34][\x34]

• LF:    Multics, Unix and Unix-like systems (GNU/Linux, Mac OS X, FreeBSD, AIX, Xenix, etc.), BeOS, Amiga, RISC OS and others.
• CR+LF: Microsoft Windows, DEC TOPS-10, RT-11 and most other early non-Unix and non-IBM OSes, CP/M, MP/M, DOS (MS-DOS, PC-DOS, etc.), Atari TOS, OS/2, Symbian OS, Palm OS

LF:    Line Feed, U+000A
 CR:    Carriage Return, U+000D 

Run MySQL SQL queries on Linux command line

This is a shell script

#!/bin/bash

deletelist="delete from capture_files where date(updated_on) < DATE_SUB(CURdate(), INTERVAL 30 DAY);"

mysql -u dbuser -pdbpasswd amp -e "$deletelist"

Argument list too long - rm files failed

Use find and rm to delete files:

find /var/mol/temp/ -name *.txt -exec rm -rf {} \;

Enable Remote Access To MySQL Server

1, update my.cnf to make it liston to ip address, not localhost

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
bind-address = 6.31.83.19

2, Save the file and Restart the mysql server, enter:
# /etc/init.d/mysql restart

3, Update your mysql database user privileges
A: If you want to update existed account, you may do:
mysql> update user set Host='%' where user='greg';
mysql> flush privileges ;

Host='%' means allow user greg access this mysql server from any IP address.
If you want a specified IP, you can do
mysql> use mysql
mysql> update user set Host='6.6.6.6' where user='greg';
mysql> flush privileges ;

B, Or you can use grant
mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO greg@'6.6.6.6' IDENTIFIED BY 'PASSWORD';
mysql> flush privileges ;

You don't need to restart mysql for step 3

linux remove the first line of a file

Two ways:
1, generate new file
sed 1d delete_old_capture_file.dump > delete_old_capture_file.list

2, update same file
sed -i 1d /tmp/delete_old_capture_file.dump

linux read line one by one from a file

[root@localhost ~]# cat delete_old_capture_file.sh
#!/bin/bash

while read line
do
echo "Delete $line" >> /var/log/delete_old_captured_file.log
find /opt/ntr/file_capture/ -name $line -exec rm -rf {} \;
done < /tmp/file_capture_delete.list

Save MySQL query results into a text file

There are 2 ways to do it.
1, save the result to a file in the sql query directly
select file_name
from files
limit 3
into OUTFILE '/tmp/file_capture_delete.list';


[root@localhost file_capture]# cat /tmp/file_capture_delete.list
20110311-154002_0001.bin
20110311-154002_0002.bin
20110311-154002_0003.bin

2, dump the results to a file
A, create a mysql file
[root@localhost delete_old_capture_file]# cat delete_old_capture_file.mysql
select file_name
from capture_files
limit 5

B, Run the mysql and dump the results to a file
mysql -u username -ppassword DBname < delete_old_capture_file.mysql > delete_old_capture_file.dump

Linux Delete Files/Folder Older Than n Days

[root@localhost /]# find /your/folder/ -mtime +30 -exec rm -rf {} \;

Test it:
1, create a new folder
mkdir 20090909

[root@localhost xxx]# ll
drwxr-xr-x 2 root root 4096 Mar 11 16:25 20090909

2, change folder time
touch -t 200909090909 20090909/

3, check time
[root@localhost xxx]# touch -t 200909090909 20090909/
[root@localhost xxx]# ll
drwxr-xr-x 2 root root 4096 Sep 9 2009 20090909

4, delete files and folder older than 30 days

[root@localhost xxx]# find /your/folder/ -mtime +30 -exec rm -rf {} \;

[root@localhost xxx]# ll
drwxr-xr-x 2 esl esl 20480 Mar 11 16:21 20110311

linux change folder date

Need to change the folder date

I created a folder on 20110311

[root@localhost file_capture]# mkdir 20110101
[root@localhost file_capture]# ll
drwxr-xr-x 2 root root 4096 Mar 11 16:13 20110101


Change the date using touch

[root@localhost file_capture]# touch -t 201101010101
touch: missing file operand
Try `touch --help' for more information.
[root@localhost file_capture]# touch -t 201101010101 20110101/
[root@localhost file_capture]# ll
total 24
drwxr-xr-x 2 root root 4096 Jan 1 01:01 20110101

[root@localhost file_capture]# touch --help
Usage: touch [OPTION]... FILE...
Update the access and modification times of each FILE to the current time.

Mandatory arguments to long options are mandatory for short options too.
-a change only the access time
-c, --no-create do not create any files
-d, --date=STRING parse STRING and use it instead of current time
-f (ignored)
-m change only the modification time
-r, --reference=FILE use this file's times instead of current time
-t STAMP use [[CC]YY]MMDDhhmm[.ss] instead of current time
--time=WORD change the specified time:
WORD is access, atime, or use: equivalent to -a
WORD is modify or mtime: equivalent to -m
--help display this help and exit
--version output version information and exit

Note that the -d and -t options accept different time-date formats.

If a FILE is -, touch standard output.

Report bugs to .

mysql select entryby date range

select * from capture_files where date(created_on) < DATE_SUB(CURdate(), INTERVAL 30 DAY) and date(updated_on) < DATE_SUB(CURdate(), INTERVAL 30 DAY);

Assign Values to variables from a text file

[root@localhost xxx]# cat test.sh

#!/bin/bash

read a < process.log
echo $a


results:
[root@localhost xx]# ./test.sh
mysql-bin.000005

linux synchronize two folder

Sync local folders:

rsync -avz /var/lib/mysql/mysql-bin.* /target/folder/

–a for archive mode (preserve all the attributes of each file and directory – ownership, permissions, etc),
-v for verbose mode (report a list of files processed by rsync)
-z for data compression to speed transfers up.

Sync remote folders over ssh:

rsync -avz ssh /root/origbinlog/* root@1922.168.1.196:/root/mysqlrecovery/binlogs/


DiskStation_230> rsync -avz chang@180:/usr/local/apache2/htdocs/RR/endeavor/download/dirty/ /volume1/lab/pdf/

cronjob every n minutes

22,58 * * * * your command here

*/5 * * * * rsync -avz /var/lib/mysql/mysql-bin.* /root/origbinlog/ >> rsync.log
every 5 minutes

mysqldump some tables

[root@localhost glen]# mysqldump -u username -pxxxxx DB users user_role role groups > user_group_role.mysql
[root@localhost glen]# less user_group_role.mysql

check cpu information on Linux machine

[root@localhost ~]# cat /proc/cpuinfo | grep core
core id : 0
cpu cores : 4
core id : 1
cpu cores : 4
core id : 2
cpu cores : 4
core id : 3
cpu cores : 4

linux cut last n chars

I want to cut last 17 chars

Before:
[root@localhost mysqlrecovery]# echo $a
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 mysql-bin.000011 mysql-bin.000012

After:
[root@localhost mysqlrecovery]# echo $a | rev | cut -c17- | rev
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 mysql-bin.000011

shell script variables assignment

lastfile=`tail -n 1 mysql-bin.index`
echo $lastfile

mysql backup a table / duplicate a table

mysql> create table compounds_201110223 like compounds;
Query OK, 0 rows affected (0.00 sec)

mysql> insert compounds_201110223 select * from compounds;

mysql insert ignore on duplicate key

INSERT IGNORE INTO all_cas (CAS, sid, source)
( SELECT DISTINCT(cas), mid, 4 FROM chemmolpharm.compounds WHERE cas REGEXP '[0-9]{2,7}-[0-9]{1,2}-[0-9]{1}' );

mysql update comments to table

ALTER TABLE all_cas COMMENT='source 1 aaa 2 ChemMol.com' ;

mysql "ERROR 1018 (HY000): Can't read dir of '.' (errno: 13)"

issue:

mysql "ERROR 1018 (HY000): Can't read dir of '.' (errno: 13)"

Solution
Restart mysql server
root@cm-test:~# service mysql stop
root@cm-test:~# service mysql start

ls ll with regular express

#!/bin/bash

for f in $(ls | egrep 'mysql-bin.0[0-9]{5}$')
do
echo "process file: $f"
done



[root@localhost recovery]# ll mysql-bin.0[0-9][0-9][0-9][0-9][0-9]
-rw-r----- 1 root root 23022429 Feb 23 06:13 mysql-bin.000001
-rw-r----- 1 root root 456769 Feb 23 06:13 mysql-bin.000002
-rw-r----- 1 root root 47295 Feb 23 06:13 mysql-bin.000003
-rw-r----- 1 root root 679680 Feb 23 06:13 mysql-bin.000004
-rw-r----- 1 root root 1742441 Feb 23 06:13 mysql-bin.000005
-rw-r----- 1 root root 5671825 Feb 23 06:13 mysql-bin.000006
-rw-r----- 1 root root 5588178 Feb 23 06:13 mysql-bin.000007
-rw-r----- 1 root root 5312684 Feb 23 06:13 mysql-bin.000008
-rw-r----- 1 root root 5569825 Feb 23 06:13 mysql-bin.000009
-rw-r----- 1 root root 5653535 Feb 23 06:13 mysql-bin.000010
-rw-r----- 1 root root 5361382 Feb 23 06:13 mysql-bin.000011
-rw-r----- 1 root root 5722384 Feb 23 06:13 mysql-bin.000012
-rw-r----- 1 root root 5759352 Feb 23 06:13 mysql-bin.000013
-rw-r----- 1 root root 6006372 Feb 23 06:13 mysql-bin.000014
-rw-r----- 1 root root 5645165 Feb 23 06:13 mysql-bin.000015
-rw-r----- 1 root root 5508167 Feb 23 06:13 mysql-bin.000016
-rw-r----- 1 root root 6258305 Feb 23 06:13 mysql-bin.000017
-rw-r----- 1 root root 5959473 Feb 23 06:13 mysql-bin.000018
-rw-r----- 1 root root 5743501 Feb 23 06:13 mysql-bin.000019
-rw-r----- 1 root root 5618976 Feb 23 06:13 mysql-bin.000020
-rw-r----- 1 root root 4157667 Feb 23 06:13 mysql-bin.000021
[root@localhost recovery]# ll | egrep "mysql-bin.0[0-9]{5}$"
-rw-r----- 1 root root 23022429 Feb 23 06:13 mysql-bin.000001
-rw-r----- 1 root root 456769 Feb 23 06:13 mysql-bin.000002
-rw-r----- 1 root root 47295 Feb 23 06:13 mysql-bin.000003
-rw-r----- 1 root root 679680 Feb 23 06:13 mysql-bin.000004
-rw-r----- 1 root root 1742441 Feb 23 06:13 mysql-bin.000005
-rw-r----- 1 root root 5671825 Feb 23 06:13 mysql-bin.000006
-rw-r----- 1 root root 5588178 Feb 23 06:13 mysql-bin.000007
-rw-r----- 1 root root 5312684 Feb 23 06:13 mysql-bin.000008
-rw-r----- 1 root root 5569825 Feb 23 06:13 mysql-bin.000009
-rw-r----- 1 root root 5653535 Feb 23 06:13 mysql-bin.000010
-rw-r----- 1 root root 5361382 Feb 23 06:13 mysql-bin.000011
-rw-r----- 1 root root 5722384 Feb 23 06:13 mysql-bin.000012
-rw-r----- 1 root root 5759352 Feb 23 06:13 mysql-bin.000013
-rw-r----- 1 root root 6006372 Feb 23 06:13 mysql-bin.000014
-rw-r----- 1 root root 5645165 Feb 23 06:13 mysql-bin.000015
-rw-r----- 1 root root 5508167 Feb 23 06:13 mysql-bin.000016
-rw-r----- 1 root root 6258305 Feb 23 06:13 mysql-bin.000017
-rw-r----- 1 root root 5959473 Feb 23 06:13 mysql-bin.000018
-rw-r----- 1 root root 5743501 Feb 23 06:13 mysql-bin.000019
-rw-r----- 1 root root 5618976 Feb 23 06:13 mysql-bin.000020
-rw-r----- 1 root root 4157667 Feb 23 06:13 mysql-bin.000021

mysql show variables

mysql> show variables like '%bin%';
+-----------------------------------------+------------+
| Variable_name | Value |
+-----------------------------------------+------------+
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_bin_trust_routine_creators | OFF |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 1073741824 |
| sql_log_bin | ON |
| sync_binlog | 0 |
+-----------------------------------------+------------+
11 rows in set (0.00 sec)

mysql truncate multiple tables

mysql> select concat('TRUNCATE TABLE ', TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='amp';

centos update path

echo 'export PATH=$PATH:/sbin' >> $HOME/.bash_profile

How to create a Directory with permission of drwxrwxrwt

t mean Sticky bit. It was used to trigger process to "stick" in memory after it is finished, now this usage is obsolete. Currently its use is system dependant and it is mostly used to suppress deletion of the files that belong to other users in the folder where you have "write" access to.

chmod +t mydir

passive nic config in redhat

[root@localhost testtcpreply]# vim /etc/sysconfig/network-scripts/ifcfg-eth1

# Broadcom Corporation NetXtreme BCM5721 Gigabit Ethernet PCI Express
DEVICE=eth1
BOOTPROTO=none
HWADDR=00:22:19:D5:28:B4
ONBOOT=yes
#NETMASK=255.255.255.0
#IPADDR=192.168.0.102
#GATEWAY=192.168.0.1
TYPE=Ethernet
USERCTL=no
IPV6INIT=no
PEERDNS=yes

mysql add enum column

mysql> alter table user_cart add cart_type enum('favorite','order') DEFAULT 'favorite';

mysql> alter table user_cart add add_time timestamp not null default CURRENT_TIMESTAMP;

Cannot connect to a remote CD-ROM drive using VMware Infrastructure Client

To access a remote CD-ROM drive, VI Client must have the correct permissions.

To assign the correct permission to VI Client:

1. Right-click the VI Client shortcut and click Properties.
2. Click the Emulation or Compatibility tab.
3. Select Run this program as Administrator.
4. Click OK.

Acute accent in programming

` Acute accent
'
"

[root@localhost ~]# echo `date`
Thu Feb 10 07:29:41 EST 2011
[root@localhost ~]# echo "date"
date
[root@localhost ~]# echo 'date'
date
[root@localhost ~]# echo "$date"

[root@localhost ~]# echo '$date'
$date
[root@localhost ~]# echo date
date

regular express not

[^0-9a-zA-Z\-\<\>\ \:\'\=\/\?\.\,\"\@\\r\\n\(\)]

[^0-9a-zA-Z\-\<\>\ \:\'\=\/\?\.\,\"\@\\r\\n\(\)\t\{\}]

replace text in single or multiple files

1, replace all aaaa to bbbbb in file 1.xml

root@li9:/var/htdocs/sitemaps# sed -i 's/aaaa/bbbbb/g' 1.xml

2 replace all aaaa to bbbbb in files *.xml
root@li9:/var/htdocs/sitemaps# sed -i 's/2011-02-02/2011-02-09/g' *.xml


* -i : sed will directly modify the file if it finds anything to replace
* s : substitute the following text
* g : global, match all occurrences in the line

You don't have permission to access - redhat

Issue: Couldn't display some file under www folder

Soulution:
Disable selinux

[root@localhost ~]# vim /etc/selinux/config
# disabled - SELinux is fully disabled.
#SELINUX=enforcing
SELINUX=disabled

Installing PHP > 5.2.X on CentOS 5 using yum

1, import testing repository key
rpm --import http://dev.centos.org/centos/RPM-GPG-KEY-CentOS-testing

2 download CentOS testing repoitory
cd /etc/yum.repos.d/
wget http://dev.centos.org/centos/5/CentOS-Testing.repo

3 install or update
update: yum --disablerepo=* --enablerepo=c5-testing update php
or fresh install: yum --disablerepo=* --enablerepo=c5-testing install php

4, restart httpd
/etc/init.d/httpd stop
/etc/init.d/httpd start

How can I rename table names in mysql

mysql> rename table sas to sds;

mysql create table command line

CREATE TABLE suppas (id int(15) NOT NULL auto_increment, su_id int(12) default NULL, c_id int(12) default NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

linux sort a file

greg@SS:/site# sort cas.txt > cas2.txt

linux get file line number

greg@SS:/site# wc -l cas.txt
3725 cas.txt

grep regular expression space

greg@SS:/site# less APAC.txt | grep '[0-9]\{2,7\}-[0-9]\{1,2\}-[0-9]\{1\}' > abc.txt

greg@test:/test$ less filtered.txt | grep "^[0-9]\{1,10\}[[:space:]]\{1,20\}[0-9]\{2,7\}-[0-9][0-9]-[0-9]$" > filtered.txt1

html redirect

Add this line in your homepage header: 




<meta HTTP-EQUIV="REFRESH" content="0; url=http://www.yourdomain.com/">

/bin/bash^M: bad interpreter: No such file or directory

-bash: ./2_genDB.sh: /bin/bash^M: bad interpreter: No such file or directory

Here is the solution:
http://www.linuxforums.org/forum/red-hat-fedora-linux/65469-bad-interpreter-no-such-file-directory.html

Quote:

#!/usr/bin/perl

die "Usage: $0 < files >\n" unless @ARGV;

for $file (@ARGV)
{
    open IN, $file or die "$0: Cannot open $file for input!\n";

    my @lines = <IN>;

    close IN;
    open OUT, "> $file" or die "$0: Cannot open $file for output!\n";

    s/\r$// for @lines;
    print OUT for @lines;
}

Save this file as fix_m.pl

run perl fix_m.pl yourshell.sh

check linux shell version

gregg@server:/opt/de$ echo $SHELL
/bin/bash

mysqldump no data , mysqldump a table with data

1, Dump whole DB schema
$ mysqldump --no-data -u mysqluser -pMysql_DB_PW api > api.mysql

2, Dump a table data from DB
$ mysqldump  -u mysqluser -pMysql_DB_PW  api_DB a_table  > db_table.mysql

enable htaccess

Need to update  #AllowOverride None to AllowOverride AuthConfig

<Directory "/var/www/html">

    Options Indexes FollowSymLinks

    #AllowOverride None
    AllowOverride AuthConfig

    Order allow,deny
    Allow from all


</Directory>

mysql regular express

Tel phone number 555-555-5555

SELECT * FROM phone WHERE pnumber  REGEXP '[0-9]{3}-[0-9]{3}-[0-9]{4}';


CAS number 
SELECT * FROM products WHERE cas REGEXP '[0-9]{2,7}-[0-9]{1,2}-[0-9]{1}';

sudo: no valid sudoers sources found, quitting - ubuntu

Issue:

When I tried to add a new user into sudoer file  /etc/sudoers, I need to chmod sudoers
chmod +w /etc/sudoers
Then, system gave this error:
sudo: /etc/sudoers is mode 0640, should be 0440
sudo: no valid sudoers sources found, quitting

Solution:
Enter Ubuntu recovery mode to change /etc/sudoers to 0440

enter ubuntu recovery mode in vm

press 'shift' when reboot the ubuntu in VM

how to embed google map in website

Recently, I just added gmap to my customer's website
http://northbeachhealth.com/contact.php

Here are instructions:
1, get a free google api key
Here is the page to get key
http://code.google.com/apis/maps/signup.html

2, find longitude and latitude of the address
A, enter your address in gmap
http://maps.google.com/maps?oe=utf-8&client=firefox-a&rlz=1R1GGLL_en___US406&q=367+Bay+St.,+Suite+B+San+Francisco,+Ca.+94133&um=1&ie=UTF-8&hq=&hnear=367+Bay+St,+San+Francisco,+CA+94133&gl=us&ei=Vpw4TdHXN8TflgfYmLmHBw&sa=X&oi=geocode_result&ct=title&resnum=1&ved=0CBMQ8gEwAA
B, click the 'link' on the right top of the map, you will see
'Paste link in email or IM' and
'Paste HTML to embed in website'

Copy the code in 'Paste HTML to embed in website'.
You will find 'll=37.805672,-122.413083', they are longitude and latitude of the address

View Larger Map

3, add these code into your html page head part.
Go to the page http://northbeachhealth.com/contact.php
right click the page and get source code from line 8 - line 40

  <script src="http://maps.google.com/maps?file=api&amp;v=2&amp;sensor=false&amp;key=ABQIAAAAwRhSY6F_2jelV6V3hr36IRQXzaJo3hs4ignG9MPTCEQrUyb-4RTCJR_RLSFtILRbBVU3WW33j5ikTw"
  type="text/javascript"></script>
 

    <script type="text/javascript">
    function createMarker(point,html,what)
    {
        var marker = new GMarker(point,{id:what, name:"chemmol", description:"chemalog"});
        GEvent.addListener(marker, "click", function()
        {
          marker.openInfoWindowHtml(html);
        });
        return marker;
    }
     
    function initialize()
    {
      if (GBrowserIsCompatible())
      {
            var map = new GMap2(document.getElementById("map_canvas"));
            map.setCenter(new GLatLng(37.805672,-122.413083), 16);
           
            var bz_point = new GLatLng(37.805672,-122.413083);
            var marker = createMarker(bz_point,'<div style="width:240px">BinZhi Acupuncture Clinic  <p>',"this")
            map.addOverlay(marker);     
       
            map.openInfoWindow(map.getCenter(), document.createTextNode("BinZhi Acupuncture Clinic"));
            map.setUIToDefault();
           
        }
    }

    </script>

4, in your html page body
copy
 <div id="map_canvas" style="width: 400px; height: 500px"></div>

dump mysql table to csv file

1, Login to mysql terminal:
c@cm-test:~$ mysql -u xxxxxx -pxxxxxxx mydatabase

2, run:
mysql> select * into OUTFILE 'abc.CSV' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM excel_unique;

3, the file should be in
/mydisk/lib/mysql/mydatabase/abc.CSV

mysql alter table column name

mysql> alter table compounds change CASNumber CAS varchar(15);

get table info

method 1:
mysql> show create table xxxxxxxxx;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| compounds | CREATE TABLE `xxxxx` (
`mol_id` int(10) NOT NULL AUTO_INCREMENT,
`uid` int(10) DEFAULT NULL,
`ucid` varchar(255) DEFAULT NULL,
`CASNumber` varchar(255) DEFAULT NULL,
`CName` varchar(255) DEFAULT NULL,
`SubstanceID` varchar(255) DEFAULT NULL,
`MF` varchar(255) DEFAULT NULL,
PRIMARY KEY (`mol_id`),
UNIQUE KEY `CAS` (`CASNumber`)
) ENGINE=MyISAM AUTO_INCREMENT=49400000 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

method 2:
mysql> select * from information_schema.tables WHERE table_name='table_name' and TABLE_SCHEMA='db_name';

+---------------+--------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-------------------------------------------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-------------------------------------------------+---------------+
| NULL | xxxxx | compounds | BASE TABLE | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 2048 | 0 | 49400000 | 2011-01-13 16:10:20 | 2011-01-13 16:10:20 | NULL | latin1_swedish_ci | 0 | checksum=1 delay_key_write=1 row_format=DYNAMIC | |
+---------------+--------------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-------------------------------------------------+---------------+
1 row in set (0.00 sec)

mysql reset auto_increment

alter table compounds auto_increment=49400000;

import openssl key to tomcat

1. Generate a self-signed certificate (or CA trusted certificate if needed)
1) Generate a private key
openssl genrsa -des3 -out es.key 2048
2) Generate a CSR
openssl req -new -key es.key -out es.csr
 3) Generate a self-signed certificate
openssl x509 -req -days 3650 -in es.csr -signkey es.key -out es.crt

2, Import the SSL Ceritificate into PKCS#12 keystore:
openssl pkcs12 -export -in es.crt -inkey es.key -out es.p12 -name es_tomcat

3, list privatekeyentry
keytool -list -v -keystore es.p12 -storetype pkcs12

4,Import CA/cacert.crt into the Java cacerts, so that the tomcat install can talk to itself if needed.
keytool -import -keystore es.keystore -file es.crt

5, Covert the PKCS#12 keystore to JKS keystore
keytool -importkeystore -srckeystore es.p12 -destkeystore es.jks -srcstoretype pkcs12

keytool -list -v -keystore es.jks

6 update server.xml
vim /opt/tomcat/conf/server.xml




cat /dev/null > /opt/tomcat/logs/catalina.out
/etc/init.d/tomcat stop
cat /dev/null > /opt/tomcat/logs/catalina.out
/etc/init.d/tomcat start
less /opt/tomcat/logs/catalina.out

move esx server - change ip

http://www.vladan.fr/how-to-change-ip-address-of-the-esx-server-console/

clear mysql-bin log file or disable mysql bin log

Step 1,
Go to folder
/var/lib/mysql

Step 2:
Run:
[root@localhost mysql]# mysqladmin -u xxxx -pxxxx flush-logs

Mysql will create a new bin-log file - for my server it is mysql-bin.000014
-rw-rw---- 1 mysql mysql 22770974 Jan 6 04:02 mysql-bin.000011
-rw-rw---- 1 mysql mysql 79427070 Jan 6 04:04 mysql-bin.000012
-rw-rw---- 1 mysql mysql 109593 Jan 6 04:07 mysql-bin.000013
-rw-rw---- 1 mysql mysql 1318 Jan 6 04:07 mysql-bin.000014
-rw-rw---- 1 mysql mysql 266 Jan 6 04:07 mysql-bin.index

Step 3:
You can delete all mysql-bin log file except the latest one.
Here is the sample:
rm mysql-bin.000004 -f
rm mysql-bin.000005 -f
rm mysql-bin.000006 -f
rm mysql-bin.000007 -f
rm mysql-bin.00000* -f


Delete mysql bin log file older than 2 days
find /var/lib/mysql/mysql-bin.* -mtime +2 -exec rm {} \;