Log in

Tutorial: set up PHP environment on Ubuntu

Suppose you are on a newly install Ubuntu server. Follow the steps to set up PHP environment:

1. sudo apt-get install apache2
2.sudo apt-get install mysql-server
3. sudo apt-get install php5
4. sudo apt-get install php5-mysql
5.sudo apt-get install php5-curl
6. sudo apt-get install libapache2-mod-php5
if libapache2-mod-php5 already installed,
1.remove it: sudo apt-get remove --purge libapache2-mod-php5
2.install it again: sudo apt-get install libapache2-mod-php5

May you are expecting more steps. But actually you have done it!

Let install wordpress to test our environment:

1. download wordpress from worpress.org
2. extract the context of wordpress to /var/www
3. open you browser and point to
Sometimes you are in an internal network, and the administrator of that internal network blocked the access to some websites, such as facebook, youtube. We all know that this happens sometimes, especially in a company internal network, and you boss wants you to work hard all the time. He will become angry if he see you are navigating facebook in business hours. So please pay attention to him after you read this tutorial :) I suppose that you are using ubuntu linux. And I suppose that you have some basic computer and linux skills. OK, let's  begin. Do the following on your proxy server:
  1. install squid:  sudo apt-get squid squid-common
  2. edit /etc/squid/squid.conf, find this line "http_access deny all", change it to "http_access allow all"
  3. restart squid: sudo /etc/init.d/squid restart
On you own computer:
  1. set proxy for you browser: the IP address is the IP of you proxy server, and the port number is 3128 (default port number)
  2. have a test of the proxy server. Use your browser to browse some website such as facebook.com
  3. If step 2 is successful, enjoy yourself.
  4. If step 2 fails, check /var/log/squid/cache.log on your proxy server and find out what's wrong.
Bingo! Hey, wait a minute! but your proxy server is not for everybody, is it?  So you may want to add authentication to your proxy server. Here is the steps to be performed on your proxy server:
  1. sudo apt-get install apache2-utils
  2. change "http_access allow all" back to "http_access deny all"
  3. edit /etc/squid/squid.conf, uncomment some lines and change them, make them look like below lines:
    • auth_param basic program /usr/lib/squid/ncsa_auth /etc/squid/passwd auth_param basic children 5 auth_param basic realm Squid proxy-caching web server auth_param basic credentialsttl 2 hours auth_param basic casesensitive off acl auth_user proxy_auth REQUIRED acl sectionx proxy_auth REQUIRED
  4. create proxy user and its password: sudo htpasswd -c /etc/squid/passwd user1
  5. restart squid: sudo /etc/init.d/squid restart
on your own computer, test again, you will be prompted to enter your username and password for the proxy server. Bingo again!!
step 1: create a function:

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert character varying)
RETURNS integer AS
THEN CAST(trim($1) AS integer)


step 2: convert:

ALTER TABLE table_name ALTER COLUMN column_name TYPE integer USING pc_chartoint(column_name);

how to fix corrupt mysql database

Sometimes mysql database may become corrupt because of large amount of data. Here is some tips about how to fix it:
  1. go to the directory where the database is stored. In ubuntu, the directory is within /var/lib/mysql/
  2. check if the database is really corrupt: myisamchk *.MYI
  3. fix the database if it is corrupt: myisamchk -o *.MYI
du: Summarize disk usage of each FILE, recursively for directories. You may want to find out which files takes you a lot of disk space. there is no need to go through every folder and ls -l. Use du instead

&>: redirect stdout and stderr to the same file. for example: echo 'abc' &> log.txt
>>: append stdout to file.
command >> filename 2>&1: append stdout and stderr to the same file (append)

sleep 5: sleep 5 seconds

free -m: How large is your memory?

migrate from mysql to postgresql

  1. use command line:
    • psql -U postgres (login command line as super user postgresql)
  2. logout command line:
    • \q
  3. use [database_name]:
    • psql -U postgres database_name
  4. view current connections: (in mysql use command "show full processlist" ):
    • SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
  5. describe table:
    • \d [table_name]
  6. alter table:
    • alter table [table_name] alter column [column_name] type [type]
  7. show databases:
    • \l
  8. show tables:
    • \dt
  9. show indexes:
    • \di
  10. easy way to increase performance:
    • edit /opt/PostgreSQL/8.4/data/postgresql.conf, increase shared_buffer by several tens of MB
    • you may also need to modify you linux kernel: sysctl -w kernel.shmmax=150000000. set a value larger than postgresql shared_buffer, otherwise postgresql won't start.
  11. backup and restore:
    • pg_dump -U [user_name] [db_name] -f [file_name]
    • psql -U [user_name] -d [db_name] -f[file_name]
  12. change a sequence:
    • alter sequence [sequence_name] restart with 100; (restart a sequence and restart it at 100)

mysql connection time out problem

If you connect to mysql and do nothing in 8 hours, the connection will be time out.  You can change the feature of mysql by editing the /etc/mysql/my.cnf file.

add the following two lines to  [mysqld] section:
wait_timeout               =86400
interactive_timeout    =86400

Above two lines change the default time out period to 86400 seconds which equal to one day. Don't forget to restart mysql: /etc/init.d/mysql restart

see you current time out settings:
SHOW VARIABLES like '%timeout%';

Note: if there are two many idle connections, you may want to change the time out period to a very short time, such as 5 minutes. Then you will not meet "already too many connections" problem.

Install sphinxsearch 0.99 on ubuntu 9.10

  1. download the latest source file from http://sphinxsearch.com/downloads.html (download the one with suffix .tar.gz)
  2. extract the file:  tar -zxvf tar -zxvf sphinx-0.9.9-rc2.tar.gz
  3. cd sphinx-0.9.9-rc2
  4. run "mysql_config --cflags" and it will tell you which package you need to install. mine is: sudo apt-get install libmysqlclient15-dev
  5. sudo ./configure
  6. sudo make
  7. sudo make install
  1. You may meet some errors when you are doing step 4 and 5. Most likely you need to install some libs. Just sudo apt-get install [lib-name] and get them all done. (you need to figure out what you have to install yourself, it depends on your server)
  2. most likey, you will need to install a C compiler if you haven't done so. Just one command: sudo apt-get install build-essential

change character set to utf8 for mysql

The default character set for mysql is latin1 which does not support Chinese, Japanese...etc. Here is how to change the character set to utf8.
  1. first change the default character set for mysql. edit /etc/mysql/my.cnf. add "default-character-set    =utf8" to section [client] and [mysqld]
  2. restart mysql "/etc/init.d/mysql restart"  and check the default character sets in mysql:    show variables like 'char%';
  3. change character set for database: ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  4. for each table ALTER TABLE table_XXX CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Then mysql, the database created and tables created are all utf8, cheers!