This article is part of a series on Installing a ruby on rails application using phusion passenger on ubuntu on slicehost .

Installing apache

sudo aptitude install apache2 apache2.2-common apache2-mpm-prefork apache2-utils libexpat1 ssl-cert

you might have gotten some warning. let’s fix that.

sudo vi /etc/apache2/apache2.conf
# at the bottom of the file add the following line
ServerName myapacheserver

restart apache

sudo apache2ctl graceful

By default apache error log is stored at

/var/log/apache2/error.log

If you make some apache configuration related changes, then you can avoid restarting apache, by asking it to simply reload its configuration.

sudo /etc/init.d/apache2 reload

Installing MySQL

sudo aptitude install mysql-server mysql-client libmysqlclient15-dev
# enter the password

sudo aptitude install libmysql-ruby1.8

irb
#irb(main):001:0>
require 'mysql'
#=> true
#irb(main):002:0>
exit

we are changing the root password for mysql and not for the root user on the slice.

mysqladmin -u root password YOURMYSQLPASSWORD
# note that there is no space between -p and the password
mysql -u root -pYOURMYSQLPASSWORD 
mysql> create database firstdb;
mysql> exit

If for any reason you need to remove mysql and start all over again the you can remove mysql by executing following command

sudo apt-get remove --purge mysql-client mysql-server mysql-common

Fine tuning MySQL

Default setting of mysql takes a lot of memory. Before we optimize it let’s see how memory mysql is currently taking. At the command prompt type ‘top’ without the quotes. Hold shift key and press M. This will sort the processes in the order of amount of memory they are taking. Look at the top 10 result. mysql should be there. For me it is displaying 9%. Hit q to quit top panel.

Now let’s begin optimization.

sudo vi /etc/mysql/my.cnf

We’re going to add two lines in the basic setting section. Find the first two lines shown below and add the two lines after that

language  = /usr/share/mysql/english
skip-external-locking
skip-locking
skip-innodb

Next, find the Fine Tuning section and adjust the settings as shown below

# * Fine Tuning
#
key_buffer    = 16K
max_allowed_packet  = 1M
thread_stack    = 64K

add following three lines after the thread_stack = 64K statement line

thread_cache_size = 4
sort_buffer=64K
net_buffer_length=2K

restart mysql

sudo /etc/init.d/mysql restart

After mysql has been restarted repeat the top command , shift + M to find the amount of memory mysql is taking. For me the new value was 2%.

Innodb vs MyISAM

MySQL supports two kinds of engine: MyISAM and Innodb. You can google about the differences. One thing to note is that MyISAM engine doesn’t support foreign key constraints and does not support transactions. If you need these two things then you must use Innodb. MyISAM does support index and unique index constraint.

Note that engine is not set at database level. Rather it is set at table level. So within a database you can have one table of type Innodb and another table of type MyISAM.

Previously we created database ‘firstdb’ and table ‘users’. Let’s find out what engine that table is using.

Following two commands will get you the engine type.

show table status from firstdb like 'users'
# mysql documentation says
#SHOW TABLE STATUS [FROM db_name]    [LIKE 'pattern' | WHERE expr]

# another way
mysqlshow -uUSERNAME -pPASSWORD -i firstdb users

As of this writing the default engine being created was MyISAM.

Let’s say that by default you want Innodb engine and not MyISAM.

sudo vi /etc/mysql/my.cnf

Browse the content and you will see something like this

user          = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking 
skip-locking
skip-innodb  

Notice the last line. That line says “skip-innodb”. If by default you want all your tables to be of type Innodb then comment that line.

Although by mentioning skip-innodb by default MyISAM engines are created, it is a good idea to add following line after skip-innodb line.

default-storage-engine = MyISAM

If you don’t want innodb then make sure that skip-innodb is there othewise innodb engine will be loaded and will consume extra memory.

Accessing MySQL from local machine

ssh -N -p 22 -c 3des nkumar@123.45.67.890 -L 3306/127.0.0.1/3306

Enter the password you use to log into ssh.

Make sure that mysql is not running on your local machine otherwise the ssh command will complain that the prot 3306 is already taken.

On the mysql client now I can access all the databases by connecting to 127.0.0.1 as root and the password as YOURMYSQLPASSWORD.

Change MySQL password

mysql -u root -pEXISTING_MYSQL_ROOT_PASSWORD ( no space in between -p and password)
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='root';
mysql> flush privileges;
mysql> quit