stream transfer on mysql server to mysql server

Mar 2, 2016 at 4:24 PM
Hi,
I want to stream transfer for dumping sql to another server.

Is this possible ?
Coordinator
Mar 3, 2016 at 9:02 AM
Yes sure. This is an example:
string connstr1 = "server=192.168.1.2;user=user1;pwd=user1;database=test1;";
string connstr2 = "server=192.168.1.3;user=user2;pwd=user2;database=test2;";

MemoryStream ms = new MemoryStream();

using (MySqlConnection conn1 = new MySqlConnection(connstr1))
{
    MySqlCommand cmd1 = new MySqlCommand();
    cmd1.Connection = conn1;
    conn1.Open();

    MySqlBackup mb1 = new MySqlBackup(cmd1);
    mb1.ExportToMemoryStream(ms);

    conn1.Close();
}

// This step is important
ms.Position = 0;

using (MySqlConnection conn2 = new MySqlConnection(connstr2))
{
    MySqlCommand cmd2 = new MySqlCommand();
    cmd2.Connection = conn2;
    conn2.Open();

    MySqlBackup mb2 = new MySqlBackup(cmd2);
    mb2.ImportFromMemoryStream(ms);

    conn2.Close();
}
Mar 7, 2016 at 5:10 PM
Edited Mar 7, 2016 at 9:00 PM
thanks adriancs

I will test and share result here.

edit: yes it's work's

but why close conn1 ?
   mb1.ExportToMemoryStream(ms);

    conn1.Close();
Coordinator
Mar 9, 2016 at 1:08 PM
that is a connection.

Why a connection must be closed?

When a connection is opened, MySQL server will allocate CPU resources (such as RAM, CPU computation thread) for the connection.
A connection will store temporary data in memory for transactions and SQL execution references.

The MySQL server comes with a settings which limit the numbers of opened connection.
The limitation is used to prevent the CPU resources to be used up/exhausted and causing other functions of the server to be ceased to perform.
When a connection is closed, the server will release any resources that are being occupied, make it stand by for allocation of new request.

Once the connection limit has reached, the MySQL server will deny any further request and cease to respond.

That's why you have to code your program to close the connection as soon as possible when the connection is no more needed.

In above example, I assume that you are trying to connect to 2 different databases, that's why I separated process into 2 connection.
The first connection will be closed before opening the second connection.