Remote Database update

[near2moon]

hello,

I am in seeking a help aur a helpful software.

I have two Same DataBases one is over the internet. Php MySQL and over local V.B and SQL

Both have diffrent information (i mean data inside). Like some come on over internet database and he/she input some thing. Same over Local one of very Employer he enter some thing. There are same Tables but there are sapreate data inside. I wana do some thing means on a spacific time like 4:00 PM. Over local database and internet data base comunicate and transfer the data to each other.

Transfer new data of MYSQL (Over internet) into Oracel [SQL] ( which is Localy)

and Oracel [SQL] ( which is localy) into MySQL (Over internet).

Tell me if any body can help me.

Or any kind of software is avalible or any kind of guide for me.

Thanks a lot

Adnan Shahid

[darthwonka]

In other words, Do you wish to mirror the DB structure but not the data?

[Ritesh]

You can use SQLyog Job Agent to sync data between TWO MySQL databases. Your case is perfect for SJA if both the source and target datasource are MySQL.

As of now it is not possible to sync data between two heterogeneous datasources.

[Shadow]

There are separate tools for doing data migration between MySql and other dbs but most of them are commercial. You should check out mysql.com for 3rd party migration tools and hope for the best.

As an alternative, you could write your own little app using ODBC to do the job.

[robmann]

So can you sync the online data and the local data even though you are entering new data all the time both online and locally? I read something about if primary keys are the same the target gets overwritten? is there a way to sync if you have the same primary keys in each table?

[Ritesh]

robmann wrote on May 28 2004, 03:21 PM:

So can you sync the online data and the local data even though you are entering new data all the time both online and locally? I read something about if primary keys are the same the target gets overwritten? is there a way to sync if you have the same primary keys in each table?

Yes 🙂 SJA is perfect for such situation.

You require 2-way sync between the source and the target database. The only thing you need to keep in mind is that SJA will try to bring the target in sync with the source. In case of conflicting Primary Keys, the data from the source overwrites the target data.

e.g. if you have the following condition:

source:

=========

1 SQLyog

2 Ritesh

target:

========

1 SQLyog Agent

3 MySQL

After you run the sync the source and target will become:

source:

==========

1 SQLyog

2 Ritesh

3 MySQL

target:

==========

1 SQLyog

2 Ritesh

3 MySQL

Note: The above is only applicable if you perform 2-way sync.

HTH

[robmann]

I understand that sync but here is the condition I am working with so what I want to do is to be able to enter data locally and online then sync the two but will have many records with the same primary but different information, is there a way to sync this sitation?

source:

=========

1 SQLyog

2 Ritesh

3 type

target:

========

1 SQLyog Agent

2 MySQL

3 work

would like to end up with onboth the source and target

1 SQLyog

2 Ritesh

3 type

4 SQLyog Agent

5 MySQL

6 work

[Ritesh]

SQLyog uses PRIMARY KEY as the locus for determining changes in the data. If you need to merge data from two sources then you need to have unique primary key values across the databases.

[JasonPotter]

Hi Guys,

Have a similar situation and wanted to know if sja could handle this one.

I have multiple remote databases, which I will represent as Source1 and Source2 below. These have to sync back to a master db once a day with their data. These remote db's do not need to be updated. Eg Source1 does not have to contain Source2 info. Below is a visual representation of what I need too do.

source1:

=========

S1_1 OnlineSQLyog

S1_2 SQLyog

S1_3 Ritesh

source2:

=========

S2_1 DiffSQLyog

S2_2 SQLyog

S2_3 Ritesh

Master:

========

S1_1 OnlineSQLyog

S2_1 DiffSQLyog

After sync is run the only table that is updated is the Master.

Master:

==========

S1_1 OnlineSQLyog

S2_1 DiffSQLyog

S1_2 SQLyog

S1_3 Ritesh

S2_2 SQLyog

S2_3 Ritesh

I look forward to your response.

Cheers

Jason

[Ritesh]

SJA is a perfect solution for you. You need to run two sync sessions.

1st session:Make source1 the source and master the target.

2nd session:Make source2 the source and master the target.

Do send me your comments on the result.

[JasonPotter]

Thanks for the response.

Though I thought sja will delete the enteries in the target if they are not in the source or is this a configurable feature? Hence when you do the first sync the target will become exactly like Source1 and then after the second sync the target will exactly be like Source2, hence wiping out the data entered into the target from the first sync.

Also what would the issues be if any, if Source1 and Source2 tried to do their syncs at the same time. In my case Source1 and Source2 are not always connected to the net and initiate their on connections. I would prefer not to have to specify connect times as these Source db's will connect to the net via GPRS and I am not always going to have a signal. So if I have a signal and can make a net connection via gprs I want to do the sync straight away. So there is a chance of 2 sync's running at the same time.

I look forward to your response on both points.

Cheers

Jason

[robmann]

Does anybody know how or where I can find out how to setup the primary to be unique for three databases like with each having the following always before the auto inc number S1_ , S2_ , S3_ so that each will be unique and none will be overwritten on a sync? like JasonPotter posted

[JasonPotter]

Hi Robmann,

To do this I would have a seperate table with has an auto inc field in it. Before I do an insert in the main table I do an insert into that table first. Get the resulting id apend my site prefix and use this as my primary key in the main table.

Another way which I just thought of but not sure it will work is as follows:

Create a multiple column primary key on the source with one of the columns being an auto inc the other has a default value of the site prefix.

On the target you have the same structure but no auto inc column.

If sja can handle this the sync will work not a problem.

Cheers

Jason

[JasonPotter]

Hi Guys,

just saw another post that stated sja can handle multiple keys. Now just have to confirm it can handle a column being auto inc at one end and not at the other.

Cheers

Jason

[Ritesh]

JasonPotter wrote on Jun 2 2004, 12:13 PM:

Thanks for the response.

Though I thought sja will delete the enteries in the target if they are not in the source or is this a configurable feature? Hence when you do the first sync the target will become exactly like Source1 and then after the second sync the target will exactly be like Source2, hence wiping out the data entered into the target from the first sync.

Also what would the issues be if any, if Source1 and Source2 tried to do their syncs at the same time. In my case Source1 and Source2 are not always connected to the net and initiate their on connections. I would prefer not to have to specify connect times as these Source db's will connect to the net via GPRS and I am not always going to have a signal. So if I have a signal and can make a net connection via gprs I want to do the sync straight away. So there is a chance of 2 sync's running at the same time.

I look forward to your response on both points.

Cheers

Jason

By default SJA will delete the extra rows that are present in target but not in source. Starting from SQLyog 3.7, you can configure the synchronization agent for not to delete extra rows in the target.

[Ritesh]

JasonPotter wrote on Jun 2 2004, 04:43 PM:

Hi Guys,

just saw another post that stated sja can handle multiple keys.  Now just have to confirm it can handle a column being auto inc at one end and not at the other.

Cheers

Jason

STRUCTURE and PRIMARY KEY has to be exactly same on both the source and target server.

SJA correctly handles multiple keys provided that the order of the columns in the keys are the same.

[Author]

Posts