Update only changed records between two tables

I am duplicating a database of student information (I don't want to
mess with the Student Information System's database). What I would
like to do is to be able to identify only the changed records, on a
daily basis, between the extract of the SIS database and my copy.

I have worked out a method for extracting the SIS database and loading
it into an mysql database, but now I am stuck. I have no clue how to
go about the following:

1. Update a "changed_records" database on a daily basis with just the
diff from the previous day's extract (to include new and deleted
records)
2. Update the copied database with the changes (including timestamps
for modification date_time and created date_time)

I hope this was descriptive enough!

Thanks,
Shane

Shane wrote:
> I am duplicating a database of student information (I don't want to
> mess with the Student Information System's database). What I would
> like to do is to be able to identify only the changed records, on a
> daily basis, between the extract of the SIS database and my copy.
>
> I have worked out a method for extracting the SIS database and loading
> it into an mysql database, but now I am stuck. I have no clue how to
> go about the following:
>
> 1. Update a "changed_records" database on a daily basis with just the
> diff from the previous day's extract (to include new and deleted
> records)
> 2. Update the copied database with the changes (including timestamps
> for modification date_time and created date_time)
>
> I hope this was descriptive enough!
>
> Thanks,
> Shane
>

to answer your question i have to ask two questions from you:
1-what platform is the actual sis on
2-what platform is your mysql server on

On Apr 26, 6:46 am, lark <ham...@sbcglobal.net> wrote:
> Shane wrote:
> > I am duplicating a database of student information (I don't want to
> > mess with the Student Information System's database). What I would
> > like to do is to be able to identify only the changed records, on a
> > daily basis, between the extract of the SIS database and my copy.
>
> > I have worked out a method for extracting the SIS database and loading
> > it into an mysql database, but now I am stuck. I have no clue how to
> > go about the following:
>
> > 1. Update a "changed_records" database on a daily basis with just the
> > diff from the previous day's extract (to include new and deleted
> > records)
> > 2. Update the copied database with the changes (including timestamps
> > for modification date_time and created date_time)
>
> > I hope this was descriptive enough!
>
> > Thanks,
> > Shane
>
> to answer your question i have to ask two questions from you:
> 1-what platform is the actual sis on
> 2-what platform is your mysql server on

They are both currently on Win32 platforms. The SIS will always be on
a Win32 platform, the duplicate data may be moving to Linux since
there really is no need for it to be on a Win32 machine.

On Apr 26, 6:46 am, lark <ham...@sbcglobal.net> wrote:
> Shane wrote:
> > I am duplicating a database of student information (I don't want to
> > mess with the Student Information System's database). What I would
> > like to do is to be able to identify only the changed records, on a
> > daily basis, between the extract of the SIS database and my copy.
>
> > I have worked out a method for extracting the SIS database and loading
> > it into an mysql database, but now I am stuck. I have no clue how to
> > go about the following:
>
> > 1. Update a "changed_records" database on a daily basis with just the
> > diff from the previous day's extract (to include new and deleted
> > records)
> > 2. Update the copied database with the changes (including timestamps
> > for modification date_time and created date_time)
>
> > I hope this was descriptive enough!
>
> > Thanks,
> > Shane
>
> to answer your question i have to ask two questions from you:
> 1-what platform is the actual sis on
> 2-what platform is your mysql server on

1. Win32 - must be Win32
2. Win32 - Can be Linux

Shane wrote:
> On Apr 26, 6:46 am, lark <ham...@sbcglobal.net> wrote:
>> Shane wrote:
>>> I am duplicating a database of student information (I don't want to
>>> mess with the Student Information System's database). What I would
>>> like to do is to be able to identify only the changed records, on a
>>> daily basis, between the extract of the SIS database and my copy.
>>> I have worked out a method for extracting the SIS database and loading
>>> it into an mysql database, but now I am stuck. I have no clue how to
>>> go about the following:
>>> 1. Update a "changed_records" database on a daily basis with just the
>>> diff from the previous day's extract (to include new and deleted
>>> records)
>>> 2. Update the copied database with the changes (including timestamps
>>> for modification date_time and created date_time)
>>> I hope this was descriptive enough!
>>> Thanks,
>>> Shane
>> to answer your question i have to ask two questions from you:
>> 1-what platform is the actual sis on
>> 2-what platform is your mysql server on
>
> They are both currently on Win32 platforms. The SIS will always be on
> a Win32 platform, the duplicate data may be moving to Linux since
> there really is no need for it to be on a Win32 machine.
>

you can use a product called sqlyog to schedule midnight extracts from
the sis database to the mysql database. all's you need is the product
(it's rather cheap to purchase) and two DSN's defined on the machine
that runs sqlyog each pointing to the 2 databases with sufficient
privileges for the users defined in DSN. you then connect to the two
databases within sqlyog and synchronize the databases and/or tables in
databases based on whatever criteria that you have. this product can
synchronize a mysql table with any data source.

i have some jobs that kick in at different hours of the night one after
the other. the job determines if it needs to update a row in my
destination database and if so, it will otherwise, it won't bother and
on to the next row. btw, you can do all of these with perl scripts
scheduled that are tied to cron jobs but you'll have to write all of
them yourself. it's cheaper to buy sqlyog in my opinion.

hope this helps.