Please rate how useful you found this document: 
Average: 2.4 (7 votes)

Overview

When working with large databases as well as working with many different users or just for security issues, system administrators often prefer to work with more than one database. This type of database management helps to improve the performance of systems when working with a great number of transactions in a short time frame or environments located at great distances from one another. It also manages better data security and integrity, making it possible to secure data that not everyone should have access to.

ProcessMaker uses MySQL as its principal database program to store ProcessMaker's wf_<WORKSPACE> database. MySQL offers replication between master and slave databases. In version 2.8 and later, ProcessMaker takes advantage of MySQL replication to offer the Double Connection feature that allows it to manage two database connections inside ProcessMaker. One of them is a connection to a "master" database with write access and the other is to a replicated "slave" database, that has only read access.

These connections can be made to two different servers where the databases are installed. One connection will be used for reading only and the other for writing. It also has a data access layer that identifies, separates and/or redirects queries to one of the two connections and inserts and updates to the other connection.

This feature is available with the acquisition of the Enterprise Edition license for ProcessMaker 2.8. To check if your installation of ProcessMaker has this feature installed, go to Admin > Plugins > Enterprise Manager. Click on the Enterprise Features tab, where it should be listed as installed and enabled

Requirements

Before setting up a second connection in ProcessMaker, take into account the following:

  • The slave database must be configured in MySQL.
  • ProcessMaker's database(s) must be replicated in another server with read-only access (slave).
  • If ProcessMaker 2.8 was installed in a clean environment the only database created is: wf_<WORKSPACE>.
  • If ProcessMaker was upgraded from a version before 2.8, there will be three databases: wf_<WORKSPACE>, rb_<WORKSPACE> and rp_<WORKSPACE>.

Configuring MySQL Replication

To set up MySQL replication with a master and slave databases, first install MySQL on a second server which will hold the slave database. Then, follow these instructions to set up the replication. In this example, the master database is located at a server named master.pmexample.com and the slave database is located at a server named slave.pmexample.com.

Configuring the master database

Go to the first server, which holds the original MySQL database which will be the master. With a plain text editor, open the MySQL configuration file my.cnf, whose location depends on the operating system.

In the my.cnf file, set the following settings:

bind-address            = master.pmexample.com
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = wf_workflow
binlog_format           = ROW

Set the bind-address to the IP address or domain name of the server holding the master database. Set the server-id to a unique integer, which must be different from the server-id of the slave database. In this example, the master has an ID of 1 and the slave has an ID of 2.

The log-bin is the name of a binary file which will hold a log of the changes made in the master database. The slave database will replicate any change recorded in this binary log file. If unsure where to place the file, just set it to mysql-bin and MySQL will place it in its default log directory.

binlog_do_db is set to the name of the workflow database which will be replicated in the slave server, which by default is named wf_workflow. Setting binlog_format to ROW will help avoid errors. If processmaker 2.8 was upgraded from a previous version, then list both the wf_<WORKSPACE> and rb_<WORKSPACE> databases. For example:

binlog_do_db            = wf_workflow
binlog_do_db            = rb_workflow

After saving the changes to the my.cnf file, then restart the MySQL server for the changes to take effect. On most Linux/UNIX systems it can be restarted with the command:

service mysqld restart

Then, enter MySQL as the root user:

mysql -u root -p

In Windows it is necessary to navigate in the file system to the directory to where the mysql.exe executable is located:

Windows XP/Server 2000/2003:

cd C:\Program Files\ProcessMaker-2_X_X\mysql\bin
mysql -u root -p

Windows Vista/7/8/Server 2008:

cd C:\User\USERNAME\AppData\Roaming\ProcessMaker-2_X_X\mysql\bin
mysql -u root -p

After logging into MySQL, configure MySQL to allow a user on the slave database to replicate the master database:

mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user'@'slave_address' IDENTIFIED BY 'password';

For better security, it is recommended to create a new user for the 'slave_user', and not use 'root'. Then also give the 'slave_user' the right to connect to the master database:

mysql> GRANT ALL ON *.* TO 'slave_user'@'slave_address' IDENTIFIED BY 'password';

For example, to setup the user 'pmreplicator' from the server slave.pmexample.com to do replication of the master database:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'pmreplicator'@'slave.pmexample.com' IDENTIFIED BY 'p4sSw0rD';
mysql> GRANT ALL ON *.* TO 'pmreplicator'@'slave_address' IDENTIFIED BY 'p4sSw0rD';

Then issue the following command to display information about the master database:

mysql> SHOW MASTER STATUS;
+------------------+----------+----------------+------------------+
| File             | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
+------------------+----------+----------------+------------------+
| mysql-bin.000023 |      107 | wf_workflow    |                  |
+------------------+----------+----------------+------------------+
1 row in set (0.00 sec)

Copy the File and Position, since these values will be used later when configuring the slave database.

Finally, exit MySQL:

mysql> QUIT;

Then enter the following command to make a copy of the database to be replicated.

mysqldump -u root -p --master-data=2 --flush-privileges wf_workflow > pm-db.sql

Change wf_workflow to the name of wf_<WORKSPACE> database to be replicated. If ProcessMaker was upgraded from version 2.5 or earlier, then make a copy of both the wf_<WORKSPACE> and rb_<WORKSPACE> databases. For example:

mysqldump -u root -p --master-data=2 --flush-privileges --databases wf_workflow rb_workflow > pm-db.sql

Then copy the pm-db.sql file from the master server to the slave server. For example, if using scp to copy the file:

scp pm-db.sql user@master.pmexample.com:~

Configuring the slave database

Go to the server where the slave database will be located. If not already installed, install MySQL in the server.

Then create the databases in the slave which are saved in the pm-db.sql file:

mysql -u root -p < pm-db.sql

If using Windows, remember to change the the directory containing the mysql.exe executable before issuing the above command and include the path to the pm-db.sql file.

Then, open the my.cnf file on the slave server with a plain text editor (such as vim, nano, gedit, notepad or Notepad++) to set the following settings:

bind-address            = slave.pmexample.com
server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = wf_workflow
binlog_format           = ROW

Set the bind-address to the domain name or IP address of the slave server. Set the slave server-id to a integer which is unique from the master server-id. Add the setting for the relay-log, which doesn't exist by default. The relay-log and log_bin can be set to any location writable by MySQL. If unsure where to place the files, then set them to mysql-relay-bin and mysql-bin and they will automatically be placed in the default log directory for MySQL.

Set the binlog_do_db to the name of the wf_<WORKSPACE> database which is being replicated, which is named wf_workflow by default. If processmaker was upgraded from version 2.5 or earlier, then also add the name of the rb_<WORKSPACE> database:

binlog_do_db            = wf_workflow
binlog_do_db            = rb_workflow

Then save the changes in the my.cnf file and restart the MySQL server for the new changes to take effect.

After restarting MySQL, login as the root user:

mysql -u root -p

If using Windows, remember to first change the the directory where the mysql.exe executable is located.

Then configure the slave database to be able to connect to the master database.

mysql> CHANGE MASTER TO MASTER_HOST='master.pmexample.com', MASTER_USER='slave_user', MASTER_PASSWORD='password',
       MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=107;

This command sets the address and login credentials to replicate the master database. The MASTER_LOG_FILE and MASTER_LOG_POS should be set to the log file and log position which were previously displayed with the SHOW MASTER STATUS command in the master database.

Finally, start the slave database replication:

mysql> START SLAVE;

Check to see whether the slave database is able to connect to the master database:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.107
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000016
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 1
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 555
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

If the database replication is set up correctly, then both Slave_IO_Running and Slave_SQL_Running will be set to Yes. If the Slave_SQL_Running is set to No, then check the value of the Last_SQL_Error for more details. To ignore the SQL error and restart the slave, issue the commands:

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

If the slave cannot connect to the master, then the SLave_IO_State will indicate Connecting:

Slave_IO_Running: Connecting
               ...
               Last_IO_Errno: 2003
               Last_IO_Error: error reconnecting to master 'root@192.168.0.107:3306' - retry-time: 60  retries: 86400

In this case, check the value of the Master_Host, Master_User, Master_Port, Master_Log_File and Read_Master_Log_Pos to verify that they are correct. If they need to be changed, then stop the database replication and issue the CHANGE MASTER command again. Then restart the slave replication:

Slave_IO_Running: Connecting
               ...
               Last_IO_Errno: 2003
               Last_IO_Error: error reconnecting to master 'root@192.168.0.107:3306' - retry-time: 60  retries: 86400

Try to connect to the master database from the command line of the slave server:

# mysql -u slave-user -h master.pmserver.com -p

If able to connect as the root user, but not as the slave-user, then the GRANT commands weren't issued correctly in the master database.

Also check whether MySQL on the master server is running and whether a firewall might be blocking its port. To check the port, use a command such as netstat to check whether the master server is listening on its MySQL port, which is 3306 by default and whether it has established a connection with slave server:

# netstat -an | grep 3306
tcp        0      0 192.168.0.107:3306       0.0.0.0:*               LISTEN    
tcp        0      0 192.168.0.107:3306       192.168.0.97:51941      ESTABLISHED

If the MySQL port is blocked by the firewall, unblock it. For example, if using IP Tables on a Linux/UNIX server, it can be unblocked with the following command:

# iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

Configuring the Double Connection in the ProcessMaker code

After setting up MySQL replication of a master and slave databases, then the code of ProcessMaker needs to be changed to read from the slave database.

Go to the directory where ProcessMaker is installed and edit the configuration file database.php with a plain text editor:

<INSTALLATION DIRECTORY>/<WORKSPACE>/engine/config/databases.php

Replace <WORKSPACE> with the name of the workspace, which by default is named workflow. For example:

/opt/processmaker/workflow/engine/config/databases.php

In the database.php file, add the following lines at the end of the script before the return $pro; statement:

  • If ProcessMaker 2.8 was installed from scratch, only one database will be created, the wf_<WORKSPACE> database:
if (defined('SYS_SYS') && SYS_SYS == 'WORKSPACE-NAME') {
    $pro ['datasources']['workflow_ro']['connection'] = 'mysql://MYSQL-USER:PASSWORD@SLAVE-SERVER-ADDRESS:PORT/SLAVE-DATABASE-NAME?encoding=utf8';
    $pro ['datasources']['workflow_ro']['adapter']    = 'mysql';
 }
 
 return $pro;
 
  • If ProcessMaker was upgraded from version 2.5 or earlier, the rbac database must also be replicated and added in the configuration:
if (defined('SYS_SYS') && SYS_SYS == 'WORKSPACE-NAME') {
    $pro ['datasources']['workflow_ro']['connection'] = 'mysql://MYSQL-USER:PASSWORD@SLAVE-SERVER-ADDRESS:PORT/SLAVE-DATABASE-NAME?encoding=utf8';
    $pro ['datasources']['workflow_ro']['adapter'] = 'mysql';
 
    $pro ['datasources']['rbac_ro']['connection'] = 'mysql://MYSQL-USER:PASSWORD@SLAVE-SERVER-ADDRESS:PORT/SLAVE-DATABASE-NAME?encoding=utf8';
    $pro ['datasources']['rbac_ro']['adapter'] = 'mysql';
 }
 
 return $pro;

The condition is added for each workspace that will use a second connection for read-only queries. Inside the condition 'WORKSPACE-NAME' must be replaced with the name of the workspace which by default is 'workflow'.

For the DSN string, replace the following parameters:

  • MYSQL-USER Replace this parameter with the MySQL user of the server where the slave database is installed. This user can be root, but for greater security it is recommended to create a new MySQL user on the slave server for this purpose.
  • PASSWORD Replace this parameter with the password of the user in the slave database. If no password is set for the MySQL user, set the string as:
mysql://MYSQL-USER@SLAVE-SERVER-ADDRESS:PORT/SLAVE-DATABASE-NAME?encoding=utf8  
  • SLAVE-SERVER-ADDRESS Specify the IP address or domain name of the server where the slave database is installed.
  • PORT Specify the number of the port where the slave database is installed. If using the default MySQL port 3306, it is not a required parameter and the string would look like.
mysql://MYSQL-USER:PASSWORD@SLAVE-SERVER-ADDRESS/SLAVE-DATABASE-NAME?encoding=utf8
  • SLAVE-DATABASE-NAME Name of the slave database in which ProcessMaker was replicated.

Testing the double connection

After setting up MySQL replication and configuring ProcessMaker to use the slave database, then test to see whether the replication works. Login to ProcessMaker and execute any action, such as opening the case list, which requires reading from the slave database. If there is a problem connecting to the slave database an error message will be displayed.

To check whether the database is replicating, add a new user in ProcessMaker. Then login to the slave database from the command line:

mysql -u root -p

After logging into MySQL, query the slave database for the new user that was just added:

mysql> select USR_USERNAME from wf_workflow.USERS;
+--------------+
| USR_USERNAME |
+--------------+
| admin        |
| bwagner      |
| rwilliams    |
| mgates       |
| abatto       |
+--------------+

If not using the default wf_workflow, change it to the name of your wf_<WORKSPACE> database. If database replication is working correctly, the username of the new user will appear in the list.

Example: How to configure the double connection to a slave database

This example includes the following:

  • ProcessMaker's main database will be installed in a local server
  • ProcessMaker's replicated (slave) database will be installed in server: 198.168.100.177

To configure a second database let's open the databases.php file located in Windows at:

C:\Users\User\AppData\Roaming\ProcessMaker-2_8\processmaker\workflow\engine\config\databases.php

As this was a clean installation of ProcessMaker 2.8, only the database wf_workflow was replicated, let's add the second connection:

Save it and the workspace "workflow" will work from now on with two database connections.

To verify that the second connection is working, let's access ProcessMaker and view all processes created in the DESIGNER:

This is already making the select query to the slave database. To prove it, let's access the slave database:replicatedprocessmaker

Let's change the name of the process in the table content, let's replace MASTER PROCESS 2 for SLAVE MASTER 2:

Let's log into ProcessMaker again and refresh the page:

As the list of processes is being shown from the slave database the name changes, nevertheless all creations and updates are still querying where ProcessMaker is installed.

To have better results keep the replicated databases always synchronized with the master database.