MySQL question

[shaddow]

I have 2 identicall web servers with apache and mysql working.
I want to backup an database from server1 to server2 and i dont want to get the database files and copy them to server2 i want to use querrys.
The php that updates databases is on the server1 or localhost

Some code here:

//first connexion on local server(server1)
$con=mysql_connect("localhost")
	or die("Error server1");
mysql_select_db("database1",$con);

$con1=mysql_connect("server2")
	or die("Error server2");
mysql_select_db("database1",$con1);

and when i want to execute a querry on the remote server(server2) dont want to.
What to do if this is posible to work? ________
George, M

 

[KarveR]

probably something like this, can't test it as I'm still at work tho ;-)

$result=mysql_query("SELECT * from server1_table",$con);

while($row=mysql_fetch_array($result)){
$update=mysql_query("insert into server_2table (list of field names ) values ( $row[0],$row[1],$row[2] etc etc)&quot,$con1);
}
if ($update > 0 ){
echo "something went wrong";
}else{
echo "all should be ok ..maybe";
} ______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[shaddow]

I did exactly like you did but even with and simple querry
$update=mysql_query("select * from mytable",$con1)
didnt work
________
George, M

 

[RbgoWeb]

There is no easy way in doing what you want.
It even gets more difficult if you like db2 to become an exact image of db1 including any auto_increment primary keys
on which other tables in your db might depend.
In this last case for each table in db2 you need to...
1. delete all rows
2. insert empty records till max auto_increment count
of each table of db1.
3. update all rows in db2 at exact same row index used in
db1
4. registering row indexes that are not in use (deleted) in
each table inside db1 in an array.
5. delete those (empty) rows in db2 as well

It could be that this is not the smartest way of doing it, because I'm pretty fresh in this field. But this is the way I would do it now. If you find a better way please let me know.

 

[KarveR]

but my line
$update=mysql_query("select * from mytable",$con1) didn't have select, it had INSERT into ....
______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[shaddow]

yes it's an easy way to do that with only an select from db1 then insert in db2 with all IS's and keys like db1
but you have to keep db2 with no indexes and pk just pure data withc you will fill up later just for keep and backup of same data and keys.
I dont use in db2 any pk or autoincrement fields all are inserted from db1 ________
George, M