mysql query 2 databases with different user accounts

Question

I have attempted to research quite a bit online before posting this question. I have 2 mysql databases on the same linux server. Each database has a different login id. I need to execute a mysql query to query both of these databases simultaneously using their specific login ids. I know its possible to do via PHP or certain other tools but how can I formulate such a query in the standard mysql client itself?

for instance, consider 2 databases with identical schema - the following would work if the same login id has permissions for both databases DB1 and DB2:

use DB1; select * from table1 where not in (select xxx from DB2.table1);

I am not sure how to modify this query if the login ids for each database is different.

Answer 1

assuming you have two instances, one on tcp/3306 and one on tcp/3307, you can accomplish what you want by implementing the logic within the code (php, python, perl, etc)

so it would look like this

resultsdb1 = qry(localhost,3306,"DB1","select * from table1")
resultsdb2 = qry(localhost,3307,"DB2","select * from table1")

then, in the code, you can compare results as necessary. the where/select cannot be across mysql servers, whether on different ports, or otherwise.

(you have to write that qry function, im just using that as example)