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]
