new MySQL user not working through SSH
We have just upgraded to a VPS plan in order to get Magento 2.4 fully working (it requires elasticsearch, which needed the VPS and which I've already installed; that's unrelated to my problem though).
I've just added a new MySQL database through cPanel, and added a user for it, which is of course listed as associated with the database.
When I go into the Root WHM panel's Terminal and try to connect to MySQL through commandline for that user, it works fine.
Example command (replaced actual username with "test_db_user"):
mysql -u test_db_user -p
It connects with that user account fine.
However, when I connect through SSH as my standard CPanel user and run the same command, giving the same password,I get:
ERROR 1045 (28000): Access denied for user 'test_db_user'@'localhost' (using password: YES)
After checking through various things, I then added privileges for an existing MySQL user to the new database. I again can connect fine through the root terminal to the older username, and running "show databases;" lists the older database that user already had access to along with the new database which I just added privileges for. No problems there.
I then connect again through SSH as my standard CPanel user and try to connect to MySQL while specifying that older MySQL user. This time it connects properly, no error. However, when I run "show databases;", it only lists the old database that MySQL user already had access to; it doesn't list the new database, even though the new database showed up through the same command through the root terminal.
It's like it's seeing an older cached version of the MySQL databases (or maybe just MySQL users) when I try to connect through my standard account through SSH. I've tried restarting the MySQL service and restarting the VPS itself, no change in behavior.
Why would this be happening? How can I fix it? Will it just fix itself given enough time? I need to be able to run the installer for Magento 2.4 through SSH which naturally requires database access, so I need to find a fix. The only workaround I can think of is to co-opt an older existing database and user, just wiping that database, but I'd rather not have to resort to that.
OK, I managed to find a workaround.
First, for testing, I added a new table to the old database that old account was showing to MySQL run from the commandline through SSH on the server. I then connected to that database again through SSH, and found that the new table wasn't showing up. So it wasn't just a user/permission caching issue, it was the entire database.
Second, in WHM panel, under "Additional MySQL Access Hosts", I added each of the IPs listed in the MySQL users table (through phpMyAdmin, the "mysql" database, the "users" table) which seemed to be associated with every user, 3 IPs in total. I don't think this did anything, as the mysql commandline run through SSH was still showing only the old databases for that one user which I added privileges on the new database. But, I'm including this step as there's a slim chance it contributed.
Finally, I tried specifying each of those 3 IP addresses as the host on the command line, ex. "mysql -u test_db_user -p -h 1.2.3.4". One of them didn't respond, the second one connected but still only showed the old database, but the third one I tried worked! It is correctly showing the new database as accessible to that user. SO I'm going to assume that was the necessary step, connecting to that one specific IP as MySQL host instead of just using localhost.
So, that works, I guess.
I'm thinking this problem likely stems from the hosting account previously being a Pro account, and having been upgraded to VPS a few days ago.