- Add orchestration of multiple hosts
- Right now this is done by editing https://phabricator.wikimedia.org/source/operations-puppet/browse/production/modules/profile/templates/mariadb/backups-codfw.cnf.erb (and when eqiad ones are available an equivalent one)
- Consolidate backups into a small number of files
- Implement a retention policy
Description
Details
Status | Subtype | Assigned | Task | ||
---|---|---|---|---|---|
Open | None | T138562 Improve regular production database backups handling | |||
Resolved | jcrespo | T184699 Generate consistent logical database backups in CODFW | |||
Resolved | mark | T184697 Failover existing eqiad database backup system to the new codfw database logical backup system | |||
Resolved | jcrespo | T184696 Finish the database backups generation script to create consistent logical backups in CODFW |
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
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
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
Change 415024 merged by Jcrespo:
[operations/puppet@production] mariadb: Set up es2001 as the temporary backup target
Change 415552 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Fix typo on config file
Change 415552 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Fix typo on config file
Change 415565 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Fix typo on codfw configuration, remove regexes
Change 415565 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Fix typo on codfw configuration, remove regexes
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
Change 416353 had a related patch set uploaded (by Jcrespo; owner: Jcrespo):
[operations/puppet@production] mariadb-backups: Allow backup consolidation and recovery
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
Change 416353 merged by Jcrespo:
[operations/puppet@production] mariadb-backups: Allow backup consolidation and recovery
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).
SG9tZVBoYWJyaWNhdG9yCk5vIG1lc3NhZ2VzLiBObyBub3RpZmljYXRpb25zLgoKICAgIFNlYXJjaAoKQ3JlYXRlIFRhc2sKTWFuaXBoZXN0ClQxOTcyODEKRml4IGZhaWxpbmcgd2VicmVxdWVzdCBob3VycyAodXBsb2FkIGFuZCB0ZXh0IDIwMTgtMDYtMTQtMTEpCk9wZW4sIE5lZWRzIFRyaWFnZVB1YmxpYwoKICAgIEVkaXQgVGFzawogICAgRWRpdCBSZWxhdGVkIFRhc2tzLi4uCiAgICBFZGl0IFJlbGF0ZWQgT2JqZWN0cy4uLgogICAgUHJvdGVjdCBhcyBzZWN1cml0eSBpc3N1ZQoKICAgIE11dGUgTm90aWZpY2F0aW9ucwogICAgQXdhcmQgVG9rZW4KICAgIEZsYWcgRm9yIExhdGVyCgpUYWdzCgogICAgQW5hbHl0aWNzLUthbmJhbiAoSW4gUHJvZ3Jlc3MpCgpTdWJzY3JpYmVycwpBa2xhcHBlciwgSkFsbGVtYW5kb3UKQXNzaWduZWQgVG8KSkFsbGVtYW5kb3UKQXV0aG9yZWQgQnkKSkFsbGVtYW5kb3UsIEZyaSwgSnVuIDE1CkRlc2NyaXB0aW9uCgpPb3ppZSBqb2JzIGhhdmUgYmVlbiBmYWlsaW5nIGF0IGxlYXN0IGEgZmV3IHRpbWVzIGVhY2guIE1vcmUgaW52ZXN0aWdhdGlvbiBuZWVkZWQuCkpBbGxlbWFuZG91IGNyZWF0ZWQgdGhpcyB0YXNrLkZyaSwgSnVuIDE1LCA3OjIxIEFNCkhlcmFsZCBhZGRlZCBhIHN1YnNjcmliZXI6IEFrbGFwcGVyLiC3IFZpZXcgSGVyYWxkIFRyYW5zY3JpcHRGcmksIEp1biAxNSwgNzoyMSBBTQpKQWxsZW1hbmRvdSBjbGFpbWVkIHRoaXMgdGFzay5GcmksIEp1biAxNSwgNzoyMiBBTQpKQWxsZW1hbmRvdSB1cGRhdGVkIHRoZSB0YXNrIGRlc2NyaXB0aW9uLiAoU2hvdyBEZXRhaWxzKQpKQWxsZW1hbmRvdSBhZGRlZCBhIHByb2plY3Q6IEFuYWx5dGljcy1LYW5iYW4uCkpBbGxlbWFuZG91IG1vdmVkIHRoaXMgdGFzayBmcm9tIE5leHQgVXAgdG8gSW4gUHJvZ3Jlc3Mgb24gdGhlIEFuYWx5dGljcy1LYW5iYW4gYm9hcmQuCkNoYW5nZSBTdWJzY3JpYmVycwpDaGFuZ2UgUHJpb3JpdHkKQXNzaWduIC8gQ2xhaW0KTW92ZSBvbiBXb3JrYm9hcmQKQ2hhbmdlIFByb2plY3QgVGFncwpBbmFseXRpY3MtS2FuYmFuCtcKU2VjdXJpdHkK1wpXaWtpbWVkaWEtVkUtQ2FtcGFpZ25zIChTMi0yMDE4KQrXClNjYXAK1wpTY2FwIChTY2FwMy1BZG9wdGlvbi1QaGFzZTIpCtcKQWJ1c2VGaWx0ZXIK1wpEYXRhLXJlbGVhc2UK1wpIYXNodGFncwrXCkxhYnNEQi1BdWRpdG9yCtcKTGFkaWVzLVRoYXQtRk9TUy1NZWRpYVdpa2kK1wpMYW5ndWFnZS0yMDE4LUFwci1KdW5lCtcKTGFuZ3VhZ2UtMjAxOC1KYW4tTWFyCtcKSEhWTQrXCkhBV2VsY29tZQrXCkJvbGQKSXRhbGljcwpNb25vc3BhY2VkCkxpbmsKQnVsbGV0ZWQgTGlzdApOdW1iZXJlZCBMaXN0CkNvZGUgQmxvY2sKUXVvdGUKVGFibGUKVXBsb2FkIEZpbGUKTWVtZQpQcmV2aWV3CkhlbHAKRnVsbHNjcmVlbiBNb2RlClBpbiBGb3JtIE9uIFNjcmVlbgoyMzg0ODJuMzc1IGFkZGVkIHByb2plY3RzOiBTZWN1cml0eSwgV2lraW1lZGlhLVZFLUNhbXBhaWducyAoUzItMjAxOCksIFNjYXAgKFNjYXAzLUFkb3B0aW9uLVBoYXNlMiksIEFidXNlRmlsdGVyLCBEYXRhLXJlbGVhc2UsIEhhc2h0YWdzLCBMYWJzREItQXVkaXRvciwgTGFkaWVzLVRoYXQtRk9TUy1NZWRpYVdpa2ksIExhbmd1YWdlLTIwMTgtQXByLUp1bmUsIExhbmd1YWdlLTIwMTgtSmFuLU1hciwgSEhWTSwgSEFXZWxjb21lLlBSRVZJRVcKMjM4NDgybjM3NSBtb3ZlZCB0aGlzIHRhc2sgZnJvbSBJbiBQcm9ncmVzcyB0byBJbiBDb2RlIFJldmlldyBvbiB0aGUgQW5hbHl0aWNzLUthbmJhbiBib2FyZC4KMjM4NDgybjM3NSByZW1vdmVkIEpBbGxlbWFuZG91IGFzIHRoZSBhc3NpZ25lZSBvZiB0aGlzIHRhc2suCjIzODQ4Mm4zNzUgdHJpYWdlZCB0aGlzIHRhc2sgYXMgTG93ZXN0IHByaW9yaXR5LgoyMzg0ODJuMzc1IHJlbW92ZWQgc3Vic2NyaWJlcnM6IEFrbGFwcGVyLCBKQWxsZW1hbmRvdS4KQ29udGVudCBsaWNlbnNlZCB1bmRlciBDcmVhdGl2ZSBDb21tb25zIEF0dHJpYnV0aW9uLVNoYXJlQWxpa2UgMy4wIChDQy1CWS1TQSkgdW5sZXNzIG90aGVyd2lzZSBub3RlZDsgY29kZSBsaWNlbnNlZCB1bmRlciBHTlUgR2VuZXJhbCBQdWJsaWMgTGljZW5zZSAoR1BMKSBvciBvdGhlciBvcGVuIHNvdXJjZSBsaWNlbnNlcy4gQnkgdXNpbmcgdGhpcyBzaXRlLCB5b3UgYWdyZWUgdG8gdGhlIFRlcm1zIG9mIFVzZSwgUHJpdmFjeSBQb2xpY3ksIGFuZCBDb2RlIG9mIENvbmR1Y3QuILcgV2lraW1lZGlhIEZvdW5kYXRpb24gtyBQcml2YWN5IFBvbGljeSC3IENvZGUgb2YgQ29uZHVjdCC3IFRlcm1zIG9mIFVzZSC3IERpc2NsYWltZXIgtyBDQy1CWS1TQSC3IEdQTApZb3VyIGJyb3dzZXIgdGltZXpvbmUgc2V0dGluZyBkaWZmZXJzIGZyb20gdGhlIHRpbWV6b25lIHNldHRpbmcgaW4geW91ciBwcm9maWxlLCBjbGljayB0byByZWNvbmNpbGUu