Connecting to MySQL

Concept Information

ACCESSDATA command

MySQL is a popular open source relational database management system. Use the MySQL data connector to import your organization's MySQL data.

Note

You cannot use this connector independently of Analytics. You can configure a DSN for the connector driver using the Windows ODBC Data Source Administrator, however you must test the DSN connection from within Analytics and not from the connector's Windows DSN configuration dialog.

Before you start

To connect to MySQL, you must gather the following:

  • the database server's host name
  • the correct connection port
  • your username and password if using standard authentication
  • read access for the schema and tables you want to connect to

For help gathering the connection prerequisites, contact the MySQL administrator in your organization. If your administrator cannot help you, you or your administrator should contact MySQL Support.

Create a MySQL connection

  1. From the Analytics main menu, select Import > Database and application.
  2. From the New Connections tab, in the ACL Connectors section, select MySQL.

    Tip

    You can filter the list of available connectors by entering a search string in the Filter connections box. Connectors are listed alphabetically.

  3. In the Data Connection Settings panel, enter the connection settings and at the bottom of the panel, click Save and Connect.

    You can accept the default Connection Name, or enter a new one.

The connection for MySQL is saved to the Existing Connections tab. In the future, you can reconnect to MySQL from the saved connection.

Once the connection is established, the Data Access window opens to the Staging Area and you can begin importing data. For help importing data from MySQL, see Working with the Data Access window.

Connection settings

Basic settings

Setting Description Example
Server

The host name or IP of the server hosting the MySQL database.

 192.168.0.1
Port The port of the server hosting the MySQL database. 3306
Database The name of the default database to connect to when connecting to the MySQL Server.  
User The user to authenticate when connecting to MySQL.  
Password The password to authenticate the specified user with the MySQL server.  

Advanced settings

Setting Description Example
Integrated Security Specifies whether to authenticate to MySQL server with Windows Integrated Security. false
Limit Key Size

The maximum length of a primary key column. Setting the size to 0 will make the key length revert to the original length.

This property makes the connector override the reported length of all the primary key columns.

 255
Map to Long Varchar

Controls whether or not a column is returned as SQL_LONGVARCHAR.

Use this setting to map any column larger than the specified size so they are reported as SQL_LONGVARCHAR instead of SQL_VARCHAR.

 -1
Map to WVarchar

Controls whether or not string types map to SQL_WVARCHAR instead of SQL_VARCHAR. It is set by default.

String columns must be mapped to SQL_WVARCHAR to accommodate various international character sets, so MapToWVarchar is set to true by default. You may set it to false to use SQL_VARCHAR instead.

 true
Pseudo Columns

Indicates whether or not to include pseudo columns as columns to the table.

The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns.

 MyTable=*
Query Passthrough This option passes the query to the MySQL server directly as is. true
Upper Case Identifiers This property reports all identifiers, including table and column names, in uppercase. false