How to run queries on live data

You can run queries on live databases in various ways.

Public replicas

For most use cases you can query the public replicas (known as Wiki Replicas) which host a complete and near real-time mirror of all MediaWiki databases in their original MySQL form. Replication lag is usually less than one second from production. You can write arbitrary select queries in SQL (browse tables and schemas at mw:Database layout).

One notable difference from production hosts is that public replicas are redacted to hide sensitive columns and private rows, such as user email addresses and deleted revisions.

The Quarry web interface is the easiest way to access public replicas. You can login with any wiki account, no privileges required. You can freely run long-running queries here as well.

If you join Toolforge (create a developer account, request Toolforge membership, and setup SSH) you can also do this via the command-line:

1. ssh to login.toolforge.org
2. run sql <DBNAME>, for example sql enwiki
3. write your query!

Learn more at Help:Toolforge/Database.

For other ways to access public replicas (including Jupyter notebooks and Superset), refer to Help:Wiki Replicas.

Production replicas

If public replicas or XML dumps are not suitable for your use case, you can query production replicas. These require production access which is granted to SREs, MediaWiki deployers, and trusted volunteers who have signed an NDA and acknowedged Server Access Responsibilities.

The below steps will connect you to a live and unredacted MediaWiki database replicas. The command selects a random replica from a MariaDB production cluster. This replica will be under load from real users on MediaWiki web servers.

For long-running queries on unredacted data, use the Analytics replicas instead. Slow or long-running queries should not be run in production and may be stopped a database administrator.

Follow these steps:

1. ssh to a deployment server
2. run sql <DBNAME>, for example sql enwiki.
3. write your query

Querying a specific production host

See also: Debugging in production#Debugging databases

If you need to debug a specific database host in production, follow the following steps:

1. ssh to a deployment server
2. Connect to specific replica by specifying its hostname with the syntax sql <DBNAME> --host <HOSTNAME>, for example sql testwiki --host db1001
3. run your query

To check the current status of a database, you can click on its server name in the dbtree chart or run the query show engine innodb status\G.

Production master database

Avoid running queries on the master databases whenever possible.

Queries here are extremely risky in the potential damage or corruption they may cause, and may bog down performance of the live websites.

1. Review your queries carefully. Write queries should exclusively match by a primary key and not with potentially slow or unindexed conditions.
2. Verify "WHERE" conditions by running them as a select query on a replica first and confirm that they return the intended rows.
3. Inform other people by sending a message to the #wikimedia-operations channel on IRC (Meta-Wiki guide, Office Wiki guide).
   • Write a one-line summary that starts with the !log prefix and includes any Phabricator task references, then send it to the same #wikimedia-operations channel. For example:

     !log Fix a corrupt testwiki revision per T00000, `UPDATE content_example SET example_address="es:DB://cluster1/example" WHERE example_id=1234 LIMIT 1`

     This prefix instructs a bot to record your action in the Server Admin Log. This enables people to discover and review what we do, and to find it in the far future when we may be wondering how or why something changed. Think of this as the equivalent to Special:Log for on-wiki administrative actions.
4. One row. If planning to edit multiple rows, consider first updating one row only with LIMIT 1 and fully verify in your browser that it has the intended on-wiki effect.
5. If you absolutely must run a query on a master database, follow these instructions:
   1. ssh to a deployment server
   2. Connect to the master database server with the syntax sql --write <DBNAME>, for example sql --write testwiki
   3. run your query

Analytics replicas

Main article: Data Platform/Systems/MediaWiki replicas

follow the instructions at SRE/Production access#Setting up your access to set up your SSH, then type:

1. ssh to a stat machine, e.g. ssh stat1011.eqiad.wmnet
2. pick a cluster and port number from the analytics replica list and connect, e.g. mysql -h s3-analytics-replica.eqiad.wmnet -P 3313 -A
3. select the right database, e.g. USE testwiki;
4. run your query