Automated Database Backup

[hikashin-rae]

Hello guys,

Anyone have a script of automatic backup for server every 1 week? or per day?

Im using debian.

Thank you.

[Jey]

#!/bin/bash

usr="backup"
pw="yoursqldbpassword"
pw7z="your7zpassword"
dbs=(rathena board website)
date=$(date -I)

for db in "${dbs[@]}"
do
	filename="${db}/${db}_${date}.sql"
	echo "Dumping Database: $db ..."
	mysqldump -u $usr -p$pw $db > $filename # creates a sql dump
	tar cf - $filename | 7za a -si -p$pw7z ${filename}.tar.7z # compresses the dump
#	ncftpput -Vmu [email protected] -p ftp ftp.host.com / ${filename}.tar.7z # copy it to an ftp server
	rm $filename
done

I'm using something like this. I'd recommend to add an explicit database user for backups with read access only. And using 7zip is good for both compression and encryption of the data.

To run it on a daily basis I'd recommend using a cronjob:

30 5 1 * * cd ~/backup && ./backup_full.sh > /dev/null 2>&1
30 5 2-31 * * cd ~/backup && ./backup.sh > /dev/null 2>&1

Mine is split into two parts. backup.sh is the above script and dumps everything except the log database. It runs every day except the first day of the month. backup_full.sh also includes the log table and is run only once every month.

dbs=(rathena rathena_log board website) # dbs entry in full_backup.sh

 

Keep in mind: A backup on the same host will not help you at all if the hard drive is broken.

 

I'm also interested in other solutions, so keep on posting them

[Akkarin]

I use the following to grab both SQL and File based backups:

#!/bin/bash

dbhost='127.0.0.1'
dbuser='user'
dbpass='pass'
dbname='db'
savepath='/home/backups'

date=`date +%Y-%m-%d_%H%M`
month=`date +%Y-%m`
filename="$savepath/$month/"$dbname"_"$date".sql"

tarname="$savepath/$month/"$dbname"_"$date".tar.gz"

if [[ ! -d "$savepath/$month" ]]; then
	mkdir -p "$savepath/$month"
	chmod 700 "$savepath/$month"
fi

mysqldump --opt --host=$dbhost --user=$dbuser --password=$dbpass $dbname > $filename
chmod 400 $filename

dirname='/var/www/htdocs'
tar -cvpzf $tarname $dirname
chmod 400 $tarname

This is ideal for when you also have file based logs (like with FluxCP) so everything is backed up and saved to a location. I have another script running on a separate vps that remotes to the server that has the new backups and copies them. This script doesn't send them anywhere.

[hikashin-rae]

Thanks both of you :). i really appreciate it!

[Habilis]

Here is what I use

https://habilisbest.com/sql-database-automatic-backups

[hikashin-rae]

On 4/26/2017 at 10:35 PM, Habilis said:

Here is what I use

https://habilisbest.com/sql-database-automatic-backups

Thank you i really appreciate it

[Habilis]

I improved a bit my version