Database Setup Guide-MySQL

Database Setup Guide-MySQL KonyOne Platform Release 5.0 Database Setup Guide - MySQL Copyright © 2012 by Kony Solutions, Inc. All rights reserved....
13 downloads 8 Views 220KB Size
Database Setup Guide-MySQL

KonyOne Platform Release 5.0

Database Setup Guide - MySQL

Copyright © 2012 by Kony Solutions, Inc. All rights reserved. April, 2013 This document contains information proprietary to Kony Solutions, Inc., is bound by the Kony license agreements and may not be used except in the context of understanding the use and methods of Kony Solutions software without prior, express, written permission. Kony, KonyOne and Empowering Everywhere are trademarks of Kony Solutions, Inc. Microsoft, the Microsoft logo, Internet Explorer, Windows and Windows Vista are registered trademarks of Microsoft Corporation. Apple, the Apple logo, iTunes, iPhone, iPad, OS X, Objective-C, Safari and Xcode are registered trademarks of Apple, Inc.. Google, the Google logo, Android and the Android logo are registered trademarks of Google, Inc. Chrome is a trademark of Google, Inc. Blackberry, Research in Motion, and RIM are registered trademarks of Research In Motion Limited. All other terms, trademarks or service marks mentioned in this document have been capitalized and are to be considered the property of their respective owners.

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 2 of 16

Database Setup Guide - MySQL

Revision History Description of Modifications/Release

Date

Document Version

02/14/2012

1.0

Document Release

06/07/2012

2.0

Made minor changes to the Database setup procedure.

08/31/2012

3.0

Document Release for KonyOne 5.0.

12/11/2012

4.0

Updated the document with spooling commands for all the scripts. Included placeholders for KONYUSERDB.

12/20/2012

5.0

Updated the document with new script files and execution commands. These changes are made to make the scripts compatible with KonyOne Server installer.

03/22/2013

6.0

Removed patch section for 5.0.3.

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 3 of 16

Database Setup Guide - MySQL

Table of Contents 1. Overview

5

1.1 Intended Audience

5

1.2 Typographical Conventions

6

1.3 Reference Documents

6

1.4 Contact Us

7

1.5 Prerequisites

8

2. Database Sizing and Configuration

9

2.1 Kony Database sizing details

9

2.2 Scripts for Kony database setup:

10

2.3 Approach for Kony Database Setup

10

3. Database Setup

11

3.1 Setup Database

11

3.2 Database Setup for Admin Module

13

3.2.1 Granting Permissions

14

3.3 Database Setup for Session Based License

15

4. Database Setup Complete

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

16

Page 4 of 16

Database Setup Guide - MySQL

1. Overview KonyOne Platform provides an integrated approach in the mobile application design, development and management. The Platform facilitates the design and development of mobile applications in a device independent manner and delivers them in different formats that run on all the major device platforms. The KonyOne Platform consists of three main components: l l

l

KonyOne Studio: Used to design and develop the mobile applications. KonyOne Server: Provides server side functionality for the applications, common data integration and device support services Client Runtime component for each major device platform that enables the same mobile application to execute directly on the device.

KonyOne Server

The application functionality developed and generated by the Kony Studio is enabled and delivered using the Application Server. The SMS and Mobile Web channels are hosted on the Application Server and the Native App binaries are deployed to the server, to be downloaded by the device. The Application Server has the following features: l

l

l

l

A sophisticated device database that enables it to detect and deliver the appropriate binary to a requesting device. Inbuilt backend data services integration capability, with out-of-the-box support for Web Services, XML feeds and HTML extraction. Integration with optional third party connector libraries that offer access to a large number of ERP, database and legacy systems. Built in usage tracking and analysis capability that provides a wide range of reports on system and application use.

Kony Database Setup Guide

This document provides the database setup instructions for Kony Database.

1.1 Intended Audience This document is intended for engineers or system administrators who are responsible for installing and deploying KonyOne Server and its related databases. We assume that the reader of this document is familiar with deploying software on Java application servers/Tomcat application server. The reader of this document

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 5 of 16

Database Setup Guide - MySQL

must understand how to install the Database software on the Sun Solaris/Unix platform.

1.2 Typographical Conventions Following are the typographical conventions used throughout the document: Convention

Monospace

Italic

Explanation n

User input text, system prompts and responses

n

File Path

n

Commands

n

Program Code

n

File Names

n

Emphasis

n

Names of Books and Documents

n

New Terminology

n

Windows

n

Menus

n

Buttons

n

Icons

n

Fields

n

Tabs

Bold

URL

Active link to a URL

Note:

Provides helpful hints or additional information

Important!

Highlights actions or information that might cause problems to systems or data

1.3 Reference Documents If you need more information about the Kony database setup for MySQL, please refer to the below documents: 1. Kony Database Data Dictionary-MySQL 2. Kony Database Logical Design 3. Kony Reporting Data Mapping Diagram 4. Kony Reports Portal Database Setup-MySQL 5. Kony Database FAQs-MySQL These documents are available at: http://developer.kony.com/KonyDocuments.

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 6 of 16

Database Setup Guide - MySQL

1.4 Contact Us We welcome your feedback on our documentation. Write to us at [email protected]. For technical questions, suggestions, comments or to report problems on Kony's product line, contact [email protected].

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 7 of 16

Database Setup Guide - MySQL

1.5 Prerequisites Prerequisites for installing the Kony Database for MySQL: 1. Only a qualified MySQL Database Administrator (DBA) must handle the MySQL Database setup in order to successfully complete the Kony database deployment. 2. MySQL software must be installed and basic database must have been already created, to continue with Kony database schema setup. 3. From Kony Platform 3.1 onwards, Device DB is used only for Mobile Web. It is not required if you are planning to develop the Native Applications only.

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 8 of 16

Database Setup Guide - MySQL

2. Database Sizing and Configuration The following is the sample sizing information. You could also use it for standard setups. Storage sizing for Kony Database Storage Elements

Space Required

Information, MySQL Schema

500 MB to 1000 MB

konydb Database

Upto 500 GB (for medium business systems), 2000 GB (for large business systems), Data files each of size 2 GB, please use n*2 GB (where n is number of data files).

Temp Area

2 GB to 8 GB

Space required for MySQL Database software installation

Upto 1 GB

Memory sizing for Kony Database RAM (MB)

No. of concurrent connections (numbers)

2048

100

4096

200

8192

400

2.1 Kony Database sizing details Database Growth Each unique user request from a device logs approximately 2 KB of data into the database. For example, if the application has 60,000 user requests from device per week, then the database grows, approximately, at the following rate: 60,000 user requests from device per week = 2 KB * 60000 = 117 MB 60,000 * 4 weeks = 468 MB (approximate) The above data does not include session error logging. For each device error/exception 2 KB data is generated.

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 9 of 16

Database Setup Guide - MySQL

2 GB of database space is used for initial database setup. From then on, the database grows as per the data volume that gets populated into it. To accommodate the data volume, it is ideal to set the storage allocation at 500 GB.

2.2 Scripts for Kony database setup: The Database (MySQL) scripts are available at the Kony Developer Portal: http://developer.kony.com/KonyDBScripts Navigate to the relevant database section and click on Standard Database Scripts to download the scripts - Kony_Database_Setup_MySQL_.zip. The Device Data scripts are available at: http://developer.kony.com/DeviceDB Navigate to the MySQL section and download devicestore_build_mysql.zip. Approximate time for Kony database setup: 2 hrs (excluding MySQL software installation).

2.3 Approach for Kony Database Setup You need to follow the "bottom>up" approach to perform the Kony database setup. It would be performed in the following order: 1. Operating System 2. Storage 3. Clusterware (if any) 4. Database software and Database 5. Finally, Web-App servers and JDBC configuration

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 10 of 16

Database Setup Guide - MySQL

3. Database Setup You can perform the database setup by executing the script files provided by Kony. The Kony_Database_Setup_MySQL_.zip file contains the following script files: n

01_KONYDEVICEDB.SQL

n

02_KONYDEVICEDB_DATA.SQL

n

03_KONYLOGDB.SQL

n

04_KONYUSERDB.SQL

n

04a_KONYUSERDB_CONFIG_DATA.SQL

n

KONYLICENSEDB.SQL

We assume that the Kony_Database_Setup_MySQL_.zip file is copied to and extracted at some location on the system. Here after, the location where the files are copied/extracted is referred to as . After the execution of the scripts, execution logs are created at this location. You can use the log files to verify the smooth execution of the script files.

3.1 Setup Database Perform the following tasks to setup the database: 1. Login to the host machine/server which has access to MySQL instance and navigate to the location of the script files. $ cd Important! In the following steps, as you execute the scripts, you need to enter the password for the root user. 2. Execute 01_KONYDEVICEDB.SQL script file. This script creates device database objects in KONYDEVICEDB schema. It also creates an execution log to verify the successful execution of the script. $ mysql -u root -p -v < 01_KONYDEVICEDB.SQL > 01_konydevicedb_ spool.log 3. The script 02_KONYDEVICEDB_DATA.SQL is provided as a placeholder. Navigate to the MySQL section on http://developer.kony.com/DeviceDB and download devicestore_build_mysql.zip.This zip file has the latest device data script devicestore_build_mysql.sql. Run this script to

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 11 of 16

Database Setup Guide - MySQL

populate data into the device database tables. $ mysql -u root -p -v < devicestore_build_mysql.sql > devicestore_build_ mysql_spool.log

4. Execute 03_KONYLOGDB.SQL script file. This script creates log database objects in KONYLOGDB schema. It also creates an execution log to verify the successful execution of the script. $ mysql -u root -p -v < 03_KONYLOGDB.SQL > 03_konylogdb_ spool.log

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 12 of 16

Database Setup Guide - MySQL

3.2 Database Setup for Admin Module Important! Use the following step if you are planning to configure the Admin module for KonyOne Server with Tomcat. Admin module of KonyOne Server is to be used only in development or testing environment and not in production. Note: This is not applicable for KonyOne Server deployment on WebSphere, WebLogic and JBoss application servers. For Admin Module setup on these servers, refer to the setup instructions in the respective KonyOne Server installation guides. The script 04a_KONYUSERDB_CONFIG_DATA.SQL contains the placeholders for admin, manager, publishuser and appuser users. These placeholders need to be replaced with the encrypted password of the respective user, generated using the MD5 mechanism of digest.sh in Tomcat application server. For more information, refer Install and Configure Admin Module section of KonyOne Server Installation Guide - Tomcat. 1. After updating the script with the encrypted passwords, execute 04_ KONYUSERDB.SQL and 04a_KONYUSERDB_CONFIG_DATA.SQL script files. These scripts create objects in KONYUSERDB schema for admin module. It also creates an execution log to verify the successful execution of the script. $ mysql -u root -p -v < 04_KONYUSERDB.SQL > 04_konyuserdb_ spool.log $ mysql -u root -p -v < 04a_KONYUSERDB_CONFIG_DATA.sql > 04a_ konyuserdb_config_data_spool.log 2. Perform database verification. mysql -u root -p Enter Password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5003 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases;

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 13 of 16

Database Setup Guide - MySQL

+--------------------+ | Database | +--------------------+ | information_schema | | KONYDEVICEDB | | KONYLOGDB | | KONYUSERDB | | mysql | +--------------------+

3.2.1 Granting Permissions 1. Create dbclient user by logging in as MySQL root user and grant all the privileges on all the databases. CREATE USER 'dbclient'@'localhost' IDENTIFIED BY ''; CREATE USER 'dbclient'@'web server' IDENTIFIED BY ''; mysql> grant all on *.* to 'dbclient'@'localhost' identified by ''; mysql> grant all on *.* to 'dbclient'@'WEBSERVER_IP' identified by ''; quit; 2. After granting all the permissions, login with dbclient user and check the permissions. mysql -u dbclient -p Enter Password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5003 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 14 of 16

Database Setup Guide - MySQL

| information_schema | | KONYDEVICEDB | | KONYLOGDB | | KONYUSERDB | | mysql | +--------------------+ mysql> show grants; +------------------------------------------------------------+ | Grants for dbclient@localhost | +------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'dbclient'@'localhost' IDENTIFIED BY PASSWORD '*C88CAA70F4710A02618C1654B38DA4861DE5D641' | +------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> quit

3.3 Database Setup for Session Based License Important! Ignore this step if you are not using Session Based License. Use the following steps if you have session based license for KonyOne Server. Execute the KONYLICENSEDB.SQL script file. This script creates license database objects in KONYLICENSEDB schema. It also creates an execution log to verify the successful execution of the script. $ mysql -u root -p -v < KONYLICENSEDB.SQL > konylicensedb_spool.log

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 15 of 16

Database Setup Guide - MySQL

4. Database Setup Complete Important! If you are planning for the Kony Reports Portal, setup the Reports Database by following the Kony Reports Portal Database Setup Guide. Once you are done with all the Database setup procedures, you can now proceed with the installation of KonyOne Server. Please follow one of the following documents to install KonyOne Server. Use the document relevant to the Java Application Server at your end. 1. KonyOne Server Installation Guide - Tomcat 2. KonyOne Server Installation Guide -JBoss 3. KonyOne Server Installation Guide - WebSphere

Copyright ©2012 Kony Solutions, Inc. All Rights Reserved.

Page 16 of 16