Wednesday, March 31, 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 send the message by manual.
ex. ( I high-light want I type in blue )
# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1 (127.0.0.1).
Escape character is '^]'.
set test1 0 0 10
testing001
STORED
set test2
ERROR
set test2 0
ERROR
set test2 0 0
ERROR
set test2 0 0 10
testing002
STORED
add test1 0 0 10
testing001-add
CLIENT_ERROR bad data chunk
ERROR
add test1 0 0 10
testing100
NOT_STORED
replace test1 0 0 10
testing003
STORED
get test1
VALUE test1 0 10
testing003
END
get test1 test2
VALUE test1 0 10
testing003
VALUE test2 0 10
testing002
END

b. test it with python script:
I donwload the python module for memcached for testing. Click here to get the module.
# gzip -cd ./python-memcached-1.45.tar.gz | tar xvf -
# cd ./python-memcached-1.45.tar.gz
# /usr/bin/python ./setup.py build
# /usr/bin/python ./setup.py install
# /usr/bin/python ./memcache.py


PS.
Max object size is 1MB (configurable at build time)


Wish this helps.

regards,
Stanley Huang

[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

Tuesday, March 30, 2010

[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 databas



## no query attribute for cursor
#cur.query("select user,host,password from mysql.user") 

cur = cxn.cursor()
cur.execute("select user,host,password from mysql.user")
for data in cur.fetchall():
  #print(data[0],data[1],data[2])
  print(data[0],data[1])
cxn.close()

5. Run it.
# ./testMySQLdb.py
('root', 'localhost', '')
('root', 'localhost.localdomain', '')
('root', '127.0.0.1', '')
#

Wish this helps.

regards,
Stanley Huang

[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

Monday, March 29, 2010

[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                               = 3306
socket                             = /tmp/mysql.sock
[mysqld]                          
port                               = 3306
socket                             = /tmp/mysql.sock
skip-locking                      
key_buffer_size                    = 16K
max_allowed_packet                 = 1M
table_open_cache                   = 4
sort_buffer_size                   = 64K
read_buffer_size                   = 256K
read_rnd_buffer_size               = 256K
net_buffer_length                  = 2K
thread_stack                       = 128K
server-id                          = 1
[mysqldump]                       
quick                             
max_allowed_packet                 = 16M
[mysql]                           
no-auto-rehash                    
[myisamchk]                       
key_buffer_size                    = 8M
sort_buffer_size                   = 8M
[mysqlhotcopy]                    
interactive-timeout               
-------------------------------------------------------------------

-------------------------------------------------------------------
{/usr/local/mysql/etc/my.cnf}
Can't open /usr/local/mysql/etc/my.cnf: No such file or directory.
-------------------------------------------------------------------

-------------------------------------------------------------------
{/src/SolarisApps/mysql-5.1.44-solaris10-x86_64/my.cnf}
Can't open /src/SolarisApps/mysql-5.1.44-solaris10-x86_64/my.cnf: No such file or directory.
-------------------------------------------------------------------

-------------------------------------------------------------------
{/home/stanley/.my.cnf}
Can't open /home/stanley/.my.cnf: No such file or directory.
-------------------------------------------------------------------


Wish this helps.

regards,
Stanley Huang

Thursday, March 25, 2010

[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 samples you can find on the net. ). And I download the sample code from code project, it create by Anders Molin.

3. Modify the udf_example.c. In step 3, I'll show you some tips about how to modify the sample code.

a. declare you function name first.
// convert MAC from char to byte
my_bool udf_ether_atob_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void udf_ether_atob_deinit(UDF_INIT *initid);
char *udf_ether_atob(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

// convert MAC from byte to char
my_bool udf_ether_btoa_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void udf_ether_btoa_deinit(UDF_INIT *initid);
char *udf_ether_btoa(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

You can see that, I high-light the function name with color red, and function with "_init" means what to do before call the main program, and function with "_deinit" means what to do after leave the main program. So you can pre-allocate the resource in the "_init" block, and release the resouce in th "_deinit" block.

b. define the max length of result.
#define a_MAXMETAPH 17
#define b_MAXMETAPH 6
In this case tha max length of char is 17, and the length of byte is 6.

c. Define the init function.
my_bool udf_ether_atob_init(UDF_INIT *initid, UDF_ARGS *args, char
*message)
{
  if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT)
  {
    strcpy(message,"Wrong arguments to udf_ether_atob;  Use the source");
    return 1;
  }
  initid->max_length=b_MAXMETAPH;
  return 0;
}
 
my_bool udf_ether_btoa_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1) // not check data type
  {
    strcpy(message,"Wrong arguments to udf_ether_btoa;  Use the source");
    return 1;
  }
  initid->max_length=a_MAXMETAPH;
  return 0;
}
 
You have see that, this two init function both check the number of input parameter. But in udf_ether_btoa_init, it doesnot check the data type of parameter, just because the input parameter is "Byte", and you can ignore to check it.





d. modify the main program.
In udf_ether_atob():
char *udf_ether_atob (...) {
  const char *pszMACAddress=args->args[0];
  char *pbyAddress;
  pbyAddress=result;
...
  if (!pszMACAddress)                                   /* Null argument */
  {
    *is_null=1;
    return 0;
  }
...
  *length= (unsigned long) 6;


  return pbyAddress;
}





I high-light 4 points that you have to take care:
i. use "args->args[num]" to get the input parameters.
ii. define the result value with "pointer", and the caller will also send the point called "result" to reference the result value that you want to pass to MySQL.
iii. remember to check if the parameter is NULL or not.
iv. set the parameter pass from caller called "length", to let MySQL knows the size of the value you return.

in udf_ether_btoa():
char *udf_ether_btoa(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error __attribute__((unused)))
{
  const char *pbyMacAddressInBytes=args->args[0];
  char *pszMACAddress;
  pszMACAddress=result;
  char cSep=':';
...
  if (!pbyMacAddressInBytes)                                    /* Null argument */
  {
    *is_null=1;
    return 0;
  }
...
  *length= (unsigned long) 17;
  return pszMACAddress;
Just the same likt udf_ether_atob, in this time, although we want to pass "Byte" to MySQL, but you can still use the type "char" to return.
PS. In real testing, I find out that in my environment (CentOS5.4) will have "big-endian, small-endidan" issue. Therefor I modify the source code udf_ether_btoa(), to print the value reversely. ( print char from arry[5], array[4] ... to array[0] )

5. compile it and copy the library file to MySQL plugin folder.
Ex.
# gcc -o ./udf_ether.so -shared -fPIC -I /usr/local/include/mysql ./udf_ether.c
&& cp ./udf_ether.so /usr/local/mysql/lib/mysql/plugin/




6. create MySQL function with library.
mysql> DROP FUNCTION IF EXISTS udf_ether_atob;
mysql> CREATE FUNCTION udf_ether_atob RETURNS STRING SONAME 'udf_ether.so';

mysql> DROP FUNCTION IF EXISTS udf_ether_btoa;
mysql> CREATE FUNCTION udf_ether_btoa RETURNS STRING SONAME 'udf_ether.so';

mysql> DROP TABLE IF EXISTS test.test_udf_ether;
mysql> CREATE TABLE test.test_udf_ether (a char(17), b bit(48));
 
mysql> insert into test.test_udf_ether values ('00:ab:cd:ef:00:99',udf_ether_atob('00:ab:cd:ef:00:99'));
mysql> select a, udf_ether_btoa(b) from test.test_udf_ether;

+-------------------+--------------------------+
| a                 | udf_ether_btoa(b) |
+-------------------+--------------------------+
| 00:ab:cd:ef:00:99 | 00:ab:cd:ef:00:99        |
+-------------------+--------------------------+


Hints for MySQL UDF:

1. Download source code
2. # ./configure --with-mysqld-ldflags=--rdynamic
3. # gcc -shared -o ./udf_example.so ./udf_example.c -I/usr/local/include/mysql -fPIC
4. # cp ./udf_example.so /usr/local/mysql/lib/mysql/plugin
5. mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 22 /usr/local/lib/mysql/plugin/udf_example.so: cannot restore segment prot after reloc: Permission denied)
6. # chcon -t texrel_shlib_t /usr/local/lib/mysql/plugin/udf_example.so


Wish this helps.

regards,
Stanley Huang

Tuesday, March 23, 2010

[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=`listDevice | head -1`
sDevice=$1
sControl=$2
nValue=$3

if [ $# -lt 1 ]
then
  showUsage
  exit 1
elif [ $# -eq 1 ]
then
  echo "Show device..."
  showDevice $1
  echo ""
  echo "Show device control..."
  showControl $1
elif [ $# -eq 3 ]
then
  audioctl set-control -v -d $sDevice $sControl $nValue
else
  showUsage
  exit 1
fi



Wish this helps.

regards,
Stanley Huang

Monday, March 22, 2010

[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

Sunday, March 21, 2010

[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, '< 0');
  end if;
end//

drop procedure if exists stanley.run//
create procedure stanley.run(in id int)
begin
  declare output text;
  set output = 'The answer is ';
  call stanley.setParam(id, output);
  select output;
end//
delimiter ;

[mysql client]
mysql> call stanley.run(1);+-------------------+
| output            |
+-------------------+
| The answer is > 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 
mysql> call stanley.run(0);+-------------------+
| output            |
+-------------------+
| The answer is = 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 
mysql> call stanley.run(-1);
+-------------------+
| output            |
+-------------------+
| The answer is < 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 


Wish this helps.

regards,
Stanley Huang

Saturday, March 20, 2010

[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 function ether_atob(sAscii char(17))
returns bit(48)

deterministic
begin
  declare bReturn bit(48);
  set bReturn=unhex(replace(sAscii,':',''));
  return bReturn;
end//

## create stored function ether_btoa(), from bit to ascii 
## must define "deterministic", or explain will not use index
drop function if exists ether_btoa//
create function ether_btoa(sBit bit(48))
returns char(17)

deterministic
begin
  declare sReturn char(17);
  set sReturn=lpad(hex(sBit),12,'0');
  set sReturn=concat_ws(':', substr(sReturn,1,2), substr(sReturn,3,2), substr(sReturn,5,2), substr(sReturn,7,2), substr(sReturn,9,2), substr(sReturn,11,2));
  return sReturn;
end//

delimiter ;



mysql> create table ether_table (b bit(48), a char(17), index(b), index(a));
Query OK, 0 rows affected (0.67 sec)

mysql> insert into ether_table values (ether_atob('
00:CD:EF:00:CD:EF'),'00:CD:EF:00:CD:EF');
Query OK, 1 row affected (0.01 sec)

mysql> select ether_btoa(b), a from ether_table
where b=ether_atob('
00:CD:EF:00:CD:EF');
+----------------+-------------------+
| ether_btoa(b)  | a                 |
+----------------+-------------------+
| 00:CD:EF:00:CD:EF | 00:CD:EF:00:CD:EF |
+----------------+-------------------+
1 rows in set (0.01 sec
)


mysql> explain select ether_btoa(b), a from ether_table where b=ether_atob('00:CD:EF:00:CD:EF');
+----+-------------+-------------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | ether_table | ref  | b             | b    | 7       | const |    1 | Using where |
+----+-------------+-------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)


Wish this helps.

regards,
Stanley Huang

Friday, March 19, 2010

[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

Thursday, March 18, 2010

[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 set (0.00 sec)

mysql> show global variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 5.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show local variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 3.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show global variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE,TABLE  |
+---------------+-------+
1 row in set (0.00 sec)

mysql>



Description:
a. general_log(--general-log)
  The flag for MySQL to decide to log slow queries or not, default is on
b. slow_query_log(--slow-query-log)
  The flag for MySQL to decide to log slow queries or not, default is off
c. long_query_time(--long-query-time)
  The thread hole for MySQL to decide which SQL statement should be logged.
d. log_output(--log-output)
  The destination for MySQL log, default is FILE.
  (MySQL 5.1.6 through 5.1.20, the default logging destination is TABLE)
e. General log table and Slow query log table can only support CSV/MyISAM storage,
   default is CSV. And if you want to change storage engine, please stop log first.

Wish this helps.

regards,
Stanley Huang

Wednesday, March 17, 2010

[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

Monday, March 15, 2010

[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 between the embedded server and the standalone server is that for the embedded server, authentication for connections is disabled by default. To use authentication for the embedded server, specify the --with-embedded-privilege-control

2. prepare UDF function source written in C/C++, and compile it.
ref:
  http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html
My sample compile command for CentOS:
# gcc -shared -o ./udf_example.so ./udf_example.c -I/usr/local/include/mysql -fPIC

3. copy library file to MySQL folder and start MySQL server
# cp ./udf_example.so /usr/local/mysql/lib/mysql/plugin
# mysqld_safe

4. create UDF function by SQL DDL command.

When I try above steps seems fine, except step 4.
I use the SQL command:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';

But I got the error message like below:
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 22 /usr/local/lib/mysql/plugin/udf_example.so: cannot restore segment prot after reloc: Permission denied)

After I google it, I found out that is because of SELinux won't let MySQL to access the linking library.
So I use the command to remove the limitration.
# chcon -t texrel_shlib_t /usr/local/lib/mysql/plugin/udf_example.so

And redo step 4 again, it works.

5. test it
mysql> select metaphon('SD');
+----------------+
| metaphon('SD') |
+----------------+
| ST             |
+----------------+
1 row in set (0.00 sec)

Wish this helps.

regards,
Stanley Huang

[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

Thursday, March 11, 2010

[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) |
+------+--------+
|    1 |      3 |
|    2 |      3 |
+------+--------+
2 rows in set (0.00 sec)

mysql> 

Wish this helps.

regards,
Stanley Huang

Wednesday, March 10, 2010

[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

Tuesday, March 9, 2010

[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

Monday, March 8, 2010

[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.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#packages used/produced in the build but not released
[addons]
name=CentOS-$releasever - Addons
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=addons
#baseurl=http://mirror.centos.org/centos/$releasever/addons/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
#baseurl=http://mirror.centos.org/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
#baseurl=http://mirror.centos.org/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

#contrib - packages by Centos Users
[contrib]
name=CentOS-$releasever - Contrib
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib
#baseurl=http://mirror.centos.org/centos/$releasever/contrib/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5




# CentOS-Media.repo
#
# This repo is used to mount the default locations for a CDROM / DVD on
#  CentOS-5.  You can use this repo and yum to install items directly off the
#  DVD ISO that we release.
#
# To use this repo, put in your DVD and use it with the other repos too:
#  yum --enablerepo=c5-media [command]

# or for ONLY the media repo, do this:
#
#  yum --disablerepo=\* --enablerepo=c5-media [command]

[c5-media]
name=CentOS-$releasever - Media
baseurl=file:///media/CentOS/
        file:///media/cdrom/
        file:///media/cdrecorder/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5
 


Wish this helps.

regards,
Stanley Huang

Friday, March 5, 2010

[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

Thursday, March 4, 2010

[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