1

Short version: How can I refer to a non-local table via DSN in a mysql query?

Long version: My intent is to copy some mysql tables from a production machine to a backup machine nightly. The path of least resistance seems to be creating an ODBC connection to the backup machine, and then setting up an Event to run nightly. The Event's SQL would then be something simple, like

SELECT * FROM localdb.table INTO dsn.database.table

The problem I'm having is it seems that's not the way to connect out from mysql using a DSN. All my googling led me to plenty of sites describing connecting form some languages into mysql, but not from mysql into another mysql. The syntax specified by http://dev.mysql.com/doc/refman/5.0/en/join.html indicates there's no way to do it.

Steve
  • 13
  • 3

1 Answers1

0

You can't connect to an ODBC source from MySQL. Why don't you just use regular dump/restore approach?

Write a script to run every night at 3AM, that does something like (assuming you want to dump my_table from localdb:

#!/bin/bash
mysqldump localdb mytable > dump-file.sql
mysql -u user -h remote_ip localdb < dump-file.sql
Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • How odd. I expected them to support it, as it seems very useful. Do you happen to know why they don't? I was trying to avoid a shell script because it feels cleaner to have fewer moving parts in the system (of course this is only part of it, and I'm trying to reduce conceptual complexity). – Steve Dec 23 '10 at 15:27
  • @Steve: I've never heard of a RDBMS supporting what you are mentioning. A lot of the support native DBLINKS (Oracle, PostgreSQL, SQLServer, MySQL) but they don't support ODBC LINKS (AFAIK). Check out this question: http://stackoverflow.com/questions/1185742/mysql-equivalent-of-postgresqls-dblink-module – Pablo Santa Cruz Dec 23 '10 at 15:28
  • I was expecting some functionality like this: http://msdn.microsoft.com/en-us/library/aa276848(v=sql.80).aspx which I believe does support a DSN. Anyway, thanks for your quick responses. – Steve Dec 23 '10 at 15:35