Moving data between two servers with SQL

--90e6ba6e843eb5616f04990b7744
Content-Type: text/plain; charset=ISO-8859-1

Hello,

I have a background process that runs as a combination of PHP&MySQL. The
end results are records in a table on server #1 (but not the entire table,
just a small subset of the table needs to move). What's the
easiest/cleanest way of moving those records to an identical table on
another server? In Oracle, we used to be able to set up connection profiles
and move data between servers with SQL, but I'm guessing that's not easy to
do with MySQL. I'd prefer not to use mysql command line client commands to
save the data as an OS file and then import that into the other server using
another mysql command line client command. I'd like to find something
cleaner than that.

I'm using 5.5.8.

thanks,

-Hank

--90e6ba6e843eb5616f04990b7744--

Re: Moving data between two servers with SQL

On 1/4/2011 15:53, Hank wrote:
> Hello,
>
> I have a background process that runs as a combination of PHP&MySQL. The
> end results are records in a table on server #1 (but not the entire table,
> just a small subset of the table needs to move). What's the
> easiest/cleanest way of moving those records to an identical table on
> another server? In Oracle, we used to be able to set up connection profiles
> and move data between servers with SQL, but I'm guessing that's not easy to
> do with MySQL. I'd prefer not to use mysql command line client commands to
> save the data as an OS file and then import that into the other server using
> another mysql command line client command. I'd like to find something
> cleaner than that.
>
> I'm using 5.5.8.
>
> thanks,
>
> -Hank
>
Have you looked at the FEDERATED storage engine?
http://dev.mysql.com/doc/refman/5.5/en/federated-storage-eng ine.html

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Moving data between two servers with SQL

--20cf3056429de4431f04990c8975
Content-Type: text/plain; charset=ISO-8859-1

Wow, that might just work! I've seen "Federated" tables mentioned about,
but I never knew that's what they are here for.. thanks.

Can I have a host (remote) table on a MySQL 4.1.x server, and the federated
table on a 5.5.8 server?

-Hank


On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

> On 1/4/2011 15:53, Hank wrote:
>
>> Hello,
>>
>> I have a background process that runs as a combination of PHP&MySQL.
>> The
>> end results are records in a table on server #1 (but not the entire table,
>> just a small subset of the table needs to move). What's the
>> easiest/cleanest way of moving those records to an identical table on
>> another server? In Oracle, we used to be able to set up connection
>> profiles
>> and move data between servers with SQL, but I'm guessing that's not easy
>> to
>> do with MySQL. I'd prefer not to use mysql command line client commands
>> to
>> save the data as an OS file and then import that into the other server
>> using
>> another mysql command line client command. I'd like to find something
>> cleaner than that.
>>
>> I'm using 5.5.8.
>>
>> thanks,
>>
>> -Hank
>>
>> Have you looked at the FEDERATED storage engine?
> http://dev.mysql.com/doc/refman/5.5/en/federated-storage-eng ine.html
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>

--20cf3056429de4431f04990c8975--

Re: Moving data between two servers with SQL

--20cf3056429d35529a04990c96ae
Content-Type: text/plain; charset=ISO-8859-1

Also, can I do this:

insert into federated_table select * from local_table?

-Hank


On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) <
shawn.l.green@oracle.com> wrote:

> On 1/4/2011 15:53, Hank wrote:
>
>> Hello,
>>
>> I have a background process that runs as a combination of PHP&MySQL.
>> The
>> end results are records in a table on server #1 (but not the entire table,
>> just a small subset of the table needs to move). What's the
>> easiest/cleanest way of moving those records to an identical table on
>> another server? In Oracle, we used to be able to set up connection
>> profiles
>> and move data between servers with SQL, but I'm guessing that's not easy
>> to
>> do with MySQL. I'd prefer not to use mysql command line client commands
>> to
>> save the data as an OS file and then import that into the other server
>> using
>> another mysql command line client command. I'd like to find something
>> cleaner than that.
>>
>> I'm using 5.5.8.
>>
>> thanks,
>>
>> -Hank
>>
>> Have you looked at the FEDERATED storage engine?
> http://dev.mysql.com/doc/refman/5.5/en/federated-storage-eng ine.html
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc.
> Office: Blountville, TN
>

--20cf3056429d35529a04990c96ae--

Re: Moving data between two servers with SQL

--90e6ba6e8c762b2a0004990d1fd0
Content-Type: text/plain; charset=ISO-8859-1

Nevermind -- it's working absolutely perfectly between 5.5.8 and 4.1.x.
Thanks again for the push.

-Hank


On Tue, Jan 4, 2011 at 5:14 PM, Hank wrote:

>
> Also, can I do this:
>
> insert into federated_table select * from local_table?
>
> -Hank
>
>
> On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) <
> shawn.l.green@oracle.com> wrote:
>
>> On 1/4/2011 15:53, Hank wrote:
>>
>>> Hello,
>>>
>>> I have a background process that runs as a combination of PHP&MySQL.
>>> The
>>> end results are records in a table on server #1 (but not the entire
>>> table,
>>> just a small subset of the table needs to move). What's the
>>> easiest/cleanest way of moving those records to an identical table on
>>> another server? In Oracle, we used to be able to set up connection
>>> profiles
>>> and move data between servers with SQL, but I'm guessing that's not easy
>>> to
>>> do with MySQL. I'd prefer not to use mysql command line client commands
>>> to
>>> save the data as an OS file and then import that into the other server
>>> using
>>> another mysql command line client command. I'd like to find something
>>> cleaner than that.
>>>
>>> I'm using 5.5.8.
>>>
>>> thanks,
>>>
>>> -Hank
>>>
>>> Have you looked at the FEDERATED storage engine?
>> http://dev.mysql.com/doc/refman/5.5/en/federated-storage-eng ine.html
>>
>> --
>> Shawn Green
>> MySQL Principal Technical Support Engineer
>> Oracle USA, Inc.
>> Office: Blountville, TN
>>
>
>

--90e6ba6e8c762b2a0004990d1fd0--