Ah, I remember doing that before we starting taking backups. I am so glad MySQL has binary logs by default.
On 17/08/2010, at 9:20 AM, Harvey Kane wrote: > It's worth noting that a mirror of a database isn't the same thing as a > backup. > > Every so often I'll forget to put the WHERE clause onto the end of an UPDATE > query and need to go running off to my backup :) > > Harvey. > > > On 17/08/2010 8:54 a.m., Dan Khan wrote: >> 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] >>> >> > > > -- > Harvey Kane > > Phone: > - Auckland: +64 9 950 4133 > - Wanaka: +64 3 746 8133 > - Mobile: +64 21 811 951 > > Email: [email protected] > If you need to contact me urgently, please read my email policy > www.ragepank.com/email/ > > -- > NZ PHP Users Group: http://groups.google.com/group/nzphpug > To post, send email to [email protected] > To unsubscribe, send email to > [email protected] --- Simon Welsh Admin of http://simon.geek.nz/ Who said Microsoft never created a bug-free program? The blue screen never, ever crashes! http://www.thinkgeek.com/brain/gimme.cgi?wid=81d520e5e -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
