Page MenuHomePhabricator

By default, have /usr/local/bin/sql use a read-only account when connecting to mysql
Closed, ResolvedPublic


To avoid accidental destruction of a DB, having the sql script by default use a read-only account on the master server for the db cluster. Passing a --write flag (or something like that) will use the normal wikiadmin account, which can make updates. But make the user intentionally indicate they want to make changes.

Also, it would be nice to add a flag which allows (defaults?) using a slave, for casual lookups that won't be affected by slave lag.

Event Timeline

csteipp raised the priority of this task from to Needs Triage.
csteipp updated the task description. (Show Details)
csteipp added a project: Security-Other.
csteipp added subscribers: csteipp, JAnstee_WMF.

This is particular harmful in the case of sqldump, where with the current defaults it will block all writes (--lock-tables) on the master for its full duration. It should be sent *always* to a slave (as mysqldump is a read-only operation) and using the --single-transaction option of mysqldump.

Sending the queries by default to a slave *may* work because as all slaves are in read_only mode, writes would automatically fail and return an error to the user. However, we should tell all users before changing it (does any script rely on sql to be executed automatically?). A "--master" or "--write" could always be forced even for reads on the master.

As an operator/DBA, I can tell you that more than most of the outages are human caused!

Change 223365 had a related patch set uploaded (by Alex Monk):
sql command: use slave server unless '--write' provided as an option

thcipriani triaged this task as Medium priority.Aug 12 2015, 4:23 PM
thcipriani moved this task from In-progress to Externally Blocked on the Deployments board.

Change 223365 merged by Yuvipanda:
sql command: use slave server unless '--write' provided as an option before DB