Hello,
I have a Lightsail instance with a LAMP stack and a web app. It works fine when connecting to localhost MariaDB (using mysqli_connect() in PHP).
I have a problem connecting to a dedicated database which is in the same region and zone as the instance. It seems to work for a while then the site breaks with "Cannot connect to MySQL". I am able to connect with a command line: mysql -u -p -h xxx so it isn't an issue with password or endpoint.
I have tried to enable public access just in case but still no luck.
I went to the log and I can see this: "[Warning] [MY-010055] [Server] IP address 'x.x.x.x' could not be resolved: Name or service not known" where x.x.x.x is my instance private ip.
I don't have a DNS zone in Lightsail, I use Cloudflare DNS to resolve to my instance static public IPv4.
I want to move my data layer to a dedicated Lightsail database (as opposed to localhost) because I want to scale my application and have several instances pull from a unique source.
Does anyone have an idea what could be the issue?
And that was me thinking Lightsail was going to be easy. Isn't a change in my.cnf going to alter the MySQL server that is on my instance (ie:localhost)? It is the external Lightsail database that I am having an issue with, there's no my.cnf that I can edit.
I have noticed a difference between localhost and the dedicated instance:
localhost: Server version: 10.6.4-MariaDB Source distribution MariaDB prompt
dedicated instance: Server version: 8.0.27 Source distribution MySQL prompt
Maybe I should restart the mysql service and stop the mariadb service when I am switching to the dedicated db instance.