MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help to upgrade MySQL from 5.6.18 to 5.7.21

3741788Aug 2 2018 — edited Aug 2 2018

Hi All,

We trying to upgrade MySQL from 5.6.18 to 5.7.21. Now we are able to successfully bring MySQL server up and install the database on it, however when we try to restart MySQL server it is failing. In our startup script, last mysqld_safe executed without any error msg on log/stdout/stderr. We provide following parameter to start mysql_self.

bin/mysqld_safe \

--defaults-file=/usr/BWhttpd/mysql /my.cnf \

--pid-file=/usr/BWhttpd/mysql/data/mysql.pid \

--basedir=/usr/BWhttpd/mysql \

--datadir=/usr/BWhttpd/mysql/data \

--user=mysql

outputs:

mysqld_safe Adding '/usr/lib64/libtcmalloc_minimal.so.4' to LD_PRELOAD for mysqld

2018-08-02T10:39:53.163827Z mysqld_safe Logging to '/usr/BWhttpd/logs/mysql.log'.

2018-08-02T10:39:53.214698Z mysqld_safe Starting mysqld daemon with databases from /usr/BWhttpd/mysql/data

2018-08-02T10:39:53.330111Z mysqld_safe mysqld from pid file /usr/BWhttpd/mysql/data/mysql.pid ended

Follwoing our my_cnf and ./init_mysql script which we are using to start/stop mysql service.

We also found that in our script we were using “mysql_install_db” which is now deprecated hence we replaced it with “mysqld --initialize” in ./init_mysql script. However we still facing issue to start mysql service. Sometimes it gets started but when we stop and restart again it failed.

Seeking help to update the mysql, please respond.

Thanks in advance

Pankaj

my.cnf file contain start

*************************************

# Example MySQL config file for medium systems.

# This is for a system with little memory (32M - 64M) where MySQL plays

# an important part, or systems up to 128M where MySQL is used together with

# other programs (such as a web server)

# You can copy this file to

# /etc/my.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options (in this

# installation this directory is /var/lib/mysql) or

# ~/.my.cnf to set user-specific options.

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

# The following options will be passed to all MySQL clients

[client]

#password = your_password

port = 3306

socket = /usr/BWhttpd/mysql/mysql.sock

default-character-set = utf8

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /usr/BWhttpd/mysql/mysql.sock

pid-file = /usr/BWhttpd/mysql/data/mysql.pid

key_buffer_size = 16M

max_allowed_packet = 1M

table_open_cache = 256

table_definition_cache = 400

character-set-server = utf8

init_connect='SET collation_connection = utf8_general_ci; SET NAMES utf8;'

user=mysql

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

#enable performance schema

sort_buffer_size = 2M

net_buffer_length = 8K

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 2M

thread_cache_size = 8

# slow queries

slow-query-log = 1

slow-query-log-file = /usr/BWhttpd/logs/mysqld-slow-query.log

log-queries-not-using-indexes

log-slow-admin-statements

#log all queries to the server

#general-log = 1

#general-log=/usr/BWhttpd/logs/mysqld-queries.log

log-error=/usr/BWhttpd/logs/mysql.log

#max connections to the database

max_connections = 2000

# mysql++ timeout is 100. This is set longer

# on purpose that the server never unexpectedly

# closes a connection on the client

wait_timeout = 110

# query caching

# Turning it OFF after talking to mysql on various mysql deadlock issues

query-cache-type= 0

query-cache-size= 0

#query_cache_limit = 10M

# base dir

basedir=/usr/BWhttpd/mysql

datadir=/usr/BWhttpd/mysql/data

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

# Allow CDAF or others to connect via tcp

##skip-networking

# Replication Master Server (default)

# binary logging is required for replication

# log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

# Replication Slave (comment out master section to use this)

# To configure this host as a replication slave, you can choose between

# two methods :

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

# the syntax is:

# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

# where you replace <host>, <user>, <password> by quoted strings and

# <port> by the master's port number (3306 by default).

# Example:

# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

# MASTER_USER='joe', MASTER_PASSWORD='secret';

# OR

# 2) Set the variables below. However, in case you choose this method, then

# start replication for the first time (even unsuccessfully, for example

# if you mistyped the password in master-password and the slave fails to

# connect), the slave will create a master.info file, and any later

# change in this file to the variables' values below will be ignored and

# overridden by the content of the master.info file, unless you shutdown

# the slave server, delete master.info and restart the slaver server.

# For that reason, you may want to leave the lines below untouched

# (commented) and instead use CHANGE MASTER TO (see above)

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

#server-id = 2

# The replication master for this slave - required

#master-host = <hostname>

# The username the slave will use for authentication when connecting

# to the master - required

#master-user = <username>

# The password the slave will authenticate with when connecting to

# the master - required

#master-password = <password>

# The port the master is listening on.

# optional - defaults to 3306

#master-port = <port>

# binary logging - not required for slaves, but recommended

#log-bin=mysql-bin

# Point the following paths to different dedicated disks

#tmpdir = /tmp/

#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables

#bdb_cache_size = 4M

#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables

innodb_large_prefix=on

innodb_file_format=barracuda

innodb_file_per_table=true

innodb_data_home_dir = /usr/BWhttpd/mysql/data/

innodb_data_file_path = ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/BWhttpd/mysql/data/

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

# Increasing buffer pool size after talking mysql on deadlock/restart issues

innodb_buffer_pool_size = 64M

#innodb_additional_mem_pool_size = 2M

# Set .._log_file_size to 25 % of buffer pool size

# Increasing buffer pool size after talking mysql on deadlock/restart issues

innodb_log_file_size = 64M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

innodb_rollback_on_timeout = 1

# Force InnoDB to use one tablespace file for

# each tables. This has plusses and minuses.

# Positive - it helps keep fragmentation down on the

# ibdata1 file.

# Negative - more files open, fsynch on multiple files

# vs one file. But as files get large and fragment

# on the disk, fsynch on one file may not be faster.

# Might be slower than a one large file

innodb_file_per_table = 1

explicit_defaults_for_timestamp = 1

# The safe_mysqld script

[safe_mysqld]

err-log=/usr/BWhttpd/logs/mysqld.log

malloc-lib=/usr/lib64/libtcmalloc_minimal.so.4

#ledir=/usr/BWhttpd/mysql/bin

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

*************************************

my.cnf file contain end

init_mysql file contain start

**************************************

#!/bin/bash

. /usr/BWhttpd/bin/init_library.sh

# If the environment variable CISCO_DEBUG is set to the value 2, then turn on

# detailed output debugging. This allows debugging to be enabled without having

# to modify the file.

# For example, you could invoke the scripts as:

# CISCO_DEBUG=2 <init-script> <argument>

# or

# export CISCO_DEBUG=2

# <init-script> <argument>

if [[ $CISCO_DEBUG -eq 2 ]]; then

echo -e "\nCISCO_DEBUG set to 2, enabling detailed debugging output\n"

set -x

ME=/usr/BWhttpd/bin/$(basename $0)

MYSQL_BASE_DIR=/usr/BWhttpd/mysql

MYSQL_BIN_DIR=$MYSQL_BASE_DIR/bin

MYSQL_SCRIPTS_DIR=$MYSQL_BASE_DIR/scripts

MYSQL_DATA_DIR=$MYSQL_BASE_DIR/data

MYSQL_LD_DIR=/usr/BWhttpd/mysql/bin

MYSQLD_EXE=$MYSQL_BIN_DIR/mysqld

MYSQLD_SAFE_EXE=$MYSQL_BIN_DIR/mysqld_safe

MYSQL_EXE=$MYSQL_BIN_DIR/mysql

MYSQL_PID_FILE=$MYSQL_DATA_DIR/mysql.pid

MYSQL_SOCK_FILE=$MYSQL_BASE_DIR/mysql.sock

FIRST_RUN_COMPLETE_TAG=$MYSQL_DATA_DIR/first_run.complete

INSTALL_COMPLETE_TAG=$MYSQL_DATA_DIR/db_install.complete

MY_CNF=$MYSQL_BASE_DIR/my.cnf

UMS_CNF=$UMS_SCHEMA_DIR/ums.cnf

DBUSER_CNF=$UMS_CONF_DIR/dbuser.cnf

MYSQL_STARTUP_LOG=$UMS_LOG_DIR/mysql_install.log

MYSQL_LOG_FILE=$UMS_LOG_DIR/mysql.log

MYSQL_UPGRADE_LOG=$UMS_LOG_DIR/mysql_upgrade.log

SLOW_QUERY_LOG=$UMS_LOG_DIR/mysqld-slow-query.log

QUERY_ROOT="$MYSQL_EXE -u root -S $MYSQL_SOCK_FILE -e"

QUERY_ROOT_DEFAULTS="$MYSQL_EXE --defaults-file=$UMS_CNF -s -N -e"

QUERY_DBUSER_DEFAULTS="$MYSQL_EXE --defaults-file=$DBUSER_CNF -e"

SILENT_QUERY_DBUSER_DEFAULTS="$MYSQL_EXE --defaults-file=$DBUSER_CNF -s -N -e"

FAILBACK_STATE=1

MYSQL_PATH_VSMS="/mysql/data/vsms"

MYSQL_PATH_DEV="/usr/BWhttpd/mysql/data"

BACKUP_INPROGRESS_FILE="/usr/BWhttpd/root/htdocs/download/vsms_backup.in_progress"

function shutdown_mysql ()

{

local pid_mysql=$1

kill -STOP $pid_mysql

kill -TERM $pid_mysql

kill -CONT $pid_mysql

}

function abort_start_and_exit()

{

if [[ $command == start ]] ; then

echo "Error starting mysql. Aborting cisco start"

ims_log "Error starting mysql. Aborting cisco start"

/etc/init.d/cisco abort-start

ims_log "start aborted"

exit 1

}

# function: is_official_mysql_running

# Return 1 if the MYSQL PID file exists, and it's PID corresponds to a running

# instance of a VSMS mysqld

# Retrun 0 if no MYSQL PID file is found, or if one is found but it's stale pid

# does not correspond to an instance of a VSMS mysqld. Stale pid files are

# deleted.

function is_official_mysql_running ()

{

# no PID file so no official instance running

if [ ! -f $MYSQL_PID_FILE ]; then

return 0

# get the PID

mysqld_pid=`cat $MYSQL_PID_FILE`

# If this pid has an executable associated with it and that executable is

# our mysqld then mysql _IS_ running.

if [ -L "/proc/$mysqld_pid/exe" ]; then

exe=`readlink "/proc/$mysqld_pid/exe"`

if [ "$exe" = "$MYSQLD_EXE" ]; then

return 1

# if this pid has an executable associated with it and that executable is

# _NOT_ our mysqld, or if there is no executable associated with this PID,

# then this is a stale PID file that needs to be cleaned up.

mysql_log "Cleaning up stale pid file for pid ($mysqld_pid)."

rm $MYSQL_PID_FILE

return 0

}

# function: get_rogue_mysql_pid <dest>

# Look for a rogue MYSQL pid and place it in the destination. If no rogue PID

# is found then dest gets 0, if one OR MORE rogue PIDs are found they are

# placed in the destination.

function get_rogue_mysql_pid ()

{

local dest=$1

local mysql_pid

local pid_check

local rogue_mysql_pid=`pgrep -d " " -f "$MYSQLD_EXE .*$MY_CNF"`

local updated_pid_list=""

# return 0 if nothing returned

if [ -z "$rogue_mysql_pid" ]; then

eval $dest=0

return

# if there is an OFFICIAL PID, then make sure we don't include it in the

# rogue PID list.

if [ -f $MYSQL_PID_FILE ]; then

mysql_pid=`cat $MYSQL_PID_FILE`

# remove the official PID from the ROGUE list

for pid_check in $rogue_mysql_pid

if [[ $mysql_pid != $pid_check ]]; then

updated_pid_list="$updated_pid_list $pid_check"

done

if [ -z $updated_pid_list ]; then

eval $dest=0

else

eval $dest="$updated_pid_list"

return

eval $dest="\"$rogue_mysql_pid\""

}

# function: check_mysql_running <reason>

# Args:

# reason : Are we checking for start|stop, or for other reasons? The

# behavior changes based on the reason

# This function returns True if an official instance of the VSMS mysqld process

# is running and False otherwise. By official we mean one that has it's PID

# file set.

# If the reason for the check is 'start' or 'stop' then it will attempt to kill

# rogue mysql sessions and can wait up to 60 seconds to complete this.

# If the reason is NOT 'start' or 'stop' then it can pause up to 3 seconds in

# the event that the PID is being created -- i.e. the rogue process is really a

# newly spawned official process.

function check_mysql_running ()

{

local reason=$1

local sleep_limit

local do_rogue_kill

local is_run

local rogue_pids

if [ "$reason" = "start" -o "$reason" = "stop" ]; then

sleep_limit=600; # 60 seconds for 0.2 sec sleeps

do_rogue_kill=1

else

sleep_limit=15; # 3 seconds for 0.2 sec sleeps

do_rogue_kill=0

for((i=0; i<$sleep_limit; i++)); do

is_official_mysql_running

is_run=$?

# return true if official MYSQL (PID FILE FOUND) is running

if [ 1 -eq $is_run ]; then

return 1

get_rogue_mysql_pid rogue_pids

# if MYSQL isn't running and no rogue is running, then IT'S NOT RUNNING

if [ "0" = "$rogue_pids" ]; then

return 0

# if we reach this point then (1) no official mysql session was found

# and (2) an unexpected rogue PID was found. The PID file might not be

# created yet, our behaviour changes based on the reason for the check

if [ 0 -eq $do_rogue_kill ]; then

# do nothing, just sleep and then check again, allowing a small

# amount of time for the rogue instance to potentially change into

# an official instance.

else

shutdown_mysql "$rogue_pids"

sleep 0.2

done

# alert if we couldn't kill the rogue mysql process

if [ 1 -eq $do_rogue_kill ]; then

get_rogue_mysql_pid rogue_pids

if [ "0" != "$rogue_pids" ]; then

local err_msg=\

"Unable to terminate rogue mysql process ($rogue_pids)"

echo $err_msg

mysql_log $err_msg

return 0

}

# upgrade_log <log-entry>

# Arguments:

# All arguments are logged

# This causes the output to be placed in the mysql upgrade log, attempting

# to mimic the format used by the gut logger as much as possible.

# The output is parsed so that the timestamp prefix is added for each

# newline encountered.

# If the output comes from a command, enclose it in quotes (") to maintain

# embedded newlines.

function upgrade_log () {

generic_log $MYSQL_UPGRADE_LOG "$*"

}

# mysql_log <log-entry>

# Arguments:

# All arguments are logged

# This causes the output to be placed in the mysql log, attempting

# to mimic the format used by the gut logger as much as possible.

# The output is parsed so that the timestamp prefix is added for each

# newline encountered.

# If the output comes from a command, enclose it in quotes (") to maintain

# embedded newlines.

function mysql_log () {

generic_log $MYSQL_LOG_FILE "$*"

}

## function mysql_cmd <cmd> <error-msg>

## This executes the specified mysql command using the password-less root

## user. Note this can only be used before the root users password has been

## set. If the command fails, then the specified error message is displayed,

## the mysql service is stopped and the init script exits.

function mysql_cmd {

cmd=$1

err=$2

$QUERY_ROOT "$cmd"

if [ $? -ne 0 ]; then

echo "$err"

ims_log "$err"

$ME stop

abort_start_and_exit

echo "cmd == $cmd, err == $err , QUERY_ROOT == $QUERY_ROOT " >> $MYSQL_STARTUP_LOG

}

## function silent_query_dbuser <dest> <cmd>

## This executes the specified mysql command using the silent dbUser

## defaults. It places the results into the first parameter <dest>

## It returns a status of 0 if the command succeeded or non-zero if the command

## failed.

function silent_query_dbuser() {

local dest=$1

local cmd=$2

local status

local result

# Execute the silent query placing the good results or the standard error

# output into the 'result' local variable.

result=`$SILENT_QUERY_DBUSER_DEFAULTS "$cmd" 2>&1`

status=$?

# "return" the result in the dest parm

eval $dest="\"$result\""

return $status

}

## function silent_query_dbuser_nofail <dest> <cmd>

## This executes the specified mysql command using the silent dbUser

## defaults. It places the results into the first parameter <dest>. If the

## command fails, it displays and logs an error as well as terminates the

## mysql session.

function silent_query_dbuser_nofail () {

local dest=$1

local cmd=$2

local sqdn_result

silent_query_dbuser sqdn_result "$cmd"

if [ $? -ne 0 ]; then

local error_msg="MySQL command failure for command:\n"\

" $cmd\n"\

"$sqdn_result\n"\

"Terminating database process"

ims_log "$error_msg"

echo -e "\n$error_msg"

$ME stop

abort_start_and_exit

# "return" the result in the dest parm

eval $dest="\"$sqdn_result\""

return 0

}

## function apply_sql_file <file>

## This applys the .sql file <file> to mysql using the DB USER

## It returns a status of 0 if the command succeeded or non-zero if the command

## failed.

function apply_sql_file () {

local file=$1

local status

$MYSQL_EXE --defaults-file=$DBUSER_CNF < $file

status=$?

return $status

}

## function apply_sql_file_nofail <file>

## This applys the .sql file <file> to the mysql using the DB USER

## If the command fails, it displays and logs an error as well as terminates

## the mysql session.

function apply_sql_file_nofail () {

local file=$1

apply_sql_file $file

if [ $? -ne 0 ]; then

local error_msg="Failed to apply sql file: $file. "\

"Terminating database process"

ims_log "$error_msg"

echo -e "\n$error_msg"

$ME stop

exit 1

return 0

}

## function apply_upgrade_script <file>

## This runs the upgrade script (xyz.sh) that is passed in, printing output to

## the console and log file.

## Returns 0 on success, non-zero on failure.

function apply_upgrade_script () {

local upgradeFile=$1

local status

local log_msg

log_msg="Applying upgrade file $upgradeFile - "

echo -ne "\n $log_msg"

$upgradeFile

status=$?

if [ $status == 0 ]; then

echo -ne "PASSED"

log_msg="$log_msg PASSED"

else

echo -ne "FAILED"

log_msg="$log_msg FAILED"

ims_log $log_msg

return $status

}

## function apply_upgrade_sql_file <file>

## This applys the .sql file to the database as the DB User, printing output to

## the console and log file.

## Returns 0 on success, non-zero on failure

function apply_upgrade_sql_file () {

local upgradeFile=$1

local status

local log_msg

log_msg="Applying upgrade file $upgradeFile - "

echo -ne "\n $log_msg"

$MYSQL_EXE --defaults-file=$DBUSER_CNF < $upgradeFile

status=$?

if [ $status == 0 ]; then

echo -ne "PASSED"

log_msg="$log_msg PASSED"

else

echo -ne "FAILED"

log_msg="$log_msg FAILED"

ims_log "$log_msg"

return $status

}

## function upgrade_database

## This upgrades the database, if an upgrade is needed.

## It determines the current database version, and the schema version. If they

## differ, it looks for all upgrade files to get us from the current DB version

## to the new schema version and applies them.

## Returns 0 on success -- i.e. if no upgrades to do or if all database

## upgrades have been completed.

## Returns 1 on failure, such as if one of the upgrade files didn't apply

## correctly or if one of the upgrade files was missing.

function upgrade_database () {

local olddbVersion

local newschemaFile

local newdbVersion

local oldDot

local oldMajorMinor

local newDot

local newMajorMinor

local dropDB=0

local err_msg

silent_query_dbuser_nofail olddbVersion \

"select schema_version from UMS.db_version;"

# get version of latest DB schema

newschemaFile=`ls $UMS_SCHEMA_DIR/ums_schema-*.sql`

newdbVersion=`basename $newschemaFile .sql | cut -f2 -d'-'`

# check if the versions differ

if [ "$olddbVersion" != "$newdbVersion" ]; then

echo -ne "\n Old DB schema version: $olddbVersion"

echo -ne "\n New DB schema version: $newdbVersion\n"

# get major/minor and dot release levels of old and new release

oldDot=`echo $olddbVersion | cut -f3 -d'.'`

oldMajorMinor=`basename $olddbVersion .$oldDot`

newDot=`echo $newdbVersion | cut -f3 -d'.'`

newMajorMinor=`basename $newdbVersion .$newDot`

# check for Major/Minor upgrade or a Dot upgrade

if [ "$oldMajorMinor" != "$newMajorMinor" ]; then

err_msg="Major/Minor upgrade currently not supported. Need to drop the database"

echo -ne "\n => $err_msg"

ims_log "$err_msg"

# drop the database

dropDB=1

else

# try upgrading between dot releases

echo -ne "\n => Doing dot release upgrades ..."

while [ $((++oldDot)) -le $newDot -a $dropDB -eq 0 ]; do

# see if there is a script or sql to do the upgrade

upgradeFile="$UMS_SCHEMA_DIR/ums_schema_upgrade-$oldMajorMinor.$oldDot"

if [ -x $upgradeFile.sh ]; then

apply_upgrade_script "$upgradeFile.sh"

dropDB=$?

elif [ -f $upgradeFile.sql ]; then

apply_upgrade_sql_file "$upgradeFile.sql"

dropDB=$?

else

# missing file, we need to drop the DB

err_msg="!!!Upgrade file '$upgradeFile' missing. Need to drop the database.!!!"

echo -ne "\n $err_msg\n"

ims_log "$err_msg"

dropDB=1

done

echo -ne "\n "

# Above, we determined if we need to drop the DB or not. If 0 then keep it,

# if 1 then drop it

return $dropDB

}

## function install_privilege_db

## If this is the first time (i.e. the data directory is missing) then install

## the database.

## http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html

## http://dev.mysql.com/doc/refman/5.5/en/mysql-install-db-problems.html

function install_privilege_db {

# return immediately if the first run complete flag file exists

if [ -f $FIRST_RUN_COMPLETE_TAG ]; then

return

cd $MYSQL_BASE_DIR

chown -R mysql:mysql .

# if the data/mysql directory exists, but the first run complete tag does

# not then that implies a previous attempt failed. Delete the data/mysql

# directory, including it's established users and privileges tables so

# mysql_install_db, which adds but doesn't update the privilege table, will

# add the standard privilege table again, which we then manipulate later in

# complete_first_run_setup()

if [ -d $MYSQL_DATA_DIR/mysql ]; then

echo "Cleaning up old data/mysql directory" >> $MYSQL_STARTUP_LOG

rm -rf $MYSQL_DATA_DIR/mysql

echo "Installing privilege database." >> $MYSQL_STARTUP_LOG

# $MYSQL_BIN_DIR/mysql_install_db --user=mysql \

# $MYSQL_SCRIPTS_DIR/mysql_install_db --user=mysql \

# --basedir=$MYSQL_BASE_DIR \

# --datadir=$MYSQL_DATA_DIR &> $MYSQL_STARTUP_LOG

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/BWhttpd/mysql/lib/

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/BWhttpd/lib

$MYSQL_BIN_DIR/mysqld --defaults-file=$MYSQL_BASE_DIR/my.cnf \

--initialize-insecure \

--user=mysql \

--basedir=$MYSQL_BASE_DIR \

--datadir=$MYSQL_DATA_DIR --verbose

if [ $? -ne 0 ]; then

local error_msg="$MYSQL_SCRIPTS_DIR/mysql_install_db failed! "\

"Please see $MYSQL_STARTUP_LOG for details."

echo "\n$error_msg"

ims_log "$error_msg"

abort_start_and_exit

chown -R root .

chown -R mysql data

}

## function start_db_server

## Start the database server in the background. Wait up to 5 minutes for the

## process to start and then wait up to 5 minutes for the server to be ready to

## accept connections.

## http://dev.mysql.com/doc/refman/5.5/en/unix-postinstallation.html

## http://dev.mysql.com/doc/refman/5.5/en/starting-server.html

function start_db_server {

cd $MYSQL_BASE_DIR

ulimit -S -c unlimited > /dev/null 2>&1

echo "start_db_server 1"

bin/mysqld_safe \

--defaults-file=$MYSQL_BASE_DIR/my.cnf \

--pid-file=/usr/BWhttpd/mysql/data/mysql.pid \

--basedir=$MYSQL_BASE_DIR \

--datadir=$MYSQL_DATA_DIR \

--skip-syslog \

--user=mysql --ledir=$MYSQL_LD_DIR &

echo "start_db_server 2"

# Wait up to 5 minutes for the server to start. We check periodically for

# the existence of the socket file.

for ((i=0; i<600; i++)); do

sleep 0.5

test -S $MYSQL_SOCK_FILE && i='' && break

done

if [ ! -z "$i" ]; then

local error_msg=\

"Startup of mysql server failed. Please check $MYSQL_LOG_FILE"

echo $error_msg

ims_log "$error_msg"

abort_start_and_exit

# Now wait until the DB is ready for connections. Wait up to 5 minutes

# periodically checking the log file for the 'ready for connections'

# message.

for ((i=0; i<600; i++)); do

sleep 0.5

count=`tail -n 4 $MYSQL_LOG_FILE | \

grep "mysql/bin/mysqld: ready for connections." | wc -l`

if [ $count -eq 1 ]; then

i=''

break

done

if [ ! -z "$i" ]; then

local error_msg="Connection startup of mysql server failed."\

"Please check $MYSQL_LOG_FILE"

echo $error_msg

ims_log "$error_msg"

abort_start_and_exit

}

## function complete_first_run_setup

## Do a series of automatic updates to the mysql database. If any upate fails

## then an error is logged and the mysql service is stopped.

## - Create the umsdb@localhost user

## - Give umsdb@localhost all privileges on database UMS

## - Delete anonymous users (empty users)

## - Delete root users from other hosts (only allow localhost)

## - Delete the test databases

## - Set the root@localhost password and re-read the database

## The password strings provided below are encrypted. They are determined by

## starting the mysql server and then using the command:

## SELECT PASSWORD('<password>');

## and using the results. This way the clear text password is not in the file.

## Changing the root password was moved to be second to last rather than first

## because in 5.5.28 we found the password update was immediately updated, so

## the commands that followed would fail. The flush privileges was combined

## with this line so that we could enact the command after the password change

## without having to logon again this time with a password.

## See http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html

function complete_first_run_setup {

# return immediately if the first run complete flag file exists

if [ -f $FIRST_RUN_COMPLETE_TAG ]; then

return

# create the umsdb user

mysql_cmd "CREATE USER 'umsdb'@'localhost' IDENTIFIED BY PASSWORD '*E395FEE1DD8499FCF366EFDC8051F9A0697BDC27';" \

"Could not create umsdb user! Exiting..."

# grant privileges to the user.

mysql_cmd "GRANT ALL PRIVILEGES ON UMS.* TO 'umsdb'@'localhost';" \

"Could not create umsdb user! Exiting..."

# delete anonymous users

mysql_cmd "DELETE FROM mysql.user WHERE User='';" \

"Could not delete anonymous users! Exiting..."

# dissallow root remote login.

mysql_cmd "DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';" \

"Could not disallow remote root logins! Exiting..."

echo "complete_first_run_setup 1111" >> $MYSQL_STARTUP_LOG

# delete the test database

#mysql_cmd "DROP DATABASE test;" \

# "Could not drop test database. Exiting..."

#echo "complete_first_run_setup 2222" >> $MYSQL_STARTUP_LOG

# second half of delete test database

#mysql_cmd "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'" \

# "Could not delete test database from mysql.db. Exiting..."

#echo "complete_first_run_setup 3333" >> $MYSQL_STARTUP_LOG

# change the root password and flush privileges

# After this point the root user needs to specify a password

mysql_cmd "SET PASSWORD FOR 'root'@'localhost' = '*AB0DC6D4A0ACFA6A216B99AF6CBF6DB24ADF7CA3'; FLUSH PRIVILEGES;" \

"Could not change root user password! Exiting..."

echo "complete_first_run_setup 2222" >> $MYSQL_STARTUP_LOG

# First run setup is now complete. Touch the file flag so subsequent calls

# won't trigger first run code

touch $FIRST_RUN_COMPLETE_TAG

}

## function upgrade_mysql_server

## Run mysql_upgrade against the database tables (if needed), and place the

## output in the mysql_upgrade.log file. If the log file has any errors in it

## then cites the errors and stop the server from starting. If the upgrade

## check is clean then a mysql-version-specific flag is created so that we

## won't run the upgrade check again. However, if errors are found the flag

## isn't created so that the upgrade check will continue to run and flag errors

## until they are corrected.

## The upgrade logic is only executed if (1) the database has been installed

## (i.e. there is something to check) and (2) we haven't completed an error

## free upgrade check for this version of mysql yet.

## Note the version here is the version of the mysql application that is

## running, not the database schema version.

function upgrade_mysql_server {

# Only check if theres is a database to check

if [ ! -f $INSTALL_COMPLETE_TAG ]; then

return 0

echo "upgrade_mysql_server 1" >> $MYSQL_STARTUP_LOG

# Determine the running version of MYSQL. If we've already executed

# the version check for this version then just return.

local version

silent_query_dbuser_nofail version "SELECT version()"

echo "upgrade_mysql_server 2" >> $MYSQL_STARTUP_LOG

local upgrade_file_flag=$MYSQL_DATA_DIR/upgrade_check_$version.flag

if [ -f $upgrade_file_flag ]; then

return 0

# No succesful upgrade check has been triggered for this version yet. Do it

# now.

upgrade_header=""\

"----------------------------------------------------------------------\n"\

"This file is auto generated by the init_mysql script when the service\n"\

"is starting. The mysql_upgrade command is invoked against our database and\n"\

"checked for failures. Refer to\n"\

"http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html"\

"for more details\n"\

"----------------------------------------------------------------------"

upgrade_error=""\

"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n"\

"One or more errors found in the mysql upgrade log file:\n"\

"$MYSQL_UPGRADE_LOG\n"\

"!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!\n"

upgrade_log "$upgrade_header"

# error and regular output to a temporary file

$MYSQL_BIN_DIR/mysql_upgrade --defaults-file=$UMS_CNF --force > \

$MYSQL_DATA_DIR/mysql_upgrade_temp 2>&1

upgrade_status=$?

echo "upgrade_mysql_server 3" >> $MYSQL_STARTUP_LOG

# place output in logs, enclose in quotes to maintain newlines

upgrade_log "$(cat $MYSQL_DATA_DIR/mysql_upgrade_temp)"

# remove the temporary output file

rm -f $MYSQL_DATA_DIR/mysql_upgrade_temp

if [ $upgrade_status -ne 0 ]; then

upgrade_log "\n$upgrade_error"

ims_log "$upgrade_error"

echo -e "\n$upgrade_error"

$ME stop

abort_start_and_exit

echo "upgrade_mysql_server 4" >> $MYSQL_STARTUP_LOG

# The upgrade check passed, so create the file that says its good

touch $upgrade_file_flag

return 0

}

function upgrade_mycnf {

#some changes may need no intervention at all

#handle cases that need something to be done

handle_logfilesize_change

#always at the end

#now copy over the existing my.cnf as .prev

#so that we do not execute this flow till there is a

#change in the my.cnf

cp $MYSQL_BASE_DIR/my.cnf $MYSQL_BASE_DIR/.my.cnf.prev

}

function handle_logfilesize_change {

#if my.cnf has a new log file size and disk contains a log file of

#different size, delete it. upon db start it will get fixed to

#the right size

#first time the file does not exist

#just do it

ret=0

if [ -f $MYSQL_BASE_DIR/.my.cnf.prev ]; then

diff $MYSQL_BASE_DIR/my.cnf $MYSQL_BASE_DIR/.my.cnf.prev | grep innodb_log_file_size > /dev/null

ret=$?

if [ $ret -eq 0 ]; then

echo -e "\nhandling a change in innodb log file size\n"

if [ -d $MYSQL_PATH_VSMS ]; then

if [ -f $MYSQL_PATH_VSMS/ib_logfile0 ]; then

cp -vfp $MYSQL_PATH_VSMS/ib_logfile* /var/ > /dev/null

rm -f $MYSQL_PATH_VSMS/ib_logfile*

if [ -d $MYSQL_PATH_DEV ]; then

if [ -f $MYSQL_PATH_DEV/ib_logfile0 ]; then

cp -fp $MYSQL_PATH_DEV/ib_logfile* /var/ > /dev/null

rm -f $MYSQL_PATH_DEV/ib_logfile*

}

command=$1

##############################################################################

## MAIN PROCESSING STARTS HERE

##############################################################################

case "$1" in

start)

echo -n "Starting database server... "

umask 0

if (( $UID != 0 ))

then

echo "Must be root to start the database server!!!"

abort_start_and_exit

# check to see if already running.

check_mysql_running $1

if [ $? -eq 1 ]

then

echo "already running."

exit 0

# clear old vsm backup inprogress file we are doing here vs

# anywhere else because $BACKUP_INPROGRESS_FILE is created/used

# by all services backup and mysql is a must for any VSM

# installation. Moreover running mysql is a must for backup

# to succeed.

rm -f $BACKUP_INPROGRESS_FILE > /dev/null 2>&1

# check we have a database directory

if [ ! -d $MYSQL_DATA_DIR ]; then

echo "Mysql database data directory missing. Aborting"

ims_log "Mysql database data directory missing. Aborting"

abort_start_and_exit

##check file permission on mysql data dir

if [ -d $MYSQL_DATA_DIR/UMS ]; then

ls -ld $MYSQL_DATA_DIR/UMS/|awk '{print $3,$4}'|grep -v 'mysql mysql' > /dev/null 2>&1

if [ $? -eq 0 ]; then

chown -HR mysql:mysql $MYSQL_DATA_DIR

chmod 700 $MYSQL_DATA_DIR/UMS/

echo -n "Fixing permissions on UMS mysql directory"

ims_log "Fixing permissions on UMS mysql directory"

ls -l $MYSQL_DATA_DIR/UMS/*|awk '{print $3,$4}'|grep -v 'mysql mysql' > /dev/null 2>&1

if [ $? -eq 0 ]; then

chown -HR mysql:mysql $MYSQL_DATA_DIR

chmod 660 $MYSQL_DATA_DIR/UMS/*

echo -n "Fixing permissions on UMS mysql directory"

ims_log "Fixing permissions on UMS mysql directory"

##check the mysql also

if [ -d $MYSQL_DATA_DIR/mysql ]; then

ls -ld $MYSQL_DATA_DIR/mysql |awk '{print $3,$4}'|grep -v 'mysql root' >/dev/null 2>&1

if [ $? -eq 0 ]; then

echo -n "Fixing permissions on mysql directory"

ims_log 'Fixing directory for mysql to be mysql:root'

chown -HR mysql:mysql $MYSQL_DATA_DIR

chown mysql:root $MYSQL_DATA_DIR/mysql

chmod 700 $MYSQL_DATA_DIR/mysql

ls -la $MYSQL_DATA_DIR/mysql/*|awk '{print $3,$4}'|grep -v 'mysql mysql' >/dev/null 2>&1

if [ $? -eq 0 ]; then

echo -n 'Fixing mysql directory files for mysql to be mysql:mysql'

ims_log 'Fixing mysql directory files for mysql to be mysql:mysql'

chown -HR mysql:mysql $MYSQL_DATA_DIR

chmod 660 $MYSQL_DATA_DIR/mysql/*

# make sure the mysql socket file doesn't exist

rm -f $MYSQL_SOCK_FILE > /dev/null 2>&1

# make sure mysql log files exist and are read/writeable

touch $MYSQL_LOG_FILE

chown mysql $MYSQL_LOG_FILE

chmod 666 $MYSQL_LOG_FILE

touch $SLOW_QUERY_LOG

chown mysql $SLOW_QUERY_LOG

chmod 666 $SLOW_QUERY_LOG

# check to see if the first run and install complete

# flags are out of synch.

if [ ! -f $FIRST_RUN_COMPLETE_TAG ] && [ -f $INSTALL_COMPLETE_TAG ]

then

# this could happen when upgrading from old 7.0 installs

touch $FIRST_RUN_COMPLETE_TAG

echo "11111111" >> $MYSQL_STARTUP_LOG

# install the privileges database if needed

install_privilege_db

echo "222222" >> $MYSQL_STARTUP_LOG

# check and perform if any my.cnf changes needs to be done

upgrade_mycnf

echo "33333" >> $MYSQL_STARTUP_LOG

# start the server

start_db_server

echo "4444" >> $MYSQL_STARTUP_LOG

# complete the first run setup if needed

complete_first_run_setup

echo "555555" >> $MYSQL_STARTUP_LOG

# do any needed mysql server upgrade items

upgrade_mysql_server

echo "6666666" >> $MYSQL_STARTUP_LOG

# check schema version

# passwords should be valid at this point

# this will try and do upgrades between versions

# if an upgrade file is missing then the DB is dropped

# and recreated with the latest schema

createDB=0

dropDB=0

silent_query_dbuser_nofail dbName \

"show databases where \`Database\`='UMS';"

umsUid=""

# if the database exists, then get the schema version

if [ ${#dbName} -ne 0 ]

then

silent_query_dbuser_nofail umsUid \

"select uid from UMS.device where id=1;"

# database exists, check to see if install complete flag is

# present

if [ -f $INSTALL_COMPLETE_TAG ]

then

upgrade_database

dropDB=$?

createDB=$dropDB

else

# install complete flag is not present - recreate the db

dropDB=1

createDB=1

else

createDB=1

if [ $dropDB -eq 1 ]

then

emsg="cannot proceed with mysql upgrade, requires manual intervention"

echo -ne "\n => $emsg ..."

ims_log "$emsg"

echo -n "done."

# get out of the script do not run any further

$ME stop

abort_start_and_exit

# create the DB only if the drop DB issue is not hit

if [ $dropDB -eq 0 ] && [ $createDB -eq 1 ]

then

emsg="creating UMS database"

echo -ne "\n => $emsg ..."

schemaFile=$(ls $UMS_SCHEMA_DIR/ums_schema-*.*.*.sql)

ims_log "$emsg for $schemaFile"

apply_sql_file_nofail $schemaFile

echo -n "done."

touch $INSTALL_COMPLETE_TAG

upgrade_mysql_server

# altering the recording table to set the auto_increment correctly

silent_query_dbuser dummy \

"USE UMS; CALL reset_recording_auto_increment;"

if [ $? -ne 0 ]

then

echo -ne "\n => WARNING: Could not reset recording auto increment. Continuing..."

ims_log " => WARNING: Could not reset recording auto increment. Continuing..."

# if umsUid was found then restore the old ums server id.

if [ ${#umsUid} -ne 0 ] && [ $dropDB -eq 1 ]

then

echo -ne "\n => restoring UMS Server identifier ..."

silent_query_dbuser dummy \

"USE UMS; UPDATE device set uid='$umsUid' where id=1;"

if [ $? -ne 0 ]

then

echo -ne "\n => WARNING: Could not reset server id ($umsUid). Continuing..."

ims_log " => WARNING: Could not reset server id ($umsUid). Continuing..."

# check if an ORA system

if [ -d /opt/cisco/ora ]

then

FAILBACK_STATE=2

if [ -f /usr/BWhttpd/ora/ora.sql ]

then

# apply ora schema changes

$MYSQL_EXE --defaults-file=$DBUSER_CNF < /usr/BWhttpd/ora/ora.sql > /dev/null 2>&1

# set failback state to "in failover"

$QUERY_DBUSER_DEFAULTS "USE UMS; insert into ums_config_parameters values(0, 'UMS_FAILBACK_STATE', 3, '$FAILBACK_STATE');" > /dev/null 2>&1

if [ $? -ne 0 ]

then

$QUERY_DBUSER_DEFAULTS "USE UMS; update ums_config_parameters set param_value='$FAILBACK_STATE' where param_name='UMS_FAILBACK_STATE';"

if [ $? -ne 0 ]

then

echo -ne "\n => WARNING: Could not set failback state. Continuing..."

ims_log " => WARNING: Could not set failback state. Continuing..."

# deleting staging tables

$QUERY_DBUSER_DEFAULTS "USE UMS; delete from port_staging; delete from device_staging;"

if [ $? -ne 0 ]

then

echo -ne "\n => WARNING: Could not clear staging tables. Continuing..."

ims_log " => WARNING: Could not clear staging tables. Continuing..."

# close any open recording events in db

$QUERY_DBUSER_DEFAULTS "USE UMS; update recording_event_history set end_time=now() where end_time > now() and event_type = 1;"

# delete dp devices

$QUERY_DBUSER_DEFAULTS "USE UMS; delete from device where device_type_value=(select enum_value from device_type where name = 'dynamic_proxy');"

#create user cdaf if not exist and grant select and execute permision

#Has to do this as root not dbuser

$QUERY_ROOT_DEFAULTS "USE UMS; GRANT SELECT, EXECUTE on UMS.* to 'cdaf'@'localhost' IDENTIFIED BY PASSWORD '*8C01356C235EF9F12BD586C629F53FAA4B48F4D8';"

if [ $? -ne 0 ]

then

echo -ne "\n => WARNING: Could not create cdaf user. Continuing..."

ims_log " => WARNING: Could not create cdaf user. Continuing..."

#update any stored procedures

if [ -f $UMS_SCHEMA_DIR/ums_storedproc.sql ]; then

ims_log "updating ums stored procedure."

apply_sql_file_nofail $UMS_SCHEMA_DIR/ums_storedproc.sql

echo "done."

;;

stop)

echo -n "Stopping database server... "

if (( $UID != 0 ))

then

echo "Must be root to stop the database server!!!"

exit 1

# check to see if already running.

check_mysql_running $1

if [ $? -eq 1 ]

then

mysqld_pid=`cat $MYSQL_PID_FILE`

# stop the database...

shutdown_mysql "$mysqld_pid"

# 60 secs to shutdown

for((i=0; i<300; i++)); do

sleep 0.2

if [ ! -e $MYSQL_PID_FILE ]

then

i=''

break

done

if [ ! -z "$i" ]

then

echo "Timeout!!"

mysql_log "Timeout waiting for database server to shutdown"

exit 1

echo "done."

else

echo "not running."

exit 0

;;

restart)

## Stop the service and regardless of whether it was

## running or not, start it again.

$ME stop

$ME start

echo "done."

;;

force-reload)

echo "Force-reload service database server, nothing to do."

;;

reload)

echo "Reload service database server, nothing to do."

;;

try-restart)

echo "Try-restart service database server, nothing to do."

;;

status)

echo -n "Checking for database server ... "

check_mysql_running $1

if [ $? -eq 1 ]

then

echo "Running."

else

echo "Down."

;;

fullstatus)

# check to see if running...

check_mysql_running $1

if [ $? -eq 0 ]

then

echo "Database server is not running."

exit 0

$QUERY_DBUSER_DEFAULTS "select version();"

echo ""

$QUERY_DBUSER_DEFAULTS "show status where (Variable_name like 'Bytes%' OR Variable_name like 'Threads%' OR Variable_name like 'Open%' or Variable_name like 'Connections%' or Variable_name like 'Innodb%' or Variable_name like 'Max%' or Variable_name like 'Qcache%' or Variable_name like 'Question%' or Variable_name like 'Slow%' or Variable_name like 'Up%' or Variable_name like 'Table_locks%');"

echo ""

mysqld_pid=`pgrep -f "$MYSQLD_EXE"`

if [ $? -eq 0 ]

then

ps -o pid,vsize,rss,user,group,time,args -p $(pgrep -d, -f "$MYSQLD_EXE") -p $(pgrep -d, -x mysqld_safe)

echo ""

# silent output without columns

dbName=$($SILENT_QUERY_DBUSER_DEFAULTS "show databases where \`Database\`='UMS';")

if [ ${#dbName} -ne 0 ]

then

$QUERY_DBUSER_DEFAULTS "select schema_version from UMS.db_version;"

else

echo "UMS database not installed."

;;

checkstatus)

check_mysql_running $1

if [ $? -eq 0 ]

then

exit 1

;;

echo "Usage: $0 {start|stop|status|restart|fullstatus|checkstatus}"

exit 1

;;

esac

exit 0

**************************************

init_mysql file contain end

Added on Aug 2 2018

1 comment

520 views