Operational Access to Master¶
Archived (pre-2022)
Preserved for reference only -- likely outdated. View original | Last updated: May 2018
All Devops (ie all people with tag 'hostmaster' in their chef databag) have a personal account on the MySQL master, that they can use to connect. Username and password are in the file .my.cnf in their respective home directories.
The account provides read-only access and should be used whenever possible.
Live view of all running queries¶
- Find out which server is master at Grafana - Mon Mysql
- Connect to that server with ssh
- Launch "mytop" to get a top-like view of MySQL activity
rwunderer@rivendell:~$ ssh dbs002
All keys already loaded
Last login: Fri May 4 14:54:23 2018 from 10.90.96.100
Installed by Razor using Red Hat Enterprise Linux 6 - CentOS 7 Installer
Repo: http://10.99.24.11:8080/svc/repo/centos-7.1/
Node: http://10.99.24.11:8080/api/nodes/161
Install log: /var/log/razor.log
rwunderer@dbs002:~$ mytop
will show
MySQL on 127.0.0.1 (5.6.32-78.1-log) up 38+03:07:49 [15:23:42]
Queries: 4.8G qps: 1574 Slow: 1.2M Se/In/Up/De(%): 54/39/21/64
qps now: 35050 Slow qps: 2.9 Threads: 12614 ( 26/ 6) 87/04/03/00
Key Efficiency: 100.0% Bps in/out: 1.3M/ 1.2M Now in/out: 6.1M/26.3M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
3017656 sws 10.99.13.15 ids_db_pro 0 Query SELECT `cms_db_production`.`programs`.`id` FROM `cms_db_production`.`programs` WHERE `cms_db_prod
3018010 bre 10.99.16.32 bre_db_pro 0 Query SELECT `country_id`, `landing_page_id`, `country_code`, `language_id`, `language_code`, `advertis
3018314 ofw 10.99.13.14 ids_db_pro 0 Query SELECT `id`, `application_id`, `currencyid`, `default`, `name` FROM `virtual_currencies` WHERE `a
3026289 ofw 10.99.9.40 ids_db_pro 0 Query SELECT `id`, `application_id`, `currencyid`, `default`, `name` FROM `virtual_currencies` WHERE `a
3035929 ofw 10.99.13.12 ids_db_pro 0 Query SELECT `id`, `subid`, `userid`, `application_id`, `amount`, `total`, `external`, `created_at`, `u
3036249 ofw 10.99.13.12 ids_db_pro 0 Query SELECT `id`, `subid`, `userid`, `application_id`, `amount`, `total`, `external`, `created_at`, `u
3037293 ofw 10.99.13.12 ids_db_pro 0 Query SELECT `id`, `application_id`, `currencyid`, `default`, `name` FROM `virtual_currencies` WHERE `a
3041758 providers 10.99.9.19 bre_db_pro 0 Query SELECT `rewarded_action_id`, `program_id`, `landing_page_id`, count(`id`) AS `count`, max(`publis
3043111 providers 10.99.44.70 bre_db_pro 0 Query SELECT `rewarded_action_id`, `program_id`, `landing_page_id`, count(`id`) AS `count`, max(`publis
3046166 root localhost:37792 bre_db_pro 0 Query INSERT INTO `engagements` (`subid`, `status`, `user_id`, `integration`, `ip`, `country_code`, `la
3046844 providers 10.99.44.42 bre_db_pro 0 Query SELECT `rewarded_action_id`, `program_id`, `landing_page_id`, count(`id`) AS `count`, max(`publis
3050416 providers 10.99.21.103 bre_db_pro 0 Query UPDATE `engagements` SET `publisher_event_at` = '2018-05-04 15:23:42', `status` = 45, `updated_at
3052283 providers 10.99.9.20 bre_db_pro 0 Query SELECT * FROM `users` WHERE ((`userid` = '537b6b808918b0ef') AND (`application_id` = 91571)) LIMI
3064162 providers 10.99.44.53 bre_db_pro 0 Query SELECT `rewarded_action_id`, `program_id`, `landing_page_id`, count(`id`) AS `count`, max(`publis
3064241 providers 10.99.44.42 bre_db_pro 0 Query SELECT `rewarded_action_id`, `program_id`, `landing_page_id`, count(`id`) AS `count`, max(`publis
3069552 providers 10.99.21.104 bre_db_pro 0 Query SELECT * FROM `interstitial_ads` WHERE (((`iphone_offer` IS TRUE) OR (`ipad_offer` IS TRUE) OR (`
3071126 rwunderer 127.0.0.1 ids_db_pro 0 Query show full processlist
Killing problematic queries¶
For killing problematic queries, root access is needed.
Either use mysqladmin:
or to get rid of all queries running for longer than a given timespan (eg 5 seconds) use pt-kill:
sudo pt-kill --host 127.0.0.1 --port 3306 --run-time 1 --busy-time 5 --interval 1 --kill --victims all --ignore-user root
This might free the database in case of short-term congestion but will not help in case of an attack or other cause of overload.
Command line access to the database¶
Warning
Do not connect to the database using 'sudo mysql'. Always use your personal (read-only) user. Any mistake as root might cause hours of downtime!
- Find out which server is master at Grafana - Mon Mysql
- Connect to that server with ssh
- Launch "mysql" to connect to the database
- Select one of the main schemas bre_db_production, cms_db_production or ids_db_production
Note
Be careful with the queries you run. Any SELECT without a proper filter might cause massive slowdown on the master. For data queries use statsdb or Exasol!
rwunderer@rivendell:~$ ssh dbs002
All keys already loaded
Last login: Fri May 4 14:54:23 2018 from 10.90.96.100
Installed by Razor using Red Hat Enterprise Linux 6 - CentOS 7 Installer
Repo: http://10.99.24.11:8080/svc/repo/centos-7.1/
Node: http://10.99.24.11:8080/api/nodes/161
Install log: /var/log/razor.log
rwunderer@dbs002:~$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9151
Server version: 5.6.32-78.1-log Percona Server (GPL), Release 78.1, Revision 4c779b7
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, 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> use ids_db_production
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>