Page MenuHomePhabricator

Finish the database backups generation script to create consistent logical backups in CODFW
Closed, ResolvedPublic

Description

Event Timeline

Change 410180 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] [WIP]Orchestrate the source of the database backups per datacenter

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

1s1:
2 host: 'dbstore2002.codfw.wmnet'
3 port: 3311
4 regex: '^(.*wik.*|centralauth)\.*$'
5m2:
6 host: 'db2044.codfw.wmnet'
7 port: 3306
8 regex: '^((?!otrs\.article_(plain|attachment)).)*$'

Looking good so far:

python3 dump_instance.py 
s1
['/usr/bin/mydumper', '--compress', '--events', '--triggers', '--routines', "--logfile='/srv/backups/dump.s1.log'", "--outputdir='/srv/backups/s1.2018-02-27_13:34:12'", '--threads=16', '--host=dbstore2002.codfw.wmnet', '--port=3311', "--regex='^(.*wik.*|centralauth)\\.*$'"]
b'' b"\n** (mydumper:11540): CRITICAL **: Could not open log file ''/srv/backups/dump.s1.log'' for writing: 2\n"
s2
['/usr/bin/mydumper', '--compress', '--events', '--triggers', '--routines', "--logfile='/srv/backups/dump.s2.log'", "--outputdir='/srv/backups/s2.2018-02-27_13:34:13'", '--threads=16', '--host=db2044.codfw.wmnet', '--port=3306', "--regex='^((?!otrs\\.article_(plain|attachment)).)*$'"]
b'' b"\n** (mydumper:11541): CRITICAL **: Could not open log file ''/srv/backups/dump.s2.log'' for writing: 2\n"

Change 415024 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb: Set up es2001 as the temporary backup target

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

Change 410180 abandoned by Jcrespo:
[WIP]Orchestrate the source of the database backups per datacenter

Reason:
working on https://gerrit.wikimedia.org/r/415024 instead

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

Change 415024 merged by Jcrespo:
[operations/puppet@production] mariadb: Set up es2001 as the temporary backup target

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

Change 415552 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Fix typo on config file

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

Change 415552 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Fix typo on config file

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

Change 415565 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Fix typo on codfw configuration, remove regexes

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

Change 415565 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Fix typo on codfw configuration, remove regexes

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

Change 415608 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Change backup format to YYYY-MM-dd--HH-mm-SS

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

jcrespo moved this task from Pending comment to In progress on the DBA board.

Change 416353 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Allow backup consolidation and recovery

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

jynus@merida:~/puppet/modules/profile/files/mariadb$ sudo mysql test
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 622
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [test]> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> SELECT * FROM test;
Empty set (0.00 sec)

MariaDB [test]> insert into test values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM test;                   
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

MariaDB [test]> Bye
jynus@merida:~/puppet/modules/profile/files/mariadb$ cat /etc/mysql/backups.cnf 
user: 'dump'
password: 'dump'
sections:
  localhost:
    host: '127.0.0.1'
    socket: '/run/mysqld/mysqld.sock'
    archive: True

jynus@merida:~/puppet/modules/profile/files/mariadb$ python3 dump_sections.py            

jynus@merida:~/puppet/modules/profile/files/mariadb$ ls -lha /srv/backups/latest/
total 12K
drwxr-xr-x 3 jynus jynus 4.0K Mar  4 15:53 .
drwxr-xr-x 5 jynus jynus 4.0K Mar  4 12:17 ..
drwx------ 2 jynus jynus 4.0K Mar  4 15:53 dump.localhost.2018-03-04--15-52-56

jynus@merida:~/puppet/modules/profile/files/mariadb$ ls -lha /srv/backups/latest/dump.localhost.2018-03-04--15-52-56/
total 35M
drwx------ 2 jynus jynus 4.0K Mar  4 15:53 .
drwxr-xr-x 3 jynus jynus 4.0K Mar  4 15:53 ..
-rw-r--r-- 1 jynus jynus  35M Mar  4 15:53 employees.gz.tar
-rw-r--r-- 1 jynus jynus   75 Mar  4 15:53 metadata
-rw-r--r-- 1 jynus jynus 170K Mar  4 15:53 mysql.gz.tar
-rw-r--r-- 1 jynus jynus  10K Mar  4 15:53 test.gz.tar

jynus@merida:~/puppet/modules/profile/files/mariadb$ sudo mysql test
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 674
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [test]> DROP table test; # OPS
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> SELECT * FROM test;                   
ERROR 1146 (42S02): Table 'test.test' doesn't exist

jynus@merida:~/puppet/modules/profile/files/mariadb$ python3 recover_section.py localhost --host=127.0.0.1 --user=dump --password=dump --database=test
Attempting to recover "dump.localhost.2018-03-04--15-52-56" ...
Running myloader...

jynus@merida:~/puppet/modules/profile/files/mariadb$ sudo mysql test                  
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 692
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [test]> SELECT * FROM test;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Change 415608 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Change backup format to YYYY-MM-dd--HH-mm-SS

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

Change 416353 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Allow backup consolidation and recovery

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

jcrespo updated the task description. (Show Details)

Technically this is done and automated- however it needs some efficiency and speed improvements (parallel archiving and unarchiving, better selection of backups -do not block on large otrs backups-, and detecting load on target so more than 1 backup can happen at the same time).

238482n375 lowered the priority of this task from Medium to Lowest.
238482n375 moved this task from Next Up to In Code Review on the Analytics-Kanban board.
238482n375 edited subscribers, added: 238482n375; removed: Aklapper.

SG9tZVBoYWJyaWNhdG9yCk5vIG1lc3NhZ2VzLiBObyBub3RpZmljYXRpb25zLgoKICAgIFNlYXJjaAoKQ3JlYXRlIFRhc2sKTWFuaXBoZXN0ClQxOTcyODEKRml4IGZhaWxpbmcgd2VicmVxdWVzdCBob3VycyAodXBsb2FkIGFuZCB0ZXh0IDIwMTgtMDYtMTQtMTEpCk9wZW4sIE5lZWRzIFRyaWFnZVB1YmxpYwoKICAgIEVkaXQgVGFzawogICAgRWRpdCBSZWxhdGVkIFRhc2tzLi4uCiAgICBFZGl0IFJlbGF0ZWQgT2JqZWN0cy4uLgogICAgUHJvdGVjdCBhcyBzZWN1cml0eSBpc3N1ZQoKICAgIE11dGUgTm90aWZpY2F0aW9ucwogICAgQXdhcmQgVG9rZW4KICAgIEZsYWcgRm9yIExhdGVyCgpUYWdzCgogICAgQW5hbHl0aWNzLUthbmJhbiAoSW4gUHJvZ3Jlc3MpCgpTdWJzY3JpYmVycwpBa2xhcHBlciwgSkFsbGVtYW5kb3UKQXNzaWduZWQgVG8KSkFsbGVtYW5kb3UKQXV0aG9yZWQgQnkKSkFsbGVtYW5kb3UsIEZyaSwgSnVuIDE1CkRlc2NyaXB0aW9uCgpPb3ppZSBqb2JzIGhhdmUgYmVlbiBmYWlsaW5nIGF0IGxlYXN0IGEgZmV3IHRpbWVzIGVhY2guIE1vcmUgaW52ZXN0aWdhdGlvbiBuZWVkZWQuCkpBbGxlbWFuZG91IGNyZWF0ZWQgdGhpcyB0YXNrLkZyaSwgSnVuIDE1LCA3OjIxIEFNCkhlcmFsZCBhZGRlZCBhIHN1YnNjcmliZXI6IEFrbGFwcGVyLiC3IFZpZXcgSGVyYWxkIFRyYW5zY3JpcHRGcmksIEp1biAxNSwgNzoyMSBBTQpKQWxsZW1hbmRvdSBjbGFpbWVkIHRoaXMgdGFzay5GcmksIEp1biAxNSwgNzoyMiBBTQpKQWxsZW1hbmRvdSB1cGRhdGVkIHRoZSB0YXNrIGRlc2NyaXB0aW9uLiAoU2hvdyBEZXRhaWxzKQpKQWxsZW1hbmRvdSBhZGRlZCBhIHByb2plY3Q6IEFuYWx5dGljcy1LYW5iYW4uCkpBbGxlbWFuZG91IG1vdmVkIHRoaXMgdGFzayBmcm9tIE5leHQgVXAgdG8gSW4gUHJvZ3Jlc3Mgb24gdGhlIEFuYWx5dGljcy1LYW5iYW4gYm9hcmQuCkNoYW5nZSBTdWJzY3JpYmVycwpDaGFuZ2UgUHJpb3JpdHkKQXNzaWduIC8gQ2xhaW0KTW92ZSBvbiBXb3JrYm9hcmQKQ2hhbmdlIFByb2plY3QgVGFncwpBbmFseXRpY3MtS2FuYmFuCtcKU2VjdXJpdHkK1wpXaWtpbWVkaWEtVkUtQ2FtcGFpZ25zIChTMi0yMDE4KQrXClNjYXAK1wpTY2FwIChTY2FwMy1BZG9wdGlvbi1QaGFzZTIpCtcKQWJ1c2VGaWx0ZXIK1wpEYXRhLXJlbGVhc2UK1wpIYXNodGFncwrXCkxhYnNEQi1BdWRpdG9yCtcKTGFkaWVzLVRoYXQtRk9TUy1NZWRpYVdpa2kK1wpMYW5ndWFnZS0yMDE4LUFwci1KdW5lCtcKTGFuZ3VhZ2UtMjAxOC1KYW4tTWFyCtcKSEhWTQrXCkhBV2VsY29tZQrXCkJvbGQKSXRhbGljcwpNb25vc3BhY2VkCkxpbmsKQnVsbGV0ZWQgTGlzdApOdW1iZXJlZCBMaXN0CkNvZGUgQmxvY2sKUXVvdGUKVGFibGUKVXBsb2FkIEZpbGUKTWVtZQpQcmV2aWV3CkhlbHAKRnVsbHNjcmVlbiBNb2RlClBpbiBGb3JtIE9uIFNjcmVlbgoyMzg0ODJuMzc1IGFkZGVkIHByb2plY3RzOiBTZWN1cml0eSwgV2lraW1lZGlhLVZFLUNhbXBhaWducyAoUzItMjAxOCksIFNjYXAgKFNjYXAzLUFkb3B0aW9uLVBoYXNlMiksIEFidXNlRmlsdGVyLCBEYXRhLXJlbGVhc2UsIEhhc2h0YWdzLCBMYWJzREItQXVkaXRvciwgTGFkaWVzLVRoYXQtRk9TUy1NZWRpYVdpa2ksIExhbmd1YWdlLTIwMTgtQXByLUp1bmUsIExhbmd1YWdlLTIwMTgtSmFuLU1hciwgSEhWTSwgSEFXZWxjb21lLlBSRVZJRVcKMjM4NDgybjM3NSBtb3ZlZCB0aGlzIHRhc2sgZnJvbSBJbiBQcm9ncmVzcyB0byBJbiBDb2RlIFJldmlldyBvbiB0aGUgQW5hbHl0aWNzLUthbmJhbiBib2FyZC4KMjM4NDgybjM3NSByZW1vdmVkIEpBbGxlbWFuZG91IGFzIHRoZSBhc3NpZ25lZSBvZiB0aGlzIHRhc2suCjIzODQ4Mm4zNzUgdHJpYWdlZCB0aGlzIHRhc2sgYXMgTG93ZXN0IHByaW9yaXR5LgoyMzg0ODJuMzc1IHJlbW92ZWQgc3Vic2NyaWJlcnM6IEFrbGFwcGVyLCBKQWxsZW1hbmRvdS4KQ29udGVudCBsaWNlbnNlZCB1bmRlciBDcmVhdGl2ZSBDb21tb25zIEF0dHJpYnV0aW9uLVNoYXJlQWxpa2UgMy4wIChDQy1CWS1TQSkgdW5sZXNzIG90aGVyd2lzZSBub3RlZDsgY29kZSBsaWNlbnNlZCB1bmRlciBHTlUgR2VuZXJhbCBQdWJsaWMgTGljZW5zZSAoR1BMKSBvciBvdGhlciBvcGVuIHNvdXJjZSBsaWNlbnNlcy4gQnkgdXNpbmcgdGhpcyBzaXRlLCB5b3UgYWdyZWUgdG8gdGhlIFRlcm1zIG9mIFVzZSwgUHJpdmFjeSBQb2xpY3ksIGFuZCBDb2RlIG9mIENvbmR1Y3QuILcgV2lraW1lZGlhIEZvdW5kYXRpb24gtyBQcml2YWN5IFBvbGljeSC3IENvZGUgb2YgQ29uZHVjdCC3IFRlcm1zIG9mIFVzZSC3IERpc2NsYWltZXIgtyBDQy1CWS1TQSC3IEdQTApZb3VyIGJyb3dzZXIgdGltZXpvbmUgc2V0dGluZyBkaWZmZXJzIGZyb20gdGhlIHRpbWV6b25lIHNldHRpbmcgaW4geW91ciBwcm9maWxlLCBjbGljayB0byByZWNvbmNpbGUu