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})

How to load CSV file to Mysql database

There some tricks to know how to load csv file to mysql:

1. mysql -u <user> -p --local-infile
   (the --local-infile is necessary, otherwise you will get error during dataloading: ERROR 1148 (42000): The used command is not allowed with this MySQL version)
2. use <database>
3. create table script (create_tables.sql) in text editor
   drop table kontakt;
   create table kontakt
    (klkood        bigint(20)
    ,kontakti_liik varchar(32)
    ,vaartus       varchar(100)
    ,kasutusluba   varchar(100)
    ,keeluaeg      varchar(100)
   );
   create index kontakt_klkood on kontakt(klkood);
4. run the script from mysql prompt
   .\ create_tables.sql
5. create load data script (load_data.sql) in text editor
   LOAD DATA LOCAL INFILE '/vagrant/uptime/EA_kliendi_kontaktinfo_3012.csv' INTO TABLE kontakt
   FIELDS TERMINATED BY ';'
   LINES STARTING BY '"' TERMINATED BY '"\r\n'
   IGNORE 1 LINES;
6. run the script from mysql prompt
   .\ load_data.sql
7. If there are warnings then execute
   show warnings

For me most difficult part was to get load_data.sql to work and specially --local-infile parameter.

How to install mysql and mysql workbench in Ubuntu 12.04

Its really simple task to install mysql server and mysql workbench in Ubuntu 12.04. You shuold run from commandline



sudo apt-get install mysql-server
sudo apt-get install mysql-workbench

First row installs mysql server, asks root password and starts mysql service. To restart server run

sudo service mysql restart

Second row installs mysql-workbench and you are ready to access mysql server in localhost.

How to make Ubuntu Desktop Vagrant compliant

Its seems me reasonable to have Vagrant compliant Ubuntu Desktop 12.04 even I will not start it from commandline. These steps are necessary:

1. Create default user as vagrant/vagrant in Ubuntu setup
2. Add admin group
   groupadd admin
   useradd -a -G admin vagrant
3. update sudoers (sudo visudo; and add these lines
   Defaults:vagrant !requiretty
   Defaults env_keep = "SSH_AUTH_SOCK"
   %admin   ALL=(ALL:ALL) NOPASSWD: ALL
4. Add a port forwarding rule in Virtualbox NAT as follows:
       Name: SSH
       Protocol: TCP
       Host IP:
       Host Port: 2222
       Guest IP:
       Guest Port: 22
5. Install and configure OpenSSH Server
   sudo apt-get install -y openssh-server
   sudo vi /etc/ssh/sshd_config
   and add/modify these lines:
   Port 22
   PubKeyAuthentication yes
   AuthorizedKeysFile %h/.ssh/authorized_keys
   PermitEmptyPasswords no
   PasswordAuthentication no
   
   restart ssh server
6. Download vargant public and private key
   $ mkdir -p /home/vagrant/.ssh
   $ chmod 0700 /home/vagrant/.ssh
   $ wget --no-check-certificate \
       https://raw.github.com/mitchellh/vagrant/master/keys/vagrant.pub \
       -O /home/vagrant/.ssh/authorized_keys
   $ chmod 0600 /home/vagrant/.ssh/authorized_keys
   $ wget --no-check-certificate \
       https://raw.github.com/mitchellh/vagrant/master/keys/vagrant \
       -O /home/vagrant/.ssh/id_rsa
   $ chmod 0600 /home/vagrant/.ssh/id_rsa
   $ chown -R vagrant /home/vagrant/.ssh
7. Remove file to fix network issues afterward
   sudo rm -f /etc/udev/rules.d/70-persistent-net.rule
8. Comment out HWADDR row in /etc/sysconfig/network-scripts/ifcfg-eth1 (to fix https://github.com/mitchellh/vagrant/issues/1777 issue)

After that you are ready to create new vagrant box. There are several articles describing same process in depth.

Quick file search in Ubuntu

Quick answer to this simple question is

 locate <filename>

and to update slocate database

sudo updatedb

As always there is also long answer.

Install postgres, pgadmin in Ubuntu 12.04

Installing postgres database to Ubuntu 12.04 run

1. sudo apt-get install postgresql
2. sudo apt-get install postgresql-contrib
3. sudo apt-get install pgadmin3

There is nice article what you can to do further in postgres database.

To allow connection to postgres database from host computer these steps are necessary:

1. In VirtualBox NAT Advanced settings add port forwarding 5432 -> 65432
2. In guest start sudo vim /etc/postgresql/9.1/main/pg_hba.conf and add these lines
   host    all    all             192.168.0.0/16          md5
   host    all    all             10.0.0.0/8              md5
3. sudo vim /etc/postgresql/9.1/main/postgresql.conf
   listen_addresses = '*'
4. sudo -u postgres psql and execute
   create user vagrant with password 'vagrant';
   create database pentaho;
   GRANT ALL PRIVILEGES ON DATABASE pentaho to vagrant;
   alter user vagrant with superuser;
   \c pentaho
   CREATE SCHEMA repo;
   CREATE SCHEMA target;
   
5. In host pgadmin III create new connection
   Name: local
   Host: localhost
   Port: 65432
   Maintenance DB: pentaho
   Username: vagrant
   Password: vagrant

Add shared folder in VirtualBox

In Win7 host, Ubuntu 12.04 guest I tried to add Shared folder, but there are some simple tricks to know.

1. Add shared folder in VirtualBox (Settings -> Shared Folder -> Machine Folders)
2. Make sure that the additions are installed, and that you have added your username as a member of the vboxsf group (sudo usermod -aG vboxsf <your username>).
3. After restart shared folder  /media/sf_shared should be in place

How to install manually guest additions in linux
$ sudo apt-get install build-essential module-assistant linux-headers-$(uname -r) dkms -y
$ wget -c http://download.virtualbox.org/virtualbox/4.0.16/VBoxGuestAdditions_4.0.16.iso
$ sudo mount VBoxGuestAdditions_4.0.16.iso -o loop /mnt

Install Oracle Java 7 to Ubuntu 12.04

Easiest way to do it is to run in terminal

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java7-installer

Nice thing is that it keeps java version up-to-date. There is longer explanation with different alternatives in Ubuntu site.

Optional: add to .bashrc
export JAVA_HOME=/usr/lib/jvm/java-7-oracle

Install Ubuntu Desktop in Virtualbox

My idea was to use Vagrant to install and start Ubuntu Desktop. First step is to downloaded Vagrant and then start command line and move into new empty directory.  Next step is to download Ubuntu server

vagrant box add ubuntu64-13.04 http://cloud-images.ubuntu.com/vagrant/saucy/current/saucy-server-
cloudimg-amd64-vagrant-disk1.box

and start it

vagrant init ubuntu64-13.04
vagrant it


ssh into new server and install graphical user interface


vagrant ssh
sudo apt-get install ubuntu-desktop

allow graphical user interface in configuration file Vagrant


config.vm.provider :virtualbox do |vb|
  vb.gui = true
  vb.customize ["modifyvm", :id, "--memory", "1024"]
end

restart the system


vagrant reload

Actually it came out that Ubuntu Desktop graphics is really slooow, so I give up and started install Ubuntu Desktop without Vargant from scratch. Lesson that I learned today: dont use hammer when you have screws. Vargant is not meant to use with graphical desktop.

 

Virtualbox Ubuntu 12.04 admin privilegs problem

I have Ubuntu 12.04 Virtualbox guest in Win7 

I get admin privilegs problem after executing
sudo usermod -G vboxsf jack

I dont had admin(sudo) privilegs anymore and to fix it I found solution, which needed some improvements

1. boot to recovery mode during Ubuntu Virtualbox startup holding down SHIFT key
2. login as root and execute
3. mount -o remount,rw /
4. usermod -a -G sudo jack
5. reboot

How to reset password in VirtualBox Ubuntu 12.04

I forgot my Ubuntu 12.04 user password and I need quick fix. Google gave me nice solution http://www.psychocats.net/ubuntu/resetpassword . Problem was that I had Virtualbox based installation.
It came out that to fix it in Virtualbox environment these additional steps must be executed

1. get focus to new Virtualbox window with mouse during startup  
2. hold down SHIFT key to get into GRUB menu.