DHIS 2 MySQL database instructions

• Download
• Install
• Setup/Configuration
• How to run the MySQL server
• Optional setup to make life easier
• Prepare MySQL for DHIS 2
• Additional tools
• Documentation

Quick guide:
MySql Download latest version. MySQL documentation.

Download

Download MySQL from http://dev.mysql.com/downloads

Note Users of Windows Vista should download version 5.1.24 or higher due to bug #30823.

Old relases can be found in the archive:
http://downloads.mysql.com/archives.php

Install

In Windows:

• Unzip the zip file and run the setup.exe.
• Follow installation instructions for your operataing system here:
  http://dev.mysql.com/doc/refman/5.0/en/installing.html (if not version 5.0 look for the right reference manual (http://dev.mysql.com/doc/mysql/en/index.html) )
  (For version 4.0/4.1: http://dev.mysql.com/doc/refman/4.1/en/installing.html)

Setup/Configuration

The default installation serves our needs as the default MySQL storage engine now (as of version 4.0) supports constraints.
This means that now further configuration is needed, however you can adjust the way MySQL uses system resources by editing the configuration file:

Windows: the my.ini file and this file must be in the Windows directory (in win2000/XP : C:\WINDOWS)

Linux: the my.cnf file
See instructions here:
5.0 http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
(For 4.0/4.1: http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html

How to run the MySQL server

Windows:

To start the MySQL server correctly we advise you to run MySQL server form the command prompt the first time:

• Startmenu->Run->Open: cmd
• Navigate to the mySQL/bin folder
• Start the mySQL server with the following
  command:
  > mysqld-nt --console
  (nt if win2000 or XP ,if not use mysqld instead of mysqd-nt)

Command-line
You will se that MySQL creates the InnoDB files that are specified in the my.ini/my.cnf file and that it is started.
You can shut down the MySQL server by opening a new command prompt window, cd to the mysql\bin folder and run the command:
> mysqladmin shutdown
This procedure can be used at any time to start and stop the MySQl server.

System service (automatically started)
To install the mySQL server as a system service (automatically started when Windows starts) you can do the following:
Open a command prompt and navigate to the mysql\bin folder where you run:
>mysqld-nt -install

• The MySQL server will start the next time you start windows, if you want to start or stop the service manually use the commands (in command prompt):
  >NET START MYSQL
  >NET STOP MYSQL

Linux:

Optional setup to make life easier

Update the windows path to ease your work in command prompt:
To use the mySQL commands form any folder in command prompt and thereby avoiding all the 'cd' commands you can update windows environment variables and add the mysql\bin folder to the windows path.
Do the following:

• Open system properties (right click on 'My Computer' and select properties or just press the Windows button and Pause at the same time)
• Go to Advanced
• Go to Environment Variables
• Under System Variables click on the variable called Path
• When Path is marked click Edit
• Go to the end of the line, add a semi-colon ( ; ) if not already there, and then type in the full path to the mysql\bin directory.
  (e.g. C:\mysql\bin)
  -Click on OK, OK and OK and when you reopen a command prompt you can access all the files in mysql\bin from anywhere.

Prepare MySQL for DHIS 2

Step 1
Make innodb the default table type (storage engine), and utf8 the default charset (character set). This means that you should have the following two lines in your mysql configuration file (my.ini on Windows):

default-storage-engine=INNODB
default-character-set=utf8

In the mysql client type "SHOW VARIABLES" to verify your default values for table_type, storage_engine and the variuos variables for charcter_set and collation.

Step 2
Create an empty database (e.g. "dhis2") with utf8 as character set and a user (e.g. "dhis") with permissions to create tables on that database.

Log into mysql (from the command line) and type:

mysql> create database dhis2 default character set utf8 collate utf8_general_ci;
mysql> grant all on dhis2.* to dhis@"localhost" identified by '';

The most important thing here is to make sure the database name and login information match what you have in the hibernate.properties file.

Optional step 3
This step is only necessary if you want to insert data into the database before starting DHIS 2.0 the first time. Otherwise Hibernate will create all the necessary tables etc.

Exit mysql and download the DHIS 2.0 database script file. Enter the download directory on the command line and:

> mysql -u root -p dhis2 <dhis2.sql

If you want to have monthly periods for 2006, you can download this dhis-2.0-M4 periods 2006 script

> mysql -u root -p dhis2 <dhis2-periods.sql

Hibernate.properties

Create a file called "hibernate.properties" in USER_HOME/dhis/.
Content of "hibernate.properties":

hibernate.dialect = org.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.url = jdbc:mysql://localhost/dhis2?useUnicode=true&characterEncoding=UTF-8
hibernate.connection.username = dhis
hibernate.connection.password = 
hibernate.hbm2ddl.auto = update

Additional tools

MySQL Administrator

Good tool to administer mySQL databases, take backups etc.
Download and info here:
http://www.mysql.com/products/administrator/

Documentation

http://dev.mysql.com/doc/

Online and searchable manual:
http://dev.mysql.com/doc/mysql/en/index.html

Downloadable pdf format manual:
http://dev.mysql.com/get/Downloads/Manual/manual-a4.pdf/from/pick