FreeBSD step 3 - FAMP (FreeBSD, Apache, MySQL, PHP)

FreeBSD Settings

Edit hosts file, for correct Apache working

# ee /etc/hosts
::1                localhost localhost.example.com
127.0.0.1        localhost localhost.example.com
9.9.9.9            ns2.v6 ns2
9.9.9.9            ns2.v6.

Update port tree:

portsnap fetch extract update
/etc/periodic/weekly/310.locate

Let startup scripts to load Apache22 and MySQL 5.5 server automatically after installation:

echo 'apache22_enable="YES"' >> /etc/rc.conf
echo 'mysql_enable="YES"' >> /etc/rc.conf

Apache/2.2.22

To start Apache22, there are need to load module "accf_http"
which buffers incoming connections until a certain complete HTTP requests arrive, into FreeBSD kernel by using kernel linker.

stats# [warn] (2)No such file or directory: Failed to enable the 'httpready' Accept Filter
stats# echo ‘accf_http_load=”YES” ‘ >> /boot/loader.conf
stats# kldload accf_http
stats# kldstat
Id Refs Address            Size     Name
 1    3 0xffffffff80200000 11cd9b0  kernel
 2    1 0xffffffff81412000 7f3      accf_http.ko

stats# apachectl restart
httpd: Could not reliably determine the server's fully qualified domain name, using 9.9.9.9 for ServerName
httpd not running, trying to start
stats# mcedit /usr/local/etc/apache22/httpd.conf
Line 148, Uncomment line with ServerName
    #ServerName www.example.com:80
    ServerName www.example.com:80

stats# apachectl -v
Server version: Apache/2.2.22 (FreeBSD)
stats# lynx 127.0.0.1
 It works!

Test config File

apachectl configtest
Syntax OK 

Restart Graceful
Graceful signal causes the parent process to advise the children to exit after their current request (or to exit immediately if they're not serving anything).
The parent re-reads its configuration files and re-opens its log files. As each child dies off the parent replaces it with a child from the new generation of the configuration, which begins serving new requests immediately.

apachectl -k graceful

Test Web server with

a) $ httperf --hog --num-conns=100 --rate=10 --timeout=5  --server=some_ip
# 100 connections are created at a fixed rate of 10 per second
b)  $ ab -n 1000 -c 5 http://some_ip/
$ ab -k -n 10000 -c 2 -e apache2r1.cvs http://some_ip/ 

PHP 5.3.10

Install PHP5.3 from FreeBSD ports:

stats# cd /usr/ports/lang/php53
Check "Apache module"
stats# make config-recursive install clean

After compiling and installing, will appear:

***************************************************************
Make sure index.php is part of your DirectoryIndex.

You should add the following to your Apache configuration file:

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
***************************************************************

Additional PHP5 extension can be installed by:

cd /usr/ports/lang/php5-extensions
make install distclean

Let's do some PHP5 settings:

stats# cd /usr/local/etc
stats# cp php.ini-production php.ini
stats# mcedit ./php.ini
1) date.timezone = "Europe/Chisinau"
 PHP Warning:  strtotime(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'Europe/Helsinki' for 'EEST/3.0/DST' instead in ./include/global_arrays.php on line 639

Configure PHP5 for Apache22:

stats# mcedit /usr/local/etc/apache22/httpd.conf
Line 104, Just verify correct PHP module load line
    LoadModule php5_module        libexec/apache22/libphp5.so

Line 216, Add Index.php to load auto index

DirectoryIndex index.php index.html

   

Line 342, Add php handler
    AddType application/x-httpd-php .php
    AddType application/x-httpd-php-source .phps

Verify Apache22 and PHP5 works OK:

stats# php -v
PHP 5.3.10 with Suhosin-Patch (cli) (built: Mar 5 2012 15:46:28)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

stats# echo '' > /usr/local/www/apache22/data/info.php

stats# apachectl restart

stats# lynx http://127.0.0.1/info.php
PHP Logo

PHP Version 5.3.10

System FreeBSD stats 9.0-RELEASE FreeBSD 9.0-RELEASE #1: Mon Mar 5 22:28:22 EET 2012 sc@stats:/usr/obj/usr/src/sys/MYKERN amd64
Build Date Mar 6 2012 09:43:23
Configure Command './configure' '--with-layout=GNU' '--localstatedir=/var' '--with-config-file-scan-dir=/usr/local/etc/php' '--enable-libxml' '--enable-mysqlnd'
Server API Apache 2.0 Handler
Configuration File (php.ini) Path /usr/local/etc
Loaded Configuration File /usr/local/etc/php.ini
Scan this dir for additional .ini files /usr/local/etc/php
Additional .ini files parsed /usr/local/etc/php/extensions.ini

MySQL Server 5.0

CHECK  AVAILABLE VERSION AND UPDATE PORT TREE IF NEEDED
$ cat /usr/ports/databases/mysql50-server/Makefile  | grep PORTVERSION
PORTVERSION=    5.0.95
#portsnap fetch update

INSTALL
cd /usr/ports/databases/mysql50-server; make config-recursive install clean;
mysql_install_db --user=mysql;
chown -R mysql /var/db/mysql/ ; chgrp -R mysql /var/db/mysql/
/usr/local/bin/mysqld_safe -user=mysql &
echo ‘mysql_enable=”YES”‘ >> /etc/rc.conf
cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
/usr/local/etc/rc.d/mysql-server restart

CHECK INSTALLATION
$ mysql -V
mysql  Ver 14.12 Distrib 5.0.95, for portbld-freebsd9.0 (amd64) using  5.2
$ ps aux | grep -v grep | grep mysql

MySQL Server 5.5

Install from ports

cd /usr/ports/databases/mysql55-server
make BUILD_OPTIMIZED=yes BUILD_STATIC=yes

or
cd /usr/ports/databases/mysql55-server ; make config-recursive install clean;

echo 'mysql_enable="YES"' >> /etc/rc.conf
cp /usr/local/share/mysql/my-large.cnf  /var/db/mysql/my.cnf
/usr/local/etc/rc.d/mysql-server start

# Set root password
/usr/local/bin/mysql_secure_installation

# Restart mysql
/usr/local/etc/rc.d/mysql-server restart

# Change root password
/usr/local/bin/mysqladmin -u root password 'NEWPASSWORD'

# check if is working
mysql -u root -p
Enter password:
Server version: 5.5.28-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
mysql> status;
mysql> show engines;
mysql> quit;

Install PHP5 support for MySQL (if not installed already):

cd /usr/ports/databases/php5-mysql
make install clean

MySQL Tools:
mysqlar - MySQL Activity Report
# Mysql Activity Report
*/5 * * * * /usr/bin/env hourly=1 daily=1 weekly=1 monthly=1 /usr/local/bin/mysqlar_graph > /dev/null


 [root]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '' (2)
[root]#
[root]# mysql -u root -p --socket=/tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

Info tools: mysqladmin - client for administering a MySQL server
FreeBSD MySQL Benchmark http://people.freebsd.org/~kris/scaling/mysql.html 

mysqltuner.pl
https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

http://stackoverflow.com/questions/1198701/storing-and-displaying-unicode-string-using-php-and-mysql

MySQL Server reset root password

service mysql-server stop
# add to rc.conf
 mysql_args="--skip-grant-tables --skip-networking"
service mysql-server start
mysql -u root
mysql> use mysql ;
mysql> update user set password=PASSWORD("new-pass") where User='root';                
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)
mysql> exit
# remove from rc.conf
 mysql_args="--skip-grant-tables --skip-networking"
service mysql-server restart

PHPMyAdmin (optional)

# cd /usr/ports/databases/phpmyadmin
# make config-recursive install clean
  ===> Setting user-specified options for phpMyAdmin-3.3.10 and dependencies
# 

After install, edit config.inc.php to suit your needs.

Edit httpd.conf:

Alias /phpmyadmin/ "/usr/local/www/phpMyAdmin/"
<Directory "/usr/local/www/phpMyAdmin/">
Options none
AllowOverride Limit
Order Deny,Allow
Deny from all
Allow from 127.0.0.1 .example.com 
</Directory>

Or simply copy http://www.adminer.org/


TESTING AND TUNING


Apache tuning
http://www.devside.net/articles/apache-performance-tuning
http://www.ibm.com/developerworks/ru/library/l-tune-lamp-2/l-tune-lamp-2.html
http://www.ul-tech.ru/bsd-linux/world-wide-web/tuning-apache2.html

MySQL tuning

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 128M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 256)
    innodb_buffer_pool_size (>= 177M)
   
   
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> set global max_connections = 300;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.00 sec)

my.cnf
[mysqld]
max_connections = 200