sunil sharma said: > Dear Friends > > I am new in this list > I am using mysql on linux > > My problem is about the backup of mysql database > > We are having 50 Databases and each database is of > minimum of 300 MB in size and maximum of 2 G.B size > > I am planning to take incremental backup of the > database > For that i am planning to use mysqldump,But i m bit > doughtful about the mysqldump because the size of the > database is very huge > I tried but it's taking a lot of time > > So can anybody guide me regarding the mysql backup > policies?
Hello and thnx for your answers. Actually I found an alternative solution. Was searching through the ftp site of the ppl. that we lease our server from and found this script. Of course if anyone has any feedback, good or bad let me know, but I tested it and it seems to work for me. Might also come in handy when it comes time for an acquaintance of mine to migrate 100+ clients onto our servers. It's a Little Perl script that I think will help answer this first persons question as well. when you need to restore it simply untar them in the /var/db/mysql directory or where ever your mysql db lies. I'll pate it simply becuse I don't think the mysql list will suport atachments, ---------------begin code----------------------------------- #!/usr/bin/env perl # $Id: mysql-backup.pl,v 1.1.1.1 2002/12/31 17:33:45 smerkel Exp $ # Author: Steve Merkel # Email: [EMAIL PROTECTED] # Date: 12.29.2002 # Notes: This is a new implementation of the mysql_backup script. # This version uses mysqlhotcopy and works with all control panels # and no control panels. It also doesn't add the full path to the # compressed database. Since it doesn't use mysqldump, the # resulting backup simply needs to be copied into /var/lib/mysql # under whatever name you want to give the database and restart # mysql. No import of data is needed. # There are several global variables that need to be set below. # TODO: # # (12.29.2002) # # - Add the ability to define everything via a command-line # option. # # - Make it work with Ensim. (Test with ensim anyway.) use strict; # Configuration Data # --------------------------------------------------------------- # Location where the live databases live. my $LIVEDBDIR = '/var/lib/mysql'; # Directory where to store the backup data. This directory # and the above directory can't be the same. my $DATADIR = '/root/mysql-backup/'; # This defines which control panel is installed on the server. # Current choices are: cpanel,psa,none my $CP = 'none'; # Admin password. Set these if the control panel isn't a supported # control panel and you need a username and password to connect to the # database. my $sqlpass = ''; my $sqluser = 'mysql'; # Control Panel Specific Values # --------------------------------------------------------------- # The location of the PSA shadow file. my $psa_shadow = ''; # Subroutines # --------------------------------------------------------------- # Function: getPSAPass # This function grabs the admin password out of the PSA shadow file. sub getPSAPass { # Open the shadow file open(PSASHADOW,$psa_shadow) || die "Can't open $psa_shadow\n"; # Get the PW. my @psapw=<PSASHADOW>; # Close the file. close(PSASHADOW); # Return the password. return $psapw[0]; } # Function genDBList # This function uses the mysql client to generate a file with a list of # databases. Cheap hack. No depend. sub genDBList { # Get the user and password passed into the function. # Get the user and password passed into the function. my $sqluser = shift; my $sqlpass = shift; # This array will contain the list of mysql databases. my (@dblist); # Bust out some mad system calls to get the db names. if (($CP =~ /psa/) or (($sqluser ne '') and ($sqlpass ne ''))){ @dblist = `/usr/bin/mysql --user=$sqluser --password=$sqlpass -e "show databases;"`; } else { @dblist = `/usr/bin/mysql -e "show databases;"`; } # Return our list. return(@dblist); } # Function: copyDB # This function will use the mysqlhotcopy command to copy the databases # listed in @dblist to $DATADIR. sub copyDB { # Get the user,password and database name info. my ($sqluser,$sqlpass,@dblist) = @_; # Check to make sure $DATADIR is defined. if ($DATADIR eq '') { die "You must define \$DATADIR\n"; } # Check to see if the datadir exists, and create it if not. if (! -d $DATADIR) { mkdir($DATADIR) || die "Can't create " . $DATADIR . ". Error: " . $! .. "\n"; } # Just in case someone desides to put the location of the existing databases in. # Assumes default location. if ($DATADIR eq $LIVEDBDIR) { die "You want to overwrite your databases?\n"; } # Run mysqlhotcopy against all databases. foreach my $line (@dblist) { # Drop the eol char. $line =~ /^(.*)$/; my $db = $1; # Make sure the db is there. Won't die if it isn't, it just looks # like I'm not error checking if I omit this. if ( ! -d $LIVEDBDIR . '/' . $db ){ # Check to see if the database name is 'Database'. This is crap that mysql show databases gives # and shouldn't exist. However, someone could have a database named "Database" so we want # to provide the information. if ($db =~ /^Database$/) { print "Skipping \'$db\'. Doesn't seem to exist. This is probably normal in this case.\n"; } else { print "Skipping \'$db\'. Doesn't seem to exist.\n"; } next; } print "Copying $db...\n"; # If we got user and password data. Use it. if (($sqluser ne '') and ($sqlpass ne '')){ # System call to copy the database. Overwrite existing directories. system('/usr/bin/mysqlhotcopy --allowold --user=' . $sqluser . ' --password=' . $sqlpass . ' ' . $db . ' ' . $DATADIR . ' 2>& 1>/dev/null'); } else { # System call to copy the database. Overwrite existing directories. system('/usr/bin/mysqlhotcopy --allowold ' . $db . ' ' . $DATADIR . ' 2>&1>/dev/null'); } compressDB($db); } } # Function: compressDB # This function will tgz all of the copied database directories. sub compressDB { # Grab the dblist. my @dblist = @_; # Hump the db list. foreach my $line (@dblist){ # Strip the EOL char. $line =~ /^(.*)$/; my $db = $1; # If the $DATADIR directory doesn't exist, create it. if ( -d $DATADIR . '/' . $db) { print "Compressing $db...\n"; # System calls to create the the tarballs and delete the dirs after doing so. system('cd ' . $DATADIR . ';/bin/tar cvzf ' . $db . '.tgz ' . $db . ' 2>&1>/dev/null'); print "Cleaning up after $db...\n"; system('rm -rf ' . $DATADIR . '/' . $db . ' 2>&1>/dev/null'); } } } # # Main # ------------------------------------------------------------------- # Global Variable my @dblist; # If this is a psa install, get the admin password and generate the DB list. # Otherwise just generate the DB list using the variables above. if ($CP =~ /psa/) { $sqlpass = getPSAPass; @dblist = genDBList('admin',$sqlpass); copyDB('admin',$sqlpass,@dblist); } else { @dblist = genDBList($sqluser,$sqlpass); copyDB($sqluser,$sqlpass,@dblist); } # Exit -- Ah DUH! exit 0; -------------end code ------------------------------------ OK, a little long for a perlscript but it works, and much faster than mysql dump -- Jerry M. Howell II --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php