Are you sure you want to call your database and user with '-' signs? It is completely allowed, but you may have to annoyingly escape it in certain contexts:

mysql> SELECT * FROM recommendation-api.my_table\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-api.my_table' at line 1
mysql> SELECT * FROM `recommendation-api`.`my_table`\G
ERROR 1146 (42S02): Table 'recommendation-api.my_table' doesn't exist

I can call it like that, but won't support any issue you have for bad escaping code. Would strongly recommend recommendationapi instead.

I forgot to ask, what is the needed backup policy? We normally create full backups every week?

@jcrespo, good call. I've updated the task description.

I am creating this database, but we need to know which server or servers this will be accessed from.

Change 462650 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Add recommendation api database accounts to m2

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

Change 462650 merged by Jcrespo:
[operations/puppet@production] mariadb: Add recommendation api database accounts to m2

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

This is ready, but without knowing the source of the connections, it may not work without firewall changes:

root@neodymium:~$ mysql --skip-ssl -h m2-master.eqiad.wmnet -urecommendationapi -p$pass
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52313505
Server version: 10.1.33-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.

recommendationapi@m2-master.eqiad.wmnet[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| recommendationapi  |
+--------------------+
2 rows in set (0.00 sec)

recommendationapi@m2-master.eqiad.wmnet[(none)]> use recommendationapi
Database changed
recommendationapi@m2-master.eqiad.wmnet[recommendationapi]> show tables;
Empty set (0.00 sec)

recommendationapi@m2-master.eqiad.wmnet[recommendationapi]> CREATE TABLE test (i int);
Query OK, 0 rows affected (0.00 sec)

recommendationapi@m2-master.eqiad.wmnet[recommendationapi]> SELECT * FROM test;
Empty set (0.00 sec)

recommendationapi@m2-master.eqiad.wmnet[recommendationapi]> DROP TABLE test;
Query OK, 0 rows affected (0.00 sec)

Password in the puppet private repo, send a puppet patch with a place to handle it and I will fill in a puppet variable for you.

Being reasonable responsive for database maintenance windows and switchovers is vital for being in a production service unless uptime is not important for you.

@Pchelolo what do you think about T205294#4613658?

@jcrespo thank you!

Password in the puppet private repo, send a puppet patch with a place to handle it and I will fill in a puppet variable for you.

Any similar patch I can get an inspiration from?

Being reasonable responsive for database maintenance windows and switchovers is vital for being in a production service unless uptime is not important for you.

What mailing list is used for these kinds of news announcements?

Any similar patch I can get an inspiration from?

Here it is an example of how I get a password into a variable and then I write into a template that writes a configuration file locally to the client:
https://phabricator.wikimedia.org/source/operations-puppet/browse/production/modules/profile/manifests/mariadb/backup/mydumper.pp;8edc1b39c310321596d24d7b9ce3f11ffb41f4ad$47?as=source&blame=off
(there are more elegant ways of doing that)

Please note that getting help with puppet is out of the scope of the ticket, not because we cannot help you, but because anyone, and not only the DBAs will be able to help you with that. Setup the rest of the puppet configuration and we can help you to find the password (or other config opetions) as a second step.

What mailing list is used for these kinds of news announcements?

Normally tasks for maintenance are created here in phabricator well in advance and I will CC you or whoever you tell me to (e.g. better a team tag than a single person). Owners should be documented on https://wikitech.wikimedia.org/wiki/MariaDB/misc#owners,_(or_in_many_cases_just_people_that_volunteer_to_help_for_the_failover)_2

One last thing- accounts are "free" to creat -we can create as many as you need- we created just an account but it has all rights. If you are going to expose in anyway this to a public web interface, you should ask for additional accounts with reduced db rights eg. "SELECT" only or "SELECT + INSERT" for security reasons.

@jcrespo could you please create an account with username 'recommendationapiservice' with the 'SELECT' right only?

As for the services to connect from, I think for now I'll need access to the DB from stat1005. Petr will help identify the services end points.

As for configuring Puppet, does the Ops team own this? I've a limited knowledge of Puppet and don't have bandwidth to dig into configuration files now.

Change 462731 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Add alternative, read-only account for recomm. api db

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

Change 462731 merged by Jcrespo:
[operations/puppet@production] mariadb: Add alternative, read-only account for recomm. api db

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

Read only account deployed:

root@neodymium:~$ mysql --skip-ssl -h m2-master.eqiad.wmnet -urecommendationapiservice -p$pass2
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52460619
Server version: 10.1.33-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.

recommendationapiservice@m2-master.eqiad.wmnet[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| recommendationapi  |
+--------------------+
2 rows in set (0.00 sec)

recommendationapiservice@m2-master.eqiad.wmnet[(none)]> use recommendationapi
Database changed
recommendationapiservice@m2-master.eqiad.wmnet[recommendationapi]> show tables;Empty set (0.00 sec)

recommendationapiservice@m2-master.eqiad.wmnet[recommendationapi]> CREATE TABLE test (i int);
ERROR 1142 (42000): CREATE command denied to user 'recommendationapiservice'@'10.64.0.166' for table 'test'

As for configuring Puppet, does the Ops team own this?

If you signed L3 (and got server access), you signed you are to research about puppet and puppet usage:

Administration

Familiarize yourself with

• Puppet
• Puppet usage

Puppet

Anything that changes the state of a server outside of your home directory should be done by puppet. This means if a file is moved, touched, or updated that is not in your own user directory, it should be done via puppet manifests, not manual commands. This allows it to be peer reviewed.

If you need help I honestly don't know who can help, I would ask someone in your department (who is your manger or do you know someone with previous experience on productionizing a service?), but that is out of the scope of this ticket (which is for the account and db setup only), I would manage that separately.

Other than the credentials transference, I would consider the database and account creation complete.

@jcrespo thanks! Looks like I misunderstood you. If DB creation is done, then I'll talk to the Services to team about the productionizing part.

@jcrespo can you share the password for the 'recommendationapi' user so that I can load some data into the database (I don't have access to the private puppet repo)? Also can you tell me which hosts allow me to connect to the database? Thanks!

We don't share passwords publicly, and you shouldn't need it to actually use it- you should create puppet code that reads it and write it to a config file or reads it and performs the load from you. Giving you the password not only it would be dangerous, it would not serve if for any reason it has to be changed and the loading code does not work anymore. Also passwords can be stolen and lost and I personally consider them sensitive data.

Access is possible from hosts in the production network (no analytics, labs, etc.).

@leila could you please help @bmansurov with common practices that every person with access to production network should follow: https://phabricator.wikimedia.org/L3 It is ok to not be aware of those, but production access is definitely not easy and will need help, specially regarding puppetization. I already showed him an example of how to do that at T205294#4614760 but he may need additional context. Production strictness is higher than such of cloud, which much more relaxed security requirements and best practices.

In T205294#4702061, @jcrespo wrote:

We don't share passwords publicly,

That's, of course, understood.

you should create puppet code that reads it and write it to a config file or reads it and performs the load from you.

ow yes! I had forgotten about this. The last time we did this was around 1.5 years ago and I had forgotten about the process. Back in the day, Yuvi or Madhu were available to pair up and teach us about puppets. Do you know who's the right person to contact today?

@leila could you please help @bmansurov with common practices that every person with access to production network should follow: https://phabricator.wikimedia.org/L3 It is ok to not be aware of those, but production access is definitely not easy and will need help, specially regarding puppetization. I already showed him an example of how to do that at T205294#4614760 but he may need additional context. Production strictness is higher than such of cloud, which much more relaxed security requirements and best practices.

The L3 form is a good reminder for myself, too. I hadn't re-read it for a while now and forgotten some details. To make things most efficient on our end, it would be best if someone that writes puppet code can pair with us, cuz I am no expert on that front.

Do you know who's the right person to contact today?

As mentioned several times already (sorry to stress that [[ T205452#4688800 | this has already been answered ]], and it is documented on the ops documentation at wikitech/officewiki, on how to get help from sysadmins- #wikimedia-opreations IRC topic contains a reference to the "Ops Clinic Duty" person in charge to help with inquiries.

I apologize if this was not clear the first times this was answered, and we beg you to follow the procedure mentioned.

To make things most efficient on our end, it would be best if someone that writes puppet code can pair with us

That is the SRE in clinic duty for that week- we rotate so it is not always the same person, as the other may be busy with other tasks. On duty person has the obligation to clear his/her agenda to assist production users and advance tickets that are non-specific.

In T205294#4716089, @jcrespo wrote:

To make things most efficient on our end, it would be best if someone that writes puppet code can pair with us

That is the SRE in clinic duty for that week- we rotate so it is not always the same person, as the other may be busy with other tasks. On duty person has the obligation to clear his/her agenda to assist production users and advance tickets that are non-specific.

I didn't know we can ask for extended 1:1 help from the SRE in clinic duty person. very helpful! thanks! :)

"extended 1:1" is not my words-- obviously I cannot guarantee such a thing, only a manager can do so, but I believe assistance on writing a puppet patch would be within the functions, as I understand them.

all clear. thanks!

Change 481871 had a related patch set uploaded (by Bmansurov; owner: Bmansurov):
[operations/puppet@production] Recommendation API: increase mysql connection limit for service

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

Change 481871 merged by Marostegui:
[operations/puppet@production] Recommendation API: increase mysql connection limit for service

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