Introduction

Your database server should be it's own server with no other services (such as web or email) on it. First thing, follow the DinkelServerBase in order to get a basic server set up, and a uniform environment to install MySQL server on.

Make Space

While the virtual machine is powered off, add a second harddrive. The size of this varies on how much use your database server is going to get, but it is best to judge small because the harddrive can easily be expanded, but it is definately not as easy to shrink after it has been in production and you need to reclaim space on the virtual machine host server. 10 to 20 GB is probably plenty of starting space.

Power on the virtual machine. Log into this server's Webmin from a workstation and partition the hard drive as a single XFS partition mounted at /var/lib/mysql which is where all the databases are stored by default.

Install MySQL Server

sudo apt-get update && sudo apt-get -y install mysql-server

This will install the latest mysql-server.

sudo nano /etc/mysql/my.cnf

Put a # in front of the line bind-address = 127.0.0.1 so it reads like this:

# bind-address = 127.0.0.1

This will allow access from other servers, particularly the webserver. We will be restricting access to only the servers that need it through the firewall.

Firewall

Use Webmin to open up firewall port TCP 3306, but I would suggest only allowing the computers that would be accessing it (based on IP). Also, use Webmin to disable and stop mysql-ndb and mysql-ndb-mgm (from the Bootup and Shutdown module). These two daemons are only needed for clustering.

Configure MySQL

Restart the mysql daemon (to get the my.cnf change) with:

sudo /etc/init.d/mysql restart

Set a password for the MySQL root user with

mysqladmin –u root password ‘new-password’

You might need sudo in front of this line, for the life of me I can not remember at the moment; I will check later.

Allow access from remote hosts with this (do not type the >, those are only to show you are in the mysql program):

mysql –u root –p
> GRANT ALL PRIVILEGES ON *.* TO root@’%’ IDENTIFIED BY ‘new-password’ WITH GRANT OPTION;
> quit

phpMyAdmin

You’ll now want to install phpMyAdmin on the webserver. Follow my instructions for setting up an Apache server DinkelVersus/ApacheServer, or use your existing web server with php installed. Here are the directions for putting it on my Apache setup. The only thing that might be different is the path to where you put files.

cd ~
wget http://superb-east.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.10.0.2-english.tar.bz2

You can find the latest version of phpMyAdmin at http://www.phpmyadmin.net/home_page/index.php

tar –xjf phpMyAdmin-2.10.0.2-english.tar.bz2
cd phpMyAdmin-2.10.0.2-english
sudo mkdir /var/www/phpmyadmin
sudo mv * /var/www/phpmyadmin/
sudo nano /var/www/phpmyadmin/config.inc.php

Insert the following:

<?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 2.8.1 setup script by Michal Cihar
 * Version: $Id: setup.php,v 1.23.2.8.2.2 2006/05/15 07:57:09 nijel Exp $
 * Date: Sun, 11 Jun 2006 17:34:57 GMT
 */
/* Servers configuration */
$i = 0;
$i++;
$cfg['Servers'][$i]['host'] = 'sql.svr.ip.addy';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['auth_type'] = 'http';
/* End of servers configuration */
?>

Be sure to change sql.svr.ip.addy to the ip address of the MySQL server.

sudo nano /etc/apache2/sites-available/default

Add the Alias directive to the virtual host:

<VirtualHost *>
  Alias /phpmyadmin /var/www/phpmyadmin
  ...

sudo /etc/init.d/apache2 restart

You can now browse to http://webserver/phpmyadmin and have at it. Log in with the root user and password you set up above with the mysqladmin command.