GeezerVison

Rotate your credentials

tools.dcaro-test14@tools-bastion-15:~$ touch ~/replica.my.cnf.rotate

tools.dcaro-test14@tools-bastion-15:~$ ls -la replica.my.cnf 
-r--r----- 1 tools.dcaro-test14 tools.dcaro-test14 71 Oct 19 16:28 replica.my.cnf

# # Connect to toolsdb
tools.dcaro-test14@tools-bastion-15:~$ sql local
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1711384314
Server version: 10.6.22-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.

MariaDB [(none)]> 


# # Connect to the replicas
tools.dcaro-test14@tools-bastion-15:~$ sql enwiki_p
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37799619
Server version: 10.11.18-MariaDB 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.

MariaDB [enwiki_p]>

Bash

-- 2> /dev/null; date; echo '
/* Bash/SQL compatible test structure
 *
 * Run time: ? <SLOW_OK>
 */
SELECT 1
;-- ' | mariadb -ch tools.db.svc.wikimedia.cloud enwiki_p > ~/query_results-enwiki; date;

C

#include <my_global.h>
#include <mysql.h>

...

 char *host = "tools.db.svc.wikimedia.cloud";
 MYSQL *conn = mysql_init(NULL);

 mysql_options(conn, MYSQL_READ_DEFAULT_GROUP, "client");
 if (mysql_real_connect(conn, host, NULL, NULL, NULL, 0, NULL, 0) == NULL) {
    printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
    ...
 }

Perl

use User::pwent;
use DBI;

my $database = "enwiki_p";
my $host = "tools.db.svc.wikimedia.cloud";

my $dbh = DBI->connect(
    "DBI:mysql:database=$database;host=$host;"
    . "mysql_read_default_file=" . getpwuid($<)->dir . "/replica.my.cnf",
    undef, undef) or die "Error: $DBI::err, $DBI::errstr";

Python

import configparser
import pathlib
import pymysql
import pymysql.cursors

replica = pathlib.Path.home().joinpath("replica.my.cnf")
config = configparser.ConfigParser()
config.read_string(replica.read_text())
connection = pymysql.connections.Connection(
    host="commonswiki.analytics.db.svc.wikimedia.cloud",
    database="commonswiki_p",
    user=config.get("client", "user"),
    password=config.get("client", "password"),
    cursorclass=pymysql.cursors.DictCursor,
)

with connection.cursor() as cur:
    cur.execute(query)  # Or something....
connection.close()

import toolforge
conn = toolforge.connect('enwiki') # You can also use "enwiki_p"
# conn is a pymysql.connection object.
with conn.cursor() as cur:
    cur.execute(query)  # Or something....

Python: Django

import configparser
import os

HOME=os.environ.get('HOME') #get environment variable $HOME

replica_path=HOME + '/replica.my.cnf'
if os.path.exists(replica_path):          #check that the file is found
    config = configparser.ConfigParser()
    config.read(replica_path)
else:
    print('replica.my.cnf file not found')

DATABASES = {
    'default': {
         'ENGINE': 'django.db.backends.mysql',
         'NAME': 's12345__mydbname',                                 
         'USER': config['client']['user'],                          #for instance "s12345"
         'PASSWORD': config['client']['password'],
         'HOST': 'tools.db.svc.wikimedia.cloud',
         'PORT': '',
     }
}

PHP (using PDO)

<?php
$ts_pw = posix_getpwuid(posix_getuid());
$ts_mycnf = parse_ini_file($ts_pw['dir'] . "/replica.my.cnf");
$db = new PDO("mysql:host=enwiki.analytics.db.svc.wikimedia.cloud;dbname=enwiki_p", $ts_mycnf['user'], $ts_mycnf['password']);
unset($ts_mycnf, $ts_pw);

$q = $db->prepare('select * from page where page_id = :id');
$q->execute(array(':id' => 843020));
print_r($q->fetchAll());
?>

PHP (using MySQLi)

<?php
$ts_pw = posix_getpwuid(posix_getuid());
$ts_mycnf = parse_ini_file($ts_pw['dir'] . "/replica.my.cnf");
$mysqli = new mysqli('enwiki.analytics.db.svc.wikimedia.cloud', $ts_mycnf['user'], $ts_mycnf['password'], 'enwiki_p');
unset($ts_mycnf, $ts_pw);

$stmt = $mysqli->prepare('select * from page where page_id = ?');
$id = 843020;
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
print_r($result->fetch_all(MYSQLI_BOTH));
?>

Java

Class.forName("com.mysql.jdbc.Driver").newInstance();
Properties mycnf = new Properties();
mycnf.load(new FileInputStream(System.getProperty("user.home")+"/replica.my.cnf"));
String password = mycnf.getProperty("password");
password=password.substring((password.startsWith("\""))?1:0, password.length()-((password.startsWith("\""))?1:0));
mycnf.put("password", password);
mycnf.put("useOldUTF8Behavior", "true");
mycnf.put("useUnicode", "true");
mycnf.put("characterEncoding", "UTF-8");
mycnf.put("connectionCollation", "utf8_general_ci");
String url = "jdbc:mysql://tools.db.svc.wikimedia.cloud:3306/enwiki_p";
Connection conn = DriverManager.getConnection(url, mycnf);

Node.js

const mysql = require('mysql2/promise');
async function sample() {
  const connection = await mysql.createConnection({
    host: 'tools.db.svc.wikimedia.cloud', 
    port: 3306,
    database: 's12345__mydbname', 
    user: 's12345', 
    password: ''
  });
  const [rows, fields] = await connection.execute('SELECT * FROM table WHERE name = ? AND age > ?', ['Morty', 14]);
  for (let row in rows) console.log(row);
}