2

I setup a script to automatically backup the database and upload it to my Dropbox account, here's the code:

#!/bin/bash
DATE=$(date +"%Y-%m-%d-%H-%M-%S")
mysqldump -u USER -PASSWORD DB_NAME | gzip -9 > /tmp/$DATE.sql.gz
./root/Dropbox-Uploader/dropbox_uploader.sh upload /tmp/$DATE.sql.gz /
rm /tmp/$DATE.sql.gz

Credits to andreafabrizi and INKHORN.

I put the file inside /etc/cron.hourly

It works just file, the only problem is that it causes MySQL to not answer any calls from NGINX during that time (while it creates the backup, depends on size, 20 MB takes about 20 seconds, I see this as serious problem when the database gets larger), which gives the 502 Bad Gateway error.

Is there any way I can stop this from happening?

user94559
  • 59,196
  • 6
  • 103
  • 103
rekt
  • 91
  • 9
  • How long does the 502 last, on average? You could run this at the early hours of the morning (or whenever) when traffic is at its lowest. – ʰᵈˑ Aug 21 '14 at 12:48
  • @hd. Just edited my post: depends on size, 20 MB takes about 20 seconds, I see this as serious problem when the database gets larger. – rekt Aug 21 '14 at 12:49

1 Answers1

3

https://stackoverflow.com/a/12142501/3953404

Just added this to the command:

--single-transaction --quick

In context with my code:

mysqldump -u USER -PASSWORD --single-transaction --quick DB_NAME

Problem solved :)

Community
  • 1
  • 1
rekt
  • 91
  • 9