How to do daily database backup automatically ?

zukro · Jan 2nd 2017

How to do daily database backup automatically?

MysteryCode · Jan 2nd 2017

Using a server's cronjobs for example.

A German thread about this topic and a Shell-script: Datenbanken richtig sichern

**Tim Düsterhus** · Jan 2nd 2017

Hi

there also is a help guide in English: https://www.woltlab.com/articl…acking-up-your-community/

cywa-management · Jan 2nd 2017

I modified MySQL Dumper's Perl script for my requirements and added a file backup.

zukro · Jan 2nd 2017

I'll take a look, I am really new to SSH,

Quote

Create a cronjob that regularly executes your database backup script at least once a day.

about cronjob, it's in ACP right?

cywa-management · Jan 2nd 2017

Not these Cronjob's. A cron from the server where your hosting your webpage.

zukro · Jan 2nd 2017

Quote from peoples

Not these Cronjob's. A cron from the server where your hosting your webpage.

I see, I found it in Cpanel, thanks.

cywa-management · Jan 2nd 2017

You're welcome.

zukro · Jan 2nd 2017

About creating file system backup, Is there anyway to do it automatically?

Quote

Second: Create a file system backup using the FTP program of your choice.

Make sure to download the files in binary mode, otherwise you will destroy them!

Double check that there were no error messages, otherwise your backup will be incomplete.

Double check that the download was not interrupted, otherwise your backup may be incomplete.

jens1o · Jan 2nd 2017

You could tar them on the server side, but I would recommend download them at least once per week.

zukro · Jan 2nd 2017

Quote from jens1o

You could tar them on the server side

It is manually done, isn't it?

Fighter456 · Jan 2nd 2017

You can do it with a cronjob, too.

tar cf backup.tar -C <path to files> .

zukro · Jan 29th 2017

Bash

#!/bin/bashDATABASE=<hier den Namen der Datenbank einfügen>cd $(dirname $0)echo "$(date +%Y-%m-%d_%H-%M-%S) Backup start"mysqldump --defaults-file=$(pwd)/mysqldump-zugangsdaten --single-transaction --skip-lock-tables --net_buffer_length 16384 $DATABASE > $(pwd)/$DATABASE.sqlecho "$(date +%Y-%m-%d_%H-%M-%S) Backup end"

Could you translate the German part to English?

https://www.woltlab.com/articl…acking-up-your-community/

zukro · Jan 29th 2017

From this article: https://www.woltlab.com/articl…acking-up-your-community/

from section “Shared hosting with SSH access”, does step 4 to 7 can be done via file manager in cpanel?

jens1o · Jan 29th 2017

zukro Here you go!

Bash

#!/bin/bash
DATABASE=<enter the name of your database here>
cd $(dirname $0)
echo "$(date +%Y-%m-%d_%H-%M-%S) Backup start"
mysqldump --defaults-file=$(pwd)/mysqldump-credentials--single-transaction --skip-lock-tables --net_buffer_length 16384 $DATABASE > $(pwd)/$DATABASE.sql
echo "$(date +%Y-%m-%d_%H-%M-%S) Backup end"

Note: I'd changed mysqldump-zugangsdaten to mysqldump-credentials too.

zukro · Feb 5th 2017

Quote from jens1o

DATABASE=<enter the name of your database here>

the database name without <> right?

MysteryCode · Feb 5th 2017

eg. DATABASE="mytestdatabase"

zukro · Feb 12th 2017

Hi, I successfully doing database backup using the previous guide, but it result only one .SQL file, the previous backup file will be replaced.

How can I am at least to have 3 days .SQL files backup?

**Tim Düsterhus** · Feb 12th 2017

Hi

zukro You can back up the SQL file itself however you like. To get "rolling" backups you could insert this before the second echo (after the mysqldump command):

Code

rm $DATABASE-3.sql
mv $DATABASE-2.sql $DATABASE-3.sql
mv $DATABASE-1.sql $DATABASE-2.sql
mv $DATABASE.sql $DATABASE-1.sql

zukro · Feb 12th 2017

Bash

#!/bin/bash
DATABASE=<enter the name of your database here>
cd $(dirname $0)
echo "$(date +%Y-%m-%d_%H-%M-%S) Backup start"
mysqldump --defaults-file=$(pwd)/mysqldump-credentials--single-transaction --skip-lock-tables --net_buffer_length 16384 $DATABASE > $(pwd)/$DATABASE.sql
rm $DATABASE-3.sql
mv $DATABASE-2.sql $DATABASE-3.sql
mv $DATABASE-1.sql $DATABASE-2.sql
mv $DATABASE.sql $DATABASE-1.sql
echo "$(date +%Y-%m-%d_%H-%M-%S) Backup end"

do you mean like that Tim Düsterhus ?