Page MenuHomePhabricator

Setup database for dmarc service
Closed, ResolvedPublic

Description

Setup misc database (probably m1) for herron to setup dmarc service:

  • Create database on the master - rddmarc
  • Puppetize grants - rddmarc (or other name, no preference)
  • Puppetize private account details
  • Deploy grants
  • Add database to weekly backups, if needed

Details

Related Gerrit Patches:

Event Timeline

jcrespo created this task.Jul 10 2017, 2:53 PM
jcrespo updated the task description. (Show Details)Jul 10 2017, 3:24 PM
jcrespo updated the task description. (Show Details)
jcrespo assigned this task to herron.EditedJul 11 2017, 7:46 AM

So we need: db name, account name, grants needed, ips/dns of the origin of the connections, estimated size/traffic of the database, if possible.

herron updated the task description. (Show Details)Jul 11 2017, 7:41 PM
herron moved this task from Backlog to Up Next on the Mail board.
jcrespo moved this task from Triage to In progress on the DBA board.Jul 13 2017, 2:53 PM

Change 365035 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] [WIP]mariadb: Add grants for rddmark to m1

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

The mysql client IP addresses are:

diadem     208.80.153.17 
dysprosium 208.80.154.24

Change 367702 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[labs/private@master] rddmarc: Add fake dbpassword for the database application

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

Change 367702 merged by Jcrespo:
[labs/private@master] rddmarc: Add fake dbpassword for the database application

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

Change 365035 merged by Jcrespo:
[operations/puppet@production] mariadb: Add grants for rddmarc to m1

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

Mentioned in SAL (#wikimedia-operations) [2017-07-25T17:33:41Z] <jynus> creating new database on m1 (rddmarc) T170158

jcrespo updated the task description. (Show Details)Jul 25 2017, 5:34 PM

You showed me a link for the database charset recommend, which one was it @herron ?

$ mysql -h m1-master.eqiad.wmnet --skip-ssl -e "SHOW CREATE DATABASE rddmarc"
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| rddmarc  | CREATE DATABASE `rddmarc` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+------------------------------------------------------------------------------------------------+
jcrespo updated the task description. (Show Details)Jul 25 2017, 5:38 PM

Change 367708 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] rddmarc: enable database connection only from the m1 dbproxies

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

Change 367708 merged by Jcrespo:
[operations/puppet@production] rddmarc: enable database connection only from the m1 dbproxies

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

jcrespo closed this task as Resolved.Jul 25 2017, 5:56 PM
jcrespo updated the task description. (Show Details)
root@diadem:~$ mysql -h m1-master.eqiad.wmnet rddmarc -u rddmarc -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 68273122
Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| rddmarc            |
+--------------------+
2 rows in set (0.04 sec)

mysql> SELECT current_user();
+----------------------+
| current_user()       |
+----------------------+
| rddmarc@10.64.16.159 |
+----------------------+
1 row in set (0.04 sec)

mysql> SELECT database();
+------------+
| database() |
+------------+
| rddmarc    |
+------------+
1 row in set (0.04 sec)

Password is on the private repo, which you should puppetize (alongside a user and the host) on a local file config on the app server, for when it changes. Right now m1 doesn't fully support TLS, but you should be ready to deploy it soon.