Running MySQL with on different directory

GOAL:

I am trying to run mysql on my work servers machines. But as admins did not install it in the usual default installation path, it fails. How can I make it works?

FACTS:

MySQL is installed under: /opt/mysql/10.1.16/

$> ls -1 /opt/mysql/10.1.16/
bin
COPYING
COPYING.LESSER
COPYING.thirdparty
CREDITS
data
EXCEPTIONS-CLIENT
include
INSTALL-BINARY
lib
man
mysql-test
README
README-wsrep
scripts
share
sql-bench
support-files

However, when I want to run mysql from a script, I do:

export LIBMYSQL_PLUGIN_DIR="/opt/mysql/10.1.16/lib/plugin"
export LD_LIBRARY_PATH="/opt/mysql/10.1.16/lib"
export LD_RUN_PATH="/opt/mysql/10.1.16/lib"
export PATH="/opt/mysql/10.1.16/bin:/home/xxxxxxxx/bin:/usr/local/bin:/usr/bin:/bin:/usr/lib64/jvm/jre/bin:/usr/lib/mit/bin:/usr/lib/mit/sbin"

/opt/mysql/10.1.16/bin/mysql -C --no-beep --line-numbers --protocol=TCP --host="${DB_HOSTNAME}" --port="${DB_PORT}" --user="${DB_USER}" --database "${DB_SCHEMA}" -o --delimiter ','

With all my DB_xxxx corectly set, I get the following error:

ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory

I checked the official documentation page:
https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html and the only mention about plugin is to check if LIBMYSQL_PLUGIN_DIR, which seems good since:

$> echo "${LIBMYSQL_PLUGIN_DIR}"
/opt/mysql/10.1.16/lib/plugin

$> ls -la /opt/mysql/10.1.16/lib/plugin
-rwxr-xr-x 1 mysql dba    17382 Sep 13  2016 adt_null.so
-rwxr-xr-x 1 mysql dba    12462 Sep 13  2016 auth_0x0100.so
-rwxr-xr-x 1 mysql dba    33039 Sep 13  2016 auth_gssapi_client.so
-rwxr-xr-x 1 mysql dba    80774 Sep 13  2016 auth_gssapi.so
-rwxr-xr-x 1 mysql dba    18007 Sep 13  2016 auth_pam.so
-rwxr-xr-x 1 mysql dba    13028 Sep 13  2016 auth_socket.so
-rwxr-xr-x 1 mysql dba    23521 Sep 13  2016 auth_test_plugin.so
-rw-r--r-- 1 mysql dba      227 Sep 13  2016 daemon_example.ini
-rwxr-xr-x 1 mysql dba    15267 Sep 13  2016 debug_key_management.so
-rwxr-xr-x 1 mysql dba    14975 Sep 13  2016 dialog_examples.so
-rwxr-xr-x 1 mysql dba    39102 Sep 13  2016 dialog.so
-rwxr-xr-x 1 mysql dba    35337 Sep 13  2016 example_key_management.so
-rwxr-xr-x 1 mysql dba    67516 Sep 13  2016 file_key_management.so
-rwxr-xr-x 1 mysql dba   534847 Sep 13  2016 ha_archive.so
-rwxr-xr-x 1 mysql dba   326244 Sep 13  2016 ha_blackhole.so
-rwxr-xr-x 1 mysql dba  6315641 Sep 13  2016 ha_connect.so
-rwxr-xr-x 1 mysql dba   270829 Sep 13  2016 ha_example.so
-rwxr-xr-x 1 mysql dba   408688 Sep 13  2016 ha_federated.so
-rwxr-xr-x 1 mysql dba   658050 Sep 13  2016 ha_federatedx.so
-rwxr-xr-x 1 mysql dba 17067973 Sep 13  2016 ha_innodb.so
-rwxr-xr-x 1 mysql dba 13007344 Sep 13  2016 ha_mroonga.so
-rwxr-xr-x 1 mysql dba  1586135 Sep 13  2016 handlersocket.so
-rwxr-xr-x 1 mysql dba  1687004 Sep 13  2016 ha_oqgraph.so
-rwxr-xr-x 1 mysql dba   745005 Sep 13  2016 ha_sphinx.so
-rwxr-xr-x 1 mysql dba  5150095 Sep 13  2016 ha_spider.so
-rwxr-xr-x 1 mysql dba   226800 Sep 13  2016 ha_test_sql_discovery.so
-rwxr-xr-x 1 mysql dba    35898 Sep 13  2016 libdaemon_example.so
-rwxr-xr-x 1 mysql dba   131020 Sep 13  2016 locales.so
-rwxr-xr-x 1 mysql dba   221483 Sep 13  2016 metadata_lock_info.so
-rwxr-xr-x 1 mysql dba    18211 Sep 13  2016 mypluglib.so
-rwxr-xr-x 1 mysql dba    16382 Sep 13  2016 mysql_clear_password.so
-rwxr-xr-x 1 mysql dba    18751 Sep 13  2016 qa_auth_client.so
-rwxr-xr-x 1 mysql dba    24478 Sep 13  2016 qa_auth_interface.so
-rwxr-xr-x 1 mysql dba    13542 Sep 13  2016 qa_auth_server.so
-rwxr-xr-x 1 mysql dba   229059 Sep 13  2016 query_cache_info.so
-rwxr-xr-x 1 mysql dba   286720 Sep 13  2016 query_response_time.so
-rwxr-xr-x 1 mysql dba   534415 Sep 13  2016 semisync_master.so
-rwxr-xr-x 1 mysql dba   445144 Sep 13  2016 semisync_slave.so
-rwxr-xr-x 1 mysql dba   175928 Sep 13  2016 server_audit.so
-rwxr-xr-x 1 mysql dba    32295 Sep 13  2016 simple_password_check.so
-rwxr-xr-x 1 mysql dba    19846 Sep 13  2016 sql_errlog.so
-rwxr-xr-x 1 mysql dba   218688 Sep 13  2016 wsrep_info.so

All libraries have execution rights, they are all here BUT MYSQL is still trying to search in the default path /usr/local/mysql/lib/plugin/. And of course on our machine, this path does not exist.

QUESTION:

Is there another option/way to specifically ask mysql to load plugin for a specific directory?

NOTES:

I found some links about the error ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory but solutions are all:

• Uninstall mysql and install it again (which I can’t)
• Create the directory or symlink (I can’t neither)
• Use the --enable-cleartext-plugin option (it does nothing and I don’t think it is related in my case)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

So I think I understood why.

Setting LIBMYSQL_PLUGIN_DIR:

export LIBMYSQL_PLUGIN_DIR="/opt/mysql/10.1.16/lib/plugin"

is somehow not read or taken in account.

However, adding directly to the binary the option --plugin-dir="${LIBMYSQL_PLUGIN_DIR}" will make it work.

So correct answer is:

/opt/mysql/10.1.16/bin/mysql --plugin-dir="${LIBMYSQL_PLUGIN_DIR}" -C --no-beep --line-numbers --protocol=TCP --host="${DB_HOSTNAME}" --port="${DB_PORT}" --user="${DB_USER}" --database "${DB_SCHEMA}" -o --delimiter ','

Why the env varibale is not read, that is a mystery

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0