Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have MySQL 5 installed in a Ubuntu server. I want backup the database for:

  • latest 7 days
  • weekly

and store those backups in another server. I prefers the tool runs in the same server that host the Database, and store those files in a remote server using for example FTP access.

I have a cPanel installed but I'm not sure if the tools the cPanel brings is the recommended, besides doesn't allow me automatically copy the backup files to another server.

The mysqldump or mysqlhotcopy commands doesn't satisfy either the needs of copy to another server.

Which tool is recommended? Any comment will be appreciated. I checked others replies to similar questions and are old or incomplete.

share|improve this question
2  
Your question is not a bad one, but there are a few problems with the way you're asking it -- Please review the Server Fault FAQ, This Stack Exchange Blog post, and this Meta topic about cPanel questions and see if you can rework this question a bit. In all likelihood the answer to your question is going to be "Write a shell script"... –  voretaq7 Jan 24 '13 at 2:21
    
There are many, many backup scripts for MySQL on the internet. NONE of those do what you need? –  fukawi2 Jan 24 '13 at 4:21

3 Answers 3

up vote 1 down vote accepted

I really recommend AutoMySQLBackup for your purposes. It covers your requirements with some added features. However, with remote backups, the design is to run the tool in the backup server and read the remote database directly. So no FTP etc. required.

It lacks good documentation a bit, but on the other hand it is really simple to use. AutoMySQLBackup is additionally available in the Ubuntu repositories.

Have fun :)

share|improve this answer
    
thanks, I will check it –  leticia Jan 24 '13 at 7:43

The easiest way I could think is to do something like the fallowing mysqldump [mysqldump options] | gzip -c | ssh user@remotehost "cat >

if this does not satisfy your need try and explain why

share|improve this answer

This is how I do the backup :

#!/bin/bash
#############   Backup script for mysql databases
###########     Copyright Ivaylo Rusimov 2012
#############       GPL
PATH=/bin:/sbin:/usr/sbin:/usr/bin:/usr/local/bin
export DB_BACKUP="/home/backups/daily"
export DB_USER="root"
export DB_PASSWD="your-some-password"

rm -rf $DB_BACKUP/07
mv $DB_BACKUP/06 $DB_BACKUP/07
mv $DB_BACKUP/05 $DB_BACKUP/06
mv $DB_BACKUP/04 $DB_BACKUP/05
mv $DB_BACKUP/03 $DB_BACKUP/04
mv $DB_BACKUP/02 $DB_BACKUP/03
mv $DB_BACKUP/01 $DB_BACKUP/02
mkdir $DB_BACKUP/01 

mysqldump   --user=$DB_USER --password=$DB_PASSWD [db_name] > $DB_BACKUP/01/[db_name]-`date +%Y-%m-%d`.sql

######################## RESTORE ###############################
######################## Example:###############################
#
# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
exit 0

The script is called every once a day, every midnight and therefore you have backup for 7 days back. After that I personaly have backuppc installed on another server, which backups the dumped files trough rsync.

If you don't want a backuppc, you can make another script, which will start after the first one completes and just copies the created files to another location. (if you want an example of that script, just say what way you can provide - ssh, rsync, scp or (i don't prefer) - FTP

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.