LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > General
User Name
Password
General This forum is for non-technical general discussion which can include both Linux and non-Linux topics. Have fun!

Notices

Reply
 
Search this Thread
Old 09-30-2004, 04:13 PM   #1
dkc_ace
Member
 
Registered: Dec 2002
Distribution: freeBSD 4.9/6.0
Posts: 194

Rep: Reputation: 30
mysql automated backup


I am running freeBSD 4.9. Is there a cronjob or some kinda of script that will automaticly backup mysql in 1 week intervals or however long i set it?
 
Old 09-30-2004, 05:06 PM   #2
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
call me old fashioned but I just tar.gz /var/lib/mysql

add the databases as you like. i.e. /var/lib/mysql /mysql
/var/lib/mysql /foo

Maybe there are cooler methods, but it seems to work.
 
Old 09-30-2004, 05:09 PM   #3
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
well and then the whole cronjob thingy

#!/usr/bin/perl

# Backup stuff to a remote server

use Net::FTP;

# Create tmp tar file of the $ARGV[0] directory
my $dir = shift;
my $host = shift || yourhost';
my $user = shift || 'foo';
my $pass = shift || 'pass;
my $remote_dir = shift || 'backups';

die "No directory to backup! - $dir\n" unless ($dir && -d $dir);

# Times
# We need this so we can allow a weeks worth of backups
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time());

my $filename;

if($dir =~ m|([^/]+)$|) {
$filename = $1 . '.tar.gz';
} else {
$filename = ' backup. '.$wday.'.'. $hour . '.' . $min . '.' . $sec .'.tar.gz';
}

# To the tar
system("tar zcf /tmp/$filename $dir");

my $ftp = Net::FTP->new($host, Debug => 0);
$ftp->login($user, $pass);
$ftp->cwd($remote_dir);
$ftp->delete($filename);
$ftp->binary;
$ftp->put('/tmp/'.$filename);
$ftp->quit;

unlink('/tmp/'.$filename);


then a shell script like

#!/bin/bash

./backup.pl /var/lib/mysql
echo "done mysql"
sleep 60
sync
./backup nextone


etc.

crontab -e

* * 1 * * /home/foo/backup

or so

Simple small efficient. Probably there are cooler things about i.e. webmin backup module depends really on your mileage.

Last edited by DrNeil; 09-30-2004 at 05:14 PM.
 
Old 09-30-2004, 05:27 PM   #4
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
If your ftp server supports the right commands maybe use ftpsync.
 
Old 09-30-2004, 05:32 PM   #5
dkc_ace
Member
 
Registered: Dec 2002
Distribution: freeBSD 4.9/6.0
Posts: 194

Original Poster
Rep: Reputation: 30
Quote:
Originally posted by DrNeil
If your ftp server supports the right commands maybe use ftpsync.
sftpd?
 
Old 09-30-2004, 07:04 PM   #6
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
Quote:
Originally posted by dkc_ace
sftpd?
http://search.cpan.org/~drolsky/Net-...ib/Net/SFTP.pm


use Net::FTP; -> use Net::SFTP;


never used this but i assume its a starting point

http://www.stupidfool.org/perl/net-ssh/

etc
 
Old 09-30-2004, 07:07 PM   #7
dkc_ace
Member
 
Registered: Dec 2002
Distribution: freeBSD 4.9/6.0
Posts: 194

Original Poster
Rep: Reputation: 30
Thanks but i have figured out what im going to do. I am going to make a cronjob such as this:

1. Check to see if MySQL is running, and if not, to start it.
2. Run the script that performs the backup job.

This is a sample crontab entry for FreeBSD (which is what your server is running) that checks to see if MySQL is running (if it's not, it starts it) and does the backup:


Code:

*/15 * * * * your_userid /path/to/mysql startup_options
* 23 * * * your_userid /path/to/backup/script


The first line checks every 15 minutes to see if MySQL is running, and starts it if it's not

The second line runs the backup script every night at 11:00 PM.

The syntax for cron is:
minute hour day month wday user command

Also, the line that identified this system as FreeBSD is this:

FreeBSD 4.4-RELEASE (SERVER) #0

You can check the FreeBSD handbook for more info on using cron:

http://www.freebsd.org/doc/en_US.IS...uning-cron.html






sh name the script.sh
If the script has exe perms do

Code:

./name the script.sh


Examples how to set cron:
http://www.webmasters-central.com/t/cron.shtml

Code:

sh #!/bin/sh date=`date '+%m-%d-%y'`mysqldump -u database_username -pdatabase_password database_name > ~/mysql_backup/database_name.$date.sh


Where would the best place to put the .sh file?
 
Old 09-30-2004, 08:39 PM   #8
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
Why ftp? FTP sucks and should be avoided if possible.

I say go with the last script mentioned using mysqldump to backup the database and then use scp or rsync using ssh keys for no password prompts mess in a script if you want to dump it to another server, which is smart for backing up, then its in two locations..
 
Old 09-30-2004, 11:18 PM   #9
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
You use ftp when your ISP provides you with FTP backup space the size of your harddisk for free

nix sftp etc dimdadum paddabum.

But if you have a full linux system to backup .. then do the whole ssh shingy dingy
 
Old 10-01-2004, 06:49 AM   #10
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
Quote:
Originally posted by DrNeil
You use ftp when your ISP provides you with FTP backup space the size of your harddisk for free
They do that in Scotland? Never heard of any ISP giving users backups space here in the USA. The most ISP's give with standard accounts is 5 to 10MB of space for a homepage...
 
Old 10-01-2004, 02:46 PM   #11
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
Germany: 69 Euro all inclusive, really cheap. You can get Debian or Suse on it.
Free traffic of 300 Gig. 2 gig Pentium 80 Gig hard disk 1 gig memory (Probably now cheaper, but you gonna have to start sometimes). It comes with external reboot possibilities, remote tty login, remote repair system etc.

Before we were in the States dead dear.

The system is up now 7 months and runs fine.

Opps I think the term problem is in ISP; i ment hosting provider which is kinda an internet service provider. I worked in Germany at one who did hosting and connectivity, I always mix that up.

Sorry if that wasn't clear.

The FTP space is obviously some big backup solution, which has 100's on them so sftp aint an option until they install it. Traffic for backup is free.

Last edited by DrNeil; 10-01-2004 at 02:53 PM.
 
Old 10-01-2004, 09:08 PM   #12
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
Ahh.. yeah ISP here provides Internet.. Hosting provides hosting. I actually had a host provider that actually provided ssh access which was nice but now I don't believe in spending money for hosting, not when I can do it myself on my own ISP's connection.
 
Old 10-02-2004, 08:19 AM   #13
DrNeil
Member
 
Registered: Aug 2004
Location: Scotland
Distribution: Debian, Suse, Knoppix, Dyna:bolic, Mandrake [couple of years ago], Slackware [1993 or so]
Posts: 150

Rep: Reputation: 15
Quote:
Originally posted by trickykid
host provider that actually provided ssh access which was nice
We have some finance guys as customers, so it's better to outsource with all the crap they want. Exchange Server 9 Euro's to rent. I'll not gonna buy a windoze machine and then they suddenly decide they want something else next month. Also an NTL business line at home is more expensive than a hosted server with all quirks. Then I would to have buy an extra mchine for all that and when things get bigger and better I would have to buy the next machine.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
automated backup with cygwin and rsync hoover93 Linux - Networking 1 07-06-2005 08:35 PM
Remote, automated backup: Windows --> Linux server hamish Linux - Software 1 12-07-2004 10:46 AM
Automated Backup via FTP KePSuX Linux - Newbie 3 02-11-2004 08:25 AM
automated FTP backup josephswagner Linux - Software 2 06-06-2003 04:42 PM
automated network backup hinoiri Linux - Networking 1 12-03-2002 11:40 PM


All times are GMT -5. The time now is 01:33 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration