• If you are still using CentOS 7.9, it's time to convert to Alma 8 with the free centos2alma tool by Plesk or Plesk Migrator. Please let us know your experiences or concerns in this thread:
    CentOS2Alma discussion

Resolved Plesk as Database Management with only IP, can not connect

Ed3000

New Pleskian
Hello, I set up a Droplet on DO with Plesk and will be using it only for the Database, nothing else installed. I think it was configured correctly as I ran some test but when I try to access it from the site ( and pull up the data, posts etc ), it does not connect, I get the connect error, here is my tests

Note on DO, I set up the droplet and only has IP address assigned to it, no Domain name or DNS settings since it direct network connection.

* From Server A ( where my wordpress files are ) I exported full DB from Plesk into the DB server, so server A was able to import DB into server B ( the new one ) and DB is there
* From SSH Console A, I can mysql -user -h ipaddress -p and login to DB server, so user is allowed access
* On Server A, I change the wpconfig with new DB password and IP address
* I have Server A, mariaDB config set to listen to its own IP instead of local ( 127.0 ), Server B I have the bind address as 0.0.0.0 to ensure it connects
* I disabled firewall on both Servers to ensure its not blocked

I get connection errors all the time. Only thing I can think of is if Server A which is SSL is not connecting to Server B because its not SSL, will be trying that out . Are there any other settings I need, maybe on Plesk Server A, like external connecting settings or something?

Thank You
 
hello @Ed3000 ,

lets check that I understand you correct:
you have serverB were wp installed and there was wp's db on this server initially hosted.
you've created droplet serverA with plesk that will only host database, nothing more.
on serverA you created database wpdb_rem with user wpdbu_rem and import initial content of WP DB from serverB to this database at serverA.

and you have verified that you are able to connect to your new db using
Code:
mysql -hserverA -u wpdbu_rem wpdb_rem
executing command above on serverB via ssh.

-------------------------

in Plesk at serverA you have to create domain (no need to make it resolvable, but this is technical realization of DB's management in Plesk)
in this domain you should create your DB called wpdb_rem and allow access for wpdbu_rem from any host to it (or allow remote connections from exact host.
in this case you should be sure from which IP connection initialized. you may check it using linux command 'who' when you logged to the serverA from serverB via ssh, or by inspecting mysql logs at serverA for authorization errors)

I get connection errors all the time
what exact errors you get ?


for the first look you did everything correct. The single wrong place that I can suggest is IP address of serverB that was defined for wpdbu_rem user at serverA
 
Hello, thank you for reply, yes to everything you mentioned,

* mysql -hserverA -u wpdbu_rem wpdb_rem / yes, I can ssh from Digital Ocean console from one server to the other, tested both ways and establish connection
* On Plesk, I used the IP address as Domain, ipaddress. com, Plesk set up all records mx, cname etc altho it does not resolve cause Im not actually using a Domain
* IP from user @ server B, when I connect, I tell server B to use Server A user and password, Server A is "Allow remote connections from any host" which is why I can export DB from Server B into A directly connected between both Plesk.

There might be a few other issues like maybe SSL is not set up and Server B is SSL, I get this error on the Log :AH01909: 159.223.104.55.com:443:0 server certificate does NOT include an ID which matches the server name" so it might be that.

It might be also that when Server B try's to connect to Server a DB, Apache might be blocking it, so it might not be an actual DB connection issue but an Apache. I will continue to try and post results here.

Thank You
 
Ok, so I did the WP debug and the log shows this issue: PHP Warning: mysqli_real_connect():, so I will try to see if the clients are missing on the server..
 
I get this error on the Log :AH01909: 159.223.104.55.com:443:0 server certificate does NOT include an ID which matches the server name" so it might be that.
Not a problem at all. You can disable the warning and ignore it, e.g. by modifying /etc/httpd/conf/httpd.conf to read "LogLevel warn ssl:error" instead of "LogLevel warn". This suppresses the useless server certificate warning.
 
There seems to be an issue with the Plesk Server A where the original site is, Plesk installs Mariadb and Server B the DB server has Mysql 8.0, after research, the issue is the MariaDB in Plesk does not communicate with the remote Server B Mysql 8.0 due to authentication plugin

PHP Warning: mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in

This is from my Server A where wordpress ( Mariadb ) is trying to reach out to the remote DB which is Mysql.

I tried adding again the Mariadb-client, but says fully installed.

Any idea how I can fix this in Plesk? I will keep researching
 
Okay, so after retesting a few more time, I know Serer B the DB server works, SSL set up etc, so seems the issue is the mysqli_real_connect() from Server A which host the files and uses Mariadb connecting with external Server B which has MysqlDB...When I change the login form on Mysql Server, so from Native to caching_256 etc, then the Maria DB says it can not load a different plugin..

So seems I have to select either MariaDB or MYSQL and make both the same so it can function correctly..

Any ideas would be appreciated

Thanks
 
@Ed3000 ,
looks like you've created user in MySQL8 with modern auth plugin that is not supported by plesk.
most probably WP also does not work with it: support this MySQL auth was added in php 7.4 or later (not sure exactly)

try to create it with legacy auth plugin, like:
SQL:
CREATE USER 'YOUR_USER_NAME_FOR_REMOTE_DB'@'%' IDENTIFIED WITH mysql_native_password BY 'YOUR_USER_PASSWORD_FOR_REMOTE_DB';
GRANT ALL PRIVILEGES ON *.* TO 'YOUR_USER_NAME_FOR_REMOTE_DB'@'%' WITH GRANT OPTION ;
FLUSH PRIVILEGES ;
 
Thanks, tried it but did not work, I get new errors:

From Server Console A ( Web Server ) To B ( Db Server ) ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

Server A ( web Server ) WP debug log / (): (HY000/1045): Access denied for user

I added the user you mentioned on Server B the DB since the connection is coming in from Server A to allow the user, but nothing

I also added the user to Server A ( Web Server ) incase its has incoming connect and denying, but same error, I put DB server IP on that one since its that server coming in.

Im going to uninstall Mysql and replace it with Maria DB on the DB server to see if that takes care of the issue, seems to be the only way to resolve it. I will post the results.

Thank You
 
@Ed3000
as I understood you have enabled requirement for connection to MySQL DB with ssl certificate but did not define the certificate in connection section. so it should looks like below:
PHP:
'
mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', 'xxx.xxx.xxx.xxx'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'xxxxxx'),
    'username' => env('DB_USERNAME', 'xxxxxx'),
    'password' => env('DB_PASSWORD', 'xxxxxx'),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'strict' => false,
    'engine' => null,
    'sslmode' => env('DB_SSLMODE', 'prefer'),
    'options' => array(
        PDO::MYSQL_ATTR_SSL_KEY => '/var/lib/mysql/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT => '/var/lib/mysql/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA => '/var/lib/mysql/ca.pem',
    ),   
],
 
Hi Nik, Thank you, yes, I had ran the rsa commands for certificate, but now since I removed Mysql and installed MariaDB, I ran into an issue with Phpmyadmin file locations / SSL etc. The Digital Ocean 1 click install configures everything so I have to reconfigure things again. I have been trying to figure this out for about 2 weeks now, I am going to try hard again one last time, here is what I am going to do with a fresh install, maybe you can see if I will be missing a step which is the issue..

* Server A, leave my current files and DB
* Digital Ocean, create a new droplet with Plesk 1 click Install which will be Server B ( DB )
* Server B, in Plesk, set my IP address as domain since there needs to be something registered
* Digital Ocean, set IP address NS records only to Digital Ocean ns1 / ns2, NO MX records, Cname records etc since it will be direct IP connect, all tho Plesk does set these records
* Server B, with Plesk create a Local DB, MariaDB so it matches Server A
* Add new user on Server B via Plesk to DB / add same user again in Phpmyadmin but with host as Server A IP address
* Server B, Plesk, select " Allow remote access" and add the IP of Server A
* Server B, change Bind-Address on Maria DB to 0.0.0.0
* With SSH Console, login from Server A to Server B to ensure user is allowed.
* Server A Plesk to Server B Plesk, export DB from A to B
* Server B, Lets Encrypt SSL the IP ( This part might be issue, not sure if I can SSL ip address only without DNS settings )
* Server B, via SSH, open port 3306 to specific ip of Server A
* Check with Netstat to ensure server B is listening to 3306
* Change Server A WP Config file to Server B

I will come back in a day or so and post results but pretty much these should be the setting for it to work..
 
While comparing a few things, I also noticed that Server B ( DB) is running on Apache and Server A is set up with Nginx, so when I import Server A DB files, its set up for Nginx and phpmyadmin can not find it, that might be an issue as well.

Im going to completely replicate the Droplet into a new one, so I will have the whole website on a different server and just remove Wordpress and leave Mariadb running, that way all is configured.

Will post results ..
 
OMGGGGG...I got it!!!!!! Finally !!!!!!!!! After 2 weeks last year and 2 weeks this year..Im going to write what I think the issue was in case there is anyone else with this issue, Im sure someone else might encounter this.

Firstly, the main issue. If you already have a working configured site with Plesk, most of the online tutorials will not work, you can grab the basic concept of some minor changes needed, but to set it up as they explain will not work. Most of those tutorials don't assume you already have a Plesk platform.

Website with Plesk is already configured with other components which are already all inside your files, permission, server info, etc. So adding a separate server will cause problem, example, the new DB server installed will probably have a different PHP version, apache mods etc which will be very difficult to configure

Files and software on your current server must match the new DB server. If you are just installing a DB server, importing the DB alone and changing settings will not work. Example, Plesk uses Mariadb, new Mysql version does not seem communicate good, Plesk is current on php 7.3, new Db server package installation will probably have 7.4 and will cause connection issues. Additional issues is phpmyadmin on Webserver has that sites settings, when you export, if you also export phpmyadmin DB table, those settings will not match. SSL will also be an issue and much more needs to be configured so both servers can communicate.

How to solve it, it was actually quite easy, it took me 30 min, I should have done this this way instead of spending 2 weeks, but thats part of learning:

* If you have a VPS and your platform allows it, take a snapshot of your current fully functioning website.
* Create a new server with that snapshot so that now you have 2 of the same site, one on your domain and the other on the ip
* Login with plesk to the new DB Server / you will see a full replication for your site
* Add your Website , the original one, ip's address to the Whitelist
* Google how to open UFW ports and login via SSH on the new DB server, open port 3306 to specific ip of original server where your file will be, open 443 /80 as well
* Restart UFW
* Change bind address on the Server B to 0.0.0.0
* Still in your plesk DB server, go to databases and click the icon on the bottom left to login to phpmyadmin as admin
* Add new newer, I recommend same user name as local now but just with an ip as a host
* Still in Plesk DB panel, go to database and you will see the same user, but that one is set up as Local. Click allow access from specific ip.
* Restart your system

Add new IP to your original site in the WP config file, it should work, if it does not, check that ports are correctly open and DB server mysql is listing to 3306 with netsats, you can install if you don't have it.

Your Plesk DB under "Websites & Domain" will show your old IP address, I don't think that is an issue since thats to server the Files and your are not doing that from this server, so should be fine to leave that way.

I really hope this helps someone.

PS: You can test this as well, I now created another snapshot of the DB server just in case and will be deleting the wordpress files to save space, if it causes issues, just rebuild the server with the snapshot.


Thank You to those who replied as this helped me research more and figure things out. I appreciate it
 
Back
Top