How To Configure Unicenta oPOS with MySQL Database Server

Here is another guide for Unicenta oPOS, that will teach how you would configure or set it up with a MySQL Database Server using Ubuntu 14.04. It’s a very easy to follow guide and all procedures are written step-by-step with reference images. So if you’re looking for such guide, then jump in to read the rest of the tutorial. You could use this guide for Windows or Linux based installation.

Jump Links

Update:

Unicenta 3.9 Installation Guide: Now available at https://chubbable.com/unicenta-3-9-ubuntu-16-install-guide

Purpose & Advantage

Unicenta’s default configuration when freshly installed is set to use the Derby Database Server. A database server embedded inside of Unicenta. This is only good for testing and experimental purposes. But when you’re ready to deploy your Unicenta box, you should be using it with an external Database Server like MySQL.

So the main purpose of this guide is to configure Unicenta with MySQL Database Server.

With this kind of setup, your Unicenta terminal should be able to access the database server whether it be online based server or locally deployed within your premises.

The advantages of this are; You have a separate database server where you store all your data and keep them safe in case your Unicenta box or terminal crashed or broke, you could also reinstall Unicenta for as many as you want while keeping your data safe and maintained from the database server, you could setup a multiple Unicenta terminal while maintaining a single database server which unify all your data and configurations and no need for a separate server for each terminal, you could also use a remote or online MySQL database server and connect any Unicenta terminal from different parts of the world and unify them using a single instance of database server, you as ADMIN could remotely login to Unicenta and check transaction logs, total sales and other managerial functions for a store or restaurant.

So for this guide, you’ll be taking advantage of those mentioned above after you successfully configured the MySQL database with Unicenta.

To following this guide, you must have the following.

Requirements

These are the things you need before starting off with this guide.

  • An instance of Unicenta installation
  • A separate machine/computer if you want a separate database server.
  • USB Web Server software for Windows based database server (if you want to use. Windows)
  • MySQL and LAMP Stack
  • PuTTy if using Windows for configuration
  • Some knowledge of Linux
  • A lot of patience

Note: I’ll be using DB and DBS here to refer to Database and Database Server.

Installation Options

There are different options and setups to configure a Database Server with your POS System.

Some options are:

  • Option 1 – DBS on the same machine where Unicenta is installed.
  • Option 2 – DBS on another separate or dedicated machine.

Option 1: MySQL DBS and Unicenta on the same computer.

Before you start with the initial step, make sure that you already have a working Unicenta instance. If not, then you first need to install and configure Unicenta.

Windows Based

In this part of the guide, we’ll configure a MySQL DBS for a Windows-based Unicenta terminal.

Both Unicenta and MySQL are on the same Windows machine.

Procedures

  • Step 1 – Download, extract and run USBWebserver.

    You know why I recommend this portable software? It’s because I’ve already tried and tested it and had used it several times for test purposes.

    But if we’re talking about multiple nodes or terminals, we should use a dedicated machine for the DBS.

    To continue, just download the software, extract it to a permanent directory of your choice. Say for instance, Drive D or any drive partition other than drive C:.

  • Step 2 – Run the software.

    Since this software is a portable server, you don’t have to configure or install any additional software, just run it.

    Click on the the usbwebserver.exe file, the software should launch.

    Now choose your preferred language from the drop-down menu and click OK to continue.

    You should see the dashboard now.

    Reference Image: USBWebserver Dashboard.

USB Web Server Dashboard - Image

USB Web Server Dashboard

  • Step 3 – Configure MySQL Port.

    You may or may not configure this further. But with this software, MySQL is bound to listen to port: 3307 whereas MySQL’s default listening port is 3306.

    If you want to change it back to the default port, then configure it from the dashboard.

    Click on Settings tab and then change port number under Port MySQL field.

    Click save button.

  • Step 4 – Create a database for Unicenta.

    Launch PHPMyAdmin using the dashboard, you could click on the the button or directly access the page using your favorite browser (Firefox or Chrome).

    To access the page using a browser, browse to:

    http://localhost:8080/phpmyadmin/
    

    Reference Image:

    Screenshot of PHPMyAdmin on Local Host

    PHPMyAdmin on Local Host

    Then login using root as the username while usbw for the password. You should see a page similar to this image.

    Click on Databases menu to create a new DB for Unicenta. Enter a DB name under the Create database field, and then click on the create button. You should now have a DB for your POS. See reference image here.

    Now let’s create a new user for the DB. The easiest way to do this is to create a new user with the same name as the DB name.

    Click on User from the top navigation menu, then click on the Add user text link. A new page should open, now fill-up the following fields like so.

    • User Nameunicenta
    • Hostlocalhost
    • Passwordyour-password

    NOTE: if you wanted to have a stronger password, use the Generate password button to create a longer and difficult password. When you click on the button, it will automatically fill the password field, plus it will show you the generated password on a separate field. You should copy and save this password and keep it somewhere safe.

    Let’s continue, browse a further down and click/tick:

    • Create database with same name and grant all privileges

    • Grant all privileges on wildcard name

    Then click Check All text link for the Global Privileges to select all options under this. Finally, click Go button from the bottom-right of the page.

    And that’s it! You’ve created a Database for Unicenta. The next step would be to configure Unicenta to connect to the server.

  • Step 5 – Connect Unicenta to the DBS.

    Go to the folder where Unicenta is installed, and then find and click the file configure.bat.

    A configuration screen should pop-up like the one shown in this image.

    Configure the settings like so;

    • From the Database dropdown menu, choose MySQL.
    • For the URL, jdbc:mysql://localhost:3306/unicenta
    • For User, unicenta
    • For Passwrd, your-password

    Now, click the Test button to test your connection. If all is well, then you should receive a You’re Connected! pop-up message. Reference image below.

    Screenshot of Successful Unicenta MySQL DB Connection

    MySQL Connection Success!

    Click OK and then finally save the settings. You’re now ready to launch and connect Unicenta to your database.

  • Step 6 – Fire up Unicenta.

    Click on your shortcut to open Unicenta, a pop-up notice saying…

    A working database cannot be detected. 
    A default database will be created. 
    
    Do you want to continue?
    

    This is not an error, but a notice. It’s just natural that your newly created DB has no tables yet. So you need to hit Continue to create the tables.

    Once you clicked OK you should be redirected to your Unicenta screen. If you’ve gotten this far, then congratulations you did it!

Option 2: MySQL DBS on another dedicated machine.

This setup will require you to use another computer to host your MySQL Database Server.

For this guide, we’ll be using Ubuntu 14.04 Server Edition. And this would mean, no GUI or it’s a headless installation of Ubuntu.

So before starting off with this part of the guide, make sure you already prepared your Ubuntu server. If not, then follow the tutorial from this post. Navigate to “Part 1: Ubuntu Server 14.04 LTS Installation” section of the post.

Procedures

  • Step 1: SSH into your Ubuntu Server to control it.

    If this is the first time you heard of SSH, it’s a Unix-based command-line tool that let’s you connect to another machine remotely and securely.

    Don’t worry too much about this because I’ll walk you through it and will provide you an easy to follow procedure.

    If you’re on Windows, download PuTTy (putty.exe) for your platform. Select from the Green Colored area of the page.

    After launching it, enter the Host IP address of your Ubuntu Server on the Hostname / IP Address field and then select port 22. Reference image below.

    Screenshot of PuTTy Screen On Windows

    PuTTy Screen On Windows

    From the left-pane of putty.exe window, go to Connection > Data menu. Then fill-up Auto-login Username with your Ubuntu Server username. It should be a user name that actually exist on your Ubuntu 14.04 Server.

    Click Open to initiate the connection.

  • Step 2: Install LAMP Stack and PHPMyAdmin.

    After logging in remotely, enter the following command to your putty.exe windows. You could copy and paste the command. To paste it, click middle mouse button.

    sudo apt-get update && sudo apt-get install lamp-server^ phpmyadmin
    

    This should take a little while, depending on your Internet speed.

    During the process a new screen should pop-up asking you to nominate a root password for your MySQL instance. Just make it so, you have to enter your password twice to confirm match. Reference Image:

    Screenshot of MySQL Installation on Ubuntu 14.04

    Installing MySQL

    The install should continue, then it will ask you again to configure PHPMyAdmin to use which web server to use. In this case, you should use Apache2 and hit OK.

    Reference Image:

    Screenshot of PHPMyAdmin Configuration During Install

    Configuring PHPMyAdmin

    Continue with the installation…

    A new screen should ask you to Configure database for phpmyadmin with dbconfig-common? Select YES for this.

    When a new screen will pop-up asking you to set a PHPMyAdmin password, do as it says and enter your. It will also ask for the MySQL application password, so you need to enter it twice.

  • Step 3: Install mcrypt and enable it.

    Mcrypt is a PHP5 module, which is needed for this setup. So we should enable it with our setup.

    To do just that, do the following command:

    sudo updatedb
    

    This command should update the locate database so you could locate certain files fast.

    Then invoke the command locate mcrypt.so. It should output the location of the extension. Copy the location and save it to a temporary text file. Then do the next command.

    locate mcrypt.ini
    

    Take note of the location again, you may copy the outputted text. Do the next command and paste the copied path.

    sudo nano then-paste-the-copied-text
    

    Sample input should look like this:

    sudo nano /etc/php5/mods-available/mcrypt.ini
    

    Then edit the extension= line to make it like so:

    extension=/usr/lib/php5/20121212/mcrypt.so
    

    Save the file by pressing ctrl + o > ENTER. o is LETTER O. Exit the editor by pressing ctrl + x.

  • Step 4: Enable the mcrypt.so extension.

    Command:

    sudo php5enmod mcrypt
    
  • Step 5: Restart Apach2 Server.

    sudo service apache2 resart
    
  • Step 6: Fire up your favorite browser and access **PHPMyAdmin page.**

    Sample:

    192.168.1.123/phpmyadmin
    

    You should use your servers IP address here.

    If you reach the Admin Login Page, then congratulations! You have now configured PHPMyAdmin. Next step that you should do is to configure MySQL to allow remote connections from within your LAN.

  • Step 7: Allow remote connection to MySQL Server.

    By default, MySQL is configured to listen from localhost (127.0.0.1) connection only. So we need to reconfigure that one so It will listen from remote connection, like from another Unicenta terminal from within your local network.

    To do just that, logon to your Ubuntu Server and enter the following command:

    sudo nano /etc/mysql/my.cnf
    

    Then scroll further down ’til you see this section, [mysqld].

    Then look for bind-address = 127.0.0.1

    Change this into:

    bind-address = your-server-ip-address
    

    Sample:

    bind-address = 192.168.1.123
    

    Save the file and exit the editor. That’s ctrl + o and then enter, then ctrl + x to exit.

    Finally, restart MySQL by doing the following command,

    sudo /etc/init.d/mysql restart
    
  • Step 8: Create a database for Unicenta to use.

    For the purpose of demonstration, We will unicenta for database name, unicenta for database user-name and password for the DB password.

    Method 1 – Using PHPMyAdmin GUI.

    Use web GUI to create a database. Follow Step 4 from Option 1.

    Method 2 – Creating a new database using the command-line.

    while still logged in to your server enter mysql -u root -p and then hit enter key. Input your password and press enter key. When you input the password, it will not show on your screen.

    Upon successful login, you should get a prompt like this:

    mysql>
    

    To create the database, type the following and press ENTER key:

    create database test;
    

    You should get an output like:

    Query OK, 1 row affected (0.00 sec)
    

    Create a username and password for Unicenta to use:

    grant all on unicenta.* to 'unicenta'@'your-unicenta-ip-address' identified by 'password;
    

    And…

    flush privileges;   
    

    Finally exit MySQL, by typing quit.

    NOTE: You must have configured your Unicenta terminal to have a static IP address instead of dynamic IP address through DHCP from your Router.

  • Step 9: Make a test connection.

    Use your Unicenta terminal to test your DB connection. To do this, just repeat the previous steps mentioned Option 1, Step 5, then use the database details you’ve created in this step.

    …and that’s it!

NOTE: You could also use this procedure on a Linux-based Unicenta installation, where you would want to setup the database server and Unicenta on the same machine. Just skip Step 7 from this part of the guide.

  • Step 10: Set basic firewall.

    Though your MySQL server is just for offline use and not online, you still have to setup a basic firewall for it. Do this is not so difficult, so read on.

    Login to your server again via SSH and do the following command:

    sudo ufw enable
    

    That should turn on Firewall blocking all ports. So you have to open the most important ports for you setup. Since this machine is just running MySQL server with Apache2 as a web server, you should only open ports 22, 80, and 3306.

    The command for this is:

    sudo ufw allow from 192.168.1.0/24 to any port 22
    

    then…

    sudo ufw allow from 192.168.1.0/24 to any port 80
    

    lastly…

    sudo ufw allow from 192.168.1.0/24 to any port 3306
    

    NOE 192.168.1.0/24 is your existing LAN subnet. This means, you are allowing access to the specified ports from port IP range 192.168.1.1 to 192.168.1.255. So use your existing network config for this step.

It’s a Wrap

Employing this kind of configuration lets you take advantage of a centralized data storage for a multiple Point of Sale terminals using Unicenta oPOS. With this, you should be able to maintain a single database from a single server, instead of maintaining a multiple copies of database on an embedded database setup of Unicenta.

Embedded database is just for testing, but when you’re ready for deployment, a separate, dedicated and centralized database server is recommended for Unicenta.

So there you have it.

This is my step-by-step guide on how to configure Unicenta oPOS with a MySQL Database Server.

I hope you find it useful and helpful. Please feel free to post your comments. Saying thank you if it worked for you motivates me to write more guides.


Chubbable

Hi, I'm Chubby! That's what my friends call me. I'm a tech savvy dude who is passionate in learning stuffs by himself. I post stuffs that I recently learned and also stuffs that I'm very knowledgeable of. I also post articles here to serve as my own reference and knowledge base archiving.

  • Asiana Kiara

    Thank you so much. You are my life saver! Appreciate a lot..Thank you for your hard work.

  • ForresterOD

    Great write up. Simple and perfectly intuitive. I followed it to the T and was up in minutes. I don’t usually do this, but thank you so much man. Much appreciated.

  • Jordan Hampson

    Hi amazing tutorial, got up and working very quickly and easy to follow instructions. I set it up and noticed that if my internet goes down, I am left with no connection and a till that will not load up. Do you know of away that this can work offline and in the cloud? So if the connections drops it will still be a functioning epos system.

    • Thanks for the comment! Technically that would be possible. But’s not yet a uniCenta feature, as far as I know. You need custom coding for that to work. Some sort of a caching feature, it will temporarily cached any activity locally, and it should transfer and record the changes as soon as you get your POS back online. I’m not a Java coder, but as I’ve told you. It’s possible.

      Another way to accomplish what you need is like this…

      Create two instances of MySQL Server one local and the other is online. The local instance is like what you had setup using this. You set your primary Dbase to the local instance, then sync the local to online.

      Local instance should write any changes to the online instance, whenever it has connection. But while it’s not connected, the POS is writing/reading from the local instance.

      I haven’t tried it yet though.

      Can you picture what I’m saying?

  • Jordan Hampson

    Hi amazing tutorial, got up and working very quickly and easy to follow instructions. I set it up and noticed that if my internet goes down, I am left with no connection and a till that will not load up. Do you know of away that this can work offline and in the cloud? So if the connections drops it will still be a functioning epos system.

    • Thanks for the comment! Technically that would be possible. But’s not yet a uniCenta feature, as far as I know. You need custom coding for that to work. Some sort of a caching feature, it will temporarily cached any activity locally, and it should transfer and record the changes as soon as you get your POS back online. I’m not a Java coder, but as I’ve told you. It’s possible.

      Another way to accomplish what you need is like this…

      Create two instances of MySQL Server one local and the other is online. The local instance is like what you had setup using this. You set your primary Dbase to the local instance, then sync the local to online.

      Local instance should write any changes to the online instance, whenever it has connection. But while it’s not connected, the POS is writing/reading from the local instance.

      I haven’t tried it yet though.

      Can you picture what I’m saying?

  • Barry talt

    Hey! first off all thanks very much for the tutorial, very clear and helpful but I unfortunately run into a road block. When attempting to connect to mysql database it says “Warning:Unable to connect to database: Database not available.”
    any ideas? (using windows)
    Cheers, Barry.

    • Please check that your MySQL Server is up and running. Check your uniCenta Database connection details. See to it that Database is set to MySQL, check the URL too.

      If all else fail, send me the details of your setup. Some screenshot will do.

  • Barry talt

    Hey! first off all thanks very much for the tutorial, very clear and helpful but I unfortunately run into a road block. When attempting to connect to mysql database it says “Warning:Unable to connect to database: Database not available.”
    any ideas? (using windows)
    Cheers, Barry.

    • Please check that your MySQL Server is up and running. Check your uniCenta Database connection details. See to it that Database is set to MySQL, check the URL too.

      If all else fail, send me the details of your setup. Some screenshot will do.

  • This is great! One question… If I go with Option 1, would I be able to connect another future uniCenta terminal to that db as long as they’re on the same LAN?

    • Yes you could do that. Follow the steps in OPTION 2 to connect your 2nd POS Terminal.

  • This is great! One question… If I go with Option 1, would I be able to connect another future uniCenta terminal to that db as long as they’re on the same LAN?

    • Yes you could do that. Follow the steps in OPTION 2 to connect your 2nd POS Terminal.

  • linkuphosting

    Hello Chubbable, I had been trying to set up UniCenta on a PC for ages with no success. Same problems over and over, i.e. connecting to the database. After finding your site and great instructions, I thought I had it made.

    Nope…

    I built a new Win7Pro PC, installed USBWebserver and Unicenta. When I went into Unicenta I got this error:

    DNG_00_0000
    Danger: Unable to connect to database. Database not available.

    In Unicenta it says:

    Database MySQL
    Driver Library:C:Program Files (x86)unicentaopos-3.91.3.libmysql-connector-java-5.1.26-bin.jar
    Driver Class: com.mysql.jdbc.Driver
    URL: jdbc:mysql://localhost:3306/nmweap
    User: nmweap
    Password: **** (suggested password)

    When doing the dB test in Unicenta, it too says it can’t connect and I thought it had to do with the user, so I created a second variant of the user to no avail.

    For testing I have two types of user account, same password, have a database that they have privileges to…I am missing something….help please!

    • Hi!

      You missed the point about PORT NUMBER that USBWEBSERVEr uses. I mentioned that it is bound to listed from port 3307. So, change your settings like so:

      URL: jdbc:mysql://localhost:3307/nmweap

  • linkuphosting

    Hello Chubbable, I had been trying to set up UniCenta on a PC for ages with no success. Same problems over and over, i.e. connecting to the database. After finding your site and great instructions, I thought I had it made.

    Nope…

    I built a new Win7Pro PC, installed USBWebserver and Unicenta. When I went into Unicenta I got this error:

    DNG_00_0000
    Danger: Unable to connect to database. Database not available.

    In Unicenta it says:

    Database MySQL
    Driver Library:C:Program Files (x86)unicentaopos-3.91.3.libmysql-connector-java-5.1.26-bin.jar
    Driver Class: com.mysql.jdbc.Driver
    URL: jdbc:mysql://localhost:3306/nmweap
    User: nmweap
    Password: **** (suggested password)

    When doing the dB test in Unicenta, it too says it can’t connect and I thought it had to do with the user, so I created a second variant of the user to no avail.

    For testing I have two types of user account, same password, have a database that they have privileges to…I am missing something….help please!

    • Hi!

      You missed the point about PORT NUMBER that USBWEBSERVEr uses. I mentioned that it is bound to listed from port 3307. So, change your settings like so:

      URL: jdbc:mysql://localhost:3307/nmweap

  • Hisham Hassan

    Hi Chubbable. I am trying out Option 3 and had everything setup as per your instruction. However , when I test the connection , I received this error .

    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

    Communications link failure

    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

    java.net.ConnectException:

    Connection timed out: connect

    I had firewall enabled . .also pls check the configuration screen attached. Please advise. Thanks !!

    • Allow incoming connection in your firewall, allow incoming for port 3306. Do you have a static public IP address for your home internet?

  • Hisham Hassan

    Hi Chubbable. I am trying out Option 3 and had everything setup as per your instruction. However , when I test the connection , I received this error .

    com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

    Communications link failure

    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

    java.net.ConnectException:

    Connection timed out: connect

    I had firewall enabled . .also pls check the configuration screen attached. Please advise. Thanks !!

    • Allow incoming connection in your firewall, allow incoming for port 3306. Do you have a static public IP address for your home internet?

  • Camilo Duque

    Hi Chubbable first I want to congratulate you for this post and really give you the thanks for share you knowledge with us, buddy I want to create my own POS for my restaurant so I want to know how to mitigate risk with the DB created by USBWebserver, My goals are trying to backup this DB daily and have the chance to full restore it if any thing happens with the PC, please Sr can you show me the path to follow with this question?

    • You could actually just do that manually. Login to PHPMyAdmin and manually export the dbase to backup it up. While importing a backup file means restoring your dbase. Import/Export of uniCenta Database is pretty straight-forward.

      • Camilo Duque

        Ty Sr I use the tools Extract in phpMyadmin and Import to recover the DB,
        Best Regards.

  • Camilo Duque

    Hi Chubbable first I want to congratulate you for this post and really give you the thanks for share you knowledge with us, buddy I want to create my own POS for my restaurant so I want to know how to mitigate risk with the DB created by USBWebserver, My goals are trying to backup this DB daily and have the chance to full restore it if any thing happens with the PC, please Sr can you show me the path to follow with this question?

    • You could actually just do that manually. Login to PHPMyAdmin and manually export the dbase to backup it up. While importing a backup file means restoring your dbase. Import/Export of uniCenta Database is pretty straight-forward.

      • Camilo Duque

        Ty Sr I use the tools Extract in phpMyadmin and Import to recover the DB,
        Best Regards.

  • Jenn Zafra

    Hi! Thank you for this very informative post. Can you please help me? I cant seem to login at phMyAdmin. I used the username ‘root’ and the password ‘usbw’ like you instructed but this is what it says when I click Go:

    #1045 Cannot log in to the MySQL server

  • Jenn Zafra

    Hi! Thank you for this very informative post. Can you please help me? I cant seem to login at phMyAdmin. I used the username ‘root’ and the password ‘usbw’ like you instructed but this is what it says when I click Go:

    #1045 Cannot log in to the MySQL server

  • Rusdi Zaman

    WRN_00_0000
    Warning: Unable to connect to database: Database not available
    How to solve it.

    • You mispelled localhost. It should be `localhost` NOT `localost`.

      • Rusdi Zaman

        Thanks a lot…… 🙂

  • Rusdi Zaman

    WRN_00_0000
    Warning: Unable to connect to database: Database not available
    How to solve it.

    • You mispelled localhost. It should be `localhost` NOT `localost`.

      • Rusdi Zaman

        Thanks a lot…… 🙂

  • Umer Solanki

    Dear Chubbable – First of all, thanks a lot for sharing information in such a wonderful manner, i tried to follwed the steps as precisely as possible but still getting error

    DNG_00_0000
    Danger: Unable to connect to database. Database not available

    I am attaching screen shots can you please guide what i might have missed

    Regards

    • Make sure you are connecting to the right Database. Are you sure you created `unicentaopos` Database in Phpmyadmin?

      Look at @rusdizaman:disqus ‘s settings below. He had used `unicenta` for both the Database Name and Database USERNAME.

      • @umersolanki:disqus Use unicenta instead of unicentaopos, in the URL.

        Based from your last screenshot, you named your Database as unicenta, NOT unicentaopos.

  • Unicenta 3.9 Installation Guide: Now available at https://chubbable.com/unicenta-3-9-ubuntu-16-install-guide