Getting MySQL Workbench to work on a remote host

When you first install MySQL on Ubuntu 10.04 server it is configured to only allow connections from localhost and so if you want to connect MySQL Workbench to the server for administration purposes then you need to allow connections from any IP address. This editing the /etc/mysql/my.cnf file and find the section [mysqld] and edit the bind-address entry from 127.0.0.1 to be your full IP address or if you want to bind to all addresses then use 0.0.0.0. Also make sure that there is no skip-networking section.

And don't forget to restart MySql

sudo /etc/init.d/mysql restart

And grant privileges to root for remote administration, through the mysql console

grant all on *.* to root;

You will also need to add the MySql port to the firewall rules to allow access

sudo ufw allow 3306

Thanks to Vivek Gite over at nixCraft for his great artical on the same subject.

Building an Development Server

So as part of a project I am working on I need to have a development server that will allow me to run a number of development tools, specifically JIRA, Confluence , Subversion, and MySQL but later maybe some other tools.

First I looked at the options for hosting. I would need a host wher eI could run tomcat and apache, this really meant a dedicated server or a cloud service. After a quite look into a number of providers I plumpted for a Rackspace Cloud Server. Sign up was astraight forward and after a phone call from Rackspace to confirm my details I was ready to create my first VM. This was very easy using their web based front end and within 10 mins I had a new Ubuntu 10.04 LTS Server up and running and was able to logon with SSH.

Security

So how to configure this new server. Since Rackspace give you root access it was nessassary to secure the server. This involved logging on as root and seting up two new groups, sshlogins and admin, adding a newuser, putting the newuser in both the groups:

addgroup sshlogin addgroup admin adduser newuser adduser newuser sshlogin adduser newuser admin

Next we need to restricting ssh to the new sshlogins group only. To do this edit the /etc/ssh/sshd_config file, find where it says PermitRootLogin yes and change it to PermitRootLogin no then at the bottom of file add:

# Permit only sshlogins group AllowGroups sshlogin

for this to take effect the sshd needs to be restarted:

sudo /etc/init.d/ssh restart

And finally allowing the admin group to perform sudo actions. This involes editing the /etc/sudoers file, however as this is so important this needs to be done using visudo, adding the following line at the bottom.

# Members of the admin group may gain root privileges %admin ALL=(ALL) ALL

Then save the file.

Now logout of root and log back in again as newuser

Next we need to stop any connections to ports except those we wish to have access through, i.e. 22 (for SSH) and 80 (for the Webserver). To do this will be use the inbuilt iptables, however to make life easier we will use ufw to configure the firewall. To do this first you will need to install ufw:

sudo apt-get ufw

And configure the firewall as follows (thanks for Mike Descy at 101Umbrellas):

sudo ufw default deny incoming sudo ufw default allow outgoing sudo ufw allow ssh sudo ufw allow www sudo ufw enable

Installing base software

The base software for this installation is JIRA, Confluence , Subversion and MySQL. To install these and the other required components tomcat6-user for the private instance features of tomcat and ttf-dejavu for the fonts required as the server is installed with X11 use:

sudo apt-get install apache2 sudo apt-get install tomcat6 sudo apt-get install tomcat6-user sudo apt-get install subversion sudo apt-get install libapache2-svn sudo apt-get install mysql-server sudo apt-get install ttf-dejavu

Configuring MySQL

MySQL needs to have some options set so it can work for Jira and Confluence. The easiest way to do this is to edit /etc/mysql/my.cnf and add the following lines at the end of the [mysqld] section just before the [mysqldump] section start

default-collation=utf8_bin character-set-server=utf8 collation-server=utf8_bin default-character-set=utf8 default-storage-engine=INNODB max_allowed_packet=32M transaction-isolation=READ-COMMITTED

And then restart MySQl

sudo /etc/init.d/mysql restart

Now we need Databases and users for Jira and confluence. To create these logon to the mysql console as root and enter:

create database jiradb character set utf8; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on jiradb.* TO 'jirauser'@'localhost' IDENTIFIED BY 'mypassword'; create database confluencedb character set utf8; GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on confluencedb.* TO 'confluenceuser'@'localhost' IDENTIFIED BY 'mypassword';

Configuring Tomcat for Jira and Confluence

The tomcat6-user is required later for creating the separate tomcat instances

Confluence and Jira will need home directories as does tomcat for it's server instances, I put these in the /srv directory where the tomcat instance will also live eventually

cd /srv sudo mkdir -p jira sudo mkdir -p confluence sudo mkdir -p tomcat sudo chown tomcat6:tomcat6 jira sudo chown tomcat6:tomcat6 confluence

We are going to create two new tomcat instances one for jira and one for confluence. Here we are setting the HTTP connection ports to 8090 and 8091 respectivly and the control ports to 8015 and 8016. Once the server instances are created the Catalina/localhost directories need to be created for each of the new tomcat instances

cd /srv/tomcat sudo tomcat6-instance-create -p 8090 -c 8015 jira sudo tomcat6-instance-create -p 8091 -c 8016 confluence sudo mkdir -p jira/conf/Catalina/localhost sudo mkdir -p confluence/conf/Catalina/localhost

Since Jira and Confluence require some extra jars to work, namely those in the http://www.atlassian.com/software/jira/downloads/binary/jira-jars-tomcat-distribution-4.3-rc1-tomcat-6x.zip and the MySQL JDBC driver these need to be downloaded and put into the system-wide tomcat /usr/share/tomcat6/lib directory.

Next we need the .war files for Jira and Confluence. These need to be compliled and I suggest you do this on a seperate machine and follow the Atlassian instructions for Jira and confluence to build .war files and then copy the .war files to the live server ready for deployment to the tomcat instances.

Web configuration files for Jira and Confluence need to be created before starting the servers. For Jira the file, /srv/tomcat/jira/conf/Catalina/localhost/jira.xml should contain:

<Context path="/jira" docBase="/srv/files/atlassian-jira-4.3.3.war" debug="0" useHttpOnly="true"> <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource" username="jirauser" password="mypassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://127.0.0.1:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8" maxActive="20" validationQuery="select 1"/> <Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction" factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/> <Manager pathname=""/> </Context>

And for Confluence, something very similar the file, /srv/tomcat/confluence/conf/Catalina/localhost/confluence.xml should contain:

<Context path="/confluence" docBase="/srv/files/confluence-3.5.4.war" debug="0" useHttpOnly="true"> <Resource name="jdbc/ConfluenceDS" auth="Container" type="javax.sql.DataSource" username="confluenceuser" password="mypassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://127.0.0.1:3306/confluencedb?useUnicode=true&amp;characterEncoding=UTF8" maxActive="20" validationQuery="select 1"/> <Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction" factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/> <Manager pathname=""/> </Context>

To set the memory allocation settings for Tomcat edit both jira/bin/setenv.sh and confluence/bin/setenv.sh and add the following line at the end:

export CATALINA_OPTS="$CATALINA_OPTS -Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true -Dmail.mime.decodeparameters=true -Xms128m -Xmx512m -XX:MaxPermSize=256m -Djava.awt.headless=true"

Next you need to stop the default tomcat instance from automatically starting and set the Jira and Confluence instances to automatically start. To do this you need to create, and make executable, /etc/init.d/jira and /etc/init.d/confluence scripts as:

/etc/init.d/jira

# Jira auto-start # description: Auto-starts the tomcat jira instance case $1 in start) sh /srv/tomcat/jira/bin/startup.sh ;; stop) sh /srv/tomcat/jira/bin/shutdown.sh ;; restart) sh /srv/tomcat/jira/bin/shutdown.sh sh /srv/tomcat/jira/bin/startup.sh ;; esac exit 0

/etc/init.d/confluence

# Confluence auto-start # description: Auto-starts the tomcat confluence instance case $1 in start) sh /srv/tomcat/confluence/bin/startup.sh ;; stop) sh /srv/tomcat/confluence/bin/shutdown.sh ;; restart) sh /srv/tomcat/confluence/bin/shutdown.sh sh /srv/tomcat/confluence/bin/startup.sh ;; esac exit 0

And make them executable:

sudo chmod 755 /etc/init.d/jira sudo chmod 755 /etc/init.d/confluence

Then it is necessary to disable the default tomcat instance and enable the jira and confluence instances. Thanks to http://www.debuntu.org/how-to-manage-services-with-update-rc.d for this. Anyway here are the commands you need:

sudo update-rc.d -f tomcat6 stop sudo update-rc.d -f tomcat6 stop 80 0 1 2 3 4 5 6 . sudo update-rc.d -f jira defaults 93 7 sudo update-rc.d -f confluence defaults 94 6

Startup Jira and Confluence:

sudo /etc/init.d/jira start sudo /etc/init.d/confluence start

Configuring Apache HTTPD

There are a number of things to configure in Apache. First you need to enable the proxy modules as these are required for the Proxy rules that are required. To do this:

cd /etc/apache2/mods-enabled sudo ln -s ../mods-available/proxy* .

Next the default site configuration file, /etc/apache2/sites-enabled/000-default needs to be edited to set the ServerName, ServerAdmin and DocumentRoot properties as such:

ServerAdmin name@domain.com ServerName server.domain.com DocumentRoot /srv/www

Then the proxy rules sections need to be added at the bottom of the file, but before the closing tag:

<Proxy *> Order deny,allow Allow from all </Proxy> ProxyRequests Off ProxyPreserveHost On ProxyPass /jira http://localhost:8090/jira ProxyPassReverse /jira http://localhost:8090/jira <Location /jira> Order allow,deny Allow from all </Location> ProxyPass /confluence http://localhost:8091/confluence ProxyPassReverse /confluence http://localhost:8091/confluence <Location /confluence> Order allow,deny Allow from all </Location>

I like to put a basic index.html file in the /srv/www directory that allows users to find the Jira and Confluence sites. I use something like:

<!doctype html> <html> <head> <meta charset="utf-8"> <title>Demo</title> </head> <body> <h1> Development Server</h1> <ul> <li>For issue tracking, including defects and change requests use <a href="/jira">jira</a></li> <li>For all knowledge sharing and documents use the <a href="/confluence">wiki</a></li> </ul> </body> </html>

And restart Apache:

sudo /etc/init.d/apache2 restart

Configuring Subversion

This is fairly easy, I have decided to use http as the access method and thanks to the Ubuntu documentation on Subversion all you need to do is:

cd /srv sudo mkdir -p svn svnadmin create /srv/svn/project sudo chown -R www-data:www-data /srv/svn

You then need to add the following to the Apache config file /etc/apache2/sites-enabled/000-default, after the previously added proxy sections but before the final tag:

<Location /svn/project> DAV svn SVNPath /srv/svn/project AuthType Basic AuthName "You repos name" AuthUserFile /etc/subversion/passwd Require valid-user </Location>

Then you need to add a password for any users, the -c option is required for the first user but not any others:

sudo htpasswd -c /etc/subversion/passwd user1 sudo htpasswd /etc/subversion/passwd user2

And restart Apache:

sudo /etc/init.d/apache2 restart

Whats Left to do:

This setup all works and is all that is really required. However there are a couple of other things that you may wish to do:

• Start jira and confluence as tomcat6 user - currently the tomcat instances run as root and this has security implications. Making the tomcat instances run as the tomcat6 user adds small amount of security such that if a hacker finds a security hole in tomcat then they only have limited access.
• Configure Apache for SSL - This again adds more security and we can even stop all access to Jira, Confluence and Subversion without going through the SSL version of the site, thus stopping any password snooping

Ubuntu 10.04

I have been playing around with virtual machines recently and the new version of VMWare PLayer is excellent. Setting up new VMs has been very easy. So far I have setup a Windows 2003 Server and an Ubuntu 10.04 Desktop VM.

The Windows VM worked fine, no problems however the Ubuntu install had a strange problem in that once started the keyboard did not appear to work and so it was not possible to login. A quick bit of searching on the web revelaed that the problem and thanks to a post by Chris at Solo Technology and a link from there to Reformed Musings I found my answer. The web, don't you just love it :-)