But using MySQL's built-in replication doesn't need any changes to the code level - purely at DB level. If you have control of your DB ini file (which you may not if hosted), simply set up your main DB as a master, and setup your backup DB to be a slave to this. Assuming they have access to each other, then any transactional statements applied to the master DB from your code automatically get pushed into your backup DB in real-time (ish).
It's suprisingly easy to setup this using MySQL, something like 1 or two lines in the ini file. Of course, if you don't have access to the DB in this way, then certainly a PHP solution will do, but much more clunky and potentially time-consuming each night. As simon suggested, a simple scheduled mysqldump through PHP exec() function should do. Out of interest, I use percona's xtrabackup (free port of Oracle's ibbackup) on the server to do hot-backups of our MySQL DB each night, although I don't use this to prep a standby server like you require, more as a disaster recovery tool should I need to rebuild. Cheers, -Dan On 17 August 2010 08:45, Brendan Brink <[email protected]> wrote: > thanks for your response Dan, > > No meant, ideally would like to write each SQL transaction for CREATE, > UPDATE, DELETE across to the backup database at the time of > transaction, but because of way the code is written this is not > achievable easily. > > But, can still replicate the task through a CRON job each night midnight... > > hope this answers your question? > > On Tue, Aug 17, 2010 at 8:37 AM, Dan Khan <[email protected]> wrote: >> Hi Brendan, >> >> Does that mean you can't use replication for this task, as this seems >> like the most sensible solution? >> >> Cheers, >> -Dan >> >> >> On 17 August 2010 08:30, Brendan Brink <[email protected]> wrote: >>> hi there, >>> >>> Am wanting to automatically create a php script to create a copy of a >>> database onto a different server. >>> >>> The script would run each evening at midnight. >>> >>> Unfortunately, we are not able to simply write each DB transaction to >>> both databases on the fly. >>> >>> The database has the following statistics: >>> >>> 127 tables >>> 237,000 rows >>> 30MB in size. >>> >>> It is a MYSQL5 database. >>> >>> Does anyone know of a script they have used that can handle this? >>> >>> Ideally want the database to be backed up to a functional secondary >>> database, so that we can switch to it if the original server goes >>> down, and hence the data would be new as at the last midnight. >>> >>> Looking forward to your advice / information regarding this. >>> >>> Thanks >>> Brendan. >>> >>> -- >>> NZ PHP Users Group: http://groups.google.com/group/nzphpug >>> To post, send email to [email protected] >>> To unsubscribe, send email to >>> [email protected] >> >> -- >> NZ PHP Users Group: http://groups.google.com/group/nzphpug >> To post, send email to [email protected] >> To unsubscribe, send email to >> [email protected] > > -- > Kind Regards, > > Brendan Brink > > SMS Marketing Consultant | Manager > Sell2Cell Ltd. > > 021 0246 1646 | [email protected] | www.sell2cell.co.nz > > > > > We provide customized, cost-effective SMS & Web Solutions > Need a website? Need to integrate text-messaging into your business > or website? Contact us today for a free no-obligation quote! > > VISIT OUR ASSOCIATED WEBSITES: textvouchers.com | textguru.co.nz > > WARNING This email contains information which is CONFIDENTIAL and may > be subject to LEGAL PRIVILEGE. If you are not the intended recipient, > you must not peruse, use, disseminate, distribute or copy the email or > attachments. If you have received this in error, please notify us > immediately by return email, facsimile, or telephone (call us > collect). > > -- > NZ PHP Users Group: http://groups.google.com/group/nzphpug > To post, send email to [email protected] > To unsubscribe, send email to > [email protected] -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
