Install on Win8 with vagrant Virtualbox/Ubuntu 14.04 with Mysql and Postgresql database inside

The goal was to have mysql/postgres databases accessible from local computer. I do not like database server installations in my own laptop and therefore VM solution is preferrable. This article describes steps to have ubuntu 14.04 Virtualbox with mysql and postgresql server inside

In host machine (Win8 laptop) first steps are to install

1. Vagrant 1.8.1
2. VirtualBox 5.0.12

Vagrant is my favourite tool to manage VM-s from commandline and Virtualbox is best VM provider. After installation open new command line window (cmder is my favourite and cmd.exe is ok also) and make new folder for VM installation. I assume that vagrant.exe is in your PATH.

First steps are to run from commandline in Win8.1 machine

• vagrant init ubuntu/trusty64
• vagrant up --provider virtualbox
• vagrant ssh

You will have VM with ubuntu 14.04 server accessible from commandline with ssh. Next steps are to install mysql database and postgres databases in Ubuntu server guest.

Install mysql database

• sudo apt-get -y install mysql-server

During installation root password must be provided (and better is to remember it also :-)).  After installation mysql server is up and running, but it is not accessible from host machine.

• sudo vi /etc/mysql/my.cnf and change bind address line to
  bind-address            = 0.0.0.0 and save file
• sudo service mysql restart
• sudo netstat -tap | grep mysql  

Something like 
tcp        0      0 *:mysql                 *:*                     LISTEN      11462/mysqld
should appear on screen

For testing is good to have some test user/database/table

• mysql -u root -p
  CREATE USER 'test'@'localhost' IDENTIFIED BY 'a';
  CREATE USER 'test'@'%' IDENTIFIED BY 'a';
  
  GRANT ALL ON *.* TO 'test'@'localhost';
  GRANT ALL ON *.* TO 'test'@'%';
     
  CREATE DATABASE IF NOT EXISTS test CHARACTER SET = 'utf8';
  create table `test_data` ( `i` bigint(20) NOT NULL, `d` datetime NOT NULL, `t` timestamp);
  create table `test_data_2` ( `seq` bigint(20) NOT NULL, `d` datetime NOT NULL);       

Next step is optional, but I prefer to have phpMyAdmin also installed on server so

• sudo apt-get -y install phpMyAdmin
  Answer (server: apache2) and provide passwords

Last step is to forward host ports to guest server so in Win8 machine run

• vagrant halt
• Edit Vagrantfile and add port forwarding
  config.vm.network :forwarded_port, guest: 3306, host: 3306
  config.vm.network "forwarded_port", guest: 80, host: 8010
• vagrant up

After these steps mysql should be up and running in guest server and accessible from host machine also. You can first validate your mysql server running http://localhost:8010/phpmyadmin in Win8 browser and access your mysql server with any client in Win8 (Oracle SQL Developer for example).

Install postgres server

In guest machine (vagrant ssh from commandline) execute

• sudo apt-get -y install postgresql postgresql-contrib
• sudo vi /etc/postgresql/9.3/main/postgresql.conf
  listen_addresses = '*'  # uncomment this line
• sudo vi /etc/postgresql/9.3/main/pg_hba.conf
  # IPv4 remote connections:
  host    all             all             0.0.0.0/0               md5
  # add previous line to config file and save file
•  sudo service postgresql restart

For testing is good to have some test user/database/table

• sudo -i -u postgres
• psql
  CREATE USER test SUPERUSER ENCRYPTED PASSWORD 'a';
  CREATE DATABASE test OWNER = test ENCODING = 'UTF8';
  ALTER USER postgres ENCRYPTED PASSWORD 'a';
  \c test
  CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test;
  create table if not exists test_data (i int, d date, t timestamp);
  CREATE TABLE IF NOT EXISTS test_data_2 (seq serial, d date);

In Win8 run    

• vagrant halt
• Edit Vagrantfile and add port forwarding
  config.vm.network "forwarded_port", guest: 5432, host: 5432
• vagrant up

After these steps postgres server should be up and running in guest server and accessible from host machine. You can check postgres with any client in Win8 (pgAdmin or Oracle SQL Developer for example).

Installed software

Software installed in host computer (Win8.1)

• vagrant 1.8.1
• VirtualBox 5.0.12

Software installed in guest computer (Ubuntu 14.04)

•  mysql 5.5.46
• phpMyAdmin 4.0.10
• postgreSql 9.3.10

Services accesible from Win8.1 (host)

#   5432 - postgres db (localhost:5432:test/a)
#   3306 - mysql db    {localhost:3306:test/a}
#   8010 - phpMyAdmin  (http://localhost:8010/phpmyadmin {root/a})