Mysql – Cannot connect to Aurora RDS using workbench over ssh

Was trying to solve the same problem and stumbled here... It's a year late, but better late than never...

OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect.

Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this.

Steps are as follows:

1. Make sure you have XCode and command line development utilities installed. You will compile lots of stuff!

2. Download and compile iODBC (I used 3.52 from www.iodbc.org). Make sure you install somewhere "isolated" with "--prefix=/usr/local/iodbc" for instance;

3. Download and compile FreeTDS:

   • cd to freetds source directory;

   • type export CFLAGS="-arch i386"

   • type export LDFLAGS="-arch i386";

   • run config with ./configure --prefix=/usr/local/freetds --with-iodbc=<path_to_iodbc_in_step_1> --enable-odbc-wide

   • (MySQL Workbench NEEDS 32-bit archtecture; the exports will ensure that, since Mavericks - my OSX - is 64 bit.)

4. Download and install ODBC Administrator from Apple (https://support.apple.com/kb/DL895?locale=en_IS - version 1.0 worked for me);

5. Run ODBC Admin, go to "Driver" tab and create a "TDS" driver; Point "driver file" to /usr/local/freetds/lib/libtdsodbc.so (it should be there after step 3!)

6. Go to MySQL Workbench Migration Tools and choose as Connection Method "ODBC (FreeTDS)". In "Driver", type the name you used in step 5 ("TDS") and fill the other info as needed.

7. "Test Connection" and go have a cup of your favorite drink before moving on! :)

One entire workday "invested", but it did work.

UPDATE

After my cup of coffee, I moved on with my test migration, only to find out my SQL Server connection was NOT actually connecting (correct user, correct password). Another morning's work and here are the adjustments:

1. Make sure you compile iODBC to 32-bit architecture. To do so:

   • use the same export you will use when compiling FreeTDS (CFLAGS,LDFLAGS);
   • use --prefix=/usr/local/iODBC32. Make sure you compile to different directory or you may break your current ODBC install.

2. Make sure you compile your "32-bit" FreeTDS version pointing to the "right" iODBC with:

   • ./configure --with-iodbc=/usr/local/iODBC32

This should do the trick.

Another piece of advice:

MySQL Migrations will use exec sys.sp_databases to fetch info about the schemas to be migrated. If you're not using sa to migrate (which you probably shouldn't!), make sure your login has enough privileges on the master database (I myself created a "temporary" clone of sa to migrate); otherwise, MySQL Migrations will have trouble fetching and copying.

All these things followed and I finally completed a successful migration (minor manageable collation problems).

Hope this helps,

A pg_dump output with the -Fc (custom) is not a SQL script, it's a compressed archive that only pg_restore is able to handle.

You may either pass the dump file to pg_restore and pipe its output to psql (often done that way and close to what you tried):

pg_restore /home/jboss/proc_dump_jan15.dump | psql  --host=blahrds [other options]...

...or use the -d,-h,... and other options of pg_restore to have it connect to the database and skip the psql invocation.

See pg_restore manpage for 9.3 for details.

Nb. the EC2 database is PG 9.0 and the RDS database is PG 9.3.5 but I assume this is ok as the dump/restore will deal with the migration?

The recommended method is to use pg_dump 9.3 against the 9.0 database to create the dump. The reasoning is that newer versions always know how to extract older databases such that they will import flawlessly into their version. The opposite is not true, since obviously at the time when pg_dump 9.0 is released, the authors don't know what compatibility problems future versions might bring.