Odoo : How To Migrate the PostgreSQL Database To A New Server

Tags

, , , , , ,

Setup

Application Server   -  Odoo Server
Database Server      -  Postgresql Server

In Database server

Install postgresql

#  sudo apt-get update
# sudo apt-get install postgresql postgresql-contrib postgresql-client

Create odoo user

# createuser --superuser odoo

Create database

# createdb omegha-odoo

Now login to psql

# psql

Change the ownership of database to odoo user.

postgres-# ALTER DATABASE "omegha-odoo" OWNER TO odoo;

Edit the following configuration files

/etc/postgresql/9.3/main/pg_hba.conf

# -----------------------------
# PostgreSQL Client Authentication Configuration File
# ===================================================

# IPv4 local connections:
host    all     all        trust

/etc/postgresql/9.3/main/postgresql.conf

# -----------------------------
# PostgreSQL configuration file
# -----------------------------

listen_addresses = '*'

Restart postgresql service

# service postgresql restart

In Application Server

 Edit the following configuration file

/etc/odoo/openerp-server.conf

[options]
db_host = <ip-address of the database server>
db_name = omegha-odoo
db_password = 
db_port = 5432
db_user = odoo

# service odoo restart
Advertisements

Installing Odoo 8 in Ubuntu

odoo_logo_white_rgb

Odoo (formerly known as OpenERP ) is a suite of open core enterprise management applications. Targeting companies of all sizes, the application suite includes billing, accounting, manufacturing, purchasing, warehouse management, and project management.

Installing Odoo

Step  1 – Adding apt repository

Open your system terminal window and execute the following commands as root.

The user need to add repository to install required packages for Odoo.  Use the following commands for adding Odoo apt repositories.

# wget -O - https://nightly.odoo.com/odoo.key | apt-key add -# echo "deb http://nightly.odoo.com/8.0/nightly/deb/ ./" >> /etc/apt/sources.list

Step 2 – Installing Odoo

 Now update the apt repository and install Odoo.

 # apt-get update && apt-get install odoo

Step 3 – Cloning Odoo files

Clone the Odoo files on your server.

# git clone https://github.com/odoo/odoo.git

Step 4 – Restart the Service

Now restart the Odoo service.

# sudo service odoo restart

Resetting the webERP password for admin user

webERP will have a default user admin with password ‘weberp’. If one has changed the default password and then later forgot it, the easiest way to login again is by resetting the admin password.

To reset the admin password, we’ll have to create a .php file named passwordreset.php in the webERP folder.

$ cd /var/www/html/webERP
$ sudo vi passwordreset.php

Add the content

<?php
include('config.php');
$db = mysqli_connect($Host, $DBUser, $DBPassword, 'put-company_name-here',  $DBPort);
$Result = mysqli_query($db, "UPDATE www_users SET password='".password_hash('weberp',PASSWORD_DEFAULT)."' WHERE userid='admin'");
?>

Save the content.

PS: You’ll have to put the webERP company name here in this .php file.

Go to web browser. Run this .php file.

For eg: Put ‘server-ip-address’/webERP/passwordreset.php in the url field

After running this, password for the user ‘Admin’ will have changed to ‘weberp’.

Login using these credentials, we can go to Main Menu> setup> users maintenance to change the password we want to use.

 

How to Install phpMyAdmin on Ubuntu 14.04

Tags

, , , ,

Prerequisites for installing phpMyAdmin

  • PHP 5
  • Apache 2
  • MySQL

 

  • Take your system terminal window and run the following command to install LAMP stack on your system
  • Lamp stack comes bundled with all three components (Apache, MySQL, PHP)
# sudo apt-get install lamp-server       
  • During the installation process, you should see a pop up window asking to set root the password for MySQL.
  • Now Install phpMyAdmin
 # apt-get -y install phpmyadmin
  • During installation process, you will see a pop up window asking a  few simple questions regarding the basic configuration of phpMyAdmin.
  • At the first screen, select apache2 by using the space bar, then hit enter to continue.

phpmyadmin1

  • At the second screen, which asks “configure the database for phpmyadmin with dbconfig-common?”, selectYes, then hit enter to continue.

phpmyadmin2

  • At the third screen enter your MySQL password, then hitenter to continue.

phpmyadmin-org-3

  • And finally at the fourth screen set the password you’ll use to log into phpmyadmin, hitenter to continue, and confirm your password.

phpmyadmin3

  • Now configure Apache
 # vi /etc/apache2/apache2.conf
  • Add the following lines in bottom of file
   # phpMyAdmin Configuration 
    Include /etc/phpmyadmin/apache.conf
  • Restart the apache service
   # service apache2 restart

        

 

webERP login error : Solved

Tags

, , ,

ERROR:  “Too many failed login attempts. You will have to see an authorised person to obtain access to the system”

This is an error that sometime pops up in webERP when you’ve entered the wrong user details a few times wrongly. This is a self protecting attribute of webERP which will ensure that the user in place will be blocked from entering again until an authorised person grants user the access to the system.

If you think you’re unfairly treated and have access to the MySQL database of the application there’s a away to get around this problem.

Login to MySQL database and select the webERP database you’re using.

 mysql > use weberp ;

Open the www_users table in the database which contains all the details about the users created in the databases

mysql > desc www_users ;

sdfsf

mysql > select * from www_users ;

This will give the list of of users in the database and their attributes.

Here the value in ‘Blocked’ field of the user who’s having difficulties logging in will be 1.

By changing the binary value to 0, one can change the status of this user. To do that

mysql > update www_users set Blocked = 0 where userid = 'user_here';

Try logging in with the correct password again, and this time it’ll be working without any issues.

Migrating a MySQL Database To Another Server

Tags

, , , , ,

Running a web application and database in two different servers in the same datacentre is a good way to scale the environment to handle more traffic and add more uniformity. For this there needs to be a database server (MySQL server installed) that can handle all database functions of the web application server.

To carry out this we need to

  1. a) Migrate existing database in the web server to new database server
  2. b) Reconfigure the web application to connect to new database

Migrating Existing Database To Database Server

For this the database server needs to have MySQL database server running in it.To know how to install MySQL server click here

By default the MySQL database is configured to listen to the localhost (127.0.0.1) only. To configure this database server to listen all public addresses that can connect to it, open MySQL configuration for editing:

 $ sudo vi /etc/mysql/my.cnf

Edit the line file:

 bind-address       = 127.0.0.1

Replace 127.0.0.1 with asterisk

 bind-address       = *

If you’re configuring the database server for only this web-application server, it’s more secure to give the IP address as bind-address. In that case

 bind-address       = 'web-server-IP'

To apply the changes, save the configuration file and restart the MySQL service.

 $ sudo service mysql restart

 

Export Backup of Original MySQL Database

We need to export a backup of original MySQL database in the web-server, which will be used to migrate to the new database. Before taking the backup of the data, stop the web-application server to prevent attempted updates to the existing database during the migration process.

For this you need to create a data snapshot. There are different methods to create the database snapshot, depending on the size of the database and the location of the files.

  1. a) Create a snapshot using mysqldump
  2. b) By copying the data files directly.

Before that flush all the tables and block write statements by executing

mysql > flush tables with read lock;
mysql > SET GLOBAL read only = ON ;

To create data snapshot we’re going to use mysqldump

 $ sudo mysqldump -uroot -p web-database > backup.sql

Here web- database being the original MySQL database in the web-server you want to migrate to database server

Copy tkup database file to the database server using scp:

 $ sudo scp backup.sql user@database-server:/tmp

Import Original Database Into Database Server

To import the original MySQL web-server database into database server

On Database server,

 mysql -u root -p < /tmp/backup.sql

All of the original MySQL database data and users are copied over to the database server. For Web-server to access the database server, the database server needs to have users that have the same privileges as the original ones in web-server.

You will have to create new users with a “host” value that matches the web-server’s IP address.

For this, create a new user with the same name as in the web-server but change its host to the IP address of the web-server. Password should remain the same.

CREATE USER 'inouser'@'web-server-IP’ IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON 'web-database'.* TO 'inouser'@'web-server-IP';
FLUSH PRIVILEGES;
exit;

The new database server is now migrated and configured. To connect to the database server, the web application configuration file needs to be updated. The configuration files reside in different locations for different applications. For inoERP application, the configuration file is seen in /var/www/html/inoERP-master/inoerp/includes/basics/settings as dbsettings.inc file.

Open inoERP configuration:

$ sudo vi /var/www/html/inoERP-master/inoerp/includes/basics/settings/dbsettings.inc

Look for the following lines:

define("DB_SERVER", "localhost");

Replace localhost with the IP address of the database server.

define('DB_HOST', ‘Database-server-IP-address’);

There’s no need to change the user or password as they were recreated in the database server.

The database migration is complete and the application now should be able to use the MySQL database running in a different server (Database server).

To verify that, access the application using the web browser.

How to add ZABBIX Agent on Ubuntu 14.04

Tags

, , ,

Step 1 – Adding apt repository

The user need to add repository to install required packages for zabbix agent. Use the following commands for adding zabbix apt repositories

$ wget http://repo.zabbix.com/zabbix/3.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_3.0-1+trusty_all.deb
$ sudo dpkg -i zabbix-release_3.0-1+trusty_all.deb
$ sudo apt-get update

Step 2 – Installing Zabbix Agent

We had successfully added zabbix apt repository. Now install zabbix agent.

$ sudo apt-get install zabbix-agent

Step 3 – Edit Configuration File

After completing installation user need to edit the configuration file /etc/zabbix/zabbix_agentd.conf.

#Server=[zabbix server ip]
#Hostname= [Hostname of client system]

Step 4 – Restart Zabbix Agent

 Now restart the Zabbix Agent service to bring up the change we made.

$ sudo service zabbix-agent restart

Now you have successfully installed Zabbix Agent.

Monitoring MySQL using Zabbix

  • Login to zabbix

1

  • Here you can see the dashboard showing host status and triggers.

2

  • Now the user should add host in the zabbix.
  • Click on configuration and select Hosts.
  • Here you can see the list of added hosts.
  • To add a new host click on add host button.

3

  • Fill the following details with hostname, groups and IP address.

4

  • Select Templates and add mysql templates.
  • To select templates click on select button.

5

  • It will prompt a window select Template App MySQL and other required templates from there.

6

  • Now add the selected templates.

7

  • Now the MySQL server is hosted with selected templates.
  • Now the user need to install zabbix-agent in MySQL server. (Click here for how to)

8

  • In that template there will be various MySQL monitoring Items.
  • If user need to add more item then select Item.

9

  • Here user can check the available items.
  • To add new item click on add item button.

10

  • Here give the item name, key, type and application .
  • Then select add.

11

  • Now the item is created.
  • Then add trigger to the item.
  • Select the item and click on create trigger.

12

13

  • It will prompt a window.

14

  • Give name, expression and severity and click on add button.

15

  • Now the trigger monitoring MySQL error log is added successfully.

16

MySQL- Dual-master replication

Tags

, , , ,

Dual-master replication is a method of database replication which allows data to be stored by two servers, and updated by any of these two. It allows data to be copied from either server to the other one. This upgrade on normal ‘master-server’ replication allows us to perform mysql read or writes from either server, and adds redundancy and increases efficiency when dealing with accessing the data.

Here we’re using Binary Log File Position Based Replication for the replication purpose.

And server IDs 1 and 2 for the servers. It’s imperative to use unique server IDs to prevent the cyclical replication and subsequent errors.

Configuration – Server1

1) To configure the binary log and server ID options, shut down the MySQL server and edit the /etc/mysql/my.cnf file.

       $ sudo vi /etc/mysql/my.cnf

       [mysqld]

       log-bin=/var/log/mysql/mysql-bin.log

       innodb_flush_log_at_trx_commit=1

       sync_binlog=1

       server-id=1

To indicate which database(s) we want to replicate between our servers, add the line

       binlog_do_db=testdb

We can add as many databases to this line as we’d like.

Comment out the bind-address line to accept connections from the internet (by not listening on 127.0.0.1).

       #bind-address            = 127.0.0.1

My.cnf file after the configuration will look like

        ….

        [mysqld]

        log-bin=/var/log/mysql/mysql-bin.log

        innodb_flush_log_at_trx_commit=1

        sync_binlog=1

        server-id=1

        binlog_do_db=testdb

        #bind-address            = 127.0.0.1

        …

Now we need to restart MySQL service

        $ sudo /etc/init.d/mysql restart

2) Creating replication user account

        mysql> create user 'repl'@'%' identified by ' password';

        mysql> grant replication slave on *.* to 'repl'@'%';

Permissions for replication can’t be given on a per-database basis. Our user will only replicate the databases that we instruct it to in my.conf file.

3) Take backup and note the position for the slave replication.

         mysql> flush tables with read lock;

         mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |     1678 | testdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need to make a note of the file and position which will be used in the next step.

To take backup, use mysqldump

     $ sudo mysqldump -uroot -p***** testdb > testdb.sql

     mysql> unlock tables;

Transfer this backup file from the server to 2nd server using scp command.

 

Configuration- Server 2

We need to do the same steps that we did on Server 1. Server ID should be unique.

    sudo vi /etc/mysql/my.cnf

    server-id              = 2

    log_bin                = /var/log/mysql/mysql-bin.log

    binlog_do_db     = testdb

    #bind-address    = 127.0.0.1

After you save and quit that file, you need to restart mysql:

    $ sudo /etc/init.d/mysql restart

2) Creating replication user account

    mysql> create user 'repl'@'%' identified by ' password';

    mysql> grant replication slave on *.* to 'repl'@'%';

3) To import data

    $ mysql -uroot -p testdb < testdb.sql

Here testdb is the name of the database in this server to which the data backup of server 1 is being imported. Create database if it’s not already there, by using

    mysql> create database testdb;

4) Pointing to the master

This step involves taking the information that we took a note of earlier and applying it to our mysql instance and starting the replication.

 mysql> change master to master_host= 'Host-address-of-server-1', master_user='repl', master_password='password', master_log_file='mysql-bin.000012', master_log_pos=1678;   
 mysql> start slave;

The last thing we have to do before we complete the mysql master-master replication is to make note of the master log file and position to use to replicate in the other direction (from Server 2 to Server 1).

We can do that by typing the following:

    mysql> show master status;

 

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000018 |   747564 | testdb       |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Take note of the file and position, as we will have to enter those on server 1, to complete the two-way replication.

Step 3 – Configuring server 1 again to complete two-way replication.

 mysql> change master to master_host = 'host-address-of-server-2, master_user ='repl', master_password = 'password', master_log_file = 'mysql-bin.000018', master_log_pos = 747564;   
 mysql> start slave;

Dual-master replication is setup now. Update the tables in the database in either servers and check if the changes are replicated to the other server. A successful replication ensures that the changes are replicated.

MySQL – Multi-Source Replication

Tags

, , , ,

MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. It requires at least two masters and one slave configured, and the slave creates a replication channel for each master that it should receive transactions from.

Masters in a multi-source replication topology can be configured to use either global transaction identifier (GTID) based replication, or binary log position-based replication. We are going to do the GTID based replication.

GTID based replication

GTID-based replication is a completely transaction-based transaction, makes it easy to determine whether masters and slaves are consistent; as long as all transactions committed on a master are also committed on a slave, consistency between the two is guaranteed.

Before getting into multi-source replication, there are some generic tasks that are common to all replication setups:

We’ll be using server IDs 1 and 2 for master servers and 3 for slave server here.

Master setup

On the master, you must enable binary logging and configure a unique server ID.

$ sudo vi /etc/mysql/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

innodb_flush_log_at_trx_commit=1

sync_binlog=1

server-id=1

To enable binary logging with global transaction identifiers, each server must be started with GTID mode

$ sudo vi /etc/mysql/my.cnf

gtid-mode = on

enforce-gtid-consistency = on

After making the changes, restart the server.

sudo /etc/init.d/mysql restart

Create user for your slaves to use during authentication with the master when reading the binary log for replication. They all can use the same user login if they’re allowed to.

mysql > CREATE USER 'repl'@'%' IDENTIFIED BY 'replpasswd';

mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

If you already have data on your master and want to use it to synchronize your slave, you need to create a data snapshot. There are different methods to create the database snapshot, depending on the size of the database and the location of the files.

  1. a) Create a snapshot using mysqldump
  2. b) By copying the data files directly.

Before that flush all the tables and block write statements by executing

mysql > flush tables with read lock;

To create data snapshot we’re going to use mysqldump

$ sudo mysqldump -uroot -p --all-databases --master-data > backupname1.dmp

Unlock all the tables in the same session.

mysql > unlock tables;

Repeat the steps in the second master server with server ID = 2 and backup name =Backupname2

Slave setup

Replication slave must have a unique server ID.

$ sudo vi /etc/mysql/my.cnf

[mysqld]

log-bin=/var/log/mysql/mysql-bin.log

innodb_flush_log_at_trx_commit=1

sync_binlog=1

server-id=3

For GTID replication

$ sudo vi /etc/mysql/my.cnf

gtid-mode = on

enforce-gtid-consistency = on

Slaves in a multi-source replication topology require TABLE based repositories. Multi-source replication is not compatible with FILE based repositories. The type of repository being used by mysqld can be configured either at startup, or dynamically.

To do it at startup

sudo vi /etc/mysql/my.cnf

master_info_repository='TABLE'

relay_log_info_repository='TABLE'

After making the changes, restart the server.

$ sudo /etc/init.d/mysql restart

When setting up replication with existing data, transfer the snapshot from the masters to the slave, one by one.

$ mysql -uroot -p 

$ mysql -uroot -p 

Warning: if GTID already is enabled, the server throws the error

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

You can resolve this error by clearing the GTID variables and importing the data again.

For that

mysql > reset master;

$ mysql -uroot -p 

$ mysql -uroot -p 

For multisource to be enabled, the slave server needs to be aware of the IP addresses of the masters and the user credentials. The ‘FOR CHANNEL’ clause can be used to add the master to the corresponding replication channel.

mysql > change master to master_host='Host-address-of-server-1', master_user='repl', master_password='replpasswd', master_auto_position=1 for channel 'channel1';

mysql > change master to master_host='Host-address-of-server-2', master_user='repl', master_password='replpasswd', master_auto_position=1 for channel 'channel2';

To start all currently configured replication channels

start slave;

To monitor the status information on essential parameters of the slave threads

show slave status \G

If everything is gone according to the plan, you now have two master MySQL databases replicating data to a single MySQL slave database.