Hi I need some help.

Ive got 2 databases.

How ( in php ) can I select * from one database row, Then compare it to another database ( on another server ).

If a row is missing on the other server, Insert it.


any thoughts?
This is a bit over my head .

MySQL has a built in feature that allows it to duplicate itself over multiple servers, so you may not even need to go the PHP route. I've never tried to implement it, but I read about how Facebook was doing this because they have to keep all the data in multiple locations to serve the entire world as quickly as possible. If you need this process to happen automatically, research this or ask a DBA that works with MySQL regularly.

You could also just dump the entire database into a .sql file in a tool like phpMyAdmin then import it using the same tool on the other server. That is usually what I do, and will work just fine if you don't have multi-millions or more rows.

There's also a migration toolkit from Sun that I've used, that is a part of their GUI tools: http://dev.mysql.com/downloads/gui-tools/5.0.html , though I've actually only used this to translate a huge MS SQL database into MySQL.

I cant go down that route. ( Red tape and all that )

Ill need to do it via php.

How many tables and how many rows in each table?

Theres about 10k on the local sql server and there should be the same on the remote SQL server.

The remote SQL server has 5 tables that all hold the same data.

IE:
I need to make sure all usernames on the local DB are in each of the remote databases 5 tables. And update those 5 remote tables with the user that isnt in it.

if that makes sense?

10K rows. So your PHP script will have to do 50,000 iterations and of course ALL the databases will have to be offline during the process.
A php script will not run for that long a period, it will time out.

Is this a "one off" process or a regular maintenance procedure?

it will need to be on the hour ( or a cron hourly )

I was hoping to do something like..

Select what i want from the master db and put into array.

Select what i want from all other 5 tables and put into arrays.

compare master array with one of the 1 ( working through the 5 )
and building then 5 other arrays of rows that need to be inserted.

Then run through and insert what needs to be done.

I suppose once its run once, Hopefully it will only be an additional 2 or 3 rows that need adding every interation..

Its just selecting from a db and putting into array to be used later im having trouble with ( i dont know how )

Then again the compare afterwards still eludes me.

Ims tarting to think perl will be the better option, however my issue would still stand lol ( i can program, Just not that greatly )

Honestly, you'd better dump a backup of the master db, copy it to the other server, and restore it on the second server.
If the connection between the 2 is fast, the whole process should be quite fast.

Of course, the second server will never be really in sync, as during the backup, some modifications can go after a table is dumped, but the compare way will just be too hard on the server.

Something as simple as:

mount remoteHost:/var/dbDir /mnt/remote
mysldump dbMaster > /mnt/remote/master.sql
ssh user@remoteHost -e "mysql slaveDb</var/dbDir/master.sql"
umount /mnt/remote

This sounds more like these two applications just need to be sharing part of the same database. What is the problem with just doing that?

Its not really that simple

The data comes from another database that gets used for something else, This data then gets transferred every hour to yet another db that gets used to build something else and then the data will be transferred to this other server afterwards.

Simply dumping the db and moving it will not work as the tables need to stay in sync to a degree but dont update unless something new has happened.

As ive explained. I think theyw ay i want to do it is really the only route in this case ( ive thought about db dumping etc, But it then balls up other things that reply on unmodified rows.

Plus its only the one column that needs to be in sync with the other databases rows.

I have a feeling it will have to be a case of select what i want form master DB, Select * from the other db's control table and then compare them both..

If theres summat missing ill just assume its missing on the other tables and replicate it

hmmmm

Take a look at SQLYog.

That has a MySQL data synching agent and there is an article at sitepoint on how to use it

Originally Posted by chrishirst

Take a look at SQLYog.

That has a MySQL data synching agent and there is an article at sitepoint on how to use it

ty, ill take a look.

Something else that comes to my mind, is to have a stored procedure, rather than a php script, to do that.

In ms sql, you can link several servers together, and execute queries on both at the same time.

It seems that something equivalent exists on mysql: the federated storage
http://dev.mysql.com/doc/refman/5.1/...ed-create.html

The idea would be to have something like

insert into remote.tblA(
  field1
  ,field2
  ,field3
)
select 
  field1
  ,field2
  ,field3
from local.tblA loc
where not exists (
  select 1
  from remote.tblA rem
  where rem.id=loc.id
)