Mysql Installation Guide

Mysql Installation Guide

Last updated: December 3, 2014

MySQL Installation guide

This guide will help you to install and configure MySQL for IQCare 3.6.0. IQCare 3.6.0 uses MySQL database for audit trail data storage. Please follow the following steps to install and configure MySQL on your server / machine.

IQCare uses MySQL 5.6.14 version.

-Click on mysql-installer-community-5.6.14.0.msi

-Select “Install MySQL Products”

-Select “Skip the check for updates” for speedy installation, you install the updates later.

-On second screen, select “Full” setup option, press Next

-

-Click Next to proceed

-

-Click Execute to run the setup.

-

-It will start the installation process

-Click Next after installation, to configure MySQL

-Click Next, to configure

-Click Next to enter other information

-Click Next to select more

-Click Next to finish installation

Enter the password “c0nstella”

IQCare Database creation in MySQL

-Open the MySQL query editor by clicking the Local instance MySQL box and then enter the password you entered in the set up.

Copy below SQL statement to create IQCare database and a stored procedure

CREATE DATABASE IQCare;

Use IQCare;

CREATE TABLE tbl_iqcareaudit ( VisitIdint(11) DEFAULT NULL, LocationIdint(11) DEFAULT NULL, VisitDatedatetime DEFAULT NULL, VisitTypesmallint(6) DEFAULT NULL,

OperationTypevarchar(30) DEFAULT NULL, TableTypevarchar(5) DEFAULT NULL,

TableNamevarchar(150) DEFAULT NULL, UserIdvarchar(10) DEFAULT NULL,

FieldNamevarchar(150) DEFAULT NULL, OldValuevarchar(1000) DEFAULT NULL,

NewValuevarchar(1000) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Use IQCare;

ALTER TABLE iqcare.tbl_iqcareaudit ADD ptn_pkint first;

ALTER TABLE iqcare.tbl_iqcareaudit ADD createdatedatetime;

Use IQCare;

Drop procedure GetAuditTrails;

DELIMITER $$

CREATE PROCEDUREGetAuditTrails()

BEGIN

Select ptn_pk, VisitId, LocationId, visitdate, VisitType, OperationType, TableName, UserId, FieldName, OldValue, NewValue, createdate from tbl_iqcareaudit;

END

-You can find IQCare database, table and stored procedure in your MySQL.

MySQL ODBC configuration steps

-On your computer open the Control Panel, Click on Administrative Tools.

-double click on ODBC Data Sources to configure for 64 bit machine. It will have same steps for 32 bit machine as well.Please Configure for System DSN as well same way as User DSN.

-Enter data source name“MySQL56”(instance of MySQL), credentials to connect and database – in this case it’s IQCare.

Steps to Create Scheduler

Click Control Panel and Select Administrative Tool

Double click on Task Scheduler

Click Create task… under Actions

Enter the task name

Click on Trigger Tab and click New…


Specify Time like by selecting Radio Delete and repeat task every 1 hour

Click on Actions Tab and select New. Select action – Start a Program

Select Backup.bat from folder in C:\Backup – or the location you had restored it. Press OK to finish.

Create a linked server in SQL Server

-Please run the following SQL in SQL Server to create a Linked Server with MySQL.

Please get the latest version of the script.

USE [master]

GO

/****** Object: LinkedServer [MySQL] Script Date: 9/10/2014 4:03:19 PM ******/

EXEC master.dbo.sp_addlinkedserver @server = N'MySQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'MySQL', @provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=localhost; PORT=3306; USER=root; PASSWORD=c0nstella; OPTION=3;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MySQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MySQL', @optname=N'remoteproc transaction promotion', @optvalue=N'true'

GO