Multiple mariadb instances - how to access to the not default instances via command line?

Question

I have a TYPO3 and a Moodle installation on one server with Debian stretch.

Moodle needs some database configurations (MariaDB 10.1.38) which are different from the ones TYPO3 needs.

I made a new configuration /etc/mysql/conf.d/mymoodle.cnf (with port 3307 and datadir /var/lib/mysql-moodle), installed the default DBs in the new datadir with

mkdir /var/lib/mysql-moodle
chown mysql:mysql /var/lib/mysql-moodle
mysql_install_db --datadir=/var/lib/mysql-moodle

and started the instance with

service mariadb@moodle start

Obviously this works:

# ps aux | grep mysql
mysql    14135  0.1  1.8 678184 74032 ?        Ssl  Jun25   1:20 /usr/sbin/mysqld --defaults-file=/etc/mysql/conf.d/mymoodle.cnf
mysql    14323  4.8  6.1 720344 241200 ?       Ssl  Jun25  55:03 /usr/sbin/mysqld
root     25475  0.0  0.0  12712   880 pts/0    S+   14:06   0:00 grep mysql

# netstat -tulpen | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      107        76817214   14323/mysqld
tcp        0      0 127.0.0.1:3307          0.0.0.0:*               LISTEN      107        76816770   14135/mysqld

That's looks good, I thought, but I didn't find an access via command line. Calling the client with

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307

leads me to the default instance:

SHOW VARIABLES LIKE '%datadir%';

gives me /var/lib/mysql instead of /var/lib/mysql-moodle.

What is the mistake?

Answer 1

As the manual says:

If hostname is not specified or hostname is localhost, then Unix sockets are used. Unused connection parameters (such as port) will be ignored.

To override the default behaviour, use either the --protocol option to use TCP

mysql --defaults-file=/etc/mysql/conf.d/mymoodle.cnf --port 3307 --protocol tcp

or the --socket option to direct it to the second instance's socket (I assume you have specified a different socket in /etc/mysql/conf.d/mymoodle.cnf