如何在远程MySQL Ubuntu服务器上调用存储过程? [英] How can I a call stored procedure on a remote MySQL Ubuntu server?

问题描述

我有一台具有MySQL和许多存储过程的Ubuntu服务器(服务器A)，另一台具有MySQL的Ubuntu服务器(服务器B).

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

我想用服务器A上存储过程中的数据填充服务器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.

我在服务器B上尝试过此操作:

I tried this on server B:

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

但是它给出了这个错误:

But it gives this error:

错误1064(42000):您的SQL语法有错误；检查 与您的MySQL服务器版本相对应的手册 在'EXEC附近使用的语法 第1行的server_ip.db_name.owner.sp_courses()'

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:

在服务器B上，我有此表:

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)

服务器A上的存储过程返回如下数据:

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:

1. 服务器A:创建表并插入一些数据

1. 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);

服务器B:创建具有结构的表

SERVER B: create a table with structure

create table course_B
(
  CID int not null primary key,
  name varchar(50)
);

服务器A:创建连接到服务器B中的表的远程表:

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';

现在，与table_B_remote有关的任何操作都会直接影响服务器B中的table_B.

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

服务器A:创建存储过程，以将查询结果从course_A表发送到course_B_remote:

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!

也许您需要更改您的程序才能使用FEDERATED表. 没有启用的FEDERATED表，请参见此答案.

Maybe you will need to alter your procedures to use the FEDERATED tables. No enable FEDERATED tables see this answer.

这篇关于如何在远程MySQL Ubuntu服务器上调用存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！