0

I'm on shared hosting, so I want to avoid using too much system resources. I have a crontab that schedules a MySQL database backup. The database is about 2gb before compression. When that crontab runs, the system seems to crash, because the http server stops for a minute or 2.

Is there anything that I can add or edit to my command that would run the db backup job gradually or in smaller steps to avoid server crashes?

Here is the command that causes the problem:

mysqldump --add-drop-table --user=xxxxxxxxx --password='xxxxxxxxxxxx' db_name | gzip >/path/to/my/backup_directory/db_backup.dmp.gz

Any tips please? or should I ask my host to change any settings in my account?

0

Try using the -q option of mysqldump to see if it could help. Otherwise, try using multiple mysqldumps specifying individual tables.

| improve this answer | |
  • Please add more details about what -q does and how it could help solve the issue. – ypercubeᵀᴹ Oct 26 '16 at 20:15
0

Complain to the Host that he has not let you chew up lots of CPU and I/O. He will probably say "Pay more". That may be your solution.

While you are complaining to him, ask if he is already doing backups. If he is, they may be much less invasive (due to replication and/or LVM).

| improve this answer | |
0

First of all - think Your server not crash, but just stoped (rebooted) by VPS provider - they normally have script which restart server in case of loading.

in Your case You can check - all of Yours tables InnoDB (if not - convert) and add --single-transaction option

mysqldump --single-transaction --user=xxxxxxxxx --password='xxxxxxxxxxxx' db_name > dump.sql

gzip result file

I just prefer split this steps

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.