Re: Best way to back up mysql database
Hello list, I apologize if I somehow highjack the thread. I just want to tell the list for further references one way (my way) of doing mysql backup and to ask you if it's safe or not. I have 2 server: one of which is the master (named for the sake of brevity with M) in mysql terms and the second being the slave (named with S). I use mysqldump on S and deliver the tarballs to a remote location. I first stop the slave, do mysqldump and start the slave. Is this approach safe? Some might argue that it's not necessary or even expensive somehow to have 2 boxes *but* in my case I have 2 servers in production and one backups the other. thanks for your input and have a great day, v ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
Hello, Yes you're right but what I meant was something like this ;)) http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ It's simple, safe and faster than mysqldump. On 3 GB database it takes only 3-5 seconds. Best regards, Sebastian Tymkow ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Sebastian Tymków wrote: | Hello, | | You can store whole db files too. It's faster to run database from scratch | than use mysqldump and mysql source. Um... no. In general you cannot do this, and it is exceedingly irresponsible to suggest such a thing. The *only* way this would ever be workable is if you shut down MySQL completely while your backup process was running. For anything other than a pissant little hobbyist DB that does no real work, that idea is just going to be a non-starter. If MySQL is running, then there is no guarantee that the contents of any disk file has been properly synched with the in-memory working copy of the data. Basically what you'ld be copying off the disk drive will be full of inconsistencies and hence useless as a backup. This is why programs like mysqldump(1) exist. Cheers, Matthew - -- Dr Matthew J Seaman MA, D.Phil. Flat 3 ~ 7 Priory Courtyard PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate ~ Kent, CT11 9PW, UK -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREDAAYFAkjkhLYACgkQ3jDkPpsZ+VYlrQCgs+tBSJfxa8cKHF+oYsu5Cai2 qZEAoJ6lZupzjapi9ugrE20Jp6Ol1xxj =PMs9 -END PGP SIGNATURE- ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
Hi, 2008/10/1 John Almberg [EMAIL PROTECTED]: First, I wanted to say how great this list is. I'm a newbie FreeBSD admin and, besides the Handbook and Absolute FreeBSD (which never seems to leave my desk), this list is the best resource I have. I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Again, I have run into a problem which is stupidly obvious to experienced admins, I'm sure. I want to slap myself, but don't have time. I'll do that after I have a better backup system in place. I am just about to dive into Google in search of a solution, but thought I would fire off a quick request, in case there is an obvious solution that everyone uses. If there is, a name or URL will do. I'll figure out the rest. Any hints much appreciated. Not going home until this is fixed... I'd recommend http://sourceforge.net/projects/automysqlbackup/ very easy to set up. -- Zbigniew Szalbot ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
So, I thought I would post my ruby script for doing this backup...
It's a little verbose for some tastes, but I like to be able to see
what's happening in a script, blow by blow.
This script rotates the backups according to the day of the month, so
you get roughly 30 days backup. It also moves the backup to a remote
backup server, keeping the latest backup on the local machine for one
day. It also sends emails in case of error, and one email for
success, to give you that warm and fuzzy feeling that comes from
having a good backup.
-- John
#!/usr/local/bin/ruby
debug = true
day_of_month = Time.now.day
backup_file = all.mysql.+day_of_month.to_s+.txt
remote_backup_location = [EMAIL PROTECTED]:backup_dir #
user must be able to login to backup_host.com without password
db_user = username
db_pass = password
db_host = dbhost
notify_email = [EMAIL PROTECTED]
# - no configuration below this line
# remove yesterday's local backup
puts removing previous backups if debug
`rm all.mysql.*.gz`
puts remove status: #{$?.exitstatus} if debug
# create backup file
backup_command = /usr/local/bin/mysqldump -Q -u#{db_user} -p#
{db_pass} -h#{db_host} --all-databases #{backup_file}
puts backup_command if debug
`#{backup_command}`
puts backup status: #{$?.exitstatus} if debug
unless $?.exitstatus == 0
`echo Mysql backup failed with status: #{$?.exitstatus} | mail -
s Mysql_backup Error #{notify_email}`
exit
end
# zip it
zip_command = /usr/bin/gzip #{backup_file}
puts zip_command if debug
`#{zip_command}`
puts zip status: #{$?.exitstatus} if debug
unless $?.exitstatus == 0
`echo Gzip failed with status: #{$?.exitstatus} | mail -s
Mysql_backup Error #{notify_email}`
exit
end
# move to backup directory
move_command = scp #{backup_file}.gz #{remote_backup_location}/#
{backup_file}.gz
puts move_command if debug
`#{move_command}`
puts move status: #{$?.exitstatus} if debug
unless $?.exitstatus == 0
`echo SCP failed with status: #{$?.exitstatus} | mail -s
Mysql_backup Error #{notify_email}`
exit
end
`echo Successfully backed up mysql to #{backup_file} | mail -s
Mysql_backup Success #{notify_email}`
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
Hello, You can store whole db files too. It's faster to run database from scratch than use mysqldump and mysql source. Best regards, Shamrock ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Best way to back up mysql database
First, I wanted to say how great this list is. I'm a newbie FreeBSD admin and, besides the Handbook and Absolute FreeBSD (which never seems to leave my desk), this list is the best resource I have. I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Again, I have run into a problem which is stupidly obvious to experienced admins, I'm sure. I want to slap myself, but don't have time. I'll do that after I have a better backup system in place. I am just about to dive into Google in search of a solution, but thought I would fire off a quick request, in case there is an obvious solution that everyone uses. If there is, a name or URL will do. I'll figure out the rest. Any hints much appreciated. Not going home until this is fixed... -- John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
On Tue, Sep 30, 2008, John Almberg wrote: First, I wanted to say how great this list is. I'm a newbie FreeBSD admin and, besides the Handbook and Absolute FreeBSD (which never seems to leave my desk), this list is the best resource I have. I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. I would suggest using something like logrotate to rotate the backups giving you several days of backup files. Bill -- INTERNET: [EMAIL PROTECTED] Bill Campbell; Celestial Software LLC URL: http://www.celestial.com/ PO Box 820; 6641 E. Mercer Way Voice: (206) 236-1676 Mercer Island, WA 98040-0820 Fax:(206) 232-9186 There is nothing as stupid as an educated man if you get him off the thing he was educated in. Will Rogers ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
John Almberg [EMAIL PROTECTED] 09/30/08 3:18 PM First, I wanted to say how great this list is. I'm a newbie FreeBSD admin and, besides the Handbook and Absolute FreeBSD (which never seems to leave my desk), this list is the best resource I have. I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Again, I have run into a problem which is stupidly obvious to experienced admins, I'm sure. I want to slap myself, but don't have time. I'll do that after I have a better backup system in place. I am just about to dive into Google in search of a solution, but thought I would fire off a quick request, in case there is an obvious solution that everyone uses. If there is, a name or URL will do. I'll figure out the rest. Any hints much appreciated. Not going home until this is fixed... -- John Off the top of my head, (someone else probably has a better solution, they always do ;) ) why don't you keep more than one backup and rotate them like logs and not overwrite yesterdays backup every day? Hope my 1¢ at least gives you an idea or two. :D ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
On Tue, Sep 30, 2008 at 06:18:35PM -0400, John Almberg wrote: I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. Do you know if the table corruption was a result of 1) a MySQL bug (and there are many), 2) filesystem corruption, or 3) disk bit rot? Did you repair the table using myisamchk (assuming it's a MyISAM table), or was the corruption in InnoDB? I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Others have recommended good solutions to you -- improve your cronjob to handle rotations of those mysqldumps, so that you have 1-2 weeks worth of data, that way you can sleep easier if you don't notice the problem for a day or two. There are programs out there (usually in ports) which can help you with this task. Also, just for the record: the fact you're doing a mysqldump is good. It's better than just blindly copying the database files using cp or rsync (there's no locking done in that case so you could risk backing up the tables in the middle of an INSERT); and the cp/rsync method won't work reliably if you're using InnoDB. -- | Jeremy Chadwickjdc at parodius.com | | Parodius Networking http://www.parodius.com/ | | UNIX Systems Administrator Mountain View, CA, USA | | Making life hard for others since 1977. PGP: 4BD6C0CB | ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
I am just about to dive into Google in search of a solution, but thought I would fire off a quick request, in case there is an obvious solution that everyone uses. If there is, a name or URL will do. I'll figure out the rest. Any hints much appreciated. Not going home until this is fixed... Most certainly would want you to not not go home having been there before. Here is a crude way to do this. Find an elegant solution at leisure. The downside is that you if you crash at the wrong time, your job won't start for the next day. Be forewarned, then you stop making backups. You just need to monitor your atq. The gzip step should probably be part of a pipe for efficiency. You could cron this to get around that. I saw the response about repairing corruptions, REPAIR TABLE has thus far kept me from ever reloading. See man on date and use something other than %a to generate a numeric date unique back, that would give you numerous backups if you have the storage. DATE=`date +%a` # echo $DATE # echo Backup Mysql database mysqldump -h localhost -u YOURSQLUSERID -pYOURPASSWORD YOURDATABASE / usr/somedirectory/somefile_$DATE.backup gzip -f /usr/somedirectory/somefile_$DATE.backup /usr/bin/at -f /usr/somedirectory/mysqlbackup.sh midnight ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
On Tue, Sep 30, 2008 at 06:18:35PM -0400, John Almberg wrote: I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. Do you know if the table corruption was a result of 1) a MySQL bug (and there are many), 2) filesystem corruption, or 3) disk bit rot? Did you repair the table using myisamchk (assuming it's a MyISAM table), or was the corruption in InnoDB? 'Corrupted' is the wrong word. I believe it was a software error that destroyed a self-referential relationship within the table. The 'parent_id' field was altered incorrectly. So, it was not a MySQL error, per se. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Others have recommended good solutions to you -- improve your cronjob to handle rotations of those mysqldumps, so that you have 1-2 weeks worth of data, that way you can sleep easier if you don't notice the problem for a day or two. There are programs out there (usually in ports) which can help you with this task. Also, just for the record: the fact you're doing a mysqldump is good. It's better than just blindly copying the database files using cp or rsync (there's no locking done in that case so you could risk backing up the tables in the middle of an INSERT); and the cp/rsync method won't work reliably if you're using InnoDB. Okay, so I've written a ruby script that will give me one month's worth of backups to a remote server. Each backup looks like 'all.mysql.12.txt', where the number is the day of the week. I'm using scp to copy the backup to a backup server, so I don't lose the backups if the whole server tanks. A month's worth of backups might be overkill, but I have plenty of room on the backup server. Whew... that added a few grey hairs to my collection. Time for a beer and a few slaps upside the head! Thanks to everyone who confirmed a script and mysqldump are an adequate solution. -- John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
DATE=`date +%a` # echo $DATE # echo Backup Mysql database mysqldump -h localhost -u YOURSQLUSERID -pYOURPASSWORD YOURDATABASE /usr/somedirectory/somefile_$DATE.backup gzip -f /usr/somedirectory/somefile_$DATE.backup /usr/bin/at -f /usr/somedirectory/mysqlbackup.sh midnight Ah, a much simpler solution than my ruby script. I hadn't thought to zip up the file before transferring it. That's an improvement I must add. Thanks: John ___ freebsd-questions@freebsd.org mailing list http://lists.freebsd.org/mailman/listinfo/freebsd-questions To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
I run a script from root's crontab (not /etc/crontab) and keep the
login credentials in /root/.my.cnf so they don't have to be embedded
in the script. Not that $gzip is defined as /bin/cat because I move
copies offsite via rsync and disk space is abundant. This script keeps
30 daily backups (configurable).
Crontab entry:
13 20 * * * cd /bak/databases /root/db_backup
db_backup perl script:
#! /usr/bin/perl
use strict;
my $maxbackups = 30;
my $gz='gz';
my $mysqldump = '/usr/local/bin/mysqldump';
my $gzip = '/bin/cat';
my $newfile;
my $filename = 'all_databases.sql';
my $curfile = $filename . .$maxbackups;
unlink $curfile if -f $curfile;
my ($i, $j);
for ($i = $maxbackups - 2; $i = 0; $i--) {
$j = $i + 1;
$curfile = $filename . '.' . $i;
$newfile = $filename . '.' . $j;
rename $curfile, $newfile if -f $curfile;
}
$curfile = $filename . '.' . '0';
my $command = $mysqldump --opt --all-databases | $gzip $curfile;
my $result;
$result = system $command and warn $result;
On Sep 30, 2008, at 4:22 PM, John Almberg wrote:
DATE=`date +%a`
#
echo $DATE
#
echo Backup Mysql database
mysqldump -h localhost -u YOURSQLUSERID -pYOURPASSWORD YOURDATABASE
/usr/somedirectory/somefile_$DATE.backup
gzip -f /usr/somedirectory/somefile_$DATE.backup
/usr/bin/at -f /usr/somedirectory/mysqlbackup.sh midnight
Ah, a much simpler solution than my ruby script. I hadn't thought to
zip up the file before transferring it. That's an improvement I must
add.
Thanks: John
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]
___
freebsd-questions@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-questions
To unsubscribe, send any mail to [EMAIL PROTECTED]
Re: Best way to back up mysql database
--On September 30, 2008 6:18:35 PM -0400 John Almberg [EMAIL PROTECTED] wrote: First, I wanted to say how great this list is. I'm a newbie FreeBSD admin and, besides the Handbook and Absolute FreeBSD (which never seems to leave my desk), this list is the best resource I have. I just had a huge scare today... One of the websites on my server uses a large Mysql database. Somehow, one of the tables got corrupted today. I have been blithely backing up mysql with a simple cron script that ran mysqldump every night. Simple, reliable, and I've never needed it. Today, when I realized the database was corrupted, I scrambled for my backup, and realized that if I hadn't caught the problem today, tomorrow my backup would have been overwritten, and I would have been... well, not a happy camper. Again, I have run into a problem which is stupidly obvious to experienced admins, I'm sure. I want to slap myself, but don't have time. I'll do that after I have a better backup system in place. I am just about to dive into Google in search of a solution, but thought I would fire off a quick request, in case there is an obvious solution that everyone uses. If there is, a name or URL will do. I'll figure out the rest. Any hints much appreciated. Not going home until this is fixed... Found this on the mysql documentation site: #!/bin/sh date=`date -I` mysqldump --opt --all-databases | bzip2 -c /var/backup/databasebackup-$date.sql.bz2 The date must be something from linux, but you can do it like this in FSBD: #!/bin/sh date=`date +%Y-%m-%d.%H:%M:%S` mysqldump --opt --all-databases | bzip2 -c /var/backup/databasebackup-$date.sql.bz2 Using this makes every dump uniquely named, even if you run several a day, so you would need to edit newsyslog.conf to rotate the dumps after a number of dumps that you choose so you don't keep writing dumps until the hard drive is full. Paul Schmehl, If it isn't already obvious, my opinions are my own and not those of my employer. ** WARNING: Check the headers before replying
