Page MenuHomePhabricator

Create mysql alias for multi-instance DB servers
Closed, ResolvedPublic

Description

If logged locally to a mult-instance host, it is not easy to guess how to connect to each MySQL instance.
Right now MOTD shows which instances are running:

$ ssh db1099.eqiad.wmnet
Linux db1099 4.19.0-16-amd64 #1 SMP Debian 4.19.181-1 (2021-03-19) x86_64
Debian GNU/Linux 10 (buster)
db1099 is a Core multi-instance server (mariadb::core)
DB section s1
DB section s8
The last Puppet run was at Mon Sep 20 05:27:11 UTC 2021 (0 minutes ago).
Last puppet commit: (cdb54e2cc5) Sukhbir Singh - durum: add motd ASCII art
Debian GNU/Linux 10 auto-installed on Mon Aug 17 05:39:11 UTC 2020.
Last login: Thu Sep  2 11:56:29 2021 from 208.80.155.110

We should either create an alias like:
mysql-s2 sort of type
And/or include how to connect on the MOTD with something like:

DB section s1: sudo mysql -S /run/mysqld/mysqld.s1.sock
DB section s8: sudo mysql -S /run/mysqld/mysqld.s8.sock

Event Timeline

Marostegui moved this task from Triage to Refine on the DBA board.

I would prefer an alias personally just so it shows up in tab complete and during an incident I personally am more likely to skip reading the MOTD (then again, if I know that's where connection info is, I'll probably read it!).

I have a bash function:

function my() {
    local instance="${1}"

    local socks
    socks=($(ls /run/mysqld/mysqld*sock)) || return
    local num_socks=${#socks[@]}

    local socket
    if [ -n "$instance" ]; then
        socket=/run/mysqld/mysqld.${instance}.sock
    elif [ $num_socks -eq 1 ]; then
        socket=${socks[0]}
    else
        local choice
        choice=$(whiptail --title "my()" --menu "Select mysqld socket:" 16 50 $num_socks $(ls /run/mysqld/mysqld*sock | nl -v0) 3>&2 2>&1 1>&3) || return
        socket=${socks[$choice]} || return
    fi

    local section=$(echo $socket | sed -nE 's@.*mysqld\.([^.]+)\.sock@\1@p')

    sudo mysql --disable-auto-rehash --socket="${socket}" --user=root --prompt="\u@$(hostname):${section:-default}[\d]> " --pager="grcat /etc/mysql/grcat.config | less -RSFXin"
}

You can either run, say, my s8:

kormat@db1099:~(0:0)$ my s8
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 638220503
Server version: 10.4.21-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@db1099:s8[(none)]>

or you can just run my:

image.png (351×475 px, 12 KB)

If there's only a single instance, my connects to it automatically.

This is a good one! Maybe we can add that alias to all the hosts and change MOTD to specify that and I would consider this task resolved

Change 731339 had a related patch set uploaded (by Kormat; author: Kormat):

[operations/puppet@production] mariadb: Add per-section alias

https://gerrit.wikimedia.org/r/731339

I decided the bash function approach was overkill, went with something much simpler instead.

Thanks for getting to this
Let me know when you want me to take a look at the patch.

Thanks for getting to this
Let me know when you want me to take a look at the patch.

Patch now available for review :)

Change 731339 merged by Kormat:

[operations/puppet@production] mariadb: Add per-section alias

https://gerrit.wikimedia.org/r/731339

Nice!

$ ssh db1099.eqiad.wmnet
Linux db1099 4.19.0-16-amd64 #1 SMP Debian 4.19.181-1 (2021-03-19) x86_64
Debian GNU/Linux 10 (buster)
db1099 is a Core multi-instance server (mariadb::core)
DB section s1 (alias: mysql.s1)
DB section s8 (alias: mysql.s8)
The last Puppet run was at Mon Oct 18 12:21:53 UTC 2021 (0 minutes ago).

Thanks! One note is that sudo mysql.s1 doesn't work, you need to do a plain mysql.s1, while on a single-instance host you need to sudo mysql. I think that's fine, but just wanted to make sure that inconsistency was known/intentional.

Change 731977 had a related patch set uploaded (by Kormat; author: Kormat):

[operations/puppet@production] mariadb: Use scripts instead of aliases for mysql.<section>

https://gerrit.wikimedia.org/r/731977

Change 731977 merged by Kormat:

[operations/puppet@production] mariadb: Use scripts instead of aliases for mysql.<section>

https://gerrit.wikimedia.org/r/731977

Change 731984 had a related patch set uploaded (by Kormat; author: Kormat):

[operations/puppet@production] mariadb: Fix quoting in prompt for mysql.<section>

https://gerrit.wikimedia.org/r/731984

Change 731984 merged by Kormat:

[operations/puppet@production] mariadb: Fix quoting in prompt for mysql.<section>

https://gerrit.wikimedia.org/r/731984

Thanks! One note is that sudo mysql.s1 doesn't work, you need to do a plain mysql.s1, while on a single-instance host you need to sudo mysql. I think that's fine, but just wanted to make sure that inconsistency was known/intentional.

Good catch. Wasn't intentional, just side-effect of using bash aliases. But fear not, it's now fixed, by switching to bash scripts instead! :)

Change 732302 had a related patch set uploaded (by Kormat; author: Kormat):

[operations/puppet@production] mariadb: Remove absented alias file

https://gerrit.wikimedia.org/r/732302

Change 732302 merged by Kormat:

[operations/puppet@production] mariadb: Remove absented alias file

https://gerrit.wikimedia.org/r/732302