Can a stored procedure work with two different databases? How about two servers?

Question

I am wondering if MySQL's stored procedures can work with two different databases on the same machine? How about if they are on different servers?

Answer 1

If we're talking about two databases on the same server: yes, a stored procedure can access another database. You have to make sure that the user under whose privileges the procedure is being run has the necessary privileges on each database.

For example, suppose you have two databases on the same server, mydb1 and mydb2, and that each contains a table named messages with the same structure. Suppose you want to add a stored procedure to mydb2 that empties the messages table in mydb2 and copies the contents of the messages table in mydb1. You could do this:

CREATE PROCEDURE `SynchroniseMessages` ()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DELETE FROM `mydb2`.`messages`;

INSERT INTO
    `mydb2`.`messages`
    SELECT * FROM `mydb1`.`messages`;

END

See how I've fully qualified the tables with the databases to which they belong. In fact you could argue that I'm being over-zealous here, because we specified that this stored procedure will belong in mydb2. I don't need to add the mydb2. qualifier. If the stored procedure were in the mydb1 database, I would need those qualifiers, but conversely I wouldn't need the mydb1. where it appears.

In order to be able to run this procedure (possibly in order to be able to define it?), I'd need to make sure my user has DELETE and INSERT privileges on mydb2, and also SELECT privileges on mydb1.

Databases on different servers sounds rather more complicated.