I am using MySQL 8.0.33
Following the official documentation, I set up multiple instances of mysql using systemd. My cnf file is below. I am able to get all the instances up and running at the same time, systemctl status shows each of them with its correct sock file per my configuration, but I am unable to connect a client to any but the initial, default instance.
When I first install mysql per the instructions here, I enter the root password.
When I initially start each additional instance, the error.log reports that a root user was created with an empty password.
After spinning up the servers (sudo systemctl start mysql@seeds; sudo systemctl start mysql@manofmystery), I try to connect with the client:
mysql --host=localhost --port=3307(or 3308) -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql --host=127.0.0.1 --port=3307 -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
mysql --host=localhost --port=3307 -u root -p
Enter password: (ENTER for empty password)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql --host=127.0.0.1 --port=3307 -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
If I enter the root password I made for the initial instance, I am able to connect and get the mysql> prompt, but when I type "status", it tells me I am connected with that sock file, NOT the one for port 3307. So I can connect to the initial instance as root, but not to any of the others.
I have tried "skip-grant-tables" in the cnf file but it made no difference.
My /etc/mysql/mysql.conf.d/mysqld.cnf file:
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
mysqlx = 0
[mysqld@seeds]
pid-file = /var/run/mysqld-seeds/mysqld.pid
socket = /var/run/mysqld-seeds/mysqld.sock
port = 3307
datadir = /var/lib/mysql-seeds
log-error = /var/log/mysql/seeds.error.log
mysqlx = 0
[mysqld@manofmystery]
pid-file = /var/run/mysqld-manofmystery/mysqld.pid
socket = /var/run/mysqld-manofmystery/mysqld.sock
port = 3308
datadir = /var/lib/mysql-manofmystery
log-error = /var/log/mysql/manofmystery.error.log
mysqlx = 0
Again, I have verified that all three instances are running, have logs and data dirs. I just cannot connect to the ones on ports 3307 and 3308.
