Friday, June 5, 2015

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

Saturday, January 4, 2014

Install Crashplan on Synology NAS DS112+


There is article from authors of CrashPlan Synology version describing installation process (PC Load Letter). This is my version of installation process of CrashPlan to DS112+.
  1. First step is to add Package Center -> Settings -> Package Sources: PC Load Letter | http://packages.pcloadletter.co.uk.
  2. Next step is to install Java SE for Embedded 7.
  3. Download manually Java SE for Embedded 7 that is suitable for you Synology NAS (ejre-7u45-fcs-b15-linux-arm-sflt-headless-26_sep_2013.tar.gz for my DS112+) and copy this file to NAS public folder
  4. Run Package Center -> Community -> Java SE for Embedded 7 "Install". If Java SE for Embedded 7 compressed file is available in public folder then everything should go smoothly.
  5. Next step is to install Crashplan to Synology NAS.
  6. Run Package Center -> Community -> CrashPlan "Install". After installation everything is in place and next step is to configure CrashPlan on NAS server.
  7. Configuration should be done from PC with ssh port forwarding.
  8. Run from PC command line
    ssh -L 4200:localhost:4243 root@<NAS ip-address>
    what will forward PC 4200 port to NAS 4243 port
  9. Change CrashPlan ui.properties file (C:\Program Files\CrashPlan\conf\ui.properties in my case)
    servicePort=4200
  10. Run CrashPlan user interface, change NAS server parameters as necessary and finally press "Save changes"
  11. Remove servicePort=4200 from ui.properties file and configure local CrashPlan (folders to backup and so on).
There is another good article describing same process, if you find any problems during installation. There are also some articles with out-of-date information (specially synology wiki).
After running all these steps you are ready to backup your PC files/directories to Synology NAS server as you configuration describes.

I have added some additional steps (optional) to my configuration. In PC I have configured to run backup every day from 1:00 AM to 6:00 AM. In NAS server I have added these 2 lines to /etc/crontab
0       0       *       *       *       root    /volume1/@appstore/CrashPlan/bin/CrashPlanEngine start
0       7       *       *       *       root    /volume1/@appstore/CrashPlan/bin/CrashPlanEngine stop

So CrahPlan will run on NAS server every day from 0:00AM to 7:00AM.

Cron daemon restart is necessary to make crontab changes effective.

/usr/syno/etc.defaults/rc.d/S04crond.sh stop
/usr/syno/etc.defaults/rc.d/S04crond.sh start

Tuesday, December 31, 2013

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.

Thursday, December 5, 2013

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.

Tuesday, November 26, 2013

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.

Monday, November 25, 2013

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