search

shift-org / shift-docs

Shift2Bikes: website and calendar for shift and pedalpalooza
https://shift2bikes.org
Other
22 stars 17 forks source link

Daily cron to do mysql dump #829

Open aaronminnick opened 1 month ago

aaronminnick commented 1 month ago

@fool @onewheelskyward

As a developer / user of the Shift website, I would like automated daily(?) backups to occur so that if something goes wrong with the database, my events aren't 'lost'.

Discussing in the meetup, we were talking about the process of restoring the event data if the database got wiped. It is possible to run a mysql dump to get the database as a text file, but this is a manual process. It would be good if we could have a cron running every day to create a dump file, to minimize data loss if something goes wrong with the database (and if AWS backups are not available / accessible).

fool commented 1 month ago

my default oneliner of shift mysqldump | tail +1 | mailx -s "shift db dump" email@addre.ss doesn't work because mailx doesn't work on prod. Not sure what's up with that exactly. I can see in root's crontab that @onewheelskyward tried something similar before but it's commented out.

Note that you want the tail +1 or something like that because otherwise the first line is a warning about command line passwords which you need to strip out before you can pipe it back into mysql (gzip -cd file.gz | shift mysqlpipe to restore)

fool commented 1 month ago

@onewheelskyward can you remind me how often we get an AWS snapshot and at what time of day with timezone? Then I can easily create a local file backup in prod daily. Not quite the same resiliency as sending it off-server but we can reduce the exposure a bit in 2 minutes with this kinda pattern.

On Mon, Oct 21, 2024 at 6:47 PM Aaron Minnick @.***> wrote:

@fool https://github.com/fool @onewheelskyward https://github.com/onewheelskyward

As a developer / user of the Shift website, I would like automated daily(?) backups to occur so that if something goes wrong with the database, my events aren't 'lost'.

Discussing in the meetup, we were talking about the process of restoring the event data if the database got wiped. It is possible to run a mysql dump to get the database as a text file, but this is a manual process. It would be good if we could have a cron running every day to create a dump file, to minimize data loss if something goes wrong with the database (and if AWS backups are not available / accessible).

— Reply to this email directly, view it on GitHub https://github.com/shift-org/shift-docs/issues/829, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABX5IDEZCJNPF2GYA5AIMLZ4WVCJAVCNFSM6AAAAABQLMGNDGVHI2DSMVQWIX3LMV43ASLTON2WKOZSGYYDIMBRGQ2TGNY . You are receiving this because you were mentioned.Message ID: @.***>

fool commented 2 weeks ago

after some struggle I have a dump running at 0243 UTC (was convenient to test with, we can/should adjust based on when the backups run) in the root crontab. The output file is /tmp/mysql-backup-YYYY-MM-DD.gz (with the placeholders set in realtime)

(edit: Hmm, still struggling, not actually running yet)

fool commented 2 weeks ago

well, this didn't end up working tonight; will try again next week

fool commented 2 weeks ago

backup runs around midnight, but let's dump at 11p.

crontab is funky so will get back to it next week

fool commented 1 week ago

I have given up on this on the current config. Will take a shot again after we do "something" about #630 . @onewheelskyward suggests moving to RDS rather than moving to non-docker host storage; I think either will work