標籤

4GL (1) 人才發展 (10) 人物 (3) 太陽能 (4) 心理 (3) 心靈 (10) 文學 (31) 生活常識 (14) 光學 (1) 名句 (10) 即時通訊軟體 (2) 奇狐 (2) 音樂 (2) 產業 (5) 郭語錄 (3) 無聊 (3) 統計 (4) 新聞 (1) 經濟學 (1) 經營管理 (42) 解析度 (1) 遊戲 (5) 電學 (1) 網管 (10) 廣告 (1) 數學 (1) 機率 (1) 雜趣 (1) 證券 (4) 證券期貨 (1) ABAP (15) AD (1) agentflow (4) AJAX (1) Android (1) AnyChart (1) Apache (14) BASIS (4) BDL (1) C# (1) Church (1) CIE (1) CO (38) Converter (1) cron (1) CSS (23) DMS (1) DVD (1) Eclipse (1) English (1) excel (5) Exchange (4) Failover (1) FI (57) File Transfer (1) Firefox (2) FM (2) fourjs (1) gladiatus (1) google (1) Google Maps API (2) grep (1) Grub (1) HR (2) html (23) HTS (8) IE (1) IE 8 (1) IIS (1) IMAP (3) Internet Explorer (1) java (3) JavaScript (22) jQuery (6) JSON (1) K3b (1) LED (3) Linux (112) Linux Mint (4) Load Balance (1) Microsoft (2) MIS (2) MM (51) MSSQL (1) MySQL (27) Network (1) NFS (1) Office (1) Oracle (125) Outlook (3) PDF (6) Perl (59) PHP (33) PL/SQL (1) PL/SQL Developer (1) PM (3) Postfix (2) postfwd (1) PostgreSQL (1) PP (50) python (1) QM (1) Red Hat (4) Reporting Service (28) ruby (11) SAP (234) scp (1) SD (16) sed (1) Selenium-WebDriver (5) shell (5) SQL (4) SQL server (8) SQuirreL SQL Client (1) SSH (2) SWOT (3) Symantec (2) T-SQL (7) Tera Term (2) tip (1) tiptop (22) Tomcat (6) Trouble Shooting (1) Tuning (5) Ubuntu (33) ufw (1) utf-8 (1) VIM (11) Virtual Machine (2) vnc (3) Web Service (2) wget (1) Windows (19) Windows (1) WM (6) youtube (1) yum (2)

2014年7月31日 星期四

sz and rz : file transfer command in teraterm over ans SSH connection

http://marge.phys.washington.edu/software/SSH_File_Transfer.html

How to transfer files over an SSH connection


This HOWTO focuses on the situation where the user connects from a Windows machine using TeraTerm with the SSH extension. The server is assumed be a Unix-like machine which will only accept SSH connections from the client.
In most of the examples, the server is assumed to be named "remote," the local machine to be named "local," and the file to be named "foo."

Samba

This ducks the entire issue. For a permanent installation, Samba is the way to go. Samba is a fantastically useful program which allows files residing on a unix machine to be shared with Windows machines. The Unix machine will appear in the Network Neighborhood, and fine-grained control is available to select which files are available to which users. Printing support is also provided. Samba has to be installed by the system administrator. Be sure to run NT service pack 3 or above, Windows 98, or recent upgrades to Windows 95, otherwise your password will be leaked to the network. Also, be sure to enable encrypted passwords in Samba, otherwise it will not be able to talk to the Windows clients. Samba can be installed by your system administrator

SCP

If you're connecting from a unix-like machine to a unix-like machine, this HOWTO is not for you. Your situation is much easier. Use the command "scp" which is analogous to the more common "rcp." If you want to copy the file "foo" to the machine "remote" you would issue the following command:   scp foo remote:
Note the colon at the end of the line. If you omit the colon, you will just make a local copy of the file named "remote."
See the man page on scp for more information.
 

Modem-Style Transfer Programs

One way to transfer files over an SSH connection is to use modem-to-modem transfer protocols like kermit or xmodem. This is probably the easiest to set up. All the communication which would normally take place between two modems is piped through the SSH connection. There's a lot of extra overhead to this technique, so it may not be the best for large files over slow connections. Of the available options, ZModem is probably the easiest to use.

ZModem: Uploading Local to Remote

  1. From the command line on the remote machine, type "sz filename"  You can specify multiple filenames on the same command line. You will see some garbage appear on the screen. This is the sz program waiting to hear the correct response. Your screen will look approximately like this:

  2. [zager@marge zager]$ sz foo
    Š*B00000000000000
  3. From the File menu in TeraTerm, select Transfer|ZMODEM|Receive. The file transfer should begin.

ZModem: Downloading Remote to Local

  1. From the command line, type "rz"  You do not need to specify a filename. rz will produce some garbage on the screen. Your screen will look approximately like this.

  2. [zager@marge zager]$ rz
    Šz waiting to receive.**B0100000023be50
  3. From the File menu in TeraTerm, select Transfer|ZMODEM|Send... You should see a standard Windows file selection dialog. Select the file(s) you would like to send, click the "Open" button. The transfer should start
If something goes wrong with the transfer, sz and rz should both time out eventually. Pressing Control-C, Control-Z, and otherwise pounding on the keyboard has little effect. sz and rz are programs which can be installed and run as a regular user. No special priviledges are required.

FTP

It is much harder to configure FTP to work over an SSH connection. The reason to do this is that you have some nifty graphical FTP clients on your PC which you enjoy using, and use often. That will make it worth the setup. Otherwise, you are encouraged to take a second look at the section above.

Overview

The SSH protocol includes a feature known as "port-forwarding." This allows a network connection which looks like it begins at one end of the SSH connection to be sent through SSH to the remote end, and passed on elsewhere. We will tell the LOCAL machine to listen for an ftp connection, then forward that connection to the REMOTE machine. Then, to make use of this new link, we will tell our FTP client to connect to the LOCAL machine instead of the REMOTE one.

Preconditions

  1. The remote server must be configured to allow ftp connections from itself, even though it does not allow connections from the outside world. The system administrator will have to do this. It may also be possible for you to run your own renegade ftp server on a non-priviledged port.
  2. Your ftp client must support passive mode. The default ftp clients in Windows 95 and Windows NT do not support passive mode. The fancier ftp programs generally do. Internet Neighborhood, available at http://www.knoware.com is a nice ftp program which does support passive mode.
  3. You must have the SSH plug-in version 1.4 or higher. Maybe version 1.3 would work, I never saw it. But version 1.2 does not support port forwarding.

Configuring TeraTerm-SSH

  1. Select the TeraTerm menu option Setup|SSH Forwarding... If you do not have an option "SSH Forwarding..." under your Setup menu, then you need to upgrade your version of the SSH plugin.
  2. Click on the Add button
  3. Forward from the local port "ftp" to the remote machine "remote" , port "ftp."
  4. Click on the Ok button
  5. The "Port Forwarding" list should now have one entry: "Local 21 (ftp) to remote "remote" port 21 (ftp)."
  6. Click on the Ok button
  7. Select the menu option Setup|Save Setup... to make your changes permanent.

Configuring your FTP Client

The particulars of how to do this will vary with your FTP client. But there are two major steps:
  1. Tell your ftp client that you want to connect to "localhost" This is counterintuitive. Remember, the port forwarding is going to magically transmit this connection to the other end, so it's ok.
  2. Tell your ftp client to use passive mode. This may be under settings labeled "advanced" or "firewall." If you are able to log in with your username and password, but are not able to transmit any data, then you have not enabled passive mode.
Once you have done these two steps, you should be able to use the ftp client normally to connect to the remote site.

Special Case: Running an FTP server on the Client

If you are already running an FTP server on the client, then you have a couple extra steps to do. SSH will not be able to set up its port forwarding since your ftp server will already have control of the ftp port. The solution is to use a non-standard port for the ftp connection.
When configuring Tera-Term SSH, instead of forwarding from the port "ftp," pick a free port. You really should check what ports are free, but a number like 54321 might be safe. The number should be over 1000 and less than 65536.
When configuring the FTP client, look for a "port" option and set that to the same port as you set for SSH. This is likely be be near where you set the name of the machine to which you connect. (Which you set to "localhost," right?) There is also probably a "port" option near firewall settings, but this is probably not what you're after.

Other Tricks

Forwarding Mail Connections

The standard POP and IMAP mail protocols blab your username and password in cleartext, just like ftp would. If you use mail programs like Eudora, Outlook, or Netscape Messenger, your password is probably being leaked. You can do a similar trick of forwarding POP, POP3, or IMAP traffic through SSH to secure these connections. Note that you will need to have an SSH window open to the server in order to check your mail.

VIM syntax highlight


1. 在$HOME/.vimrc加上以下內容

colorscheme default
set term=ansi
syntax on

2. http://www.cnblogs.com/ifantastic/p/3524751.html

Vim手记:语法高亮

本文覆盖范围:
  • Vim的着色方案
  • 设置高亮
  • 选择颜色
  • 语法高亮除错
每个程序员的文本编辑器缺少了语法高亮、特殊关键字和短语着色,都是不完整的。语法高亮突出了文档的结构,帮助发现排字错误,利于调试,整体上让文本编辑更轻松更有效率。本篇我们将探讨一下Vim的语法高亮范式以及如何建立和定制着色方案。

Vim着色方案
首先说明,本文谈论的不是哪种颜色最好,而是Vim如何处理语法高亮的问题。

在决定如何给一个文件着色时,Vim首先将文件分解成若干结构组。这些都是通过编写好的语法命令文件来实现,这些文件在Vim加载一个文件的时候就 自动加载了,加载过程则是基于一系列复杂规则。如果Vim选择了一个错误的语法命令文件,你可以通过以下命令手工指定一个正确的文件:
:set syn=syntax-type

其中syntax-type是一个文件的类型。通常文件类型就是文件扩展名,例如选择PHP语法高亮,则将syntax-type设置为“php”。

Vim主要使用以下九种高亮分组:
Comment    : 注释
Constant   : 常量,例如数字、引号内字符串、布尔值。
Identifier : 变量标识符名称。
Statement  : 编程语言的声明,一般是像“if”或“while”这样的关键字。
PreProc    : 预处理,例如C语言中的“#include”。
Type       : 变量类型,例如“int”。
Special    : 特殊符号,通常是类似字符串中的“\n”。
Underlined : 文本下划线。
Error      : 显示编程语言错误的文本。

另外还有一个分组叫做Normal,表示普通文本。

也许你已经注意到,这些高亮分组主要针对编程语言,这是因为语法高亮对它们最有用。但是,Vim的高亮命令覆盖的文件类型远超过编程语言,包括从HTML到EMAIL的任何东西。

Vim还支持三种输出设备:black-and-white terminal(黑白终端),color terminal(彩色终端)和GUI(图形化的用户接口)。因为它们每一个都有自己独特的高亮能力,Vim为它们维护了三个独立的高亮方案。

建立高亮

实现高亮的最简便方式就是使用别人写好的.vimrc文件,就像这个(点击下载)。大多数时候这样做都挺好使。但是,如果它不好使或者你想从头开始,以下就是告诉你如何从头开始。

你必须在你的.vimrc文件的第一行写入:
syntax on

这行命令告诉Vim开始使用语法高亮。如果你暂时不想改动文件,你也可以在Vim窗口输入它作为一行命令。

如果你正在使用黑白终端或者GUI接口,一切都工作得刚刚好。如果你正在使用一个支持彩色的终端,你也许不得不将以下命令置于syntax on上一行:
set term=ansi

你也可以尝试一下color_xterm来代替ansi。

选择颜色

Vim有一套默认颜色方案,在我看来,有点过度明亮和混乱(注释和变量颜色是一样的)。但是每个人都有一套自己的颜色偏好,因此你可能会很想了解如何改变它的色彩来符合自己的口味。

Vim着色使用:highlight和:hi命令。:hi命令实际上是多用途命令,它负责许多例行着色,我们仅用它的颜色选择功用。它具有以下语法:
:hi Group key=value ...

Group就是前面所说的9个高亮分组名。

key-value键值对规定了不同输出终端的高亮值。这里有三个基本键名:term、cterm和gui,代表着黑白终端、彩色终端和图形界面。 除了term以外,另外两个基本键还有两个扩展键名base-namefg和base-namebg,分别代表对应终端的前景和背景颜色。基本键只能拥有 非色彩的高亮值,例如加粗和下划线。

以下是一些高亮键和它们可能的值:
term/cterm/gui: bold, underline, reverse, italic, none

ctermfg/ctermbg: red, yellow, green , blue, magenta, cyan, white, blcak, gray

guifg/guibg: 以上所有颜色,而且还可以使用#rrggbb格式色彩。

例如,我想高亮我的预处理声明为bright magenta,可以使用如下命令:
:hi PreProc ctermfg=magenta cterm=bold guifg=#FF00FF

注意,我可以在一行中使用多个键值对,他们都会应用于同一个高亮分组。

通常来说,只要你喜欢一个着色方案,你可以将它设置在你的~/.vimrc文件中,Vim会自动将其加载。

语法高亮除错

Vim的语法高亮偶尔也存在一些小问题,我就经常遭遇到其中的三个。

没有彩色,只有黑白终端高亮。
导致这种情况的最常见的原因是Vim无法确定终端是否具备彩色能力。要修复它,首先确保在.vimrc文件第一行设置了term=ansi,一定要 在syntax on所在行之前。另外还要在shell中将环境变量TERM设置为color_xterm和color-xterm。

颜色有了,但不是我想要的。
这常发生在你试图修改别人的.vimrc文件以为己用的时候。有时,你只需要将自己的色彩定义全部放到文件的底部去就可以修复。如果不奏效的话,检查所有可疑的代码,然后将它们注释。(注释方法为在行首添加双引号)

滚屏时颜色乱成一团
Vim在确定高亮法则的时候使用多种探索方法来节约时间,有时这会造成一些故障。查看:h syn-sync可以得到更多细节。要修复这个,你可使用以下命令:
syn sync minlines=1000

你也可以在.vimrc文件中设置更大的数值。在Vim 6.0及以上版本,你可以使用以下命令:
syn sync fromstart

该命令强制高亮引擎从文件第一行开始解析文件,这对大文件而言会很慢,但它可以确保色彩准确度。

此时此刻,如果你习惯了使用Vim,并且你已经鼓捣了一阵它的配置,发现一些你喜欢的特性,恭喜你,你现在是一个Vim User了。当然,在本教程中我还会教更多的关于Vim的东西,有人写了一整本关于编程的书,但我希望你从本教程可以掌握一些基础知识并且探索更多知识。 享受学习,这是一项你永远不会后悔拥有的技能。

2014年7月26日 星期六

如何在Ubuntu, Linux Mint 連接 4.x Android MTP device


1.
sudo apt-get install mtp-tools mtpfs

cat > /lib/udev/rules.d/69-mtp.rules
ATTR{idVendor}=="0b05", ATTR{idProduct}=="5480", SYMLINK+="libmtp-%k", ENV{ID_MTP_DEVICE}="1", ENV{ID_MEDIA_PLAYER}="1"
 

cat > /etc/udev/rules.d/51-android.rules
# Asus AUSU_T00F
SUBSYSTEM=="usb", ATTR{idVendor}=="0b05", ATTR{idProduct}=="5480", MODE="0666" #MTP media, USB debug on


2.
sudo add-apt-repository ppa:webupd8team/unstable
sudo apt-get update
sudo apt-get install go-mtpfs

3. mount MTP device

sudo mtpfs -o allow_other /media/MyAndroid/
sudo go-mtpfs /media/MyAndroid

4. unmount MTP device

sudo fusermount -u /media/MyAndroid


http://ubuntuforums.org/showthread.php?t=2226702

2014年7月25日 星期五

用 xrandr 指令變更螢幕解析度


http://www.hongquan.me/?p=37

我在用virtual box新安装了一个linux mint 12, 但是最近在使用的时候发现一个问题。当我用Right_Ctrl + F键全屏进入mint的时候,发现整个屏幕明显没有被占满。上下留出一大段的黑色。看起来实在不爽。于是看了一下当前的屏幕分辨率,居然是 1440X793. 这个太没有道理了,我的显示器是分辨率是1440×900的,不知道从哪个地方跑出这么一个乱七八糟的分辨率来。
我开始认为应该是virtual box中设置错误。但是我怎么都没有找到在什么地方可以设置这个分辨率。于是我打算在mint里面修改以下这个分辨率。但是12(也就是ubuntu 11.10)里面已经找不到/etc/X11/xorg.conf这个文件了。在google之后我找到了方法。
首先使用xrandr命令列出当前的模式
RoyM1 ~ # xrandr
Screen 0: minimum 64 x 64, current 1440 x 900, maximum 32000 x 32000
VBOX0 connected 1440x793+0+0 0mm x 0mm
   1440x793       60.0 +
   1024x768       60.0
   800x600        60.0
   640x480        60.0
发现里面没有1440×900这个模式。于是使用xrandr添加一个新的模式进去。在添加之前,首使用cvt列出所需的模式的完整配置。然后添加这个新的模式并且应用。如下
RoyM1 ~ # cvt 1440 900
# 1440x900 59.89 Hz (CVT 1.30MA) hsync: 55.93 kHz; pclk: 106.50 MHz
Modeline "1440x900_60.00"  106.50  1440 1528 1672 1904  900 903 909 934 -hsync +vsync
RoyM1 ~ # xrandr --newmode "1440x900_60.00"  106.50  1440 1528 1672 1904  900 903 909 934 -hsync +vsync
RoyM1 ~ # xrandr --addmode VBOX0 "1440x900_60.00" 
RoyM1 ~ # xrandr --output VBOX0 --mode "1440x900_60.00"
RoyM1 ~ #
这样以来,整个屏幕空间都被利用上了。感觉明显好了许多

 http://www.arthurtoday.com/2012/08/ubuntu-change-resolution-command.html

 其實,Ubuntu 目前在偵測螢幕解析度的功能上,已經算是滿準的,可是,就阿舍在筆電插上外接螢幕後,有時候這個外接螢幕的解析度就不是很準了哩 ! 所以,阿舍就得去找出設定解析度的畫面,然後,再調整設定,阿舍覺得這樣有點麻煩,如果可以指令來調的話,這樣阿舍在操作上,應該會比較順手吧 !



因此,阿舍就上網找了一下,呵呵 ! 似乎有不少人也和阿舍一樣的需求哩!而操作方式也很簡單,只要用 xrandr 指令 (Ubuntu 內建,不需要安裝) 加上 -s 參數及解析度就可以變更螢幕的解析度了,以阿舍要把解析度調成 1440x900 為例,操作指令如下 (要注意的是,解析度的中間是小寫英文字母的 “x",而不可以使用 “*” 符號哩!):

xrandr -s 1440x900

另外,阿舍想要更簡單一點的來切換螢幕解析度,所以,就在家目錄下建立一個檔名叫做「1440*900」的檔案,然後,在檔案裡加入下面這二行指令後就存檔。

#!/bin/bash

sudo xrandr -s 1440x900

接著,再用下面的指令來把這個檔案變更可執行檔。

chmod +x 1440*900

完成後,就可以在家目錄下,透過下面的指令執行這個檔案來切換螢幕解析度哩 ! 如果會需要經常切換不同的解析度的話,也可以用上述的方式來建立不同解析度的執行檔哩 !

./1440*900

2014年7月17日 星期四

MySQL AUTOCOMMIT

http://php.net/manual/pt_BR/mysqli.commit.php

1. 使用PHP mysqli
<?php
$mysqli 
= new mysqli("localhost""my_user""my_password""world");
/* check connection */if (mysqli_connect_errno()) {
    
printf("Connect failed: %s\n"mysqli_connect_error());
    exit();
}
$mysqli->query("CREATE TABLE Language LIKE CountryLanguage Type=InnoDB");
/* set autocommit to off */$mysqli->autocommit(FALSE);
/* Insert some values */$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");
/* commit transaction */$mysqli->commit();
/* drop table */$mysqli->query("DROP TABLE Language");
/* close connection */$mysqli->close();?>
 
 2.
MySQL 預設都會啟動 Auto Commit, 要在 InnoDB 做 Transaction 有分兩種方法:
  1. BEGIN 直到 COMMIT, ROLLBACK 結束
  2. SET AUTOCOMMIT=0 直到 SET AUTOCOMMIT=1 結束
兩者差別在於:
  1. 使用 BEGIN 是當下的做 Transaction 的功能, 一直到 COMMIT 或 ROLLBACK 為止.(之後一樣是會 AUTO COMMIT)
  2. 使用 SET AUTOCOMMIT 的話, 只要設 0 就是 "在這次的 Connection" 都當做 AUTOCOMMIT是關閉的, 不管做幾次 COMMIT, ROLLBACK 都一樣, 直到設為 1 才算開啟 AUTO COMMIT.
不過以上的方法, 都要小心是否要做 Read Lock 的問題.

Test:
CREATE DATABASE innodb_test;
CREATE TABLE customers(a int, b char(20), index(a)) TYPE=InnoDB;

# 看 table 的狀態
SHOW TABLE STATUS FROM innodb_test LIKE 'customers'\G;
# BEGIN 的方法
BEGIN;
INSERT INTO customers VALUES(10, 'abc');
COMMIT;   # 此 BEGIN Transaction 已在此結束
SELECT * FROM customers;
INSERT INTO customers VALUES(11, 'aaa');
ROLLBACK;  # 此命令是無作用的, 已經不在 BEGIN 的範圍了, 資料會自動 COMMIT 進去.
SELECT * FROM customers;
# SET AUTOCOMMIT 的方法
# (注意: 其它 Connection 並不會因為這 Connection 設定而不 自動COMMIT)
SET AUTOCOMMIT=0;
INSERT INTO customers VALUES(15, 'def');
ROLLBACK;
INSERT INTO customers VALUES(16, 'ggg');
COMMIT;
SELECT * FROM customers;
# 回復每筆交易都會自動 COMMIT 的狀態
SET AUTOCOMMIT=1;
以上最好可以開兩個 Connection 互相 新、刪、改、查 測試.
 
 

利用jQuery and CSS 做到放大境效果 (jQZoom 插件)

1.
在 <head> </head> 之間放入

<script type="text/javascript" src="/js/jquery.jqzoom-core.js"></script>
<link rel="stylesheet" href="/css/jquery.jqzoom.css" type="text/css" media="screen" />

2.
在 <head> </head> 之間放入
<script type="text/javascript" language="javascript">
<!--
$(document).ready(function(){
    var options = {
        zoomType: 'standard',
        lens:true,
        preloadImages: true,
        alwaysOn:false,
        zoomWidth: 350,
        zoomHeight: 290,
        //xOffset:90,
        //yOffset:30,
        position:'right'
        //...MORE OPTIONS
    };
    $('.product_zoom').jqzoom(options);
    //取得css物件
    });
-->
</script>

3.
選定圖案做放大效果,class是放在外層的<a></a>
<a class='product_zoom' id='product_zoom_id' href='onpylu.jpg' title='75 %藥用酒精( 安皮露75%酒精液)' rel='gal1'><img src='onpylu.jpg' width='360' border='0' name='box_img_name' title='75 %藥用酒精( 安皮露75%酒精液)'></a>

2014年7月9日 星期三

MySQL 計算 日期差距

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-sub

日期差距:datediff(sysdate(),欄位)
時間差距:timediff(sysdate(),欄位)
時間差距,只看小時:time_format(timediff(sysdate(),欄位),'%H')

2014年7月7日 星期一

[PHP] empty(),is_null(),isset 判斷結果列表

http://seanphpbook.blogspot.tw/2009/10/php-empty-isset-php.html

寫PHP常常會用 empty 或 isset 等函式去判斷某些變數是否存在,這類函式很類似,但實際上有些許的不同,在PHP官網上特別針對類似功能的函式,整理出一張表,讓使用者可以更清楚的知道差異在哪…

php big5跟utf-8互轉的指令

php big5跟utf-8互轉的指令

big5 轉 utf-8
PHP 程式碼:
$str=iconv("big5","UTF-8",$str);

utf-8 轉 big5
PHP 程式碼:
$str=iconv("UTF-8","big5",$str);

PHP write/mail file

#!/usr/bin/php

<?php
    $cnt = 0;
    function getData() {
        require "setEnv.php";

        //MySQL Connect
        $con = mysql_connect("$hostMySQL","$usernameMySQL","$passwordMySQL");
        if (!$con)
          {
            die('Could not connect: ' . mysql_error());
          }
        mysql_select_db("$databaseMySQL", $con);
        mysql_query("SET NAMES 'utf8'");

        $result = mysql_query("
select ima01,ima127,ima128,ima262_01,ima262_02 from mobile.IMA_FILE
 where ima262_01+ima262_02 > 0
   and ima127 = 0 and ima128 = 0
");

        $fp = fopen('/tmp/MailNoPrice.csv', 'w');
        fwrite($fp, iconv("UTF-8","big5","料號").",".iconv("UTF-8","big5","可出量")."\n");
        while($row = mysql_fetch_assoc($result))
        {
          fwrite($fp, $row['ima01']);
          fwrite($fp, ",");
          fwrite($fp, $row['ima262_01'] + $row['ima262_02']);
          fwrite($fp, "\n");
          $GLOBALS['cnt'] += 1;
        }
        fclose($fp);

        mysql_close($con);
    }

    function sendMail() {
        require_once('sysmail.inc.php');
        require_once('class.phpmailer.php');

        $phpmail = new PHPMailer();
        $phpmail->IsSMTP(true);
        $phpmail->Host = $SysMail['SMTPhost'];
        $phpmail->CharSet = "utf-8";
        $phpmail->From = 'tyruan@echochem.com.tw';
        $phpmail->FromName = 'MIS';
        $phpmail->Subject = '有庫存,沒訂價 料號列表 (影響外網訂價)';

        $html = "共計:" . $GLOBALS['cnt'] . "筆,請儘速修正。謝謝";
        $phpmail->MsgHTML($html);
        $phpmail->AddAttachment( "/tmp/MailNoPrice.csv" , 'MailNoPrice.csv' );

        $phpmail->AddAddress( "tyruan@echochem.com.tw",'MIS' );
        $phpmail->AddCC( "tyruan@echochem.com.tw",'MIS');
        $phpmail->Send();
    }

    function main() {
        getData();
        if ($GLOBALS['cnt'] > 0) {
            sendMail();
        }
    }

    main();
?>

2014年7月3日 星期四

定存股






http://knightkasim.pixnet.net/blog/post/37834438


定存  
郵局定存利率只有1%左右,利息相當微薄,因此,轉而投資定存股是不錯的選擇
郵局利率    
定存股,顧名思義,就是把股票當作定存,每年領配股配息,挑到好的定存股,殖利率大於郵局定存利率
定存股需具備以下特性,不然很容易「賺了股利,賠了差價」

1. 比銀行定存利率高的殖利率:
    殖利率=股利/股價
其中,股利=現金股利+股票股利
例如:今年中華電股價89.5元,現金股利5.4608元,股票股利0元,殖利率=(5.4608+0)/89.5=6.10%
也就是說,若以買一張中華電89.5元,配息5.4608元,獲利為6.10%,效率相當於郵局定存利率的6.1%/1.1%=5.55倍
投資同金額的情況下,5460.8元定存股只要一年,郵局定存卻要五年半才有這利息,或者郵局定存要比定存股多投入5.55倍金額才會有同等利息
要小心的是,若該股股價波動太大,從89.5元掉到80元,這樣配息賺了5460.8元,但股價卻賠了9500元,整體而言,賠了9500-5460.8=4039.2元,這就是「賺了股利,賠了差價」,所以只有殖利率高並不是好的定存股

2. 股價波動小與景氣循環相關度低 
    承1,除了殖利率高之外,存股還需具備股價波動小與景氣循環相關度低,否則會「賺了股利,賠了差價」
例如:2412中華電景氣循環相關度就相當低,因為打電話、上網是無關景氣的,可以躺著賺
        2002中鋼是景氣循環股,但股價波動小,故也是定存股

3. 配息穩定且不會倒閉
    若公司倒閉,股票就變成廢紙了,錢就拿不回來了,故要選好公司;配息亦要穩定,否則一年有一年沒有,就比銀行定存還弱了。

4、可扣抵稅率高
    一般來說,傳統產業股會有比較高的「扣抵稅率」,而高科技產業因為有租稅優惠,故「扣抵稅率」都不高。
    兩稅合一將營利事業所得稅與綜合所稅兩種稅目合而為一了,故一般公司已先繳納營利事業所得稅,所以股東在領取股息時,可以用來扣抵個人綜所稅,而該股票依其「可扣抵稅率」來扣稅。
   「可扣抵稅率」要高過「個人綜所稅率」才有節稅效果
    例如:
    1、王小明今年綜合所稅為20%,但中華電今年可扣抵稅率為17.46%,小明若參加除息,依照兩稅合一,股利要併入綜所稅申報,要補稅 20%-17.46%=2.54%,即補稅股利*2.54%的金額,若股利為10000元,則要補稅10000*2.54%=254元 
    2、王小明今年綜合所稅為12%,但中華電今年可扣抵稅率為17.46%,小明若參加除息,依照兩稅合一,可退稅17.46%-12%=5.46%,即退稅股利*5.46%的金額,若股利為10000元,則退稅10000*5.46%=546元 

補充:定存利率對股價的影響
    殖利率=無風險報酬+風險溢酬
其中,無風險報酬=銀行利率,風險溢酬即投資者可接受的報酬
例如:
    假設郵局定存利率為1.1%~10.1%,而風險溢酬為4%,穩定配息5元,則股價的變化為
定存利率對股價的影響      
由上圖可見,定存利率越高,投資者就傾向更低價購買該股,簡言之,若定存利率提高,投資者很容易會「賺了股利,賠了差價」,所幸,定存利率不太可能變動太大,也不會那麼高,故不用擔心。


相關文章:
[1]股票買賣手續費:說明股票手續費與證卷交易稅的計算公式,學會計算手續費與證交稅更能掌握股票損益平衡點 
[2]定期定額懶人投資法解釋定期定額微笑曲線的意義、優點與迷思,定期定額不是穩賺不賠,它只適用於懶人投資 
[3]定價定額投資法說明定價定額投資法的特點,此方法適用於定存股投資 
[4]定存股票與一般股票的投資方法差異 :說明投資一般股票與定存股之間的差異,投資需慎選方法 
[5]股票攤平策略與風險說明投票攤平策略、操作方法與其風險 
[6]ETF指數股票型基金介紹ETF指數股票型基金的運作方法與風險 
[7]零股買賣說明零股買賣的相關事項
[8]二代健保補充保費以實例說明二代健保補充保費如何計算 
[9]簡單看懂證所稅簡單了解證所稅的實施方法與計算證所稅實例 
[10]複利才是致富的關鍵說明單利與複利的意義,以及比較相同時間兩者的投資結果
[11]均線意義與多空判斷:說明均線意義,以及如何用均線判斷市場多空,避免與市場為敵而賠錢
[12]均線黃金交叉與死亡交叉應用:圖解說明均線黃金交叉與死亡交叉的應用,深入了解如何用均線判斷趨勢
[13]均線扣抵判斷均線未來走向:說明均線扣底的意義以及其應用,判斷均線未來走向 
[14]台灣50 ETF0050投資法打敗定存 :台灣50簡易投資法,年報酬率可高於台幣定存

參考來源:
[1]http://www.masterhsiao.com.tw/Smart/SC159/SC159.php
[2]http://statementdog.com/blog/archives/479

2014年7月2日 星期三

MySQL Query Cache

http://blog.shian.tw/mysql-query-cache.html

MySQL Query Cache

MySQL 也有 cache可以使用! 提高資料庫效能有很重要的效用,可以節約大量的 CPU 時間 ,並且設定也非常的簡單,但是MySQL的版本要4以上才有這功能!!

設定:
1. 修改 /etc/my.cnf 檔案

# vi /etc/my.cnf
2.在 [mysqld] 裡加入下面這2行
query_cache_size=2m
query_cache_type = 1

註:
query_cache_type = 1 => 全部使用 query cache
query_cache_type = 2 => 選擇性使用或不使用 query cache
SELECT SQL_CACHE ‘*’ FROM table => 使用 query cache
SELECT SQL_NO_CACHE ‘*’ FROM table => 不使用 query cache
query_cache_size=2m : 設定cache大小
3. 重啟MySQL
# /etc/local/mysql/share/mysql/mysql.server restart
指令:
1. 查看是否有開啟 query cache

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
have_query_cache = YES 表示有開啟
2.查看緩衝的情況
mysql> show status like ‘Qca%’;

3.伺服器執行了多少 SELECT 語句
mysql> show status like ‘Com_sel%’;
實際測試:
使用 phpmyadmin 一次查詢五萬筆資料
未使用 query cache

第一次

第二次

第三次

第四次

第五次

使用 query cache
第一次

第二次

第三次

第四次

第五次

結論:
經過測試後發覺有使用 query ache  後, 第一次之後的查詢時間就明顯的減少很多,雖然看起來好像沒有減少多少,但是當資料量多或是更多人使用查詢時,效果就可以明顯感覺出來了, 而且這個測試只是五萬筆資料測試而已,

MySQL資料庫簡易效能調教

http://www.cc.ntu.edu.tw/chinese/epaper/0025/20130620_2510.html

作者:游子興 / 臺灣大學計算機及資訊網路中心網路組幹事

MySQL 資料庫是個使用廣泛的 Open Source 資料庫,本文以實際的範例搭配淺顯的說明與實作,一步一步進行效能之調教,而使大部分非科班出生之 MySQL 資料庫管理人員得以輕鬆應付與日遽增大量的資料。
前言
MySQL 是一個Open Source的中小型關連式資料庫,因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package,因此許多學術單位與中小型企業十分愛用。但隨著時間增加,資料量也持續增加,如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA,則成為一個棘手的問題。 本文將介紹兩種方法來改善資料庫之
MySQL 是一個Open Source的中小型關連式資料庫,因本身簡單易用且大部分的 Linux Distribution 皆有提供 MySQL Package,因此許多學術單位與中小型企業十分愛用。但隨著時間增加,資料量也持續增加,如何作效能之調教與改善 (Performance Tuning) 對於可能非科班出生之資料庫管理員 DBA,則成為一個棘手的問題。
本文將介紹兩種方法來改善資料庫之效能:
  1. 使用 MySQLTuner 進行 MySQL 之整體參數改善分析
  2. 使用 Explain Plan 進行個別 SQL 調教
本文將在CentOS release 5.5 (Final) + MySQL 5.0.77 平台上,針對上述兩種方法進行實作與說明。
一. 使用 MySQLTuner 進行 MySQL 之整體參數改善分析
官方網站: http://mysqltuner.pl/mysqltuner.pl
目前的版本Version 1.2.0,支援 MySQL 資料庫版本:
MySQL 3.23, 4.0, 4.1, 5.0, 5.1 (full support)
 MySQL 5.4 (not fully tested, partially supported)
 MySQL 6.0 (partial support)
此程式主要的功能在於蒐集運行中的MySQL 之 STATUS 與 Variable 相關變數來判斷並提供改善建議,程式本身使用 Perl Script Language 撰寫,因此不需安裝,僅需下載執行即可。
Step1. 指令: wget http://mysqltuner.pl/mysqltuner.pl
登入 Linux 後下載 MySQLTuner 之執行檔案 mysqltuner.pl

圖1. 下載 MySQLTuner 之執行檔案 mysqltuner.pl
Step2. 指令: chmod +x mysqltuner.pl
因下載檔案預設無 Execute 執行權限,使用 chmod +x 增加 Execute 權限。

圖2. 使用 chmod +x 增加 Execute 權限
Step3. 指令: ./mysqltuner.pl
不需安裝,直接執行程式即可進行測試。程式會先要求輸入 MySQL 具有管理權限之帳號密碼,在此輸入 root 及其密碼。

圖3. 執行 mysqltuner.pl 並輸入帳號密碼
Step4. 分析執行結果
完全不需設定與安裝就可產生分析結果,是不是非常簡單?

圖4. mysqltuner.pl 執行結果報告
接著我們將逐步分析執行的結果,各項目分成綠色[OK]與紅色[!!]來顯示,我們僅需針對 紅色[!!] 的結果來做改善與調教:
(1)[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
  因為32bit 之定址能力最高只到2^32= 4,294,967,296 (4G),若該 Server設備已安裝超過4G 之實體記憶體,則建議使用 64bit 之作業系統才能使用所有的記憶體。
(2)[!!] InnoDB is enabled but isn't being used
[!!] BDB is enabled but isn't being used
上述兩行的意思是 InnoDB 與 BDB 狀態為啟用,但並未使用,因此可搭配報告中最後的建議:
-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Add skip-bdb to MySQL configuration to disable BDB
增加兩行設定到 MySQL 之設定檔 ( 以CentOS 5.5 為例,預設檔案為: /etc/my.cnf)
skip-innodb
skip-bdb

圖5. 編輯設定檔 /etc/my.cnf
 之後需重新啟動 MySQL
 ~# service mysqld restart
(3)[!!] User '@localhost' has no password set.
[!!] User '@localhost.localdomain' has no password set.
上述兩行是指資料庫中有兩個帳號沒有設定密碼,可能有 Security Issue,經檢查Table: mysql.user 後發現,此兩個帳號預設為MySQL 安裝時所建立,並無 User 名稱也無設定權限,如下圖,因此可忽略此訊息。

圖6. 觀察 Table: mysql.user 所設定之帳號密碼
(4)[!!] Key buffer size / total MyISAM indexes: 8.0M/1.1G
此處點出了一個很重要的問題,就是 Key buffer size 太小(僅有 8.0M),參考報告中最後的建議:
-------- Recommendations -----------------------------------------------------
Variables to adjust:
    key_buffer_size (> 1.1G)
建議將 key_buffer_size 由 8.0 M 調整至 1.1G,原因在於 Key buffer size 的作用在於 Cache Table中的 indexes(索引),目前的索引佔據的空間已有 1.1G ( /var/lib/mysql/ 目錄中索引檔案 *.MYI 之檔案大小加總),因此建議至少調高與目前之 indexes 大小相符。設定方法是增加一行設定到設定檔: /etc/my.cnf
key_buffer_size= 1100000000
設定後一樣需重新啟動 MySQL。
(5)[!!] Query cache is disabled
Query cache的作用在於 Cache 查詢(Query) 之結果以供後續相同的查詢使用,由變數query_cache_size 所控制,預設值為0,因此預設為 disabled,參考報告中最後的建議:
-------- Recommendations -----------------------------------------------------
Variables to adjust:
    query_cache_size (>= 8M)
增加一行設定到 /etc/my.cnf
query_cache_size = 8000000
設定後一樣需重新啟動 MySQL。
(6)[!!] Thread cache is disabled
Thread cache的作用在於每次建立新的連線 (Thread) 時,會先看Thread cache 中是否有可用的 Thread,若有則直接取用,若無才重新建立新的連線,如此可減少CPU Loading。若系統常有大量且短暫的連線發生,則適當的設定此參數非常重要。由變數thread_cache_size 所控制,預設值為0,因此預設為 disabled,參考報告中最後的建議:
-------- Recommendations -----------------------------------------------------
Variables to adjust:
    thread_cache_size (start at 4)
增加一行設定到 /etc/my.cnf
thread_cache_size=4
設定後一樣需重新啟動 MySQL,之後可用指令:
SHOW STATUS like 'Threads_%';
觀察變數 Threads_created 若已不會再持續增加,表示thread_cache_size已經足夠。

圖7. 觀察 STATUS 中 Threads 開頭之變數
 
(7)[!!] Connections aborted: 14%
此訊息搭配報告中最後的建議:
-------- Recommendations -----------------------------------------------------
General recommendations:
    Your applications are not closing MySQL connections properly
表示時常有連上 MySQL 之連線不正常斷線,一般正常的斷線程式需呼叫mysql_close() 來正常關閉,可搭配指令:
SHOW STATUS like 'Aborted_%';

圖8. 觀察 STATUS 中 Aborted 開頭之變數
可觀察變數Aborted_clients, Aborted_connects 是否已經不再持續增加,來判斷是否已經改善。
(8)General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
報告中最後還有兩個建議:
 1.因為MySQLTuner 是利用 MySQL 系統中之 STATUS 與 Variable 相關變數來判斷並提供建議,因此必須有足夠的執行時間來蒐集資料庫運作的效能,因此若資料庫運作時間不到 24小時,則會出現警告訊息。
 2.系統建議可開啟slow query log 之功能來記錄執行特別久的 SQL,以供後續的分析,方法為增加兩行設定到 /etc/my.cnf
log_slow_queries= mysqld-slow.log
long_query_time= 10
設定後一樣需重新啟動 MySQL。
log_slow_queries 指定 slow query log 存放之檔名,預設存在 /var/lib/mysql/ 目錄下。
long_query_time 設定超過幾秒之 SQL Query 會被記錄在 log_slow_queries 之log 檔中,系統預設值為 10秒。
二. 使用 Explain Plan 進行個別 SQL 調教
當資料庫的效能不符需求時,大部分發生在特定的 SQL 語句拖慢整個系統效能,蒐集這些 SQL 的方法本文提供兩種,一種就是用先前介紹的slow query log 方法記錄執行超過特定時間的 SQL,另一種方法是即時觀察系統目前之 Performance。
Step1. 指令: top

圖9. 使用 top 指令觀察系統資源使用情形
觀察CPU Usage%排名第一就是 mysqld,而且佔用了 99.9% 的 CPU Resource。
Step2. login MySQL,使用指令:
SHOW FULL PROCESSLIST;

圖10. 使用 SHOW FULL PROCESSLIST指令之執行結果
可看到目前正在執行之所有 SQL 語句,特別注意其中Process Id 8,Command 顯示 Query 表示正在執行,Time 欄位顯示已經執行了 117秒,Info 中有正在執行的 SQL 語句。
在得到了 SQL 語句之後,接著使用 Explain 觀察是否有可以改善之處.

圖11. 使用 Explain指令之執行結果
 
簡單說明各欄位之意義:
(1)select_type: SELECT 使用之語法型態。SIMPLE表示為簡單的查詢語句,也就是沒有 UNION、Subquery 等語法在此查詢中。
(2)table: 此 SQL 查詢所用到的 table,此案例因為使用了 Join因此用到了兩個 table。
(3)type: 使用何種類型進行查詢。最優至最差的類型為:
system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> ALL
此案例值為”ALL”,也就是最差之狀況。
(4)possible_keys:可能使用的索引欄位。若為NULL 表示無索引可使用。
(5)key:實際使用的索引欄位。若為NULL 表示無索引可使用。
(6)key_len:使用的索引長度。
(7)rows: SQL 必須搜尋的資料筆數。
(8)Extra:
Using where: SQL 中有使用WHERE 語句來限制查詢的範圍。
Using temporary : MySQL 必須建立一個臨時的 Table 來儲存查詢結果,通常發生在 SQL 語法中有 Order By 或 GROUP BY。
Using filesort:MySQL必須進行額外的步驟來進行 ORDER BY 之排序作業。
此段 SQL 執行之狀況,也可參考前面第一部分之介紹啟用slow query log,log 記錄檔中顯示:
/var/lib/mysql/mysqld-slow.log

圖12. slow query log 記錄之內容
此 SQL 共花了 121 秒才執行完成。
由上述的Explain 分析可知,此段 SQL 並未使用任何的索引,我們再次分析此段 SQL 中 JOIN 與 WHERE 條件中所出現的欄位:
LEFT JOIN … ON aruba.ArubaLocationID = arubalocationmap.ArubaLocationID
WHERE (YEAR(`AcctStartTime`) > '2012'…..
AND aruba.`ArubaLocationID` LIKE '113.%'  ….
其中特別注意的是 JOIN 語法中兩個 Table 的對應欄位都必須加上索引才有作用,因此我們決定針對
Table: aruba 增加 ArubaLocationID, AcctStartTime 兩個欄位的索引
Table: arubalocationmap增加 ArubaLocationID 一個欄位的索引
建立索引的語法如下:
CREATE Index ArubaLocationID On aruba (ArubaLocationID);
CREATE Index AcctStartTime On aruba (AcctStartTime);
CREATE Index ArubaLocationID On arubalocationmap (ArubaLocationID);
索引建立後,我們再次使用 Explain 觀察此段 SQL 執行之情況:


圖13. 使用 Explain指令之執行結果
由 type, key 欄位發現新建的索引已經發揮作用。
再次檢查 slow query log,log 記錄檔中顯示:
/var/lib/mysql/mysqld-slow.log

圖14. slow query log 記錄之內容
執行時間由 121 秒減少為 13秒,僅需原來 1/10的時間,由此可知建立正確的索引將有非常大幅度之改善。
 
結論
MySQL 是個功能強大的資料庫,尤其搭配 PHP 程式後可做出各式的動態網頁資料庫進行各類的應用,本文拋磚引玉使用簡單的範例來調整並改善資料庫的效能,投入的時間與改善並不需要太久,但卻能有十倍之 執行效能改善,這樣的改善效益是非常令人期待的。
 
參考資料
1. MySQLTuner http://mysqltuner.pl/
2. Explain plan http://dev.mysql.com/doc/refman/5.0/en/explain-output.html 3.query cache size (query_cache_size) http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

MySQL 設定檔優化-cache

http://article.denniswave.com/6345

From:http://www.bsdlover.cn/html/89/n-1789.html
  • thread_concurrency
  • 數量設置為CPU核心數量的兩倍.
  • thread_cache_size
  • 按照內存大小來設置, 1G=8, 2G=16, 3G=32, >3G=64

  • wait_timeout
  • 超時時間,如果連接數比較大,可以減少此參數的值,我使用的是10
  • max_connections
  • 最大連接數,mysql實際允許連接數的值是max_connections+1,按照系統庫不同而有不同性能。一般是500~1000,MySQL AB提供的linux靜態庫可以達到4000。
  • query_cache_size
  • 查詢緩衝,默認是0,所以必須打開以提高mysql性能,其本身需要40K來保存結構數據。所以不能設置的太小,初期可以設置成32M,然後根據實際運行情況另行調整。
  • query_cache_type
  • 指定查詢緩衝的類型:
    0是關閉
    1是緩衝除了使用 SELECT SQL_NO_CACHE 語句指明了不需要緩衝的數據意外的所有查詢
    2是只緩衝 SELECT SQL_CACHE 指定的查詢
    一般設置為1
  • query_cache_limit
  • 允許進入查詢緩衝區的最小數據大小,默認值是1MB,可以修改的小一點以滿足更多查詢的需求。但是如果設置的過於小,則會導致很多新的小查詢的結果將原有的查詢結果交換出去,增加系統的顛簸。
相關命令
  • 查詢mysql服務器相關狀態數據
  • >SHOW STATUS;
  • 查詢mysql服務器相關配置選項
  • >SHOW VARIABLES;
  • 整理查詢緩衝區裡的碎片
  • >flush query cache;
  • 刪除查詢緩衝區裡的所有內容
  • >reset query cache;
  • 設置mysql參數
  • >SET GLOBAL;
  • 查詢mysql當前執行的sql語句
  • >show processlist;
變量含義
  • 查詢緩存中的空閒內存塊的數目
  • Qcache_free_blocks
  • 查詢緩存的空閒內存總數
  • Qcache_free_memory
  • 緩存採樣數數目
  • Qcache_hits
  • 被加入到緩存中的查詢數目
  • Qcache_inserts
  • 因為缺少內存而被從緩存中刪除的查詢數目
  • Qcache_lowmem_prunes
  • 沒有被緩存的查詢數目 (不能被緩存的,或由於 QUERY_CACHE_TYPE)
  • Qcache_not_cached
  • 在緩存中已註冊的查詢數目
  • Qcache_queries_in_cache
  • 查詢緩存中的塊的總數目
  • Qcache_total_blocks
MySQL查詢優化
查詢Cache狀態
>SHOW STATUS LIKE 'Qcache%';
如果Qcache_lowmem_prunes非常大,說明因為內存不足而被交換出cache的數據很多.如果增加內存.可以保證較小的交換次數以及較高的命中率
例如現在我們查詢的結果如下:
Qcache_free_blocks 1234
Qcache_free_memory 25957504
Qcache_hits 55771119
Qcache_inserts 7441153
Qcache_lowmem_prunes 28332
Qcache_not_cached 1233788
Qcache_queries_in_cache 4810
Qcache_total_blocks 11038
設置為64M cache內存後
>set global query_cache_size=67108864;
Qcache_free_blocks 1
Qcache_free_memory 66623616
Qcache_hits 55788258
Qcache_inserts 7445445
Qcache_lowmem_prunes 28332
Qcache_not_cached 1234057
Qcache_queries_in_cache 183
Qcache_total_blocks 392
自由內存塊看起來變小了。
是因為現在自由內存塊是一個整塊,而以前的內存塊都是分散的小塊。而因為重建了cache區,Qcache_queries_in_cache 變量變小了。因為此操作重新建立了cache內存區,所有數據重新緩存,在運行一兩天後我們再看此數據。如果變大了,說明增大cache內存區域是有效 的,如果和以前數據差不多說明增加的內存並沒有實際起到多大的作用。
有人會覺得如果我將cache內存設置的非常大,然後將cache_limit設置成0,那麼所有查詢都會被緩存了。理論上是這樣,但是一台數據庫 服務器的查詢非常多,如果連查詢單條數據都要緩存,那麼內存再大也會不夠的。到時候老的內容就會被交換出去,當cache內存使用滿的時候,就會不停的有 新查詢進來將老查詢替換出去。
這樣導致兩個結果:一個是內存顛簸,效率反而下降;第二個是cache內存的小碎塊增多,內存利用率降低。如果是只有內容很少的小庫,並且查詢率不高,是可以使用這種方法提高響應速度。但是如果是實際生產環境,數據量會比較大,還是需要按照最佳比例來配置。
而不同的應用不同的數據量會有不同的搭配,這點大家不要看網上的優化配置隨便的填寫,還是要時時的查看mysql的狀態進行調整。即便是這個月調整好的優化參數,到了下個月業務不同,數據量增加,也會需要調整的。

2014年7月1日 星期二

PHP中查看错误log

http://blog.csdn.net/hopestar2/article/details/5960616

PHP中查看错误log

分类: PHP 4522人阅读 评论(1) 收藏 举报
    php中需要查看错误log,如何设置?
    首先需要在php.ini打开开关和制定错误log路径
    log_errors = On
    error_log = d://phperror.log   //windows下的设置

    如果是在linux 下,使用lighttpd服务器,则可以使用如下方式查看log文件
    tail -n 20 /usr/local/lighttpd/log/php-error.log

    在php代码中如何记录log,在PHP官方文档中有个函数error_log(),使用方法如下:

  定义和用法

    error_log() 函数向服务器错误记录、文件或远程目标发送一个错误。
   若成功,返回 true,否则返回 false。

   语法

   error_log(error,type,destination,headers)
 
参数 描述
error 必需。要记录的错误消息。
type 可选。规定错误记录的类型。
可能的记录类型:
  • 0 - 默认。根据在 php.ini 文件中的 error_log 配置,错误被发送到服务器日志系统或文件。
  • 1 - 错误被发送到 destination 参数中的地址。只有该类型使用 headers 参数。
  • 2 - 通过 PHP debugging 连接来发送错误。该选项只在 PHP 3 中可用。
  • 3 - 错误发送到文件目标字符串。
destination 可选。规定向何处发送错误消息。该参数的值依赖于 "type" 参数的值。
headers 可选。只在 "type" 为 1 时使用。
规定附加的头部,比如 From, Cc 以及 Bcc。由 CRLF (/r/n) 分隔。
注释:在发送电子邮件时,必须包含 From 头部。可以在 php.ini 文件中或者通过此参数设置。

例子

本例发送一封带有自定义错误的电子邮件:
<?php
$test=2;

if ($test>1)
{
error_log("A custom error has been triggered",
1,"someone@example.com","From: webmaster@example.com");
}
?>
输出:
A custom error has been triggered
 PS: 在调试php代码的时候,除了使用error_log()函数,还可以使用 throw new Exception()抛出异常信息。

PHP logging

http://www.devshed.com/c/a/php/logging-with-php/

Logging With PHP

Want to log script activity in your Web application, but have no clue where to begin? Take our handy tour of PHP’s logging functions, and find out how simple it really is.

I have to warn you not to be misled by the title of this article. It isn’t really about cutting down trees with PHP.

I know that PHP is the Swiss Army knife of Web scripting languages, but I don’t think its developers have come up with a way to have it reduce our tree cover (at least not yet). Rather, this article is about a different sort of logging – the sort which involves writing messages to files or devices on a regular basis, and using these messages to “do something useful”, be it generating a report or building an audit trail for activity tracking.

Over the next few pages, I’m going to take a quick look at the various mechanisms available to log script activity via PHP, demonstrating both built-in functions and add-on classes that allow you to add logging functionality to your applications. I’ll also spend some time on the PHP functions and configuration options related to logging, demonstrating how, for example, PHP errors can be sent to the Web server error log or emailed to an administrator when they are over a particular priority level.

My primary audience for this tutorial is the novice to intermediate PHP user – if you’re an advanced user, you probably already know enough about logging with PHP, and might prefer to skip the rest of this article in favour of more nutritious fare. Everyone else – keep reading!{mospagebreak title=The Bare Necessities} Logging data to a file in PHP can be as simple or as complex as you want to make it. Break it down, though, and it all comes down to these three simple lines of code:

<?php // open file $fd = fopen($filename, “a”); // write string fwrite($fd, $str . “n”); // close file fclose($fd); ?>
Fundamentally, logging data to a file consists of three steps:

1. Open the target file (or create it if it doesn’t already exist);

2. Append your data to the end of the file;

3. Close the file.

You can encapsulate this as a function,

<?php function logToFile($filename, $msg) { // open file $fd = fopen($filename, “a”); // write string fwrite($fd, $msg . “n”); // close file fclose($fd); } ?>
and then use it liberally within your code as and when required.

<?php function logToFile($filename, $msg) { // open file $fd = fopen($filename, “a”); // write string fwrite($fd, $msg . “n”); // close file fclose($fd); } $v = “Mary had a little lamb”; if (!is_numeric($v)) { logToFile(“my.log”, “Non-numeric variable encountered”); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { logToFile(“my.log”, “No fish available”); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { logToFile(“my.log”, “Could not connect to database”); die(“Could not connect to database”); } ?>
You can make the function a little more professional by having it automatically append the date and time of the log message to the log file as well:

<?php function logToFile($filename, $msg) { // open file $fd = fopen($filename, “a”); // append date/time to message $str = “[" . date("Y/m/d h:i:s", mktime()) . "] ” . $msg; // write string fwrite($fd, $str . “n”); // close file fclose($fd); } ?>
Here’s an example of the output:

[2002/11/25 06:02:42] Non-numeric variable encountered [2002/11/25 06:02:42] No fish available [2002/11/25 06:02:43] Could not connect to database
{mospagebreak title=Turning The Tables} While the most common destination for log data is a text file, it’s quite possible that you might want to send your log messages elsewhere as well. A frequently-used alternative to a text file, especially when the number of writes isn’t too high, is an SQL database, where log messages are appended as records to the end of a table.

In order to illustrate this, consider the following example of an SQL table used to store log messages:

CREATE TABLE `log` ( `date` TIMESTAMP NOT NULL, `type` TINYINT NOT NULL, `msg` VARCHAR(255) NOT NULL );
And here’s the rewritten logToDB() function, this time built around PHP’s MySQL database functions and the table above:

function logToDB($msg, $type) { // open connection to database $connection = mysql_connect(“localhost”, “joe”, “pass”) or die (“Unable to connect!”); mysql_select_db(“logdb”) or die (“Unable to select database!”); // formulate and execute query $query = “INSERT INTO log (date, type, msg) VALUES(NOW(), ‘$type’, ‘$msg’)”; mysql_query($query) or die (“Error in query: $query. ” . mysql_error()); // close connection mysql_close($connection); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { logToDB(“No fish available”, 14); }
If you don’t estimate seeing a very large number of writes, using a database to store logs can offer significant advantages over a regular file, purely from the point of view of easier retrieval and sorting. Storing log messages in this manner makes it possible to easily retrieve ordered subsets of the log data, either by date or message type.

Another option might be to send log messages to a specified email address via PHP’s mail() function – for example, alerting a sysop whenever a script encounters errors. Here’s an example:

function logToMail($msg, $address) { // append date/time to message $str = “[" . date("Y/m/d h:i:s", mktime()) . "] ” . $msg; mail($address, “Log message”, $str); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { logToMail(“Could not connect to database”, “webmaster@my.domain.name.com”); }
{mospagebreak title=Turning Up The Heat} If the thought of rolling your own code doesn’t appeal to you, you can also use PHP’s built-in error_log() function, which logs data both to a file and elsewhere. The error_log() function needs a minimum of two arguments: the error message to be logged, and an integer indicating where the message should be sent. There are three possible integer values in PHP 4.x:

0 – send the message to the system logger (“syslogd” on *NIX, and the Event Log on Windows NT);

1 – send the message to the specified email address;

3 – send the message to the specified file;

Here’s a trivial example which demonstrates how this works:


<?php // set a variable $temp = 101.6; // test it and log an error // this will only work if // you have “log_errors” and “error_log” set in your php.ini file if ($temp > 98.6) { error_log(“Body temperature above normal.”, 0); } ?>
Now, if you look at the system log file after running the script, you’ll see something like this:

[28-Feb-2002 15:50:49] Body temperature above normal.
You can also write the error message to a file,

<?php // set a variable $temp = 101.6; // test it and log an error if ($temp > 98.6) { error_log(“Body temperature above normal.”, 3, “a.out”); } ?>
or send it out as email.

<?php // set a variable $temp = 101.6; // test it and log an error if ($temp > 98.6) { error_log(“Body temperature above normal.”, 1, “administrator@this.body.com”); } ?>
It’s possible to combine this error logging facility with a custom error handler to ensure that all script errors get logged to a file. Here’s an example which demonstrates this:

<?php // custom handler function eh($type, $msg, $file, $line) { // log all errors error_log(“$msg (error type $type)”, 0); // if fatal error, die() if ($type == E_USER_ERROR) { die($msg); } } // report all errors error_reporting(E_ALL); // define custom handler set_error_handler(“eh”); // let’s now write some bad code // this will trigger a warning, since the file doesn’t exist include(“common.php”); ?>
And here’s the output that gets logged to the system log file:

[28-Feb-2002 16:15:06] Failed opening ‘common.php’ for inclusion (include_path=’.;’) (error type 2)
{mospagebreak title=Biting Into A PEAR} As if all that wasn’t enough, PHP offers one more alternative – a special Log class that comes courtesy of PEAR, the PHP Extension and Application Repository (http://pear.php.net). In case you didn’t know, PEAR is an online repository of free PHP software, including classes and modules for everything from data archiving to XML parsing. This Log class is maintained by Jon Parise, and the latest version can be downloaded from http://pear.php.net/package-info.php?package=Log

Like the error_log() function, the PEAR Log class allows logging to a variety of different destinations – system logger, text file, email address, database and even MCAL. Here’s an example demonstrating basic usage:

<?php // include class include(“Log.php”); // create Log object $l = &Log::singleton(“file”, “my.log”); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
The first step here is to include() the Log class:

// include class include(“Log.php”);
Once that’s done, a new Log object can be created. This object constructor requires, as its first parameter, a string indicating the kind of log to open – current valid values are ‘console’, ‘syslog’, ‘sql’, ‘file’, and ‘mcal’. Depending on the log type, additional data may be provided – the name of the file to use in case of a file logger, for example.

// create Log object $l = &Log::singleton(“file”, “my.log”);
The Log class internally defines eight priority levels for logging – here’s the list, culled directly from the source code of the class:

$priorities = array( PEAR_LOG_EMERG => ‘emergency’, PEAR_LOG_ALERT => ‘alert’, PEAR_LOG_CRIT => ‘critical’, PEAR_LOG_ERR => ‘error’, PEAR_LOG_WARNING => ‘warning’, PEAR_LOG_NOTICE => ‘notice’, PEAR_LOG_INFO => ‘info’, PEAR_LOG_DEBUG => ‘debug’ );
Each log message that you send to the logger can be flagged with a specific priority, and the logger can be set up to log only those messages matching or exceeding a specific priority level (by default, all messages are logged). This can be clearly seen from the test code below, in which each message to the logger includes a priority level:

$v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); }
Here’s what the log file looks like:

Nov 26 06:47:15 [warning] Non-numeric variable encountered Nov 26 06:47:15 [error] No fish available Nov 26 06:47:17 [critical] Could not connect to database
If you’d like to tell the logger to only log messages above a certain priority level – for example, critical and above – this priority level should be specified as the fifth argument to the object constructor. Consider the following revision of the previous example:

<?php // include class include(“Log.php”); // create Log object $l = &Log::singleton(“file”, “my.log”, NULL, array(), PEAR_LOG_ERR); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
In this case, only messages flagged as PEAR_LOG_ERR and above will be written to the specified log file.{mospagebreak title=Destination Unknown} The Log class also supports sending log messages to the console, the system logger, an SQL database or a user-specified email address. Take a look:

<?php // include class include(“Log.php”); // create Log object $l = &Log::singleton(“console”); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
In this case, log messages are directed to the text console (if you’re running this script via a Web server, the console is the browser window). Here’s the output:

Nov 26 22:05:25 [warning] Non-numeric variable encountered Nov 26 22:05:25 [error] No fish available Nov 26 22:05:27 [critical] Could not connect to database
Messages can also be sent to an SQL database,

<?php // include class include(“Log.php”); // create Log object // second argument is table name // fourth argument is PHP::DB compatible DSN for database access $l = &Log::singleton(“sql”, “log_table”, “”, array(‘dsn’ => ‘mysql://joe:pass@localhost/test’)); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
which can then be queried to retrieve subsets of the log messages, sorted by date or priority level.

mysql> SELECT * FROM log_table WHERE priority >= 4; +—————-+——-+———-+———————————-+ | logtime | ident | priority | message | +—————-+——-+———-+———————————-+ | 20021126074936 | | 4 | Non-numeric variable encountered | | 20021126074936 | | 4 | Non-numeric variable encountered | | 20021126074937 | | 4 | Non-numeric variable encountered | +—————-+——-+———-+———————————-+ 3 rows in set (0.05 sec)
Finally, log messages can also be directed to a specified email address, as in the following example:

<?php // include class include(“Log.php”); // create Log object $l = &Log::singleton(“mail”, “admin@host.com”); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
You can customize the mail message by adding an array containing a custom From: and Subject: line to the object constructor, as below:

<?php // include class include(“Log.php”); // create Log object $l = &Log::singleton(“mail”, “admin@host.com”, NULL, array(‘from’ => ‘L. Ogger’, ‘subject’ => ‘Log message’)); // test it $v = “Mary had a little lamb”; if (!is_numeric($v)) { $l->log(“Non-numeric variable encountered”, PEAR_LOG_WARNING); } $a = array(“chocolate”, “strawberry”, “peach”); if (!in_array(‘fish’, $a)) { $l->log(“No fish available”, PEAR_LOG_ERR); } $conn = @mysql_connect(“localhost”, “joe”, “pass”); if (!$conn) { $l->log(“Could not connect to database”, PEAR_LOG_CRIT); } ?>
{mospagebreak title=Artificial Intelligence} You can also configure PHP to automatically log all script errors to a specific file via the special “log_errors” configuration directive in “php.ini”. This directive, when set to true, logs all PHP errors to either a user-specified log file (the value of this file must be specified in the “error_log” configuration directive, also set via “php.ini”), or to the Web server error log if no log file is specified.

Consider the following example, which demonstrates:

<?php // turn on automatic error logging ini_set(‘log_errors’, true); // include non-existent file include(“non.existent.file.php”); ?>
In this case, I’m manually instantiating an error by attempting to include a file which does not exist. Obviously, PHP will barf and display an error screen containing the following:

Warning: Failed opening ‘non.existent.file.php’ for inclusion (include_path=’.;/usr/local/php/includes’) in error.php on line 10
The same error also appears in the server’s error log:

[Tue Nov 26 12:49:31 2002] [error] PHP Warning: Failed opening ‘non.existent.file.php’ for inclusion (include_path=’.;/usr/local/php/includes’) in error.php on line 10
You can turn off PHP error display, and only have the error message appear in the log file, by setting the “display_errors” variable (also accessible via “php.ini”) to false. Consider the following variant of the previous example, which demonstrates:

<?php // turn on automatic error logging ini_set(‘log_errors’, true); // turn off error display ini_set(‘display_errors’, false); // include non-existent file include(“non.existent.file.php”); ?>
In this case, though an error takes place, it is never displayed to the user, but merely gets logged to the server’s error log.

On *NIX systems, setting the “error_log” variable to the special value “syslog” logs all errors via the standard “syslog” daemon.{mospagebreak title=Big Brother Is Watching} Finally, let’s wrap things up with a couple of examples that show how the various techniques demonstrated above can be used to build logs and audit trails for a Web application.

In the first example, every time a Web page is displayed, a log entry is made in an “access.log” file. This log entry is a comma-separated list of values containing the URL requested, the client browser identification string, and a timestamp.

<?php // create log string $str = date(“Y/m/d h:i:s”, mktime()) . “,” . $_SERVER['REQUEST_URI'] . “,” . $_SERVER['HTTP_USER_AGENT'] . “n”; // write to file error_log($str, 3, “access.log”); // rest of page here ?>
Here’s a snippet from the access log:

2002/11/26 09:07:38,/alpha.php,Mozilla/4.0 (compatible; MSIE 5.0; Windows 95) 2002/11/26 09:07:38,/alpha.php,Mozilla/4.0 (compatible; MSIE 5.0; Windows 95) 2002/11/26 09:10:31,/home/hello.php,Mozilla/4.0 (compatible; MSIE 5.0; Windows 95) 2002/11/26 09:17:38,/index.php,Mozilla/4.0 (compatible; MSIE 5.0; Windows 95) 2002/11/26 09:17:38,/base/index.php,Mozilla/4.0 (compatible; MSIE 5.0; Windows 95)
Since this data is in a structured format, it can easily be analyzed and a “hit count” created for each URL in the file. This next script does exactly that:

<?php // create array to hold unique URLs $urlStats = array(); // read access log $lines = file(“access.log”); // iterate through access log foreach ($lines as $l) { $data = explode(“,”, $l); $ts = $data[0]; $url = $data[1]; $agent = $data[2]; // check to see if URL exists in array // if it does, increment incidence count // if it does not, create a new key with incidence count 1 if ($urlStats[$url]) { $urlStats[$url]++; } else { $urlStats[$url] = 1; } } // print list of unique URLs with count print_r($urlStats); ?>
This script parses the “access.log” file, and creates a PHP associative array whose keys correspond to the URLs found in the file. The value associated with each key is an integer indicating the number of appearances the URL makes in the file. Once the entire file has been parsed, the $urlStats array contains a list of all the unique URLs in the access log, together with the number of times each has appeared. This data can then be used to generate a report of the most frequently-accessed URLs.

Consider this next example, which provides an API for adding, editing and deleting users to (from) a Web application. Each time the user database is edited, a separate audit() process tracks the change, logging both the nature of the change and information about the user initiating the change. This log data is stored in a separate SQL table, from where it can be retrieved for statistical reporting, user activity monitoring or debugging.

<?php // assume that administrator has logged in to system to perform user-administration tasks // admin username is stored in a session variable by default // this is useful for audit purposes session_start(); $_SESSION['LOGGED_IN_USER'] = “john”; // add a new user function addUser($user, $pass, $perms) { // open connection to database $connection = mysql_connect(“localhost”, “joe”, “pass”) or die (“Unable to connect!”); mysql_select_db(“myapp”) or die (“Unable to select database!”); // formulate and execute query $query = “INSERT INTO users (user, pass, perms) VALUES(‘$user’, ‘$pass’, ‘$perms’)”; mysql_query($query) or die (“Error in query: $query. ” . mysql_error()); // log activity to audit database audit(“ADD_USER”, $_SESSION['LOGGED_IN_USER'], “$user:$pass:$perms”, addslashes($query)); // close connection mysql_close($connection); } // edit an existing user function updateUser($user, $pass, $perms) { $connection = mysql_connect(“localhost”, “joe”, “pass”) or die (“Unable to connect!”); mysql_select_db(“myapp”) or die (“Unable to select database!”); // formulate and execute query $query = “UPDATE users SET pass = ‘$pass’, perms = ‘$perms’ WHERE user = ‘$user’”; mysql_query($query) or die (“Error in query: $query. ” . mysql_error()); // log activity to audit database audit(“UPDATE_USER”, $_SESSION['LOGGED_IN_USER'], “$user:$pass:$perms”, addslashes($query)); // close connection mysql_close($connection); } // delete an existing user function deleteUser($user) { $connection = mysql_connect(“localhost”, “joe”, “pass”) or die (“Unable to connect!”); mysql_select_db(“myapp”) or die (“Unable to select database!”); // formulate and execute query $query = “DELETE FROM users WHERE user = ‘$user’”; mysql_query($query) or die (“Error in query: $query. ” . mysql_error()); // log activity to audit database audit(“DELETE_USER”, $_SESSION['LOGGED_IN_USER'], “$user”, addslashes($query)); // close connection mysql_close($connection); } // generic audit function // logs all activity to a database function audit($op, $owner, $args, $msg) { $connection = mysql_connect(“localhost”, “root”, “pass”) or die (“Unable to connect!”); mysql_select_db(“trails”) or die (“Unable to select database!”); // formulate and execute query $query = “INSERT INTO audit (timestamp, op, owner, args, msg) VALUES (NOW(), ‘$op’, ‘$owner’, ‘$args’, ‘$msg’)”; mysql_query($query) or die (“Error in query: $query. ” . mysql_error()); } addUser(“joe”, “joe”, 3); addUser(“sarahh”, “bsdfg49″, 1); updateUser(“joe”, “joe”, 4); deleteUser(“sarahh”); addUser(“sarah”, “bsdfg49″, 1); ?>
Here’s a snippet from the audit table:

+———————+————-+——-+ | timestamp | op | owner | +———————+————-+——-+ | 2002-11-26 08:28:05 | UPDATE_USER | john | | 2002-11-26 08:28:05 | DELETE_USER | john | | 2002-11-26 08:28:05 | ADD_USER | john | | 2002-11-26 08:33:14 | ADD_USER | joe | +———————+————-+——-+
This audit table can then be queried to obtain detailed information on the activities performed by the various users, sorted by time or type of activity. For example,

mysql> SELECT timestamp, op, args FROM trails WHERE timestamp >= mysql> 2002-11-26 AND owner = ‘joe’; +———————+————-+——————+ | timestamp | op | args | +———————+————-+——————+ | 2002-11-26 08:33:29 | ADD_USER | joe:joe:3 | | 2002-11-26 08:33:29 | ADD_USER | sarahh:bsdfg49:1 | | 2002-11-26 08:33:29 | UPDATE_USER | joe:joe:4 | | 2002-11-26 08:33:29 | DELETE_USER | sarahh | | 2002-11-26 08:33:29 | ADD_USER | sarah:bsdfg49:1 | +———————+————-+——————+
This is a somewhat trivial example, but it serves to demonstrate the concept of logging activity and using those logs to build an audit trail. While you can make this as complex as you want, tracking everything from user clicks to form input in order to gain a better understanding of how users navigate through and use your application, remember that every addition to the log affects the overall performance of your application; log too much data and your application will suffocate and die.{mospagebreak title=Closing Time} And that’s about it for the moment. In this article, I offered you a brief overview of logging in PHP, demonstrating how you can use built-in functions, off-the-shelf libraries or your own code to easily and efficiently create logs of the activity taking place in your application. I demonstrated logging to a file, to a database, to an email address and to the console, and wrapped things up with a couple of simple examples that demonstrated how logs could be built and analyzed in a real-world application.

I hope you enjoyed reading this article as much as I enjoyed writing it. If you’d like to know more about the topics discussed in the previous pages, here are some links you should bookmark:

PHP file manipulation functions, at http://www.php.net/manual/en/ref.filesystem.php

PHP mail functions, at http://www.php.net/manual/en/ref.mail.php

PHP MySQL functions, at http://www.php.net/manual/en/ref.mysql.php

PHP error logging functions, at http://www.php.net/manual/en/ref.errorfunc.php

The PHP Log class, at http://pear.php.net/package-info.php?package=Log

PEAR, at http://pear.php.net

Till next time…stay healthy!

Note: All examples in this article have been tested on Linux/i586 with PHP 4.2.3. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!
This entry was posted in PHP. Bookmark the permalink.