Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    52

    Can cron help me with this? (back ups)

    I want to set up a cron job to make daily back-ups of my database, but by turning my site off first.

    This is how I envisage it to work:

    1: rename '.htacess' (in public_html folder for the site) to .htaccess-open
    2: rename '.htaccess-closed' to .htaccess
    // this closes the site down so no-one can write/access the db (they are basically shown a 'site down for maintenance' page - I already have the code for this)

    3: mysqldump --opt (DB_NAME) -u (DB_USERNAME) -p(DB_PASSWORD) > /path/to/dbbackup-$(date +%m%d%Y).sql
    // this backs up the database

    4: wait for 3 to finish
    5: rename '.htaccess' to .htaccess-closed
    6: rename '.htacess-open' to .htaccess
    // this opens the site back up

    Is this easy enough to do? Anyone got any tips/pointers?

  2. #2
    Join Date
    Mar 2008
    Posts
    1,717
    I'm assuming you're wanting to do this to keep the database in a known-good situation... well... mysqlhotcopy will lock the tables prior to copying them so you know everything's going to be in a known-good state without having to manually shut down your site... queries which are waiting to update tables should just block until the copy is completed.

    If that won't meet your needs for some reason, then sure - you could use .htaccess to close off access to your site with a cron script, rather trivially actually.
    I used to run the oldest commercial Mumble host.

  3. #3
    Join Date
    Jan 2008
    Posts
    52
    The database is quite large - usually takes over 10 minutes to mysqldump. I think it would be nice for the end user to see a 'come back soon' page too.

    Are there any other downsides to using mysqlhotcopy?

  4. #4
    Join Date
    Mar 2009
    Location
    Chicago, IL
    Posts
    219
    With locked tables and a busy site, you're going to be seeing queries backing up until the table locks expire. Now, depending on how long it takes, and what your MySQL timeout value is, your clients may see MySQL database connection errors, timeouts, etc.

  5. #5
    Join Date
    Mar 2008
    Posts
    1,717
    Quote Originally Posted by Brook View Post
    The database is quite large - usually takes over 10 minutes to mysqldump. I think it would be nice for the end user to see a 'come back soon' page too.
    Understood, but mysqlhotcopy is substantially faster I believe (it's about as quick as it takes to cp -R I think) so I'd suggest giving it a try first, and then if that doesn't work, going with your idea.

    As long as the cron job has permissions to write to the .htaccess files I don't see any real hurdles to implementing your original idea.

    Are there any other downsides to using mysqlhotcopy?[/QUOTE]

    Yes, as far as I know it's a binary copy, so for example if you're using it for backups you can only restore it to versions which store the tables in the same exact format (I think that limits you to pretty close minor revisions, but I'm not exactly an expert on MySQL's storage - heck I may be completely wrong about this entire point).

    By contrast, a text dump of the database will allow you to restore the database to any MySQL version which is SQL-compatible with the dump... going forward that should basically be any version whatsoever.

    Finally, I'm going to assume you've chopped out all the unnecessary stuff out of the database - for example I recently worked on a site that had a vBulletin "shoutbox" that had nearly 3 years of shoutbox logs in the shoutbox table... We stored those logs in a flat file and erased everything but the last 30 days' chats and it sped things up a lot.
    I used to run the oldest commercial Mumble host.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •