mysql怎么远程存储过程,如何在远程MySQL Ubuntu服务器上调用存储过程？

I have an Ubuntu server with MySQL and many Stored Procedures (server A) and another Ubuntu server with MySQL (server B).

I'd like to populate the database on server B with data from the stored procedures on server A.

At this point I'd like to test the connection with no success.

I tried this on server B:

mysql> EXEC server_A_IP.DB_name.username.sp_courses();

But it gives this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the

manual that corresponds to your MySQL server version for the right

syntax to use near 'EXEC

server_ip.db_name.owner.sp_courses()' at line 1

This is an example of what I'd like to do eventually:

On server B I have this table:

mysql> describe Course;

+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| CID | int(11) | NO | PRI | 0 | |

| name | varchar(50) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

The Stored Procedure on Server A returns data like this:

call sp_courses();-- where the parameter indicates level 1.Returns [courseID, name]

1 CS1

2 CS2

10 CS3

12 CS4

13 CS5S

Can I fill data into the table from stored procedure on a different server?

解决方案

There is a way to send data to a remote table using stored procedures, the trick is use federated tables. I created tables in two servers and stored procedure to reproduce your situation:

SERVER A: create table and insert some data

create table course_A

(

CID int not null primary key,

name varchar(50),

year int

);

insert into course_A values

(1,'CS1P',2016),

(2,'CS1Q',2016),

(109,'CS1-CT',2016),

(120,'CS1PX',2016),

(121,'CS1S',2016);

SERVER B: create a table with structure

create table course_B

(

CID int not null primary key,

name varchar(50)

);

SERVER A: create a remote table connected to table in server B:

create table course_B_remote

(

CID int not null primary key,

name varchar(50)

)

ENGINE=FEDERATED

CONNECTION='mysql://user:password@SERVER_B_IP:3306/database/course_B';

Now, whatever to do with table_B_remote affects directly to table_B in SERVER B.

SERVER A: Create stored procedure to send the query results from course_A table to course_B_remote:

delimiter $$

drop procedure if exists sp_coursesForYear$$

create procedure sp_coursesForYear(p_year int)

begin

/*

Your procedure code...

*/

insert into course_B_remote (CID,name)

select CID, name

from course_A

where year = p_year;

end$$

delimiter ;

Now, test the stored procedure:

server A prompt> call sp_coursesForYear(2016);

Query OK, 5 rows affected (0,00 sec)

Check if it worked:

server B prompt> select * from course_B;

+-----+--------+

| CID | name |

+-----+--------+

| 1 | CS1P |

| 2 | CS1Q |

| 109 | CS1-CT |

| 120 | CS1PX |

| 121 | CS1S |

+-----+--------+

5 rows in set (0.00 sec)

Enjoy!

Maybe you will need to alter your procedures to use the FEDERATED tables.

No enable FEDERATED tables see this answer.