How to create a MySQL database for use with Mobility Suite.

book

Article ID: 178481

calendar_today

Updated On:

Products

Mobility Suite

Issue/Introduction

 

Resolution

The following steps show how to install MySQL 5.6 on a CentOS/RHEL 6 server, create the databases needed by Symantec Mobility Suite, and give a MySQL administrative account with rights to the two databases. There is a script located in the 'Attachments' section of this article that can be used to perform these action automatically.The commands listed in a bold font below are intended to be run in the Linux Terminal with root access:

1. Type wget -P /tmp/ http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm and press <enter>

2. Type cd /tmp/ and press <enter>

3. Type yum -y localinstall mysql-community-release-el6-5.noarch.rpm and press <enter>

4. Type yum repolist enabled | grep "mysql.*-community.*" and press <enter>

5. Type yum repolist all | grep mysql and press <enter>

6. Type yum-config-manager --enable mysql56-community and press <enter>

7. Type yum-config-manager --disable mysql57-community-dmr and press <enter>

8. Type yum repolist enabled | grep mysql and press <enter>

This downloads the yum repository listing for mysql 5.6 on CentOS/RHEL 6 and verifies it has been configured for installation. Ensure that the output of the last command entered only displays a result for mysql56-community.


9. Type yum -y install mysql-community-server and press <enter>

This installs MySQL 5.6.


10. Type service mysqld start and press <enter>

11. Type service mysqld status and press <enter>

This starts MySQL and verifies that it is running.


12. Type mysql –u root –p and press <enter>

13. Type the root account's password for the password prompt and press <enter>

(The default root password for MySQL is blank)

14. Type create database appstore character set utf8 collate utf8_bin​; and press <enter>

15. Type create database mdmcore character set utf8 collate utf8_bin​; and press <enter>

This will do as it says; create two databases named 'appstore' and 'mdmcore'.


16. Type show databases; and press <enter>.

This will display the available databases. Ensure 'appstore' and 'mdmcore' are listed.


17. Type GRANT ALL PRIVILEGES ON appstore.* TO 'symantec'@'%' IDENTIFIED BY '[email protected]'; and press <enter>

18. Type GRANT ALL PRIVILEGES ON mdmcore.* TO 'symantec'@'%' IDENTIFIED BY '[email protected]'; and press <enter>

(Please note that the '%' character is a wildcard symbol in mysql that allows the 'symantec' user to access the target databases from any IP address. It is recommended that this is hardened to the specific IP address of the Mobility Suite server once it is determined).

This will allow the example 'symantec' account on 'any IP address' to have all rights to the 'appstore' and 'mdmcore' databases with the password of '[email protected]'.


19. Type SHOW GRANTS FOR 'symantec'@'%'

This will list the new user and its privileges on the MySQL database.


20. Type SET SESSION binlog_format = 'MIXED';

21. Type SET GLOBAL binlog_format = 'MIXED';

22. Type quit and press <enter>

23. Type yum -y install sed and press <enter>

24. Type sed -i '/# log_bin/a binlog_format=mixed' /etc/my.cnf and press <enter>

This will set the binary logging format to a mode that Mobility Suite's configurator will accept.

25. Type mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -Dmysql -u root mysql -p and press <enter>

26. Type the root account's password at the password prompt and press <enter>

(The default root password for MySQL is blank)

This configures the MySQL's databases' timezone information which needs to be defined for the Mobility Suite .iso installation.


A script which performs these steps automatically has been attached to this article. The script's permissions may need to be modified before it can be run after it has been uncompressed. In order to uncompress, qualify the script for use, and execute it the following commands need to be run from the Terminal in the directory where the script is located:

# yum -y install unzip
# unzip MySQLMobilitySuite_Custom.sh.zip -d ~
# cd ~
# chmod +x MySQLMobilitySuite_Custom.sh
# ./MySQLMobilitySuite_Custom.sh

​​For reference, the contents of the attached 'MySQLMobilitySuite_Custom.sh' script has been included below:​

#!/usr/bin/env bash

#Check for outbound internet connectivity for the yum repo

echo "Checking for outbound internet connectivity..."

YUM_RESPONSE="$(curl -s http://mirrorlist.centos.org/ --dump-header - | grep HTTP/1.1 | awk  '{print $2}')"

if [[ "$YUM_RESPONSE" == "200" ]] || [[ "$YUM_RESPONSE" == "302" ]];then 
 echo "Outbound Internet connection detected. Moving onto installation..."
else
 yum -y install curl

YUM_RESPONSE="$(curl -s http://mirrorlist.centos.org/ --dump-header - | grep HTTP/1.1 | awk  '{print $2}')"

  if [[ "$YUM_RESPONSE" == "200" ]] || [[ "$YUM_RESPONSE" == "302" ]];then 
   echo "Outbound Internet connection detected. Moving onto installation..."
  else
   echo "Please configure this VM for outbound internet connectivity and attempt installation again."
   echo "If you believe this script to be in error, then investigate connectivity to the 'yum' command's mirrorlist."
   exit
  fi
fi

#Install MySQL and dependencies for script

yum -y install wget
wget -P /tmp/ http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
cd /tmp/
yum -y localinstall mysql-community-release-el6-5.noarch.rpm
yum repolist enabled | grep "mysql.*-community.*"
yum repolist all | grep mysql
yum-config-manager --enable mysql56-community
yum-config-manager --disable mysql57-community-dmr
yum repolist enabled | grep mysql

yum -y install policycoreutils-python
yum -y install sed

yum -y install mysql-community-server

service mysqld start
service mysqld status

rm -f mysql-community-release-el6-5.noarch.rpm

echo "+---------------------------+"
echo "|MySQL 5.6 is now installed!|"
echo "+---------------------------+"

DB_USER='root'
DB_PASS=''

#TimeZone Fix

mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -Dmysql -u "$DB_USER" mysql --password="$DB_PASS"

#Configuration Collection

echo "--------------------------------------------------------------------------------"
while [[ "$Yes" != *"y"* ]];do

NEW_DB_USER="$1"
NEW_DB_PASS="$2"
NEW_DB_IP="$3"
NEW_DB_APPSTORE="$4"
NEW_DB_MDMCORE="$5"
NEW_DB_ROOT_PASS="$6"
NEW_DB_PORT="$7"
NEW_DB_PATH="$8"
HOSTNAME="$( hostname )"

echo ""
echo "Press <Enter> to accept the default values in brackets."
echo ""

if [ -z "$NEW_DB_PATH" ]; then
 echo "The path must contain '/' at the beginning, no '/' at the end, and no spaces."
 echo -n "What installation path do you want to use for MySQL?[/var/lib]: "
 read NEW_DB_PATH
fi

if [ -z "$NEW_DB_PATH" ]; then
 NEW_DB_PATH=/var/lib
fi

while [[ "$NEW_DB_PATH" == *"/" ]] || [[ "$NEW_DB_PATH" != "/"* ]] || [[ "$NEW_DB_PATH" == " "* ]] || [[ "$NEW_DB_PATH" == *" " ]] || [[ "$NEW_DB_PATH" == *" "* ]]; do
 echo "The path must contain '/' at the beginning, no '/' at the end, and no spaces."
 echo -n "Please re-type it: "
 read NEW_DB_PATH
done

echo "-->$NEW_DB_PATH"
echo ""

if [ -z "$NEW_DB_USER" ]; then
 echo -n "What username do you want to create for the MySQL databases?[symantec]: "
 read NEW_DB_USER
fi

if [ -z "$NEW_DB_USER" ]; then
 NEW_DB_USER=symantec
fi

echo "-->$NEW_DB_USER"
echo ""

if [ -z "$NEW_DB_PASS" ]; then
 echo -n "What do you want $NEW_DB_USER's password to be?[[email protected]]: "
 read NEW_DB_PASS
fi

if [ -z "$NEW_DB_PASS" ]; then
 [email protected]
fi

echo "-->$NEW_DB_PASS"
echo ""

if [ -z "$NEW_DB_IP" ]; then
 echo "The '%' character allows access from any IP address."
 echo -n "What is the IP address of the Mobility Suite Server?[%]: "
 read NEW_DB_IP
fi

if [ -z "$NEW_DB_IP" ]; then
 NEW_DB_IP=%
fi

echo "-->$NEW_DB_IP"
echo ""

if [ -z "$NEW_DB_APPSTORE" ]; then
 echo -n "What name do you want to give the appstore database?[appstore]: "
 read NEW_DB_APPSTORE
fi

if [ -z "$NEW_DB_APPSTORE" ]; then
 NEW_DB_APPSTORE=appstore
fi

echo "-->$NEW_DB_APPSTORE"
echo ""

if [ -z "$NEW_DB_MDMCORE" ]; then
 echo -n "What name do you want to give the mdmcore database?[mdmcore]: "
 read NEW_DB_MDMCORE
fi

if [ -z "$NEW_DB_MDMCORE" ]; then
 NEW_DB_MDMCORE=mdmcore
fi

echo "-->$NEW_DB_MDMCORE"
echo ""

if [ -z "$NEW_DB_ROOT_PASS" ]; then
 echo -n "What do you want the MySQL root user's password to be?[[email protected]]: "
 read NEW_DB_ROOT_PASS
fi

if [ -z "$NEW_DB_ROOT_PASS" ]; then
 [email protected]
fi

echo "-->$NEW_DB_ROOT_PASS"
echo ""

if [ -z "$NEW_DB_PORT" ]; then
 echo -n "What port do you want the MySQL database to use?[3306]: "
 read NEW_DB_PORT
fi

if [ -z "$NEW_DB_PORT" ]; then
 NEW_DB_PORT=3306
fi

echo "-->$NEW_DB_PORT"
echo ""

#Configuration Verification Info

echo "--------------------------------------------------------------------------------"

echo "Mobility Suite DB User-->$NEW_DB_USER"
echo "DB User Password-------->$NEW_DB_PASS"
if [ "$NEW_DB_IP" = "%" ]; then
 echo "IP Allowed-------------->Any IP"
else
 echo "IP Allowed-------------->$NEW_DB_IP"
fi
echo "appstore DB Name-------->$NEW_DB_APPSTORE"
echo "mdmcore DB Name--------->$NEW_DB_MDMCORE"
echo "MySQL Root Password----->$NEW_DB_ROOT_PASS"
echo "MySQL DB Port----------->$NEW_DB_PORT"
echo "MySQL Install Path------>$NEW_DB_PATH"

echo "--------------------------------------------------------------------------------" 

echo -n "Do the configurations listed above look correct, y or n? [y]: "
read Yes
Yes=${Yes:-y}
done

echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log

echo "Mobility Suite DB User-->$NEW_DB_USER" >> ~/MySQL_Script.log
echo "DB User Password-------->$NEW_DB_PASS" >> ~/MySQL_Script.log
if [ "$NEW_DB_IP" = "%" ]; then
 echo "IP Allowed-------------->Any IP" >> ~/MySQL_Script.log
else
 echo "IP Allowed-------------->$NEW_DB_IP" >> ~/MySQL_Script.log
fi
echo "appstore DB Name-------->$NEW_DB_APPSTORE" >> ~/MySQL_Script.log
echo "mdmcore DB Name--------->$NEW_DB_MDMCORE" >> ~/MySQL_Script.log
echo "MySQL Root Password----->$NEW_DB_ROOT_PASS" >> ~/MySQL_Script.log
echo "MySQL DB Port----------->$NEW_DB_PORT" >> ~/MySQL_Script.log
echo "MySQL Install Path------>$NEW_DB_PATH" >> ~/MySQL_Script.log
echo "--------------------------------------------------------------------------------"
echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log

echo "Setting SE Linux and Firewall Exceptions on port $NEW_DB_PORT for $NEW_DB_USER."
echo "This may take a few minutes..."

#Port and SE Linux exceptions

/sbin/service iptables status >/dev/null 2>&1
if [ $? = 0 ]; then
/sbin/iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport $NEW_DB_PORT -j ACCEPT
/etc/init.d/iptables save
else
/sbin/iptables -I INPUT -m state --state NEW -m tcp -p tcp --dport $NEW_DB_PORT -j ACCEPT
/etc/init.d/iptables save
/sbin/iptables -L -v -n
/etc/init.d/iptables stop
fi

semanage port -a -t mysqld_port_t -p tcp $NEW_DB_PORT
setsebool -P allow_user_mysql_connect 1
setsebool -P mysql_connect_any 1

if [ "$NEW_DB_PORT" = "3306" ]; then
 echo -e "innodb_log_file_size=128M" >> /etc/my.cnf
 echo -e "max_allowed_packet=128M" >> /etc/my.cnf
 sed -i '/# log_bin/a binlog_format=mixed' /etc/my.cnf
else
 echo -e "port=$NEW_DB_PORT" >> /etc/my.cnf
 echo -e "max_allowed_packet=128M" >> /etc/my.cnf
 echo -e "innodb_log_file_size=128M" >> /etc/my.cnf
 sed -i '/# log_bin/a binlog_format=mixed' /etc/my.cnf
fi

#Installation path change

if [ "$NEW_DB_PATH" == "/var/lib" ]; then
 echo "Default Installation Path Selected. No need to change..."
else
 service mysqld stop
 mkdir -p $NEW_DB_PATH/
 chown mysql:mysql $NEW_DB_PATH
 mv /var/lib/mysql/* $NEW_DB_PATH/
 sed -i "s#datadir=.*#datadir=$NEW_DB_PATH#" /etc/my.cnf
 sed -i "s#socket=.*#socket=$NEW_DB_PATH/mysql.sock#" /etc/my.cnf
 echo -e "[client]" >> /etc/my.cnf
 echo -e "socket = $NEW_DB_PATH/mysql.sock" >> /etc/my.cnf
 semanage fcontext -a -t mysqld_db_t "$NEW_DB_PATH(.*)?"
 restorecon -Rv $NEW_DB_PATH
 service mysqld start
fi

semanage port -l | grep mysql
sestatus -b | grep mysql
/sbin/iptables -L -v -n

#MySQL Commands

mysql -u "$DB_USER" --password="$DB_PASS" -e \
"SET SESSION binlog_format = 'MIXED';SET GLOBAL binlog_format = 'MIXED';create database $NEW_DB_APPSTORE character set utf8 collate utf8_bin;create database $NEW_DB_MDMCORE character set utf8 collate utf8_bin;GRANT ALL PRIVILEGES ON $NEW_DB_APPSTORE.* TO '$NEW_DB_USER'@'$NEW_DB_IP' IDENTIFIED BY '$NEW_DB_PASS';GRANT ALL PRIVILEGES ON $NEW_DB_MDMCORE.* TO '$NEW_DB_USER'@'$NEW_DB_IP' IDENTIFIED BY '$NEW_DB_PASS';SET PASSWORD FOR 'root'@'localhost' = PASSWORD('$NEW_DB_ROOT_PASS');SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('$NEW_DB_ROOT_PASS');SET PASSWORD FOR 'root'@'$HOSTNAME' = PASSWORD('$NEW_DB_ROOT_PASS');SET PASSWORD FOR 'root'@'::1' = PASSWORD('$NEW_DB_ROOT_PASS');show databases;SELECT User, Host, Password FROM mysql.user;SHOW GRANTS FOR '$NEW_DB_USER'@'$NEW_DB_IP'"

echo "The MySQL daemon is restarting..."

service mysqld restart

#Summary Info

echo "+---------------------------------------+"
echo "|MySQL is Configured for Mobility Suite!|"
echo "+---------------------------------------+"
echo ""
echo "The root user's password is now $NEW_DB_ROOT_PASS."
echo "The root user's password is now $NEW_DB_ROOT_PASS." >> ~/MySQL_Script.log
echo ""
echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log

echo "MySQL has been installed in the $NEW_DB_PATH directory."
echo "MySQL has been installed in the $NEW_DB_PATH directory." >> ~/MySQL_Script.log
echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log

if [ "$NEW_DB_IP" = "%" ]; then
 echo "The username, '$NEW_DB_USER', has been created with the password of '$NEW_DB_PASS' and the $NEW_DB_APPSTORE/$NEW_DB_MDMCORE databases have been created which can be accessed from any IP on port $NEW_DB_PORT. The MySQL server is available from the following Hostname, Fully Qualified Domain Name (FQDN), or IP Address:"
 echo "The username, '$NEW_DB_USER', has been created with the password of '$NEW_DB_PASS' and the $NEW_DB_APPSTORE/$NEW_DB_MDMCORE databases have been created which can be accessed from any IP on port $NEW_DB_PORT. The MySQL server is available from the following Hostname, Fully Qualified Domain Name (FQDN), or IP Address:" >> ~/MySQL_Script.log
else
 echo "The username, '$NEW_DB_USER', has been created with the password of '$NEW_DB_PASS' and the $NEW_DB_APPSTORE/$NEW_DB_MDMCORE databases have been created which can be accessed from '$NEW_DB_IP' on port $NEW_DB_PORT. The MySQL server is available from the following Hostname, Fully Qualified Domain Name (FQDN), or IP Address:"
 echo "The username, '$NEW_DB_USER', has been created with the password of '$NEW_DB_PASS' and the $NEW_DB_APPSTORE/$NEW_DB_MDMCORE databases have been created which can be accessed from '$NEW_DB_IP' on port $NEW_DB_PORT. The MySQL server is available from the following Hostname, Fully Qualified Domain Name (FQDN), or IP Address:" >> ~/MySQL_Script.log
fi
echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log
echo ""
hostname
hostname >> ~/MySQL_Script.log
hostname -A
hostname -A >> ~/MySQL_Script.log
echo -e $( ifconfig  | grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}' )
echo -e $( ifconfig  | grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}' ) >> ~/MySQL_Script.log
echo ""
echo "--------------------------------------------------------------------------------" >> ~/MySQL_Script.log

echo "The output above has been saved in your home directory as 'MySQL_Script.log'."

chkconfig --level 2345 mysqld on

cd ~

read -s -n 1 -p "Press any key to view the MySQL Configuration Summary Log."

vi MySQL_Script.log

$SHELL

Attachments

MySQLMobilitySuite_Custom.sh.zip get_app
MySQLMobilitySuite_Custom.sh.zip get_app