Skip to content

Instantly share code, notes, and snippets.

@wxiaoguang wxiaoguang/mysql-ctl.sh
Last active Nov 16, 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}
# and intergated with systemd: service {install|start|stop|status|...}
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_TYPE=mysql
test -f "$MYSQL_PACKAGE_ROOT/bin/mariadb" && MYSQL_TYPE=mariadb
MYSQL_INSTANCE_CONFIG_FINAL="$MYSQL_INSTANCE_ROOT/.my.cnf"
MYSQL_INSTANCE_NAME=$(basename "$MYSQL_INSTANCE_ROOT")
MYSQL_SERVICE_NAME="fv-${MYSQL_TYPE}-${MYSQL_INSTANCE_NAME}"
FV_CTL_SCRIPT=$(realpath "$0")
prepare() {
if [[ -f /etc/debian_version ]]; then
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
fi
if [[ -f /etc/redhat-release ]]; then
test -f /usr/lib64/libaio.so.1 && has_aio=y
test -f /usr/lib64/libnuma.so.1 && has_numa=y
if [[ "$has_aio" != 'y' || "$has_numa" != 'y' ]]; then
yum install libaio numactl
fi
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() {
cmd="$1"
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"
mkdir -p "$MYSQL_INSTANCE_ROOT/binlog"
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"
if ! grep "^[[]client[]]" "$MYSQL_INSTANCE_CONFIG_FINAL" > /dev/null; then
echo "[client]" >> "$MYSQL_INSTANCE_CONFIG_FINAL"
fi
sed '/^[[]client[]]$/r'<(
cat << EOL
socket = $mysqld_socket
EOL
) -i -- "$MYSQL_INSTANCE_CONFIG_FINAL"
if [[ ! -d "$MYSQL_INSTANCE_ROOT/data" ]]; then
echo "No data directory found. Init first ..."
mkdir -p "$MYSQL_INSTANCE_ROOT/data"
chown mysql:mysql "$MYSQL_INSTANCE_ROOT/data"
if [[ "$MYSQL_TYPE" == "mariadb" ]]; then
echo "MariaDB: https://mariadb.com/kb/en/library/mysql_install_db/"
echo "$MYSQL_PACKAGE_ROOT/scripts/mysql_install_db --defaults-file='$MYSQL_INSTANCE_CONFIG_FINAL'"
"$MYSQL_PACKAGE_ROOT/scripts/mysql_install_db" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL"
else
echo "MySQL: (>=5.7) https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html"
echo "$MYSQL_PACKAGE_ROOT/bin/mysqld --defaults-file='$MYSQL_INSTANCE_CONFIG_FINAL' --initialize-insecure"
"$MYSQL_PACKAGE_ROOT/bin/mysqld" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" --initialize-insecure
fi
ret="$?"
if [[ "$ret" != "0" ]]; then
echo "Failed to init database"
exit 1
fi
fi
if [[ "$cmd" == "start" || "$cmd" == "" ]]; then
echo -n "Starting mysqld as daemon ."
"$MYSQL_PACKAGE_ROOT/bin/mysqld_safe" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL" &
elif [[ "$cmd" == "serve" ]]; then
echo "Starting mysqld to serve ..."
exec "$MYSQL_PACKAGE_ROOT/bin/mysqld_safe" --defaults-file="$MYSQL_INSTANCE_CONFIG_FINAL"
else
echo "unknown start option: $1"
exit 1
fi
result=''
for ((i=0;i<30;i++)); do
sleep 1
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 "."
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" "$@"
}
service_install() {
suc_local="$MYSQL_INSTANCE_ROOT/service/$MYSQL_SERVICE_NAME.service"
mkdir -p "$(dirname "$suc_local")"
systemctl disable "$MYSQL_INSTANCE_ROOT"/service/*.service 2>/dev/null
rm "$MYSQL_INSTANCE_ROOT"/service/*.service 2>/dev/null
cat <<EOT > "$suc_local"
[Unit]
Description=FV Database $MYSQL_TYPE $MYSQL_INSTANCE_NAME
After=network.target
# TODO: "Before=bar.service" means this before "bar.service"
[Service]
Type=simple
Restart=no
User=root
Environment="MYSQL_INSTANCE_ROOT=${MYSQL_INSTANCE_ROOT}"
ExecStart="$FV_CTL_SCRIPT" serve
[Install]
WantedBy=multi-user.target
EOT
systemctl enable "$MYSQL_INSTANCE_ROOT"/service/*.service
systemctl status "$MYSQL_SERVICE_NAME"
}
service_cmd() {
if [[ "$1" == "install" ]]; then
service_install
return
fi
systemctl $1 $MYSQL_SERVICE_NAME
}
main() {
case "$1" in
start|serve)
start "$@"
;;
stop)
stop
;;
restart)
stop
start
;;
status)
status
;;
client)
client "$@"
;;
admin)
admin "$@"
;;
dump)
dump "$@"
;;
service)
shift
service_cmd "$@"
;;
*)
echo "Usage: $0 {start|stop|restart|status|client|admin|dump}"
echo " or: $0 service {install|start|restart|stop|status|...}"
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.