You are browsing a read-only backup copy of Wikitech. The primary site can be found at wikitech.wikimedia.org

Mysql.py: Difference between revisions

From Wikitech-static
Jump to navigation Jump to search
imported>Jcrespo
(+where it is installed)
 
imported>Volans
m (Use the CuminHosts template instead of hardcoding the hostnames)
 
(One intermediate revision by one other user not shown)
Line 1: Line 1:
'''mysql.py''' is a wrapper around the mysql command line client from mariadb, which is tuned for WMF production admin convenience. It is intended primarily for interactive usage, so it defaults to WMF configuration and saves typing. It requires sudo or root user privileges, to read <code>/root</code>'s config It is backwards compatible with the mysql command line client, but has some additional configuration changes and shortcuts:
'''mysql.py''' is a wrapper around the mysql command line client from mariadb, which is tuned for WMF production admin convenience. It is intended primarily for interactive usage, so it defaults to WMF configuration and saves typing. It requires sudo or root user privileges, to read <code>/root</code>'s config. It is backwards compatible with the mysql command line client, but has some additional configuration changes and shortcuts:


* It will try to force the use of '''TLS''' for any connections outside of localhost (for security), using the right CA (independently of the <code>/etc/</code> configuration.
* It will try to force the use of '''TLS''' for any connections outside of localhost (for security), using the right CA (independently of the <code>/etc/</code> configuration.
Line 7: Line 7:
* It will '''translate sections names into ports''', based on the <code>/etc/wmfmariadb/section_ports.csv</code> file, generated from puppet's hiera: So if one writes <code>mysql.py -h db1115:s5</code> it will be as if one writes <code>mysql -h db1115.eqiad.wmnet -P 3315</code>
* It will '''translate sections names into ports''', based on the <code>/etc/wmfmariadb/section_ports.csv</code> file, generated from puppet's hiera: So if one writes <code>mysql.py -h db1115:s5</code> it will be as if one writes <code>mysql -h db1115.eqiad.wmnet -P 3315</code>


<code>mysql.py</code> is available on cumin hosts (mysql management profile) as it requires private account information access to execute database server admin account queries. Those are <code>cumin1001</code> and <code>cumin2001</code> as of the writing of this lines.
<code>mysql.py</code> is available on cumin hosts (mysql management profile) as it requires private account information access to execute database server admin account queries: {{CuminHosts}}.


== Example usage ==
== Example usage ==

Latest revision as of 11:58, 11 March 2022

mysql.py is a wrapper around the mysql command line client from mariadb, which is tuned for WMF production admin convenience. It is intended primarily for interactive usage, so it defaults to WMF configuration and saves typing. It requires sudo or root user privileges, to read /root's config. It is backwards compatible with the mysql command line client, but has some additional configuration changes and shortcuts:

  • It will try to force the use of TLS for any connections outside of localhost (for security), using the right CA (independently of the /etc/ configuration.
  • It will use current user's .my.cnf configuration, so typically you want to run it with sudo meaning it will try to connect as root and with the appropriate root password, depending on the role (mediawiki db vs wikireplicas, etc.)
  • It will try to complete the fully qualified name of the host, with just the hostname: So mysql.py -h db1115 will be interpreted as if one had written mysql -h db1115.eqiad.wmnet
  • It will make the port easier to write, so if one writes mysql.py -h db1115:3315 it will be as if one writes mysql -h db1115.eqiad.wmnet -P 3315
  • It will translate sections names into ports, based on the /etc/wmfmariadb/section_ports.csv file, generated from puppet's hiera: So if one writes mysql.py -h db1115:s5 it will be as if one writes mysql -h db1115.eqiad.wmnet -P 3315

mysql.py is available on cumin hosts (mysql management profile) as it requires private account information access to execute database server admin account queries: cumin1001.eqiad.wmnet, cumin2002.codfw.wmnet.

Example usage

Connect quickly to a server:

mysql.py -h db1115

Connect quickly to a multinstance server:

mysql.py -h db1115:3315

Alternative syntax:

mysql.py -h db1115:s5

All other mysql parameters can still be used, they are just passed on:

mysql.py -A -h labsdb1009 enwiki_p

Faster way to query 1 time things:

mysql.py -h db1115 -u dump -e "SELECT @@read_only" -p

Combined with lists of host:section or host:port, it can do batch requests:

./section s5 | while read host; do mysql.py -h $host -e "SELECT @@hostname, @@port"; done

Development

Its code can be found on the WMFMariaDBpy repo.