#5 Database locking for large databases over slow connections

closed
nobody
None
5
2010-06-29
2006-09-06
Brendon
No

Hi there,

I run the backup script on one of our local boxes in
New Zealand, it's backing up lots of databases in the
US in NY so the connection is relatively slow. We
have a shopping cart site that stores its images in
the database so the database is quite big. The
problem we're seeing is that it seems the backup
script is locking the database for about 20 minutes
in order to dump it straight to our backup server in
NZ. Is there a way of making the script generate a
local copy of the dump then unlock the database and
then copy the dump? (I guess there probably isn't
because the script isn't running on the remote
machine :) ) The basic problem is the shoppingcart
is down for 20 minutes :)

Would like to hear your thoughts on this.

Cheers,

Brendon

Discussion

  • Dmitry T.
    Dmitry T.
    2006-09-13

    Logged In: YES
    user_id=1597054

    Hello,

    Setup on our server MySQL replication(configure second mysqld) or setup
    another server for replication.
    End configure backup script to slave replicaption server.

    P.S. i use same configuration.

    http://dev.mysql.com/doc/refman/5.0/en/replication.html

     
  • Logged In: NO

    you could do this using the mysql command line tools and cron :)

     
  • Logged In: NO

    Thanks Dmitry :) We now just back up onto the same server then have a backup agent that runs and copy's the backups off over to NZ. We also leave them on the main server so that it's quicker and easier to restore one if needed :)

    Cheers,

    Brendon

     
    • status: open --> closed
     
  • Fixed by running the script on the local machine.