Install Apache on CentOS v5 x86/64-bit

• yum install -y httpd
• chkconfig –level 2345 httpd on
• service httpd start

• yum install php php-mysql php-common php-gd php-mbstring php-mcrypt
  php-devel php-xml

• service httpd restart

• mkdir /var/www/vhosts
• mkdir /var/www/ vhosts/mydomain.com
• mkdir /var/www/ vhosts/mydomain.com/httpdocs
• mkdir /var/www/ vhosts/mydomain.com/logs

• vi /etc/httpd/conf.d/welcome.conf
• <Comment out all the lines with a #>

• vi /etc/httpd/conf/httpd.conf

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

• NameVirtualHost <PublicIPAddress>

• <VirtualHost PublicIPAddress>
• ServerAdmin email@domain.com
• ServerAlias www.mydomain.com
• ServerName mydomain.com:80
• UseCanonicalName Off
• DocumentRoot /var/www/vhosts/mydomain.com/httpdocs
• CustomLog /var/www/vhosts/mydomain.com/logs/access_log common
• ErrorLog /var/www/vhosts/mydomain.com/logs/error_log
• DirectoryIndex index.php
• </VirtualHost>

• service httpd restart

• /usr/sbin/apachectl configtest

Log management is the next step, as unmaintained log files can grow and grow and if your website becomes exceptionally busy then these can eat up in to the server’s disk space:

• vi /etc/logrotate.d/httpd/vhosts

• /var/www/vhosts/somethingisaw.co.za/logs/*log {
• monthly
• rotate 12
• compress
• missingok
• notifempty
• sharedscripts
• postrotate
• /sbin/service httpd reload > /dev/null 2>/dev/null || true
• endscript
• }

Optimise MySQL on CentOS v5 x86/64-bit

The final step in this MySQL series of posts (read the Install and Backup posts), is the regular optimisation of MySQL.

The my.cnf file has a lot of options that will enable you to customise your MySQL environment to the hardware available and the demand it receives. There are some very good performance monitoring scripts available that will run diagnostics against the MySQL server and offer advice on how to enhance your MySQL configuration.

I had MySQL databases running in one database environments and others in Shared Hosting with multiple environments, so I would set these scripts to run weekly and provide me with the necessary advice to continually optimise the performance.

First step, is to download the performance script and set the execute permissions:

• cd /home/scripts
• wget mysqltuner.pl
• chmod u+x mysqltuner.pl.pl

We can run the above script, and it will provide the necessary diagnostics. However, that is a manual task and we want to automate the script to run weekly and send us an email with the results:

• vi mysql_tuner_weekly.sh

• #!/bin/sh
• /home/scripts/mysqltuner.pl –user mysql-local –pass <Password> > /home/scripts/mysqltuner.log
• cat /home/scripts/mysqltuner.log | mail -s “MySQL Tuner Log” email@domain.com
• rm -rf /home/scripts/mysqltuner.log

The above script calls the mysqltuner.pl Perl script, providing the authentication to connect to the local MySQL instance. It will run the diagnostics, outputting the results to the mysqltuner.log file. This file is then emailed off to email@domain.com and the script then does housekeeping by remove the log file.

This script will not run on Windows, so if you want to optimise a Windows instance of MySQL, you need to use the following script:

• #!/bin/sh
• /home/scripts/mysqltuner.pl –host <RemoteServerFQDN> –port <RemoteMySQLPort> –forcemem <RemoteServerRAM> –user mysql-public –pass <Password> > /home/scripts/mysqltuner.log
• cat /home/scripts/mysqltuner.log | mail -s “<RemoteServerFQDN> MySQL Tuner Log” email@domain.com
• rm -rf /home/scripts/mysqltuner.log

I actually had a management server that had one script, connecting to all the Windows instances of MySQL and a script running locally on all the Linux instances.

Final step is to now schedule the script to run daily at 21:30:

• vi /etc/crontab

• 0 6 * * 0 root /home/scripts/mysql_tuner_weekly.sh

I would allow your MySQL server to run for a few days before you first run the script, so that MySQL can generate some stats for the scripts to analye. You will find that the first time it runs, you will get a lot of options to update in my.cnf, but after that, it die down.

Another performance script is available:

• wget http://www.day32.com/MySQL/tuning-primer.sh

However, it only ever ran it locally because the output to email was too messy and I couldn’t get clean results. However, it is very useful to keep and run when running heavy performance diagnostics on the local MySQL server.

Backup MySQL on CentOS v5 x86/64-bit

My previous post ran through the steps to install, configure and secure MySQL on CentOS. This post will run through some steps to keep your MySQL installation backed up.

First up, login as root and create a location to store local backups as well as a location to store our backup scripts:

• mkdir /home/backups
• mkdir /home/backups/mysql
• mkdir /home/scripts

We don’t need to reinvent the wheel and there are excellent backup scripts available with regular backup rotation features built in, freely available on the Internet:

• cd /home/scripts
• wget “http://sourceforge.net/projects/automysqlbackup/files/AutoMySQLBackup/AutoMySQLBackup
  VER 2.5/automysqlbackup-2.5.1-01.sh”
• mv automysqlbackup-2.5.1-01.sh mysql_backup_all_daily.sh
• chmod u+x *.sh

The last two commands above rename the backup script to something a little more friendly, and then set the required permissions for the script to be executed.

We can now customise the script to connect to our MySQL instance and back up the databases:

• vi mysql_backup_all_daily.sh

• USERNAME=mysql-local
• PASSWORD=<Password>
• BACKUPDIR=”/home/backups/mysql”
• MAILADDR=”email@domain.com

The above configurations will essentially back up the local MySQL instance to /home/backups/mysql and then send an email to you, informing you of the completion success/failure of the backup. The script has notes inside that will allow you to customise more features such as emailing you the backups, selecting databases to backup, etc.

Final step is to now schedule the script to run daily at 21:30:

• vi /etc/crontab

• 30 21 * * * root /home/scripts/mysql_backup_all_daily.sh

The script will retain a backup for every day, a week and a month, and will rotate these backups for you. Nice!

You can run this script from a remote backup server, but you will need to ensure you open the necessary firewall ports and use the mysql-public account for remote access.