5 ms·

how do you backup and recover your stuff ? I can't find any prepackaged thing on the web. I'd like to address 2 needs : disaster recovery and point in time rec

how do you backup and recover your stuff ? I can't find any prepackaged thing on the web. I'd like to address 2 needs : disaster recovery and point in time recovery. For now I only dump the database from time to time, but it's far from a real solution, if I loose my whole server it would take quite some time to get its configuration back on track.

shafqat 18y ago
I'd love to get people's thoughts on this as well. What are the best ways to automatically back up your DB.
- chmac 18y ago
  For a smaller database, dmpayton's suggestion is spot on. Simply cron schedule a mysqldump and then push that offsite. Here's a few example code snippets I use: /usr/bin/mysqldump -u dbusername -pdbpassword --skip-lock-tables database_name | gzip > /path/to/backup/file/file_$(/bin/date +\%Y-\%m-\%d_\%H-\%M-\%S).sql.gz I'm using --skip-lock-tables because on a ZenCart site, the lock tables option was adversely affecting site performance. Then you could add something to automatically email or scp the file to another host. For bigger, more complex databases, look into MySQL binary logging, DRBD replication, and MySQL slaves. That should give you some keywords to get started.
  - shafqat 18y ago
    Thanks this is exactly what we are doing... (the first option).
dmpayton 18y ago
If it's a *nix system, why not setup a cron job to do it for you? I have a Python script in /etc/cron.daily that performs a mysql-dump, tars it, and uploads it to S3. For total completeness and peace-of-mind, you could have the script add anything else you need backed up, like your projects or public_html directories, configuration files, etc.
- chmac 18y ago
  Running mysqldump on a database the size of CouchSurfing's is a real problem. It's a resource hog. To properly back up a consistent data image you need to use locking, which prevents writes to your database while the backup is running. That causes downtime on every backup, not ideal. Likewise, when backing up 100s of GiBs of data, a simple tar gz is not suitable. More sophisticated strategies need to be used when the data becomes that big. I've found http://HighScalability.com http://HighScalability.com to be a great source of information on building scalable applications.

shafqat 18y ago

I'd love to get people's thoughts on this as well. What are the best ways to automatically back up your DB.

chmac 18y ago
For a smaller database, dmpayton's suggestion is spot on. Simply cron schedule a mysqldump and then push that offsite. Here's a few example code snippets I use: /usr/bin/mysqldump -u dbusername -pdbpassword --skip-lock-tables database_name | gzip > /path/to/backup/file/file_$(/bin/date +\%Y-\%m-\%d_\%H-\%M-\%S).sql.gz I'm using --skip-lock-tables because on a ZenCart site, the lock tables option was adversely affecting site performance. Then you could add something to automatically email or scp the file to another host. For bigger, more complex databases, look into MySQL binary logging, DRBD replication, and MySQL slaves. That should give you some keywords to get started.
- shafqat 18y ago
  Thanks this is exactly what we are doing... (the first option).

dmpayton 18y ago

If it's a *nix system, why not setup a cron job to do it for you? I have a Python script in /etc/cron.daily that performs a mysql-dump, tars it, and uploads it to S3. For total completeness and peace-of-mind, you could have the script add anything else you need backed up, like your projects or public_html directories, configuration files, etc.

chmac 18y ago
Running mysqldump on a database the size of CouchSurfing's is a real problem. It's a resource hog. To properly back up a consistent data image you need to use locking, which prevents writes to your database while the backup is running. That causes downtime on every backup, not ideal. Likewise, when backing up 100s of GiBs of data, a simple tar gz is not suitable. More sophisticated strategies need to be used when the data becomes that big. I've found http://HighScalability.com http://HighScalability.com to be a great source of information on building scalable applications.