Skip to content

Instantly share code, notes, and snippets.

@wxiaoguang wxiaoguang/mysql-ctl.sh
Last active May 31, 2019

Embed
What would you like to do?
A mysql & mariadb server helper (use downloaded generic binary package). You can run multi instances on one server, each instance uses their own data & log directory.
#!/bin/bash
# how it works:
#
# put mysql.cnf and mysql.env in a directory for an instance, cd to this directory, run `mysql-ctl.sh ...`
# or run `MYSQL_INSTANCE_ROOT=the_directory mysql-ctl.sh ...`
#
# mysql.cnf keeps all configurations except directory-related things (eg: do not set data or log dir in mysql.cnf)
# the mysql.cnf and directory-related configs will be automatically merged into the MYSQL_INSTANCE_CONFIG_FINAL file
#
# mysql.env contains MYSQL_PACKAGE_ROOT variable, which points to your mysql installation directory ($MYSQL_PACKAGE_ROOT/bin/mysql and $MYSQL_PACKAGE_ROOT/share should exists)
#
# then you can use mysql-ctl.sh to start/stop the instance, or use `mysql-ctl.sh client` to run mysql client cli, etc.
# currently support commands: {start|stop|restart|status|client|admin|dump}
test "$MYSQL_INSTANCE_ROOT" == "" && MYSQL_INSTANCE_ROOT='.'
MYSQL_INSTANCE_ROOT=$(realpath "$MYSQL_INSTANCE_ROOT")
if [[ ! -f "$MYSQL_INSTANCE_ROOT/mysql.cnf" ]]; then
echo "Invalid MySQL instance root: '$MYSQL_INSTANCE_ROOT'. No 'mysql.cnf' found."
exit 1
fi
if [[ ! -f "$MYSQL_INSTANCE_ROOT/mysql.env" ]]; then
echo "Can not find '$MYSQL_INSTANCE_ROOT/mysql.env'."
exit 1
fi
source "$MYSQL_INSTANCE_ROOT/mysql.env"
test ! -f "$MYSQL_PACKAGE_ROOT/bin/mysqld_safe" && (echo "Can not find bin/mysqld_safe"; exit 1)
test ! -f "$MYSQL_PACKAGE_ROOT/bin/mysql" && (echo "Can not find bin/mysql"; exit 1)
MYSQL_INSTANCE_CONFIG_FINAL="$MYSQL_INSTANCE_ROOT/.my.cnf"
prepare() {
dpkg -l libaio1 > /dev/null && has_aio=y
dpkg -l libnuma1 > /dev/null && has_numa=y
if [[ "$has_aio" != 'y' || "$has_numa" != 'y' ]]; then
echo "Installing libaio and libnuma ..."
apt update
apt install -y libaio1 libnuma1
fi
id -g mysql > /dev/null || (echo "Add group mysql ..."; groupadd mysql)
id -u mysql > /dev/null || (echo "Add user mysql ..."; useradd -r -g mysql mysql)
}
find_mysqld_pid() {
pgrep -f "$MYSQL_PACKAGE_ROOT/bin/mysqld .*=$MYSQL_INSTANCE_ROOT"
}
start() {
pid=$(find_mysqld_pid)
if [[ "$pid" != "" ]]; then
echo "Already started: pid=$pid"
exit 0
fi
echo "MYSQL_INSTANCE_ROOT=$MYSQL_INSTANCE_ROOT"
echo "MYSQL_PACKAGE_ROOT=$MYSQL_PACKAGE_ROOT"
prepare
mkdir -p "$MYSQL_INSTANCE_ROOT/tmp"
mkdir -p "$MYSQL_INSTANCE_ROOT/log"
mkdir -p "$MYSQL_INSTANCE_ROOT/run"
find "$MYSQL_INSTANCE_ROOT" -not -user mysql -exec chown mysql:mysql "{}" \;
cp "$MYSQL_INSTANCE_ROOT/mysql.cnf" "$MYSQL_INSTANCE_CONFIG_FINAL"
mysqld_socket="$MYSQL_INSTANCE_ROOT/run/mysqld.sock"
sed '/^[[]mysqld[]]$/r'<(
cat << EOL
basedir = $MYSQL_PACKAGE_ROOT
lc-messages-dir = $MYSQL_PACKAGE_ROOT/share
pid-file = $MYSQL_INSTANCE_ROOT/run/mysqld.pid
socket = $mysqld_socket
datadir = $MYSQL_INSTANCE_ROOT/data
log-error = $MYSQL_INSTANCE_ROOT/log/mysqld.err
slow-query-log-file = $MYSQL_INSTANCE_ROOT/log/slow.log
general_log_file = $MYSQL_INSTANCE_ROOT/log/query.log
log_bin = $MYSQL_INSTANCE_ROOT/binlog/mysql-bin.log
tmpdir = $MYSQL_INSTANCE_ROOT/tmp
EOL
) -i -- "$MYSQL_INSTANCE_CONFIG_FINAL"
sed '/^[[]client[]]$/r'<(
cat << EOL
socket = $mysqld_socket
EOL
) -i -- "$MYSQL_INSTANCE_CONFIG_FINAL"
echo -n "Starting mysqld ."
"$MYSQL_PACKAGE_ROOT/bin/mysqld_safe" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" &
result=''
for ((i=0;i<30;i++)); do
if [[ -e "$mysqld_socket" ]]; then
result="done"
break
fi
pid=$(find_mysqld_pid)
if [[ "$pid" == "" && "$i" -ge 5 ]]; then
result="failed"
break
fi
echo -n "."
sleep 1
done
echo " ${result}."
}
stop() {
pid=$(find_mysqld_pid)
if [[ "$pid" == "" ]]; then
echo "No mysqld to kill."
return
fi
echo -n "Killing mysqld pid=$pid ."
kill "$pid"
while true; do
pid=$(find_mysqld_pid)
if [[ "$pid" == "" ]]; then
echo " done."
break
fi
echo -n "."
sleep 1
done
}
status() {
pid=$(find_mysqld_pid)
if [[ "$pid" != "" ]]; then
echo "running. pid=$pid"
else
echo "stopped"
fi
}
client() {
shift
exec "$MYSQL_PACKAGE_ROOT/bin/mysql" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" "$@"
}
admin() {
shift
exec "$MYSQL_PACKAGE_ROOT/bin/mysqladmin" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" "$@"
}
dump() {
shift
exec "$MYSQL_PACKAGE_ROOT/bin/mysqldump" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" "$@"
}
main() {
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
status)
status
;;
client)
client "$@"
;;
admin)
admin "$@"
;;
dump)
dump "$@"
;;
*)
echo "Usage: $0 {start|stop|restart|status|client|admin|dump}"
exit 1
;;
esac
}
main "$@"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.