Querying data by joining two views in two database on different servers

Question

There are two views in two different databases on different servers, I need to join them so as to make few queries.

What options do I have?

What should I do?

Join views in same database is working.

SELECT * FROM view1
UNION ALL
SELECT * FROM view2

Thanks

See this link http://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers — Capslock, Dec 26 '15 at 09:32
@Capslock the following link for tables but I am asking for views.I already follow the link but my code is not working. — amit prasad, Dec 28 '15 at 05:36
see [link1](http://stackoverflow.com/questions/5832787/mysql-joins-between-databases-on-different-servers-using-python), [link2](http://dba.stackexchange.com/questions/106739/how-can-i-select-data-from-two-differents-servers).. use the idea from these to get values from views. — mehere, Dec 29 '15 at 07:32
@EvaMariam thanks for u r link.But still i did not get sucess. — amit prasad, Dec 29 '15 at 07:54

mehere · Accepted Answer · 2015-12-29T15:51:27.817

You can use the federated feature in mysql.

Enable Federated in your mysql

Log into the mysql CLI with root (or another account with sufficient privilege).

Type: show engines; You should see no FEDERATED engine at this point, like this:

mysql> show engines;
+------------+---------+------------------------------------------------------------+---    -----------+------+------------+
| Engine     | Support | Comment                                                    |  Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--- -----------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO               | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO            | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO            | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

To enable the federate engine, type the following:

install plugin federated soname 'ha_federated.so';

Dont mind if you get ERROR 1125 (HY000): Function 'federated' already exists

You can now safely add the line 'federated' to the /etc/my.cnf file like this:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
federated

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart mysqld (service mysqld restart, etc...)

After the restart, go back in to the mysql CLI.

Type 'show engines;' You should now see the FEDERATED Engine available and with SUPPORT as YES.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED  | YES     | Federated MySQL storage engine                             | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)

Restart mysql

service mysqld status
service mysqld stop
service mysqld start

Create view1 in db1 and view2 in db2

In db1,

CREATE table federated_table (
id int not null,
name   VARCHAR(32) NOT NULL DEFAULT ''
) 
ENGINE=FEDERATED

CONNECTION='mysql://user_name:password@remote_server_name:3306/db2/view2';

Note the federated_table and view2 must same column names and type

Execute SELECT * FROM db2.federated_table;
Execute select * from db1.view1 union all select * FROM db1.federated_table;

Refer : http://eves4code.blogspot.in/2015/12/querying-data-by-joining-two-views-in.html

Querying data by joining two views in two database on different servers

1 Answers1