Linux Perform MySQL Backup to time-based path from Webmin

Question

I was trying to setup a scheduled task in Linux Ubuntu Server 12.04 (CronJob) to perform a daily backup of all my MySQL Databases on midnight.

I have installed the known Webmin (A nice web interface for managing the Web Servers).

So my issue is: whenever the backup is being performed, the files are getting overwritten!

That means: The backup of the day before yesterday are LOST, only the "Yesterday" backup is being saved!

I have tried something like setting dynamic file path like:

/var/www/mysqlbackups/%d-%m-%y

but I had no success with that :(

Can anybody help me.

Thanks alot guys.

Vishnu

Answer 1

I made a shell script (not for webmin). Put it in /etc/cron.daily.

The scripts makes a backup of the database (stores it as .gz), then uploads it by ssh to another server. For the auth. i setup ssh keys, so no password is needed. The backup files have a unique name, so you don't overwrite the backup files.

This is how you can create a filename within script:

now=`date  +%Y%m%d_%H%M`
dst_path=/var/local/backups
filename="$dst_path/$database.$now.sql.gz"

Then you should write a a small script that removes all backup files that are older then x days.

#!/bin/sh

#
# Creates a backup of a MySQL databases and uses ssh (sFTP) to send if  to another server
# This script shouldbe called from the crontab

PATH=/usr/sbin:/usr/bin:/sbin:/bin

# MySQL user and password
mysql_cmd=/opt/bitnami/mysql/bin/mysqldump
mysql_usr=user_name
mysql_pass=password

# destination ssh
dst_user=user_name
dst_hostname=192.168.1.1

# Database to backup
database=test

# create timestamp
now=`date  +%Y%m%d_%H%M`

# where we store the files
dst_path=/var/local/backups

# backup filename
filename="$dst_path/$database.$now.sql.gz"
dst_filename="$database.$now.sql.gz"

# run backup
$mysql_cmd -u $mysql_usr --password=$mysql_pass  $database | gzip > $filename

# upload to sever (ssh)
scp $filename $dst_user@$dst_hostname:

Menderes

Answer 2

MySQL Database Server > Module Config > Chose Yes on "Do strftime substitution of backup destinations?"

It work for me! :)

Schultz

And here is a list of variables if you turn on "variable substitution" -- virtualmin.com/documentation/id,template_variable_listing – Rustice

Answer 3

You can use dynamic path, but before you must enable it in module config:

System > Filesystem Backup > Module Config > Do strftime substitution of backup destinations? > Yes

(If you are not sure about placeholders, just click on the text "Do strftime substitution of backup destinations?" in the config and help will show you.)

Tailpipe

This is only for filesystem backups (vritualmin 4.08). The answer of @Mr Vector is the correct one. – Cosmogony

Answer 4

I had the same problem and solved like this :

From Server select MySQL Database Server. Go to Module config (top left), and select Do strftime substitution of backup destinations? to Yes.

I use db_%d-%m-%Y.sql format for backups. (db_13-03-2013.sql)

Aleciaaleck

Answer 5

You can do it in following way

Webmin > MySQL Database Server > select database > backup Database > file path in Other backup option you can use > Command to run after backup Enter ======> mv file path/filename file path/filename_date +"%Y%m%d%H%M%S".sql

Tick