Database Backup..

[FredC]

My Server is copying the database nightly at around 3am for a backup and dumping it i dont know where.. the problem (besides not knowing where the backups are being stored) is that this puts quite a strain on my server and causes a half dozen server errors every single night and sometimes more depending on the activity levels on the forum itself that time of night..

So id be interested in knowing my options in regards to backing up my DB that would be less resource intensive??

I know it would help if you knew how i was currently backing up my data but i dont really know that either..

I am on a Dedicated Apache CENTOS 6.5 x86_64 standard server with WHM/cPanel

These backups have rendered my forum 90% useless for an hour each night and has destroyed my international traffic.. I really need a better solution. Preferably Free!!

HELP!!

 

[rdn]

This: https://community.centminmod.com/resources/auto-database-backup-script.2/

 

[Kintaro]

My Server is copying the database nightly at around 3am for a backup and dumping it i dont know where.. the problem (besides not knowing where the backups are being stored) is that this puts quite a strain on my server and causes a half dozen server errors every single night and sometimes more depending on the activity levels on the forum itself that time of night..

do you know at least how are you backing up your db? from a control panel? with a shell script?

 

[FredC]

do you know at least how are you backing up your db? from a control panel? with a shell script?

Unfortunately No..

I know more about Unicorns then i do Servers. Once upon a time we hired a young guy to manage our server for us.. He actually was a member of my forum and worked for the company that hosted my site.. He did all the server maintenance then some.. unfortunately for me and good for him he wound up getting work with Facebook and hasnt been heard from since.. That was at least a year ago.

 

[rdn]

and good for him he wound up getting work with Facebook

That's very good for him

 

[TPerry]

Does cPanel have the ability to do a full backup? I haven't played with it that much (just got a license for it a few days ago but haven't set up a VPS to install it on yet).
If so, that could be part of the problem.

 

[Mr. Goodie2Shoes]

Does cPanel have the ability to do a full backup?

By full you mean this? http://www.liquidweb.com/kb/enable-cpanel-backups-in-whm/

 

[TPerry]

By full you mean this? http://www.liquidweb.com/kb/enable-cpanel-backups-in-whm/

Like I said... I'm not sure since I don't use panels and just recently purchased a cPanel license but haven't taken the time to set up the VPS to install it on.

 

[Mr. Goodie2Shoes]

There is a full back-up and restore option in cPanel.

 

[TPerry]

There is a full back-up and restore option in cPanel.

From comments that I've read about it, it's pretty much all inclusive and if that is correct, then it would tend to be resource intensive when performing the task.

 

[Mr. Goodie2Shoes]

From comments that I've read about it, it's pretty much all inclusive and if that is correct, then it would tend to be resource intensive when performing the task.

Don't know since I don't use cPanel for my VPS.

 

[Kintaro]

@FredC
you can launch a search for files bigger that X size. In this way you are going to find where backups are stored.
or
take a look at cron entries, if the guy created a custom script to run the backup process you can find it there.

 

[Carla Birch]

If your just wanting to do a database back up, you could try this add-0n: https://xenforo.com/community/resources/sypex-dumper-backup-and-restore-your-database.1102/
You can then backup your database direct from your Xenforo Admin CP and save the backup file to your computer, it also saves a copy in your backup folder that you make within your sites install path.

My database thats almost 400MB becomes around a 40MB backup file and only takes around 30 sec to backup.

 

[rdn]

If your just wanting to do a database back up, you could try this add-0n: https://xenforo.com/community/resources/sypex-dumper-backup-and-restore-your-database.1102/
You can then backup your database direct from your Xenforo Admin CP and save the backup file to your computer, it also saves a copy in your backup folder that you make within your sites install path.

My database thats almost 400MB becomes around a 40MB backup file and only takes around 30 sec to backup.

View attachment 83128

When I'm starting using MariaDB 10 with Sypex I have serious problem with it.
The reason I stop using Sypex as my dumper.

Don't know how to fix, I even ask for assistance of Me @Brogan.

 

[FredC]

Thanks for the advice guys, good stuff.. I havent had a chance to try anything special yet but i will certainly rport back when i start solving trying to solve and fix this issue..

 

[Mr. Goodie2Shoes]

Just my two cents: I created a bash script and added it to crontab... everyday, the database is dumped on a private folder, everything is copied from the public folder to the private one, and push them on my bitbucket's private repo... not so secure but does the job

EDIT: and also, this does not put that much pressure on the resources

 

[rdn]

I created a bash script and added it to crontab... everyday, the database is dumped on a private folder, everything is copied from the public folder to the private one, and push them on my bitbucket's private repo... not so secure but does the job

Care to share the script?

 

[Mr. Goodie2Shoes]

Care to share the script?

Of course

Here's what I've created:

#! /bin/bash
DB_NAME=''
DB_USER=''
DB_PASS=''
BACKUP_DIR=''
BACKUP_FROM=''
TIMESTAMP=$(date +'%F %T (GMT%:z)')
cd "$BACKUP_DIR"
mysqldump --opt --single-transaction -u$DB_USER -p$DB_PASS $DB_NAME > "$DB_NAME.sql"
tar -zcpf files.tar.gz "$BACKUP_FROM"
BRANCH_NAME=$(date +'%Y%m%d')
git checkout -b $BRANCH_NAME
git add -A
git commit -q -m "$TIMESTAMP"
git push origin $BRANCH_NAME
rm "$DB_NAME.sql" files.tar.gz

Create a .sh file some where else beside in public and in the back up directory
e.g. /root/daily_backup.sh

fill in the necessary variables 'BACKUP_DIR' is the directory where everything will be backed up (e.g. /root/backups) make sure to init a git repo inside that directory and add your bitbucket repo:

git remote add origin https://{username}:{pass}@bitbucket.org/{username}/{repo name}.git

and 'BACKUP_FROM' is the public facing diretory, (e.g. /home/user/public_html).

Make sure to give the sh file execution capabilities:

chmod 755 /root/daily_backup.sh

And add a cron entry:

crontab -e

At the end of the file, create a new line and add:

0 0 * * * /root/daily_backup.sh > /dev/null 2>&1

And if you want some kind of logging system so that you can check if there's any error change /dev/null to /root/daily_backup.log


Disclaimer
Use at your own risk, I wont be held responsible if your server gets f**ked up Though mine is working without any hiccups

 

[rdn]

Value to use on BACKUP_FROM?

 

[Mr. Goodie2Shoes]

Value to use on BACKUP_FROM?

The directory you want to back up.