Okay, I've attempted Google searches, but I'm not hitting the correct combination of keywords it seems.

Server1
- Database 1
- Table A

Server2
- Database 2
- Table B
- Database 3
- Table C

That's the setup. I want to create a script that copies the contents of Table A to Table B, then go through Table B and verify everything was copied correctly, upon verification delete everything from Table A


Unfortunately I do not know the MySQL commands to allow copying between two different servers. Anybody able to offer some help?

12/16/2008 8:13:36 AM

On server A:
mysqldump -uuser -ppass -hhost database > my.sql

Transfer my.sql to server B.

On server B:

mysql -uuser -ppass -hhost database < my.sql

[Edited on December 16, 2008 at 8:20 AM. Reason : There are additional flags for verification and whatnot; but this is the gist of it]

12/16/2008 8:20:28 AM

This has to be done on Server1 unfortunately.


Script on Server1 dumps Table A to Table B

Script on Server2 reads new records in Table B, verifies contents of each record with cooresponding record in Table A
If record data matches, delete record from Table A, otherwise, recopy data to Table B

The script must be able to move the data from Server1 to Server2 with no human interaction.

12/16/2008 8:28:02 AM

Well then you're screwed.

12/16/2008 8:39:00 AM

Quote :

"This has to be done on Server1 unfortunately."

Honestly the server change isn't necessary. The host flag is what's important. Both can be run from the same box.

12/16/2008 9:04:23 AM

Due to the way the users are setup on each of the machines, I need to figure out how to do this from the different machines.

Servers are located in different countries, Server1 must keep minimal data due to internal theft, however it much be able to dump its information into the database on Server2

[Edited on December 16, 2008 at 10:52 AM. Reason : Its really odd situation, should be possible though]

12/16/2008 10:51:11 AM

So long as you have a computer and database accounts that can access both MySQL servers, it's very possible from one box.

mysqldump -uuser -ppass -hserverA.mysql database > my.sql
mysql -uuser -ppass -hserverB.mysql database < my.sql

Could be run from any computer that has MySQL installed.

12/16/2008 11:16:19 AM

Nope, user permissions are set so:

User1 can only INSERT into Table A (Server1 access only)

User2 can only SELECT, REMOVE from Table A (Server2 access only)
User2 can only SELECT from Table B (Local access only)

User3 has full permissions on Table B and Table C (Local access only)

--------------------

Server1 will run a script, using User2 to SELECT * from Table A and INSERT into Table B

Server2 will run a script, using User2 to verify Table B against Table A, removing records from Table A upon verification

--------------------

Basically we need to keep MINIMAL amount of data on Server1, users of Server1 should not be able to see anything much if any of Table B

A file transfer is not possible between the Server1 and Server2

12/16/2008 11:28:47 AM

Stein I'll look at your suggestion some and see if I can edit it to fit this model... however the -h option is new to me, so MAYBE that'll work.

12/16/2008 11:30:13 AM

Okay, is there some way to connect to the different databases on two servers at the same time?

Server1 has TableA (Database3)
Server2 has TableB (Database4)

Server1 needs to run a command

SELECT * FROM TableA, TableB where (Stuff happens);


What needs to happen beforehand to get Server1 access to both databases

12/18/2008 6:04:47 AM

Quote :

"Okay, is there some way to connect to the different databases on two servers at the same time?"

No, at least not in the way you want to do it.

12/18/2008 8:41:29 AM

can you link server 2 to server 1 and execute remote transactions onto server 2?

[Edited on December 18, 2008 at 5:04 PM. Reason : .]

12/18/2008 5:04:10 PM

this is 5 lines of code in SAS.

libname server1 mysql ...server connection info... user="user2"
libname server2 mysql ...server connection info... user="user3"
data server2.Table B;
set server1.Table A;
run;

since user3 is the only user with insert permissions to Table B, you will have to run this on server2; otherwise you could run it on either server1, server2, or a third server.

12/18/2008 8:34:51 PM

What about creating/break replication? Setup a master->slave setup from A->B. On server1 you can choose to break the replication and then drop A. When you want to push to B, your script can re-enable replication and it will copy everything over. Or is it important that it all happens at one specific time (mysql replication is asynchronous)?

12/18/2008 10:13:51 PM