A fairly common question when preparing to integrate your web application’s MySQL database into a FileMaker solution using ESS (External SQL Sources) is, “What information do I need from my hosting provider to connect to MySQL?”

The list is pretty straightforward, and you’ll probably have an easier time asking your hosting company for these MySQL specifics than trying to explain FileMaker ESS:

Database connection settings

Most web applications store the MySQL connection information in a settings file, and you may be able to find most of this information there. For example, Drupal’s database settings are usually stored in the sites/default/settings.php file. Often the settings are stored in a URL-style, DSN format, for example:

mysql://username:password@hostname/databasename

If you’re not comfortable (or authorized) to be digging around in your web application’s source files, the person who manages the application for you should be able to get look these up for you:

• User name
• Password
• Database Name
• Port Number - This will almost always be 3306, which is the MySQL default.
• Host Name - If your web application is configured to connect to mysql at ‘localhost’, this means MySQL is running on the same server as your web site. So, to connect to MySQL remotely you would use the same domain name as your web site.

Things to watch out for

• Many security-conscious hosting providers are wary of allowing remote connections to MySQL, and may block incoming connections to port 3306 altogether. One workaround for this problem is to tunnel to port 3306 over SSH, but this can be less than ideal. You may need to shop around to find a host that does allow remote connections.
• Even if port 3306 is open, you may still need to have remote access granted to your MySQL user.
• Be very careful with your web application’s database. Your MySQL login probably gives you access to insert, edit, or delete records, and maybe even drop tables! Most web applications don’t expect external forces to manipulate their data, so making changes via ESS could cause unpredictable behavior. If possible, obtain a separate, read-only MySQL account you can use to connect via ESS, and you won’t have to worry about accidentally corrupting your web database.