SQLines Data - Configure and Troubleshoot Connection to MySQL

If you are going to connect to a localhost MySQL database you do not need to setup any additional libraries to use SQLines Data tool as they are supplied with MySQL server.

If you need to connect to a remote MySQL database, you need to have MySQL C API libmysqlclient installed in your system.

Before downloading check if you already have MySQL libraries:

Linux:
- /usr/lib/mysql/libmysqlclient.so
- /usr/lib64/mysql/libmysqlclient.so
Windows: (Drive letter and version can be different)
- C:\Program Files\MySQL\MySQL Server 8.0\lib\libmysql.dll
- C:\Program Files\MySQL\MySQL Server 5.7\lib\libmysql.dll
- C:\Program Files\MySQL\MySQL Connector C 6.1\lib\libmysql.dll

If MySQL client is not available, go to MySQL Download page https://dev.mysql.com/downloads/connector/ and download MySQL Connector/C or C API (libmysqlclient):

Select C API (libmysqlclient) (https://dev.mysql.com/downloads/c-api/)
Select MySQL Product Archives (https://downloads.mysql.com/archives/c-c/)
Download in install the connector
Add MySQL lib directory to PATH (C:\Program Files\MySQL\MySQL Connector C 6.1\lib i.e.)

Installing MySQL Connector/C on Linux

Download the latest version of MySQL Connector/C, for example, mysql-connector-c-6.1.9-linux-glibc2.5-x86_64.tar.gz and extract files:

tar -xvzf mysql-connector-c-6.1.9-linux-glibc2.5-x86_64.tar.gz

You can find libmysqlclient.so library at lib directory. Before running sqldata command, add MySQL library location to LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:<mysql_connector_dir>/lib

An alternative way to install MySQL libraries is to run:

yum install mysql-devel

If your .so file ends with the library version, for example, libmysqlclient.so.16 then create a symbolic link, so SQLines Data tool is able to load the required library:

cd <mysql_connector_dir>/lib
ln -s libmysqlclient.so.16 libmysqlclient.so

Setup SSL Connection

When connecting to a MySQL database you can get the following error: “SSL connection is required. Please specify SSL options and retry”

Use SSL connection options in sqldata.cfg or command line:

-mysql_ssl_mode - SSL connection mode (–ssl-mode MySQL option), possible values are DISABLED, PREFERRED (default), REQUIRED, VERIFY_CA and VERIFY_IDENTITY
-mysql_ssl_key - SSL private key file (–ssl-key MySQL option), the absolute path to the file
-mysql_ssl_cert - SSL certificate file (–ssl-cert MySQL option), the absolute path to the file in PEM format
-mysql_ssl_ca - SSL CA file (–ssl-ca option of MySQL CLI), the absolute path to the file in PEM format

Troubleshooting Connection on Windows

When you click Test Connection you get the following error:

Error Loading MySQL C API libmysql.dll. The specified module could not be found

Possible reasons:

MySQL C API is not installed. Downlaod and install MySQL C API client at http://dev.mysql.com/downloads/connector/

MySQL C API is installed but SQLines Data tool cannot find libmysql.dll for some reason. Add the directory where libmysql.dll is located (for example, C:\Program Files\MySQL\MySQL Connector C 6.1\lib) to the PATH environment variable.

If you run SQLines Data on MySQL 8.x server host and do not use MySQL Connector C, make sure that both C:\Program Files\MySQL\MySQL Server 8.0\lib and C:\Program Files\MySQL\MySQL Server 8.0\bin are added to the PATH environment variable.

The reason is that although libmysql.dll in located in lib directory, it still needs libssl-1_1-x64.dll and libcrypto-1_1-x64.dll located in the bin directory.

To set the PATH variable on Windows, right-click the Computer icon, select Properties, click the Advanced System Settings. Then select Advanced tab and click the Environment Variables button. Go to System Variables, find and append Path.

Note. You have to restart SQLines Data tool after modifying the PATH environment variable.

You can also edit sqldata.cfg configuration file and set -mysql_lib option.

Error - Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled

To fix this issue set the environment variable LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1. To do this right-click the Computer icon, select Properties, click the Advanced System Settings. Then select Advanced tab and click the Environment Variables button. Go to System Variables and add the variable.

Note. You have to restart SQLines Data tool after adding an environment variable.