Preparing web server in Amazon Linux

Once you install an EC2 instance with Amazon Linux AMI you will need to install web server and other requirement of setting up a website.

We are going to install httpd (webserver), php (scripting), mysqli (db client) and memcached (caching).

First things first, we do a clean up in case this has been used before:

yum update -y 
yum remove -y mysql*
yum remove -y http*
yum remove -y php*

and let the installation begin:

yum install -y httpd24 
yum install -y mod24_security memcached 
yum install -y php55 php55-mysqlnd php55-pecl-memcached php-pear php55-gd php55-devel php-channel-phpunit php-channel-amazon php55-cli  
yum install -y mysql55 mysql55-devel mysql55-libs 

Next, we need to do some httpd configurations like enabling mod-rewrite (if we need it!) and turning off signatures for security:

vi /etc/httpd/conf/httpd.conf

We a re going to add this to end of the file:

# Disable ETag headers
FileETag none

# Hide Apache and PHP info
Header unset Server
Header unset X-Powered-By

# Don't expose server versions
ServerSignature Off
ServerTokens Prod

# Enable server-status for internal IP
<Location /server-status>
   SetHandler server-status
   Order Deny,Allow
   Deny from all
   Allow from 127.0.0.1
</Location>

# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
KeepAlive On

# Configure /var/www/html
<Directory "/var/www/html">
	Options FollowSymLinks
	AllowOverride All
	DirectoryIndex index.html index.php
	Order allow,deny
	Allow from all
</Directory>

We also need to do minor php configurations to enable sessions being saved in centralized memcached server (if you dont want to store sessions in memcached then just ignore this):

vi /etc/php.ini

And adding/editing the followings. The time zone should be changed to your own timezone, memory limit is 128 by default and you may want to increase it.

date.timezone = Asia/Kuala_Lumpur
memory_limit = 256M
session.save_handler = memcached
session.save_path = "192.168.1.2:11211"

*Please note, in case you are going to use memcache instead of memcached (which are two different things) you should specify protocol in save_path (i.e. tcp://192.168.1.2:11211).

At the end we need to do some Linux stuff!! Here is the explanation in order: making www group, adding current user to that group, take ownership of website directory, grant default permissions to website directory, grant specific permission to directories, grant specific permission to files, auto-start httpd service, auto-start memcached service, start httpd, start memcached.

groupadd www
usermod -a -G www ec2-user
chown -R root:www /var/www
chmod 2775 /var/www
find /var/www -type d -exec chmod 2775 {} +
find /var/www -type f -exec chmod 0664 {} +
chkconfig httpd on
chkconfig memcached on
service httpd start
service memcached start

echo "<?php phpinfo(); ?>" > /var/www/html/info.php

And we are all set ;)

Linking a table in Microsoft Access to MySQL table

MySQL is my favorite DBMS but it lacks a nice reporting interface. There are some reporting systems designed to generate customized reports out of MySQL databases but I want to show you a Microsoft Access based solution for using its awesome tools.

First you need to prepare your system by installing necessary drivers:

1-  Depends on the architecture of the MS-Access installed on the system (x86/x64) download and install proper edition of MySql ODBC driver for windows from http://dev.mysql.com/downloads/connector/odbc/

2-  Open ODBC data source administrator and Create a system DNS:

  1. For x32 ms-access: c:\windows\sysWOW64\odbcad32.exe
  2. For x64 ms-access: C:\Windows\System32\odbcad32.exe

3- Goto System DNS and click on Add and select MySQL ODBC x.x ANSI Driver

Image

4- Fill up the fields and use an account on MySQL to connect and finally test the connection

2

5- At the end you should be able to see your record in system DNS

3

The next phase is to actually linking the MySQL database:

1-  Create a blank ms-access db and name it mysql_link.accdb

2-  Goto “External Data” tab and select “ODBC Database” to get the external data linked in ms-access

3- Click on the link to data source

a4

4- In machine data source tab you should be able to see the System DNS you created previously. Select it and click OK

a5

5- Then chose the table you want to link (in our example: “Database_Name”)

a6

6- Chose a record identifier (usually primary key)

a7

Now, you can see a table in list of table which simply linked to MySQL database ( to the table and field you have defined before). It is possible to design forms, queries and reports for this linked table just like a native table.

P.S. Just for security reasons, it is recommended to define a separate user in MySQL and limit the access to tables and fields of desire; and use that user for your link.