Skip to content
This repository has been archived by the owner on Feb 19, 2019. It is now read-only.

MySQL Database Setup

Jump to bottom
David Podhola edited this page Feb 7, 2018 · 30 revisions

Database setup

Assuming you already have a version of MySql (or MariaDB) installed. To get started quickly you can just setup the latest demo database.

If you are using Mac-OS, and want to install MySQL, follow the instructions mentioned here: https://github.com/openMF/mifosx/wiki/Installing-MySQL-on-Mac-OS. Then come back here, and follow the remaining configuration steps mentioned below.

Please first check if your MySql (or MariaDB) 'root' user has the default 'mysql' password (this is the default on a fresh installation; on Linux at least):

$ mysql -u root -pmysql
mysql>

Note: For quick development, unless it's already the case, it is recommended that you change your MySQL 'root' user password to 'mysql'. For doing that you need to fire the following command:

$ mysqladmin -u root -p'oldpassword' password newpassword

OR (If you don't have any password already for Root login as Root then follow the instructions)

mysql> update mysql.user SET password=PASSWORD('newpassword') WHERE user ='root';
mysql> flush privileges;

Mifos X has support for hosting multiple tenants so we use two database schemas:

  1. mifosplatform-tenants: which is responsible for persisting the tenant information which is used when deciding what schema each incoming request in the platform should route to. It acts as a registry which contains the details of all the tenant databases, and their connection information, which is used by MifosX to connect to the appropriate tenant.
  2. mifostenant-default: All tenant specific schemas follow the pattern mifostenant-xxxx, out of the box the default schema is used to represent a demo

Create schema/database named mifosplatform-tenants 

$ mysql -u root -pmysql
mysql> create database `mifosplatform-tenants`;

Then run the following gradle command to create tables and fill it with data for connecting to the tenant db. All gradle commands should be executed from mifosng-provider directory.

$ ./gradlew migrateTenantListDB -PdbName=mifosplatform-tenants

Create schema/database named mifostenant-default 

mysql> create database `mifostenant-default`;

Optional

You don't need to run any SQL scripts for this database. When you start MifosX tomcat server, it will auto upgrade your DB. Still, in case you would like to populate the DB from command line, you can execute the following gradle command from inside the mifosng-provider directory:

$ ./gradlew migrateTenantDB -PdbName=mifostenant-default

If you would like to see the state of your DB migrations, you can run the following command (which internally uses Flyway):

$ ./gradlew showTenantDBInfo -PdbName=mifostenant-default

Important: If Using Different MySQL User/Password than what is configured

Note: By default, the files you clone from repository for setting up mysql and launching the application from the commad line assume a database user/password of root/mysql. Its likely that you have a different database user/password on your development machine so you should do the following:

(a) If you have not run the command $ ./gradlew migrateTenantListDB -PdbName=mifosplatform-tenants yet, open the mifosng-db/mifospltaform-tenants-first-time-install.sql file ,find the following line.

INSERT INTO 'tenants' VALUES (1,'default','default','mifostenant-default','Asia/Kolkata',NULL,NULL,NULL,NULL,'localhost','3306','root','mysql',1,5,30000,1,60,1,50,1,40,20,10,60,34000,60000);

And then replace the entries of root and mysql with the appropriate values for username and password.

else if you ran the command $ ./gradlew migrateTenantListDB -PdbName=mifosplatform-tenants and it failed, update the schema settings associated with each entry in the mifosplatform-tenants database for example:

Example of updating tenant with id 1 to use mifos/somepassword as username/password for database connections.

UPDATE mifosplatform-tenants.tenants
SET
schema_server = 'localhost',
schema_server_port = '3306',
schema_username = 'mifos',
schema_password = 'somepassword'
WHERE id=1;

(b) Also, to connect to the mifosplatform-tenants database, mifosX refers to this file: mifosx/mifosng-provider/src/test/resources/META-INF/context.xml. You might need to change the credentials here too.

see Launching platform server locally from the command line for actions required for launching platform locally from the command line.

(c) The properties project.ext.mysqlUser and project.ext.mysqlPassword in mifosx/mifosng-provider/build.gradle should be updated to reflect your credentials.

Tips & Tricks

While normally this isn't needed, it may be useful in certain situations to completely delete the DB content and restart from scratch; this can be done using: gradle flywayClean. This and other useful Gradle commands can be found using: gradle tasks. (Thanks to Andreas Weigel for the tip.)

Clone this wiki locally