11 messages in com.mysql.lists.perlRE: Backing of Database -Dumping the ...| From | Sent On | Attachments |
|---|---|---|
| ed phillips | 06 Sep 2001 08:08 | |
| Nuno Dias | 06 Sep 2001 08:30 | |
| Rajeev Rumale | 10 Sep 2001 03:06 | |
| Stephen Howard | 10 Sep 2001 05:28 | |
| ed phillips | 17 Sep 2001 11:02 | |
| ed phillips | 17 Sep 2001 11:20 | |
| Todd Finney | 17 Sep 2001 11:36 | |
| Sean Cannon | 17 Sep 2001 11:47 | |
| Sean Cannon | 17 Sep 2001 12:14 | |
| Don Krause | 17 Sep 2001 12:17 | |
| Todd Finney | 17 Sep 2001 12:29 |
| Subject: | RE: Backing of Database -Dumping the database tables. |
|---|---|
| From: | Sean Cannon (scan...@ccgroupnet.com) |
| Date: | 09/17/2001 11:47:35 AM |
| List: | com.mysql.lists.perl |
Why not just copy the table's file over, gzip it, and archive it wherever? Do you explicitly need to export it, or are you just backing up?
MySQL, unlike many RDBMS, stores it's tables in actual filesystem-accessible file, rather than it's own filesystem/recordset system. As a result, you can copy out those files and save them (tape, CD, elsewhere on the hard drive, whatever). Afterwards, if the table becomes damaged beyond repairability, you can backup simply by shutting down the server, copying the file back over, making sure the ownership and permissions are correct, and restarting MySQL, without need for an export.
If you do want to export, MySQL has loads of functionality built in for this. Use the docs, luke.
-- Dodger
-----Original Message----- From: Todd Finney [mailto:tfin...@boygenius.com] Sent: Monday, September 17, 2001 2:37 PM To: msql...@lists.mysql.com Subject: Re: Backing of Database -Dumping the database tables.
At 06:11 AM 12/20/01, Rajeev Rumale wrote:
I need to take periodic back up of my database, tables and data both into a file. Its seems the many disk backup utilities are not able to back up the MySqL files as they are locked.
Hence I would like develop a script to dump whole database into file. The way we export the whole database strcture with data from MySql.
I would like to seek advice from all on the following things
I have a perl script that does this, which I've been wanting to get feedback on for some time. I've never had the chance to ask, though, and here's my window. The script follows, anyone is welcome to use it or pieces from it if they so desire.
I run this as a cron job nightly, as a user with the appropriate.
If it's broken for some reason, any advice on it would be most welcome.
Todd
#!/usr/bin/perl -w $|++; use strict;
# mySQL backup script, written by Todd Finney # last modified by Todd Finney, 8/12/2001
# a few variables which may change from system to # system, adjust accordingly my $data_dir = "/var/mysql/"; my $gzip = '/usr/bin/gzip'; my $mysqldump = '/usr/local/mysql/bin/mysqldump'; my $backup_dir = '/var/mysql_backup/'; my $opts = "--opt -q "; my $keep_number_of_update_backups = 3;
# get a list of all the databases, based on the # directory in which the db's reside. Done this way # so I don't have to update the script when I add # a new database. opendir(DIR,"$data_dir") || die "failed to open $data_dir $!\n"; my @databases = sort(readdir(DIR)); closedir(DIR);
# dump all the databases using mysqldump print "Backing up databases under $data_dir...\n"; for (@databases ){ next if /\.{1,2}/o; next unless -d $data_dir.$_; my $dir = $backup_dir.$_.'/'; my $file = $dir.$_.'.gz'; `/bin/mkdir $dir` if ! -d $dir; print "\tBacking up $_ in $dir..."; `$mysqldump $opts $_ | $gzip >$file`; print "done.\n"; }
# flush the logs, because the man page says to do so print "\nFlushing logs..."; `/usr/local/bin/mysqladmin flush-logs`; print "done.\n";
# get a list of all the old backup files and remove # the desired files (default any but the last 3) print "\nRemoving old update log files...\n"; opendir(LOGS, "$backup_dir"); my @ary = grep /^update-log/, readdir(LOGS); closedir(LOGS); my @sorted = reverse sort @ary; for (@sorted) { if ($keep_number_of_update_backups-- > 0 ) { print "keeping $_\n"; } else { print "deleteing ",$_,"\n"; unlink $backup_dir.$_; }
}
--------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail msql...@lists.mysql.com
To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail msql...@lists.mysql.com instead.