What is Digg?53 Comments
- emostar, on 10/12/2007, -1/+11I use my own custom built script to backup all my databases. After I compress it with bz2, I encrypt it with GPG and email the log and file to my GMail account I have for system backups. I also use scp to copy it to another machine's hard disk so I have 3 different locations of the same backup (1 offsite). The only problem with this is I have one database that is around 1GB, so I can't send that off to GMail. It contains data that will only change when I update it so I just burn it to DVD and keep a copy at my house and my girlfriend's house.
- emostar, on 10/12/2007, -0/+6The only reason I encrypt it is because I send it to GMail. Otherwise there is no reason to encrypt a backup. And this backup runs once a day so I don't have to worry about data changing, cuz it doesn't matter.
@Seumas:
pgpool isn't a real-time backup. Run a query that deletes your data, and it gets deleted on the mirror too. If you rely upon pgpool as a backup, you will face some serious problems if some data gets changed or deleted accidently. - echimu, on 10/12/2007, -0/+5I think many diggers are new to linux and unix so if they see something like this they will digg it. But main rule of game is learn to use Google if you wanna be in field of Linux or UNIX.
- inactive, on 10/12/2007, -0/+4And by the time you've done all of that, your backup is no longer current.
Unless your database is really static, redundancy is more important than backing up. My backups take about 30 minutes on postgresql (doing a database dump for a several gigabyte database). By the time it's backed up, there's 30 minutes worth of new data in the database.
But then that's where real-time mirroring (thank's pgpool!) comes in. Real time backup on another server. I still back up regularly, but I'm not as concerned with losing last month's data as I am the last hour's data. - emostar, on 10/12/2007, -0/+3@Dr. Who
If you are in a big process of writing to the DB it may mess up your backup. For example, the index hasn't been written but the data files have been written. You are much better off running mysqlhotcopy which is meant to copy the files while the database is running. Basically you need to LOCK TABLES when you do a backup to prevent any kind of corruption. Sure you can copy your MyISAM files around, but sometime you won't be lucky and get a corrupt database. - mendicant, on 10/12/2007, -0/+3And now I see that it does allow all.
- mendicant, on 10/12/2007, -0/+3Looks like a pretty sweet setup.
I like the option to only backup the databases you choose, but I think it would be nice to have something where you can edit the properties and just have it backup everything and not have to worry about adding every new database to the script. (Unless I'm misunderstanding the step-by-step near the bottom of the page) - gorkish, on 10/12/2007, -0/+3Mysqldump is really a horrible way to backup a mysql database. It is a debugging, development, and migration tool, not a backup tool. It won't even get you a coherent snapshot of the entire database at one time and it's SLOOOOOOOOOW as a dog.
There are plenty of ways to do it "properly" but the simplest to explain is to just do a LOCK TABLES on all databases then do a file copy of the actual data in /var/lib/mysql (or whereever you have it). If you want to avoid any downtime at all, you can lock the tables and perform the backup from a seperate mysql server replicated from your primary data store. If you don't want to run a second machine or second mysql server on the same machine and you can incur a second or two of downtime, you can put your data store onto an LVM or EVMS volume, LOCK TABLES, snapshot the disk, UNLOCK TABLES then backup from the volume snapshot. The LVM approach is a very elegant single-server approach that is straightforward and free.
There are also several commercial and noncomemrcial mysql backup solutions that do database snapshot/backup properly as well such as the InnoDB hot backup mentioned above. - wetelectric, on 10/12/2007, -2/+5I wouldnt describe emailing your database via gmail as an ''Extreme secure environment".
- emostar, on 10/12/2007, -0/+2InnoDB Hot Backup works well.
- echimu, on 10/12/2007, -0/+2LOL you work in Extreme secure environment.. good
- inactive, on 11/14/2007, -2/+4Pretty common stuff. If someone wanted this, wouldn't they have just googled for it?
- milkfilk, on 10/12/2007, -0/+2Don't call phpmyadmin a script in this context. PHP is a script but this story is about something you can cron. You could theoritically cron a PHP script but that's not its intended purpose.
mutt: an oracle backup script (like rman) is so ridiculously complicated and stupid that it would poison digg forever causing a meltdown and community exodus. - mutt, on 10/12/2007, -0/+2somebody should submit a good oracle backup script....
- mrASSMAN, on 10/12/2007, -1/+3there's already a backup script built into phpMyAdmin..
- nofxjunkee, on 10/12/2007, -0/+1I dugg this because right I use a simple script I wrote that backs up my entire db every day. Then I have another script on a remote machine that gets a copy of the backup using scp in a cron job that runs later. Not the best setup, but it works. This script will work better though, so I'm going to use it to make a new backup system for myself.
- housemaister, on 10/12/2007, -0/+1For big databases redundancy is of course necessary. But offsite backups can be important too. The script is pretty common - not hot news, sic.
- inactive, on 10/12/2007, -0/+1Why though? It's all file based.
I have yet to hear any reason why I simply can't copy files. I have been doing it for years now.
I have in the past moved DB filed from server to server, complete DBs, and got them up and running lickity split.
And there are not small DBs. I have tables with 6 Million records in them.
So unless there is a reason that someone can pin point, I see nothing wrong with my method. - DoubtfulSalmon, on 10/12/2007, -0/+1This looks good. Smells like a MySQL version of the concept that rsnapshot (http://www.rsnapshot.org/) implements for filesystems (that's a good thing in my view). I'll be using this script (and I'll be looking into haxoring it to backup PostgreSQL DBs!)
- merreborn, on 10/12/2007, -0/+1Not only that, but PHPMyAdmin's export function is just a frontend for mysqldump anyway!
- ohhmaagawd, on 10/12/2007, -0/+1don't even think of using mysqldump on a big DB unless you have big maintenance windows... you're looking at multiple hours of downtime. You can just do a file copy, much faster. Or go Innodb and buy the innodb hotbackup product.
http://boondoggle.wordpress.com - epinephrine, on 10/12/2007, -0/+1That has nothing to do with MySQL backup scripts.
- deceplus, on 10/12/2007, -0/+1i'm no sysadmin or a mysql guru, but isn't simply copying the database files a bad idea? there has to be a reason mysqldump was created. i only say this because my (now ex- for good reason) boss had created some crazy backup methods. he even detailed his backup schedule for us in an e-mail saying he "backs up three times daily at 0, 6, 12, and 18." ...right... he also managed to fill up a 200 gb hd that was installed strictly for him to back up to. mind you it was merely a backup of a university housing's website and the associated databases.
long story short, he wasn't fit for his job and he's now 'resigned,' but your method sent fear through my bones. you should look into mysqldump and quit playing with the actual files. - makgyver, on 10/12/2007, -1/+2I agree. It's a good reference but it might not be the best idea to take a script and use it without completely understanding it. It would be better to use something like this as a starting point and write your own so you know exactly what it is doing.
- amoeba, on 10/12/2007, -0/+1I'm using a similar script myself that emails me the db backup as a tar.gz file. However, this script looks quite comprehensive, I might just try it out.
- thatsiebguy, on 10/12/2007, -0/+1phpmybackup is a good little app too for on the spot dumps.
I run a hosting network and I have scripts that run the backups on the linux servers (including database dumps), tars them up, drops them in an ftp directory accessible only by the backup server that then logs on after the backup is done via SyncBack and downloads them, then uploads them to our offsite NAS. That leaves me with three copies. I get summary emails as well. All automated. - emostar, on 10/12/2007, -0/+1If you shut down the DB and then copy then that is perfectly fine and one of the simplest ways to backup your MyISAM tables.
You can in fact copy files while the DB is being written to. You can copy data files in windows at any time, but you cannot always delete them as you said. Copy just copies what is currently on disk to a new location, no matter if it is in use or not. It is a read only operation.
Good luck experimenting, this kinda stuff is hard to reproduce just like a thread race issue. - robotify, on 10/12/2007, -0/+1sqlite backup script: `cp -a /path/to/my.db /backup/date/`
- jasonmcaffee, on 10/12/2007, -0/+1The best 'script' i've found so far for backing up my MySQL database is the MySQL Administrator program. http://www.mysql.com/products/tools/administrator
- cabazorro, on 10/12/2007, -0/+1Backing up a mySQL database is common stuff? We might be stuck with semantics or context. I call common using and installing a web brawser or Word Processor. Knowing how to install and run a relational database pays more than knowing how to install and run MS Office right?
- amoeba, on 10/12/2007, -0/+1You could run the remote script as a scheduled task from your local computer
- thereddwarf, on 10/12/2007, -0/+1hey any chance you can send me a copy of that please to the.red.dwarf.4eva@gmail.com as i've been looking for a script for years now that will email me a daily backup of my db
- inactive, on 10/12/2007, -0/+1I shut down the DB and Copy the tables over. Takes barely a minute. Then DB is turned back on.
But I also have moved live databases manually with mysql was running and never had a issue.
In fact, I don't think I can even copy the DB while the files are being used or written too, like most any windows files if you did the same. The file is already locked while in use. I am sure windows users have had this issue. For instance try to delete an mp3 while it's playing.
I'll experiment and try to break some tables with this, but copying has always worked just fine for me.
In the event there was an error, running a repair on the database tables in question should fix everything just fine. - thereddwarf, on 10/12/2007, -0/+1hey emostar any chance you can send me a copy of that please to the.red.dwarf.4eva@gmail.com as i've been looking for a script for years now that will email me a daily backup of my db
- v3xt0r, on 10/12/2007, -0/+1I agree, mysqldump makes me wanna take a dump.
Speaking of which... =) - inactive, on 10/12/2007, -0/+1Nice. Does it do hot backups without locking? Wait...nevermind.
- gooru, on 10/12/2007, -0/+1However, AFAIK you can't run phpmyadmin export as a cron job or easily automate it.
- Nagilum, on 10/12/2007, -1/+1Seems everyone has its own solution, here is mine:
http://www.nagilum.net/unix/db_backup-1.2.sh - analgesia, on 10/12/2007, -0/+0Just added it to a clients server. Seems to do the job just fine. Digg
- kozmic, on 10/12/2007, -0/+0Nice. But this backup script is only good for the default DB type in MySQL. It's more problematic to find a good MySQL database backup script for InnoDB, so if anyone have a good solution to InnoDB backup scripts that can be used without shutting down MySQL server I would love to see it :)
- nutcase, on 10/12/2007, -0/+0I wish someone would post a DB backup method (preferably in PHP, think free) that would work on hosts such as GoDaddy (don't bash me for GoDaddy, clients have what they have) or other hosts that don't allow you to do cron jobs. I know i can run a sql dump statement. But I would like to simulate the auto part of the cron job? Any Ideas?
- inactive, on 10/12/2007, -1/+1oh, it's unix based
on windows here's my script/batch file
simply copy all the databases to the external D: drive for instance
so simple really
Each DB is 3 files.
copy mysqldata D:backupmysql - lorax, on 10/12/2007, -0/+0Nutcase, try out this script for php based backups -> http://restkultur.ch/personal/wolf/scripts/db_backup/ I've been using it for a while on a site that I can't get shell access to. Just upload the php script and then setup a cron job with wget on your own PC to kick it off periodically. (Be careful where you have it store the backup file though don't leave it somewhere that others can download from!)
- retinaburn, on 10/12/2007, -0/+0There is a patch on the sourceforge site for the script for InnoDB
- sholdowa, on 10/12/2007, -0/+0Looks like there's a bit of oracle in your past (:
- nutcase, on 10/12/2007, -0/+0thx lorax
- BoyOnSkates, on 10/12/2007, -1/+1we run this at http://www.tenbills.com all the time. luckily only needed to restore once but makes backing up sooo ez.
- saifatlast, on 10/12/2007, -2/+0Whoops, I agree with Seumas (Accidentally dugg him down instead of up like I meant to.) My first thought upon reading this was, "wow, that's not something I could have googled for if I needed it." Being sarcastic of course. I think from now on, whenever I search for something on google, I'll post it on digg, that way, if someone happens to need the same thing by some pure coincidence, they won't have to do it themselves, because as we all know, it's really hard.
- inactive, on 10/12/2007, -3/+1I have used a similar script for years although my script also scp's it to another machine in a different location. Intersting how soem of thes escripts that many of us have always used are now considered sourceforge 'projects'.
Great digg though!
http://www.gfx.com - koguma, on 10/12/2007, -3/+1This is so old, I think my grandma wrote that script.
Check out the new & improved 
© Digg Inc. 2009
— Content created and posted by Digg users is