Automatic database Backup

General discussion of OpenCATS

Moderators: RussH, cptr13

Forum rules
Just remember to play nicely once you walk through the door. You can disagree with us, or any other commenters in this forum, but keep comments directed to the topic at hand.

Automatic database Backup

Postby Ilovecats » 13 Mar 2017, 21:58

Hi there,

Is there a way to automate the backup daily? i can do it manually but this would save me time

Thanks
Ilovecats
 
Posts: 28
Joined: 31 Jan 2017, 18:00

Re: Automatic database Backup

Postby cptr13 » 14 Mar 2017, 10:16

This can be automated. It will be a little but technical. What environment are you running opencats in again? Windows or shared hosting?
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Re: Automatic database Backup

Postby Ilovecats » 14 Mar 2017, 13:58

windows
Ilovecats
 
Posts: 28
Joined: 31 Jan 2017, 18:00

Re: Automatic database Backup

Postby cptr13 » 14 Mar 2017, 13:59

Ok. I'll get back to you on this later
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Re: Automatic database Backup

Postby manuel » 14 Mar 2017, 14:28

On the old website was some documentation about automated Back ups using cron jobs. Its was very complicated. Not intended for the average user.
User avatar
manuel
 
Posts: 87
Joined: 24 Jun 2014, 15:20
Location: The Netherlands

Re: Automatic database Backup

Postby cptr13 » 14 Mar 2017, 14:30

For windows? Or Linux?
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Re: Automatic database Backup

Postby manuel » 14 Mar 2017, 19:29

It was Linux.
This is how the script was shown on the website.
*****************************************************************

How to script database backups

Backup all databases nightly w/ mysqldump

(from linux.org) So, I want to take a shell script and be able to put it on any machine - and have it backup the databases on that machine using mysqldump.. and put them each separately into a backup directory.. here's what I came up with.

Can you make it better?


#!/bin/bash

DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`"
DB_USER="root"
DB_PASSWD="secretttt"
HN=`hostname | awk -F. '{print $1}'`

# Create the backup directory
mkdir -p $DB_BACKUP

# Remove backups older than 10 days
find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} ;

# Option 1: Backup each database on the system using a root username and password
for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -vi information_schema);
do mysqldump --user=$DB_USER --password=$DB_PASSWD --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
done

# Option 2: If you aren't using a root password then comment out option 1 and use this
# for db in $(mysql -e 'show databases' -s --skip-column-names|grep -vi information_schema);
# do mysqldump --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
# done

# Make it so only root can read the backup files
chmod -R 600 $DB_BACKUP


If you use this, throw this text into something like /usr/local/bin/mysql_backup.sh and since it has mysql's root password in it, make sure that you chmod 700 to it so no one else can read it. Then just call it from cron like:


30 3 * * * /usr/local/bin/mysql_backup.sh


BTW, a simpler way to grab all of them is to use the --all-databases flag in the mysqldump command.. but it doesn't make nice separate files for you..
User avatar
manuel
 
Posts: 87
Joined: 24 Jun 2014, 15:20
Location: The Netherlands

Re: Automatic database Backup

Postby cptr13 » 14 Mar 2017, 19:42

Ok thank you. I'll get that into the documentation. However, this question is local windows xampp. So the solution will be different.
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Re: Automatic database Backup

Postby cptr13 » 15 Mar 2017, 10:55

There are two steps/sides to manual backups for OpenCATS.
1) Backup your MySQL/MariaDB database
2) Backup your OpenCATS directory

This post only covers point 1 for a local, Windows, XAMPP installation.

I will follow up later with another post to automate point 2. At some point, I will include this in the documentation.

For now, follow the steps on this link, it looks easy to follow. Please post back and let us know how it goes.

https://www.tectut.com/2016/04/automati ... n-windows/
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Re: Automatic database Backup

Postby cptr13 » 15 Mar 2017, 10:57

Please remember, for automatically scheduled processes to run...your computer needs to be on! So if you set this to run at 3AM....you'll need to make sure the computer is on :D
User avatar
cptr13
 
Posts: 405
Joined: 23 Apr 2008, 01:53
Location: Mobile, AL

Next

Return to OpenCATS Talk

Who is online

Users browsing this forum: No registered users and 1 guest