Connecting to MySQL
Skyvia supports usual TCP/IP MySQL connections and secure SSL and SSH MySQL connections.
To connect to a MySQL server, you need to specify the database server host name or IP address, the port the MySQL server is running on, the user name and password to log in with, and the database name.
For connecting to local MySQL servers, Skyvia offers two options: direct connections and agent connections.
In order to use a direct connection, your MySQL server must be available through the Internet. If you are connecting to MySQL server on your local computer directly from Skyvia, allow such connections in your firewall. See the IPs to allow in the How to Configure Local Database Server to Access It from Skyvia topic. If you are connecting to a computer in your local network, you should use port forwarding.
Agent connections don’t have such requirements, but you need to install the Skyvia Agent application in order to use them.
You need to specify the following parameters for TCP/IP MySQL connection:
- Server — domain name or IP address of the MySQL Server host to connect to. Please note that your MySQL Server host must be accessible from the Internet by this IP address or domain name. If you use an IP address for the Server parameter, this must be the external IP address of the host, not the internal one.
- Port — MySQL Server connection port; default value is 3306.
- User Id — user name to log in with.
- Password — password to log in with.
- Database — name of the MySQL database you want to connect to.
If you need to pass UTF8 characters to a MySQL database, click Advanced Settings and select the Unicode checkbox.
If you want to use SSL or SSH connection, additionally you need to click Advanced Settings and select the required Protocol.
After this, for SSL connection you need to specify:
- SSL CA Cert — authority certificate. Paste the content of the certificate file into the box or click the button to open a multiline editor and paste the content of the certificate file there.
- SSL Cert — client certificate. Paste the content of the certificate file into the box or click the button to open a multiline editor and paste the content of the certificate file there.
- SSL Key — client private key. Paste the content of the key file into the box or click the button to open a multiline editor and paste the content of the key file there.
- SSL TLS Protocol — preferred TLS protocol version reported to a server when establishing an SSL connection. The default value is 1.1 to avoid errors with older server versions, not supporting TLS 1.2. Acceptable values are 1.0, 1.1, 1.2 (without quotes).
For SSH connection specify the following:
- SSH Authentication Type — type of SSH authentication to use: Password or PublicKey. When using the password authentication you need to specify the SSH password. For public key authentication you need to specify the passphrase for the private key and the private key.
- SSH Host — name or IP address of the SSH server.
- SSH Port — TCP/IP port to connect to the SSH Server. By default, it is 22.
- SSH User — user name on the machine where the SSH Server is running. It is a Windows user, not a user of the MySQL Server.
- SSH Password — user account password on the SSH Server. It is available if Password SSH Authentication Type is selected.
- SSH Passphrase — passphrase for a private key. You can set it while generating public and private key files through a key generator tool, for example PuTTygen. It is available if PublicKey SSH Authentication Type is selected.
- SSH Private Key — private key. Paste the content of the key file into the box or click the button to open a multiline editor and paste the content of the key file there. It is available if PublicKey SSH Authentication Type is selected.
In Advanced Settings, you can also select the Unicode checkbox to use Unicode encoding for the connection and specify the Command Timeout interval. The latter specifies the wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn’t include the time necessary to fetch data if the command selects some data.
Additionally, if you want Skyvia to treat MySQL TINYINT(1) data type as numeric data type instead of boolean, with values 0 and 1 instead of False and True, you can clear the TINYINT(1) As Boolean check box.
Besides, Advanced Settings include the Connection Timeout parameter. This parameter determines the time (in seconds) to wait while trying to establish a connection before terminating the attempt and reporting an error. Generally you don’t need to modify it, but in some specific cases when a connection to the database server is not good you may try increasing their values.
Supported Actions and Actions Specifics
MySQL connector supports the following actions:
- Execute Command in Source, Lookup, and Target Data Flow components and in Import and Export tasks in the Advanced mode.
Execute Query in Source Data Flow components and in Import and Export tasks in the Advanced mode.
- Lookup in Lookup Data Flow components.
- Insert in Target Data Flow components.
- Update in Target Data Flow components.
- Delete in Target Data Flow components.