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.
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.
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.
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
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-184.108.40.206-english.tar.bz2
You can find the latest version of phpMyAdmin at http://www.phpmyadmin.net/home_page/index.php
tar –xjf phpMyAdmin-220.127.116.11-english.tar.bz2 cd phpMyAdmin-18.104.22.168-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 22.214.171.124.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.