Skip to Content

MySQL database can be used as a database for SAP Business Objects data services. This is a light weight database and can be used to store user authentication or data services and also as business objects IPS with tomcat services to host CMS. The repository can reside on HANA. Here we describe steps to install a MySQL database in a non-default location on Linux platform.

Mysql database installation is a simple procedure if it is installed in the default location. When mysql is to be installed in a non-default  location, certain aspects of the installation change from the standard. This document describes the same. The below installation is for a SAP business objects Data services database. The mysql version being installed is 5.1.72.

Install it under a user created as owner for mysql database. Here we use <sid>dba as the user, suppose the SID is prd, the user is created as prddba to identify that it is the owner of the database.

Download location

MySQL 5.1.72-1 64-bit ->http://dev.mysql.com/downloads/mysql/5.1.html#downloads

MySQL-community-5.1.72-1.sles11.x86_64.rpm-bundle.tar

JDBC client ->Mysql-connector-java5.1.18-bin

http://dev.mysql.com/downloads/connector/j/

Login as root and execute the following

Create a folder for mysql home

mkdir /mysql/<SID>/db/5.1.72

cd /mysql/<SID>/db/5.1.72

Unpack the downloaded mysql database package and use the –relocate option to unpack it in the non-default location i.e. mysql home (/mysql/<SID>/db/5.1.72).

rpm -ivh –relocate /=/mysql/<SID>/db/5.1.72 –nodeps –noscripts MySQL-server-community-5.1.72-1.sles11.x86_64.rpm

From the unpacked package create the configuration file .cnf, here name it as <SID>.cnf and use parameters from small, medium or large settings from the template configuration file. Eg. prd.cnf

Contents of prd.cnf. Please note that some of the parameters in this file are introduced for data services application.

============================================================

[client]

user=prddba

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[mysqld_server]

user=prddba

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[mysqld_safe]

ledir = /mysql/PRD/db/5.1.72/usr/sbin

user=prddba

datadir=/mysql/PRD/sapdata1/data

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[safe_mysqld]

err-log=/mysql/PRD/db/admin/prdbin.log

[mysqld]

user=prddba

datadir=/mysql/PRD/sapdata1/data

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

#skip-networking

expire_logs_days = 1

sync_binlog      = 1

max_binlog_size  = 500M

[mysqladmin]

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[mysqld_server]

user=prddba

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[mysqld_safe]

ledir = /mysql/PRD/db/5.1.72/usr/sbin

user=prddba

datadir=/mysql/PRD/sapdata1/data

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[safe_mysqld]

err-log=/mysql/PRD/db/admin/prdbin.log

[mysqld]

user=prddba

datadir=/mysql/PRD/sapdata1/data

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

#skip-networking

expire_logs_days = 1

sync_binlog      = 1

max_binlog_size  = 500M

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

[safe_mysqld]

err-log=/mysql/PRD/db/admin/prdbin.log

[mysqld]

user=prddba

datadir=/mysql/PRD/sapdata1/data

basedir=/mysql/PRD/db/5.1.72/usr

log-error=/mysql/PRD/db/admin/prd.err

pid-file=/mysql/PRD/db/admin/prd.pid

log-bin=/mysql/PRD/binlogs/prd-bin

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

#innodb_log_group_home_dir=/mysql/PRD/saplog/logs

language=/mysql/PRD/db/5.1.72/usr/share/mysql/english

socket=/mysql/PRD/db/admin/prd.sock

port=3377

#skip-networking

expire_logs_days = 1

sync_binlog      = 1

max_binlog_size  = 500M

[mysqladmin]

user = root

password = <give password here>

[mysqlbackup]

user=root

datadir=/mysql/PRD/sapdata1/data

innodb_data_home_dir=/mysql/PRD/sapdata1/data/

innodb_data_file_path=ibdata01:1000M;ibdata02:1000M:autoextend:max:6000M

innodb_log_group_home_dir=/mysql/PRD/saplog/logs

socket=/mysql/PRD/db/admin/prd.sock

port=3377

execute install_db command

cd /mysql/PRD/db/5.1.72/usr/

./bin/mysql_install_db –defaults-file=/mysql/PRD/db/admin/prd.cnf \

–basedir=/mysql/PRD/db/5.1.72/usr \

–datadir=/mysql/PRD/sapdata1/data

change permissions for all folders to prddba from root

Start the database to see if it starts properly

cd /mysql/PRD/db/5.1.72/usr

./usr/sbin/mysqld –defaults-file=/mysql/PRD/db/admin/prd.cnf \

   –user=prddba \

–basedir=/mysql/PRD/db/5.1.72/ \

–datadir=/mysql/PRD/sapdata1/data \

   –port=3377 –socket=/mysql/PRD/db/admin/prd.sock \

–language=/mysql/PRD/db/5.1.72/usr/share/mysql/English

The process is running in foreground, after creating the database this process can be killed and mysql started in background mode.

install mysql client

Create a folder /mysql/PRD/db/client

install the client rpm package, this step can be done either now or in the beginning immediately after installing the server rpm package.

rpm -ivh –relocate /=/mysql/PRD/db/client –nodeps –noscripts MySQL-client-community-5.1.72-1.sles11.x86_64.rpm

start mysql using mysqld_safe

edit prd.cnf and add all the parameters to mysqld_safe section.

Edite mysqld_safe script and replace in beginning for user=’mysql’ with user=’prddba’

cd /mysql/PRD/db/5.1.72/usr

./bin/mysqld_safe –defaults-file=/mysql/PRD/db/admin/prd.cnf&

Secure the database and remove test and miscellaneous users etc by running mysql_secure_installation

Add client to the env path variable

setenv PATH /usr/local/bin:/bin:/usr/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin:/mysql/PRD/db/client/usr/bin

cd /mysql/PRD/db/5.1.72/usr/bin

./mysql_secure_installation –defaults-file=/mysql/PRD/db/admin/PRD.cnf

This will try to connect through the default socket location. This is a bug. Since we have the socket in non-default location, we need to tweak the script mysql_secure_installation to reflect this as per the link below.

http://forums.mysql.com/read.php?11,572000,572048

Bug info:

http://bugs.mysql.com/bug.php?id=46842

set the environment varaibles for mysql in the environment profiles in prddba home.

setenv MYSQL_HOME /mysql/PRD/db/admin

setenv MYSQL_UNIX_PORT /mysql/PRD/db/admin/prd.sock

setenv PATH /usr/local/bin:/bin:/usr/bin:/usr/bin/X11:/usr/X11R6/bin:/mysql/PRD/db/client/usr/bin:/mysql/PRD/db/5.1.72/usr/bin:/mysql/PRD//db/5.1.72/usr/sbin

setenv LD_RUN_PATH /mysql/PRD/db/client/usr:/mysql/PRD/db/5.1.72/usr

setenv MYSQL_BASE /mysql/PRD/db/5.1.72/usr

Backups in mysql

Mysqlbackup is the best method recommended to backup mysql database. Mysqldump does not  recover upto point in time, but mysqlbackup can be used to recover a database upto a point-in-time by applying the logs.

Copy mysqlbackup executable from the mysql enterprise backup package to the binary location of your mysql database. /mysql/PRD/db/5.1.72/usr/bin

A backup script can be configured in crontab to take regular backups using mysqlbackup as below. Sample script. This includes Dataservices configuration file backups also.

#!/bin/sh

# The path the backups will be dumped to

DUMP_DIR=”/mysql/PRD/backup/”

# SQL user who can access the db

SQL_USER=”root”

# SQL password for above user

SQL_PASS=<root password>

# Back up folder name (mmddyyyy)

BACKUP_DIR=”`date +%Y%m%d%H%M%S`”

mkdir $DUMP_DIR$BACKUP_DIR

# Dump the database to /tmp/…

/mysql/PRD/db/5.1.72/usr/bin/mysqlbackup  –port=3377 –protocol=tcp –user=$SQL_USER –password=$SQL_PASS –backup-dir=$DUMP_DIR$BACKUP_DIR backup-and-apply-log >/dev/

null

#mysqldump –events –all-databases > $DUMP_DIR$BACKUP_DIR/prd_alldatabase.sql

#Create Directory

mkdir $DUMP_DIR$BACKUP_DIR/config

# Initialise Paths

IFRS=”/usr/sap/PRD/ips/sap_bobj/data/frsinput”

OFRS=”/usr/sap/PRD/ips/sap_bobj/data/frsoutput”

# Config files Backup

# Start Backup

# 1. Backup ODBC.ini

cp /mysql/PRD/db/5.1.72/usr/lib/odbc.ini $DUMP_DIR$BACKUP_DIR/config

# 2. Backup Tnsnames.ora

cp /oracle/client/11x_64/instantclient_11204/tnsnames.ora $DUMP_DIR$BACKUP_DIR/config

# 3. Backup IFRS

cp -r $IFRS $DUMP_DIR$BACKUP_DIR/config

# 4. Backup OFRS

cp -r $OFRS $DUMP_DIR$BACKUP_DIR/config

# 5. Backup Conf Folder

cp -r /usr/sap/PRD/ips/dataservices/conf $DUMP_DIR$BACKUP_DIR/config

# 6. Backup Adapter Folder

cp -r /usr/sap/PRD/ips/dataservices/adapters $DUMP_DIR$BACKUP_DIR/config

# 7. Backup al_env.sh

cp /usr/sap/PRD/ips/dataservices/bin/al_env.sh $DUMP_DIR$BACKUP_DIR/config

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply