Posts

Showing posts from March, 2010

[Info] Password Meter

You can test the strength of your password on this website. http://www.passwordmeter.com Wish this helps. regards, Stanley Huang

[Level 2] Install Memcached(BSD License) on CentOS 5.4

If you don't know what memcached is, please refer to the link here . 1. Download the source from official website. http://memcached.org/ 2. Unzip/Untar the source. # gzip -cd ./memcached-1.4.4.tar.gz | tar xvf - 3. Configure source/Make/Make install. # cd ./memcached-1.4.4 # ./configure # make && make install PS. In my environment, when I configure the source, the error message show up, and told me that the memcached need libevent. If you have the same problem, just download it from here . And compile it by yourself. And I create a symbolic link for libevent. # ln -s /usr/local/lib/libevent-1.4.so.2 /lib64/ libevent-1.4.so.2 4. Start memcached. # vi /etc/memcached.conf                                    ## memcached configuration file # memcached -l 127.0.0.1 -P 11211 -m 128 -d`   ## for deamon # memcached -l 127.0.0.1 -P 11211 -m 128 -vv` ## for development debug 5. test it. a. test it by command: You can telnet the memcached server with telnet command. And

[Level 2] Use _mysql Module in Python

The sample code for Python to use _mysql module: #!/usr/bin/python import _mysql ## you can pass parameter directly if you know the parameter sequence of connect() method. #cxn=_mysql.connect("localhost","root","admin123","test") ## use vp to pass parameters # cxn =_mysql.connect(host="localhost",user="root",passwd="admin123",db="test") cxn =_mysql.connect(host="localhost",port=3306,user="root",passwd="admin123",db="test") cxn .query("""SELECT user, host, password FROM mysql.user""") #cxn .execute("""SELECT user, host, password FROM mysql.user""") ## connection no execute attribute   cur= cxn .store_result() #cur= cxn .use_result() while True:   data=cur.fetch_row()   if data:     print(data)   else:     break Wish this helps. regards, Stanley Huang

[Level 3] Install Python MySQLdb.

When you want to write a Python program to connect to MySQL database, there is a useful API for Python to connect database, called "MySQLdb". There are the steps for you to install the package: 1. Download setuptools: # wget -q http://peak.telecommunity.com/dist/ez_setup.py # python ez_setup.py 2. Download MySQLdb from SourceForge : 3. Build MySQLdb and install it: (I download MySQLdb version for 1.2.3c1) # gunzip -c ./MySQL-python-1.2.3c1.tar.gz | tar xvf - # cd ./MySQL-python-1.2.3c1 # python setup.py build # python setup.py install 4. Prepare Python script. testMySQLdb.py: #!/usr/bin/python import MySQLdb cxn = MySQLdb.connect(host='localhost', db='test', user='root', passwd='admin123' ) ## execute command only cxn.query("grant all on test.* to 'stanley'@'localhost' identified by 'stanley123'") cxn.query("drop user 'stanley'@'localhost'") ## query from datab

[Level 2] How to Repair MySQL Replication.

When MySQL slave stop, how to start it. You can use the command as following: mysql> stop slave; mysql> set global sql_slave_skip_counter=1; mysql> start slave; mysql> show slave status; Wish this helps. regards, Stanley Huang

[Level 3] XtraDB: The Top 10 enhancements

http://www.mysqlperformanceblog.com/2009/08/13/xtradb-the-top-10-enhancements/ Wish this helps. regards, Stanley Huang

[Level 3] Log incoming MySQL connections to a table.

http://www.futhark.ch/mysql/143.html Wish this helps. regards, Stanley Huang

[Level 3] Partitioning new features of MySQL 5.5

http://dev.mysql.com/tech-resources/articles/mysql_55_partitioning.html Wish this helps. regards, Stanley Huang

[Level 2] Rotate MySQL General Log and Slow Query Log.

By default, general Log and slow query log will not be rotated in MySQL Server. So, we can use "logrotate" on Linux. All we have to do is to create a file named /etc/logrotate.d/mysqld, and the setting as following: /var/lib/mysql/mysqld-slow.log /var/lib/mysql/mysqld.log {     weekly     rotate 8     create 0644 mysql mysql     missingok     notifempty     sharedscripts     postrotate     # flush-logs and the MySQL account granted with RELOAD permission     /usr/bin/mysqladmin -uroot -padmin123 flush-logs     endscript } The way to test: # logrotate -fv /etc/logrotate.d/mysqld Wish this helps. regards, Stanley Huang

[Level 2] The script for get MySQL configuration.

If you want to check about MySQL configuration, you can try this script. #!/usr/bin/bash sUserHome=~ for file in `my_print_defaults  | grep \/etc\/my\.cnf` do   file=`echo $file | sed -e "s|~|$sUserHome|"`   echo "-------------------------------------------------------------------"   echo "{$file}"   perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' `echo $file`   echo "-------------------------------------------------------------------"   echo "" done After run it: # ./showMyCnf.sh ------------------------------------------------------------------- {/etc/my.cnf} [mysqld]                           log                                innodb-file-per-table              ------------------------------------------------------------------- ------------------------------------------------------------------- {/etc/mysql/my.cnf} [client]                           port            

[Level 2] Column Base Database Overview.

Column Base Database Overview: http://dev.mysql.com/tech-resources/articles/mysql-based-column-database.html http://www.mysqlperformanceblog.com/2010/01/07/star-schema-bechmark-infobright-infinidb-and-luciddb/ http://my.dimdim.com/view/all/mysqluniversity/default/b2f6d148-5b50-4900-a22b-d08cfe07b651 Wish this helps. regards, Stanley Huang

[Level 3] Create UDF for Storing MAC Address.

Last time, I introduce about MySQL have two function call inet_aton(), inet_ntoa(). These two functions are for convert IPv4 address between char and it. By reducing the size of storing IPv4 data ( less disk I/O ), to increase the query performance. Click here for reference. After that, I write the stored procedure for this purpose. Click here for reference. Now I want to show up how to create a UDF for convert MAC address data between char and byte. If you have no idea about how to create UDF, Click here for reference. The step as the following: 1. Prepare a template source code ( for C Language ) from MySQL udf sample file called 'udf_example.c'. If you don't have the MySQL source code, you can download it from official website. ( In my server, the source code folder is /usr/src/redhat/SOURCES/mysql-5.1.44/sql/udf_example.c ). 2. Prepare the source code for convert MAC address from char to byte ( you can write it by your own or google it, there are many samp

[Level 3] Control Audio Device.

My laptop will always beep on while boot, and I cannot use the command "xset -b" to turn it off. On community friend( evereasy ) told me that I can use the command "audioctl" to control it. Then I wrote a sample script for it. #!/usr/bin/bash showUsage() {   cat < <EOF Usage:   $0 device [display]   $0 device control value Ex.   $0 $sSampleDevice   $0 $sSampleDevice beep 0 Device: `listDevice | sed -e 's/^/  /'` Control($sSampleDevice): `showControl $sSampleDevice | awk '{print($2)}' | tail +1 | sed -e 's/^/  /'` EOF } listDevice() {   #echo "audioctl list-devices ..."   audioctl list-devices } showDevice() {   #echo "audioctl show-devices ..."   sDevice=$1   audioctl show-device -v -d $sDevice } showControl() {   #echo "audioctl show-control ..."   sDevice=$1   audioctl show-control -v -d $sDevice } alias audioctl='/usr/bin/audioctl' sSampleDevice=`listDevi

[Info] Top 10 SQL Performance Tips

Top 10 SQL Performance Tips, please refer to the link: http://forge.mysql.com/wiki/Top10SQLPerformanceTips Wish this helps. regards, Stanley Huang

[Level 1] Install Wine on CentOS5.4

First is to use EPEL repository to install. EPEL is the short cut of "Extra Packages for Enterprise Linux" Please refer to the EPEL wite. Install EPEL repository 1. get rpm from http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm 2. rum rpm: rpm -ivh epel-release-X-X.noarch.rpm 3. install by yum command: yum install wine  Wish this helps. regards, Stanley Huang

[level 2] Innotop, the tool for monitoring MySQL database

FYI. http://sourceforge.net/projects/innotop/ Wish this helps. regards, Stanley Huang

[Level 2] The Samples of MySQL Stored Function and Stored Procedure.

FYI. [SQL] delimiter // drop function if exists stanley.getSQL// create function stanley.getSQL(name varchar(32)) returns text begin   return concat('Hello World, ',name,'!'); end// delimiter ; [mysql client] mysql> select getSQL('stanley'); +-----------------------+ | getSQL('stanley')     | +-----------------------+ | Hello World, stanley! | +-----------------------+ 1 row in set (0.00 sec)   mysql> select length(getSQL('stanley')); +---------------------------+ | length(getSQL('stanley')) | +---------------------------+ |                        21 | +---------------------------+ 1 row in set (0.00 sec) [SQL] delimiter // drop procedure if exists stanley.setParam// create procedure stanley.setParam(in id int, inout word text) begin   if id > 0 then     set word=concat(word, '> 0');   elseif id = 0 then     set word=concat(word, '= 0');   else     set word=concat(word, '<

[Level 3] Enhance MySQL Performance to Store MAC Data with Stored Functions.

We know MySQL support inet_aton()/inet_ntoa() to convert ip address data to unsigned integer. These will reduce the data capacity and enhance performance. But MySQL seems not support MAC address. So I think maybe I can create stored functions just like the purpose of inet_aton()/inet_ntoa() to also enhance performance. By the way, stored function has a declaration option call "DETERMINISTIC". Default is "NOT DETERMINISTIC", it implies that the procedure may produce different results, given the same inputs. The alternative is "DETERMINISTIC", if the procedure always gives the same results given the same input. In this case, to let SQL parser can use the right index, we should declare the stored function with "DETERMINISTIC". My  sample code as following: delimiter // ## create stored function ether_atob(), from ascii to bit ## must define "deterministic", or explain will not use index drop function if exists ether_atob// create

[Level 1] Install Chinese Fonts on CentOS

If you want to install Chinese Fonts on CentOS, You can install fonts-chinese.norach by yum. ex. # yum install fonts-chinese.noarch Wish this helps. regards, Stanley Huang

[Level 1] Update/Upgrade CentOS.

You can update/upgrade CentOS by using command "yum". # yum update # yum upgrade Wish this helps. regards, Stanley Huang

[Level 3] Coustomize your MySQL distribution.

If you want to customize your onw MySQL distribution, you can compile MySQL source by you own setting by command configure. The steps as the following: 1. Download the source.    You can download the source from MySQL official website. 2. Confgiure. ex. # configure \ > --prefix=/usr/local \ > --exec-prefix=/usr/local/mysql \ > --localstatedir=/usr/local/mysql/data \ > --basedir=/usr/local/mysql \ > --datadir=/usr/local/mysql/data \ > --with-mysqld-ldflags=--rdynamic \ > --with-plugins=partition,innobase \ > --with-embedded-privilege-control; 3. Make/Make install. # make && make install Wish this helps. regards, Stanley Huang

[Level 1] Data Generator

During database designing/system developing, you might need data capacity forecasting or stress test. Then you will need to generate mass data. There is a project for MySQL and a website for data generator, please refer to the website bellow: http://forge.mysql.com/projects/project.php?id=214 http://www.generatedata.com/#about Wish this helps. regards, Stanley Huang

[Level 2] MySQL GeneralLog and Slow Query Log.

If you want MySQL to log "slow" queries, there are two ways to do. In configuration file: [mysqld] general_log slow_query_log long_query_time=5 log_output=FILE,TABLE In parameter: #mysqld_safe --general_log --slow-query-log --long-query-time=5 --log-output=FILE,TABLE Use command to change run-time environment: mysql> set global general_log=1; Query OK, 0 rows affected, 1 warning (0.18 sec) mysql> set global slow_query_log=1; Query OK, 0 rows affected, 1 warning (0.18 sec) mysql> set global long_query_time=5; Query OK, 0 rows affected (0.00 sec) mysql> set local long_query_time=3; Query OK, 0 rows affected (0.00 sec) mysql> set global log_output='FILE,TABLE'; Query OK, 0 rows affected (0.00 sec) mysql> Check environment: mysql> show global variables like 'general_log'; +----------------+-------+ | Variable_name  | Value | +----------------+-------+ | slow_query_log | ON   | +----------------+-------+ 1 row in s

[Level 1] Additional resources for CentOS

Good information for CentOS. http://wiki.centos.org/AdditionalResources/ Wish this helps. regards, Stanley Huang

[Level2] Let CentOS 5.3 support ext4

Default CentOS 5.3/5.4 not support ext4. So you have load kernel module manually. # modprobe ext4 # modprobe ext4dev If you want to let 5.3 support ext4, you can install the package: PS. the kernel must after 2.6.18-128.e15 # yum install e4fsprogs After that, your system will have commands "fsck.ext4, mkfs.ext4" Transfer boot partition from ext3 to ext4 case 1: boot on one partition mounted on /boot # umount /boot # tune4fs -O extents,uninit_bg,dir_index /dev/sda1 # e4fsck -y /dev/sda1 case 2: boot on / # tune4fs -O extents,uninit_bg,dir_index /dev/sda1 Modify /etc/fstab /dev/sda1             /boot                   ext3    defaults        1 2 to /dev/sda1             /boot                   ext4    defaults        1 2 reboot and check. # df -lhT Wish this helps. regards, Stanley Huang

[Level 1] Change Hostname in CentOS.

How to change hostname in CentOS. 1. Changing online server hostname. Use command "hostname". ex.   # hostname my_new_hostname.my_new_domainname; 2. Changing hostname permanently.   a. change setting "HOSTNAME" of file /etc/sysconfig/network ex.   HOSTNAME=my_new_hostname.my_new_domainname   b. use command "sysctl" ex.   # sysctl kernel.hostname=\ my_new_hostname.my_new_domainname; Wish this helps. regards, Stanley Huang

[Level 1] Install Input Utility on CentOS.

If you don't have any input utility on CentOS, I recommend you to use SCIM for input. All you have to do is to install scim package, the easy way to install package is by using yum. ex. # yum install scim* Then you have input utility. Wish this helps. regards, Stanley Huang

[Level 3] Create MySQL User Define Function in CentOS.

If you want to create user define function. You have following steps to do: 1. Compile MySQL source code with option "--with-mysqld-ldflags=--rdynamic" ex.   # ./configure --with-mysqld-ldflags=--rdynamic PS. mysql> show plugins; +------------+--------+----------------+---------+---------+ | Name       | Status | Type           | Library | License | +------------+--------+----------------+---------+---------+ | binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | | CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     | | MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | | MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     | +------------+--------+----------------+---------+---------+   a. for partitioning: --with-partition # unrecognized                                  --with-plugins=partition   b. for innodb: --with-plugins=innobase    c. for libmysqld: One difference

[level 1] Boot CentOS to Single-User-Momde.

If you want any problem need to boot CentOS to  Single-User-Mode, you can press any key at boot screen and show up grub menu. Then press 'e' and append "single" at the end of the 'kernel' line. And press 'b' to boot OS. And you will see the CentOS boot to Single-User-Mode. Wish this helps. regards, Stanley Huang

[Level 2] The side effect of MySQL "group by" clause.

When you use group by clause but not using aggregation function for all selected columns. The result set will always get the first record, and it will cause data logic fail. If you want to avoid this situation happens, you can modify the sql_mode with the value "only_full_group_by". The sample as the following: mysql> select * from t; +------+------+ | a    | b    | +------+------+ |    1 |    1 | |    1 |    2 | |    1 |    3 | |    2 |    3 | |    2 |    2 | |    2 |    1 | +------+------+ 6 rows in set (0.00 sec) mysql> select * from t group by a; +------+------+ | a    | b    | +------+------+ |    1 |    1 | |    2 |    3 | +------+------+ 2 rows in set (0.00 sec) mysql> set sql_mode=only_full_group_by; Query OK, 0 rows affected (0.00 sec) mysql> select * from t group by a; ERROR 1055 (42000): 'test.t.b' isn't in GROUP BY mysql> select a, max(b) from t group by a; +------+--------+ | a    | max(b) | +------+--

[Level 1] Rar tools on CentOS.

Today, my college send me an email with rar attach file. How do I extract it on CentOS? Then I google it, I find some guy show his rar tool download from rpmfind. It really works~ Wish this helps. regards, Stanley Huang

[Leve 1] Setting color termianl.

If you want your terminal has color, you can set the environment variable "TERM" to xterm-color in your profile. echo "export TERM=xterm-color" >> ~/.bashrc echo "alias ls=ls --color=auto" >> ~/.bashrc Wish this helps. regards, Stanley Huang

[Level 1] Set yum repository configuration

 Put following two file in /etc/yum.repos.d # CentOS-Base.repo # # This file uses a new mirrorlist system developed by Lance Davis for CentOS. # The mirror system uses the connecting IP address of the client and the # update status of each mirror to pick mirrors that are updated to and # geographically close to the client.  You should use this for CentOS updates # unless you are manually picking other mirrors. # # If the mirrorlist= does not work for you, as a fall back you can try the # remarked out baseurl= line instead. # # [base] name=CentOS-$releasever - Base mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5 #released updates [updates] name=CentOS-$releasever - Updates mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates #baseurl=http://mirror.

[Level 1] How to check the interface is link up or down in OpenSolaris?

Before, I always got the question "How to check the interface is link up or donw?". Now, you can use the command 'dlamd' with option 'show-link' to check it. # dladm show-link LINK        CLASS     MTU    STATE    BRIDGE     OVER iwh0        phys      1500   down     --         -- bge0        phys      1500   up       --         -- vboxnet0    phys      1500   unknown  --         -- Wish this helps. regards, Stanley Huang

[Level 1] Remove ^M by vi.

When you got a text file from Windows, you will see " ^M " at the end of the line. That's because Windows use " CrLf " for line end, but OpenSolaris only use " Lf ". If you want to remove ^M, you can use the command in last line mode of vi. :1,$s/\r$// Wish this helps. regards, Stanley Huang