Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
mysql wrapper to autologin using settings from ~/.mylogin.cnf, ~/.my.cnf and ~/.ssh/config
#!/bin/bash
# Copyright (c) 2016 Olivier Mengué
# License: Apache 2.0
# The connection settings to use are based on the filename of this script
suffix="$(basename "$0" .sh)"
# Connection through SSH
if [[ "_${suffix:0:9}" = _mysql+ssh ]]; then
suffix="${suffix:5}"
# The SSH settings (User, Host, LocalForward to mysql) must be in ~/.ssh/config
# in a section "Host $0" and you must have a access to the remote using a key (not password).
# Host/port configuration in ~/.mylogin.cnf must point to the forwarding listening port
# Example:
# ~/.my.cnf:
# [mysql+ssh_employees]
# database=employees
# ~/.ssh/config
# Host mysql+ssh_employees
# HostName db-host
# User ssh-user-on-db-host
# PreferedAuthentications publickey
# #LocalForward localhost:1234 /var/run/mysql/mysqld.sock
# LocalForward localhost:1234 localhost:3306
# ~/.mylogin.cnf (do not use 'localhost' for --host or mysql will try to connect using Unix socket instead of TCP and fail)
# mysql_config_editor set --skip-warn --login-path=mysql+ssh_employee --user=root --host 127.0.0.1 --port 1234 --password
local_host=$(mysql_config_editor print --login-path="mysql$suffix" | sed -n 's/^host = //p')
local_port=$(mysql_config_editor print --login-path="mysql$suffix" | sed -n 's/^port = //p')
ssh -n -N -T -o ExitOnForwardFailure=yes "mysql$suffix" &
#echo ssh: $!
sleep 0.5 2>/dev/null || sleep 1
if [[ "$(jobs | wc -l)" -eq 1 ]]; then
mysql --defaults-group-suffix="$suffix" -h ${local_host:-127.0.0.1} -P $local_port "$@"
err=$?
kill %1
exit $err
else
exit 1
fi
# Direct connection using default socket
# mysql_config_editor set --skip-warn --login-path=mysql_employees --user=... --host localhost --password
# Direct connection using TCP
# mysql_config_editor set --skip-warn --login-path=mysql_employees --user=... --host x.x.x.x --port xxx --password
# In ~/.my.cnf:
# [mysql_employees]
# database=employees
elif [[ "_${suffix:0:5}" = _mysql ]]; then
suffix="${suffix:5}"
else
suffix="_$suffix"
fi
exec mysql --prompt="${suffix:1}> " --defaults-group-suffix="$suffix" "$@"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment