Page MenuHomePhabricator

Separate host lookup from the sql shell script
Open, MediumPublic

Description

On the terbium host there is a shell script called sql, which conveniently opens the mysql prompt on the database for the needed project, for example sql frwikiquote.

This script does two things (at least): Looks up the host, and then actually runs mysql on that host.

Having a way to look up the host without running mysql would be useful, too. For example, if I need to connect to a host from another script in a way that doesn't involve running the mysql command as such.

Details

Related Gerrit Patches:
operations/puppet : productionSplit sql to sql and sqlhost
operations/puppet : productionamire80 .bashrc, add alias for sql host lookup

Event Timeline

Amire80 created this task.Jul 25 2016, 11:52 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 25 2016, 11:52 AM
Krenair edited projects, added Operations; removed DBA.Jul 25 2016, 3:50 PM
Dzahn added a project: DBA.Jul 27 2016, 2:37 AM
Dzahn added a subscriber: Dzahn.
[terbium:~] $ which sql
/usr/local/bin/sql
[terbium:~] $ cat /usr/local/bin/sql
#!/bin/bash

# This file is managed by Puppet (modules/scap/files/sql).
Dzahn added a comment.Jul 27 2016, 2:40 AM
# Look up MySQL host to connect to.  For centralauth the host cannot
# be determined this way, so we need to use fawiki instead as it is
# located on the same server in both production and labs.
# wikishared is hosted on x1 (i.e. without any local wikis), just use
# meta to lookup the password.
if [ "$db" = "centralauth" ]; then
    lookupdb="fawiki"
elif [ "$db" = "wikishared" ]; then
    lookupdb="metawiki"
else
    lookupdb="$db"
fi

if [ "$db" = "wikishared" ]; then
    lbcode="\$lb = wfGetLBFactory()->getExternalLB('extension1');"
else
    lbcode="\$lb = wfGetLB();"
fi
host=`echo $lbcode $hostcode | /usr/local/bin/mwscript eval.php --wiki="$lookupdb"`

Change 301326 had a related patch set uploaded (by Dzahn):
amire80 .bashrc, add alias for sql host lookup

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

Dzahn added a comment.Jul 27 2016, 3:08 AM

@Amire80 on terbium, in your home dir in .bashrc , add this code:

https://gerrit.wikimedia.org/r/#/c/301326/1/modules/admin/files/home/amire80/.bashrc

then "source .bashrc" and then just try "sqlhost frwikiquote".

If we merge that it gets automatically added to all your home dirs in the future.

Dzahn added a comment.Jul 27 2016, 3:09 AM

[terbium:~] $ vi .bashrc
[terbium:~] $ source .bashrc
dzahn@terbium:~$ sqlhost dewiki
db1092

Dzahn triaged this task as Medium priority.Jul 27 2016, 3:10 AM

Thanks, but the point is that I already made such tricks for my own account, and it would be useful to have the same for other accounts. I have several scripts that I run there regularly, and I want them to be usable by other people in my team and quite possibly others. See https://gerrit.wikimedia.org/r/#/c/300862/ .

if I need to connect to a host from another script in a way that doesn't involve running the mysql command as such.

I have several scripts that I run there regularly

Could you give an example of scripts that you run outside of a Mediawiki/LoadBalancer context?

if I need to connect to a host from another script in a way that doesn't involve running the mysql command as such.
I have several scripts that I run there regularly

Could you give an example of scripts that you run outside of a Mediawiki/LoadBalancer context?

https://gerrit.wikimedia.org/r/#/c/282312/3/bash/published_despite_errors.py

https://gerrit.wikimedia.org/r/#/c/282312/3/bash/published_despite_errors.py

That looks like something you shouldn't run from terbium (but more analytics-research-y), but from one of the stats* hosts, as the research user, and use analytics-slave or analytics-store hosts, always (which contains all the shards(*) + eventlogging, so no need to "search" wikis.

It is important not to run long-running queries on regular hosts because it can affect the performance or regular queries (in some cases they could lead to outages because it interaction with schema changes). It is also important to separate mysql users (wikiadmin for jobs and maintanance, research for analytics and research). When I mean analytics here, I mean analytics done by all departments, not only the analytics team.

If you have reasons why you cannot run queries there (there could be legitimate reasons, but then they should have some restrictions) or if you need advice about the best way to do something or request access, please, I will be glad to help, just ping me or send me an email.

Answer your original question, these are the easy dns entries that you should probably be using, which makes things easier, and generally points to the right hosts on each occasion:

s1-analytics-slave  5M  IN CNAME    db1047.eqiad.wmnet.
s2-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
s3-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
s4-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
s5-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
s6-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
s7-analytics-slave  5M  IN CNAME    dbstore1002.eqiad.wmnet.
x1-analytics-slave  5M  IN CNAME    db1031.eqiad.wmnet.

Change 301326 abandoned by Dzahn:
amire80 .bashrc, add alias for sql host lookup

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

Change 300862 abandoned by Amire80:
Split sql to sql and sqlhost

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

1978Gage2001 moved this task from Triage to In progress on the DBA board.Dec 11 2017, 9:45 AM
1978Gage2001 moved this task from Triage to In progress on the DBA board.
Marostegui moved this task from In progress to Triage on the DBA board.Dec 11 2017, 11:01 AM
Dzahn removed a subscriber: Dzahn.Dec 11 2017, 4:50 PM