create a MySQL view that queries a remote db server RSS

• • Reply
  

  Megunticook

  Member

  1 Points

  14 Posts

  create a MySQL view that queries a remote db server

  Jan 22, 2013 01:08 PM|LINK

  I need to create a view in a MySQL database that displays data from another MySQL database on another server. Ideally, I also want the view to include some data from the local MySQL database (using JOIN to include some data from one of the local tables).

  Can I create a VIEW that queries multiple databases if one of those databases is on a remote server? I'm hung up on how to handle the connection string to the remote database, yet still preserving the credentials needed for the localhost.

  Any ideas? Thanks.

• • Reply
  

  bbcompent1

  All-Star

  32974 Points

  8505 Posts

  Moderator

  Re: create a MySQL view that queries a remote db server

  Jan 22, 2013 01:57 PM|LINK

  Well first off you are going to have to create a Linked Server in your MySQL DB Server. Then when you run your queries, you would reference that linked server like this: (can't remember exactly off hand, google should find it quick enough)

  remotedbname..database.table

  Mark as answer posts that helped you.
  
• • Reply
  

  bbcompent1

  All-Star

  32974 Points

  8505 Posts

  Moderator

  Re: create a MySQL view that queries a remote db server

  Jan 22, 2013 01:59 PM|LINK

  Its called Federated Tables: http://stackoverflow.com/questions/178401/can-mysql-5-0-have-a-view-of-a-table-located-on-another-server

  Mark as answer posts that helped you.
  
  • Marked as answer by bbcompent1 on Jan 30, 2013 03:24 PM
• • Reply
  

  Megunticook

  Member

  1 Points

  14 Posts

  Re: create a MySQL view that queries a remote db server

  Jan 24, 2013 12:18 AM|LINK

  Thanks. As it turns out, both databases are on the same server.

  Can I simply prepend the name of the database? So to create the view it would look like this perhaps:

  CREATE OR REPLACE VIEW  my_view AS
  SELECT
  database1.table1.field1 AS field1,
  database2.table2.field2 AS field2;

• • Reply
  

  bbcompent1

  All-Star

  32974 Points

  8505 Posts

  Moderator

  Re: create a MySQL view that queries a remote db server

  Jan 29, 2013 12:24 PM|LINK

  Even though it is still on the same server it will still require federated tables: http://stackoverflow.com/questions/508100/mysql-select-from-another-server

  Mark as answer posts that helped you.