SEO

Hi Folks,

I am fairly new to MySQL and I am going to be setting up a web site on a
third party hosting machine. I continuously hear horror stories about
machines/sites being hacked and databases being destroyed. Despite my
best efforts I am sure I have some security flaws in my site. What I am
looking to do is provide myself with a mechanism to roll back my
database to a clean state if I happen to suffer one of these attacks (or
a failure). I was wondering what is the best way to do this. From my
limited knowledge of MySQL I think maybe I could use one of the following:

1) Binary Logs - Not really sure how these work but I was thinking of
maybe coping them to an off site machine every half hour and apply them
in sequence if I need to go back to a point in time ?
2) Incremental Backups - say one every half hour, then a script to
transfer that to an off site machine that way I can get the DB back to
within the last good half hour...

I don't really know much about either so if someone could give me some
pointers as to which is best it would be much appreciated...

Thanks,
JC

On Wed, May 14, 2008 at 10:25 PM, John Comerford
<johnc@optionsystems.com.au> wrote:
> 2) Incremental Backups - say one every half hour, then a script to transfer
> that to an off site machine that way I can get the DB back to within the
> last good half hour...

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

Being compromised is not inevitable, but hardware failure is. Having
trusted (an therefore tested) backups is the only way to operate. Is
there some practice in particular you are concerned about?

Blanket suggestion: Don't escape things manually, have the db (or
emulation) do it for you using prepared statements. It is easier to
code this way, and much more secure in the long run.

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Quickly scanning this page, it doesn't seem to give syntax for an
incremental backup. I am hoping to be able to run something that dumps
only data changed since the last backup.



Rob Wultsch wrote:
> On Wed, May 14, 2008 at 10:25 PM, John Comerford
> <johnc@optionsystems.com.au> wrote:
>
>> 2) Incremental Backups - say one every half hour, then a script to transfer
>> that to an off site machine that way I can get the DB back to within the
>> last good half hour...
>>
>
> http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
>
> Being compromised is not inevitable, but hardware failure is. Having
> trusted (an therefore tested) backups is the only way to operate. Is
> there some practice in particular you are concerned about?
>
> Blanket suggestion: Don't escape things manually, have the db (or
> emulation) do it for you using prepared statements. It is easier to
> code this way, and much more secure in the long run.
>
>

On Wed, May 14, 2008 at 11:13 PM, John Comerford
<johnc@optionsystems.com.au> wrote:
> Quickly scanning this page, it doesn't seem to give syntax for an
> incremental backup. I am hoping to be able to run something that dumps only
> data changed since the last backup.
>

You could use diff and the previous dump to generate a incremental
dump. I very much suggest against this.

If you don't spend the time to really understand what is going on then
you are going to end up hosed without a good backup to revert to. I
suggest that if don't know how to sanitize input then you are over
your head. People get paid a lot of money to to be DBA's, and good
chunk of that is understanding disaster mitigation/recovery.

K.I.S.S.: words to live by.

--
Rob

I have worked as a DBA for a long time, just not with MySQL and I have
spent a lot of time making sure the application is secure. I suppose my
question was really what is the best way to do incremental backups? The
DB I have most experience with has thing like after imaging etc. which
allows you to easily roll A DB forward to a particular point in time,
making disaster recover pretty straight forward. I am wondering if
there is such a thing in MySQL or is there a product/scripts that are
considered the "standard" for doing this sort of thing?


Rob Wultsch wrote:
> On Wed, May 14, 2008 at 11:13 PM, John Comerford
> <johnc@optionsystems.com.au> wrote:
>
>> Quickly scanning this page, it doesn't seem to give syntax for an
>> incremental backup. I am hoping to be able to run something that dumps only
>> data changed since the last backup.
>>
>>
>
> You could use diff and the previous dump to generate a incremental
> dump. I very much suggest against this.
>
> If you don't spend the time to really understand what is going on then
> you are going to end up hosed without a good backup to revert to. I
> suggest that if don't know how to sanitize input then you are over
> your head. People get paid a lot of money to to be DBA's, and good
> chunk of that is understanding disaster mitigation/recovery.
>
> K.I.S.S.: words to live by.
>
>