On 6/29/06, Tim Lucia <[EMAIL PROTECTED]> wrote:
I am in the process of designing the backup procedures for a soon-to-be production DB. I have gone back and forth on mysqldump and mysqlhotcopy. As I see it (for MyISAM tables), the hot copy backs up faster, restores faster, but does not allow for selective restores ("Hello, Support? I accidentally deleted student Tim Lucia... can you get him back"). Mysqldump is slower to back up, slower to restore, but allows for selective restores.Are those the only things to consider? I know that piping mysqldump into gzip results in disk space savings of the dump file, whereas mysqlhotcopy requires n * 2 free bytes on your system. But at least you know in advance exactly how much space it will take...
A few years back, we had a database that was worth a lot of money, monthly paid by 3 or 4 clients that wanted security and easily restore/crash recovery. So we studied a mysql fail-safe setup like this: We had 3 servers, 2 running on mirrors, 1 master, 2 slaves replicating. We scheduled the slave to be stopped and started so it would be a backup till yesterday, the second would be a "mirror" of the master in real time. We start one the slaves at midnight (low usage) and stop it at around 5am, so, the rest of the day its data would not be updated. We also setup cron jobs backing up the mirror slave everyday (no load on the master), and keeping the backups for 6 months. On a "crash" situation, we simply turned the hot mirror the new master and tried to fix the old master. In the event of lost data (your missing student) we would use the slave (starting it using no-network) to retrieve the rows (easier than getting it from a .sql), or we would setup a new server running on different port and populate it with one of the table separated backups, retrieving the data. It was efficient at that time, nowadays you have heartbeat and other tools, allied to a DNS, it can do a LOT more (even non-assisted crash recovery). But if you have a small dataset you can try this approach. -- Daniel da Veiga Computer Operator - RS - Brazil -----BEGIN GEEK CODE BLOCK----- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ ------END GEEK CODE BLOCK------ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
