automating mysqltuner

I found the mysqltuner a while back and had been using it on one of my servers sporadically. I suddenly thought... why don't I run it automatically via a cron every week and test the server optimisation. Easy...

sudo -s
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
cp ./mysqltuner.pl to /usr/local/bin/mysqltuner

Ensure that it's in the right place by running it and then optimise the current mysql server and restart it:

mysqltuner
joe /etc/mysql/my.cnf
/etc/init.d/mysql restart

Now we need to add a cron entry to make it run every week.

crontab -e

43 4 * * * thur mysqltuner

And that's it, saving the crontab file will install it and on the next thursday at 4:43 in the morning, the optimiser will run as root and email the server admin the output.

Multiple apache sites on localhost

I do all my application development locally, but for a while haven't needed to have two instances of locally running applications. In the past, I had setup apache to serve multiple local instances, but I hadn't written down the process.

First of all, alter /etc/hosts to add a line for your new site.

127.0.0.1 localhost.localdomain localhost<br />127.0.0.2 localccc.localdomain localccc

Then, alter the apache config: create a file per site in /etc/apache2/sites-available:

<VirtualHost localhost>

ServerName localhost
ServerAdmin webmaster@localhost
ServerSignature On

DocumentRoot /home/elements/htdocs/

<Directory>
Options FollowSymLinks
AllowOverride None
</Directory>

<Directory home/elements/htdocs/>
Options Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
allow from all
</Directory>

ErrorLog /var/log/apache2/error.log
CustomLog /var/log/apache2/access.log combined
LogLevel warn

</VirtualHost>

Also remove the contents of default: just empty it.
Then enable the sites you've created (run it once for each filename you've created):

a2ensite localhost

Finally, restart the server

/etc/init.d/apache

And, as Gordon Ramsay would say... DONE.

PCI compliance

Some of my clients are being hassled with these new PCI checks... not that they're really all that new, but PDQ companies are certainly using them a lot more.

A really helpful article can be found here: Making Plesk more PCI compliant

On the last check, the servers were failing on several fronts, and I thought I'd note down some pointers for the future.

1.
SSH host keys. Being Debian, the servers fell fowl of a packaging mistake with SSH in which the keys became less than secure... need to re-generate them.

sudo -s
rm /etc/ssh/ssh_host_*
dpkg-reconfigure openssh-server

Then all you need to do is go around the update all the known_host files... yawn

2.
PHP version. Problem with Plesk 8.4 is that is uses one version of PHP for Apache and one for the control panel. And, the version used for the control panel was horribly out of date.

The version used for the forward facing pages is not completely up to date either, so I've used the dotdeb repros to get the latest versions.

sudo -s
joe /etc/apt/sources.list

deb http://dotdeb.netmirror.org/ stable all
deb-src http://dotdeb.netmirror.org/ stable all

apt-get update
apt-get upgrade

Make sure that you keep your old config files, or at least do a diff to work out which bits have been changed.

The version that is used for the Plesk control panel is independant on that installed on the system and is shipped separately. According to the Forum, it will be upgraded to 5.2.6 from the next release... 8.5... whenever that will be... have posted to the thread to find out.

As well as this, the scores for psa were being counted twice because port 8880 and port 8443 were both open and creating the same errors. As far as I can tell, port 8880 isn't used in a standard setup. So, until the next release, I thought I'd just try and block its use.

sudo -s
joe /opt/psa/admin/conf/httpds.conf

#Listen 8880

/etc/init.d/psa restart

You can then test the connection to port 8880 from a remote location.

telnet servername 8880

3.
Somebody had left a phpinfo() file in a forward facing position. (5 points)

4.
Apache needs to be over 2.2.8 and if it's not, that's another 6 points.

5.
Port 53 was open
Close it using the Firewall module in Plesk (DNS server)

6.
SSL 2.0 was being used... that added another 4 points.

sudo -s
joe /etc/apache2/httpd.conf

SSLCipherSuite ALL:!ADH:!LOW:!SSLv2:!EXP:+HIGH:+MEDIUM
SSLProtocol all -SSLv2

/etc/init.d/apache2 restart

You also need to alter the psa (Plesk Server Admin):

sudo -s
/opt/psa/admin/conf/httpsd.custom.include

SSLCipherSuite ALL:!ADH:!LOW:!SSLv2:!EXP:+HIGH:+MEDIUM
SSLProtocol all -SSLv2

/etc/init.d/psa restart

(If the .custom.include file doesn't exist, create it: it will ensure that the config changes persist on package upgrade)

You can double check that the server doesn't respond to a SSL2.0

openssl s_client -connect hostname:443 -ssl2
openssl s_client -connect hostname:8443 -ssl2
openssl s_client -connect hostname:25 -starttls smtp -ssl2

If SSL2.0 is disabled, you should get the following returned.

419:error:1407F0E5:SSL routines:SSL2_WRITE:ssl handshake failure:s2_pkt.c:428:

420:error:1406D0B8:SSL routines:GET_SERVER_HELLO:no cipher list:s2_clnt.c:450:

.

Plesk 8.4 upgrade

When I bought Plesk initially for my growing server farm (small collection of servers that sits in a proper server farm), I didn't purchase the continuing version support... I didn't think it was worth the cash.

Well, I finally decided to crack out the credit card and but upgrades for them all. Especially since an amnesty on upgrades was being offered by the new owners, Parallels, and it was cheaper...

So, armed with my new keys, I went ahead and upgraded... it was one of those fingers crossed moments, but I'd backed everything up. And... everything worked... there were some niggles with the switch from PHP4 to PHP5, but everything went surprisingly well.

Now I just need to get round to upgrading Debian to etch on two of the older machines... that's not going to be as pretty, I'm sure.

Upgrading Ubuntu server

I'm not quite sure why I'd not got round to upgrading my Ubuntu server locally, but... I hadn't.

Took me a wee while to work out exactly how to do it, so I thought I'd post the results of my searches here... so that I have it for next time.

sudo -s
apt-get update
apt-get upgrade
apt-get dist-upgrade

apt-get install upgrade-manager-core
do-release upgrade

Simple...

MySQL optimisation

I found a nice MySQL tuner at mysqltuner.com
After using it to find out where there were issues, all I needed to do was alter the my.cnf file in /etc/mysql/

I added the following definitions and saw a significant improvement in efficiency.

#
# * Personal additions JBJ
#
set-variable = thread_cache_size=4M
set-variable = join_buffer_size=128K
set-variable = table_cache=512
set-variable = key_buffer_size=64M

log-slow-queries = /var/log/mysql/mysql-slow.log
long-query_time=1

innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M

Adding mysql admin user

Needed to add an admin to the mysql db and couldn't remember how:
Log in with admin credentials from a root login

sudo -s
mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Then:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;