Using the LEMP Marketplace image, what’s the step by step way of allowing MySQL access from remote connections?
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×Hey friend!
Great question. These steps should do it.
Setting MySQL to allow remote connections:
Question
sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mysql.conf.d/mysqld.cnf
systemctl restart mysql
Creating a remote user:
mysql
create user 'username'@'ipaddress' identified by 'password';
quit
Jarland
Hi Jarland,
That series of commands to create a user don’t quite work…
ERROR 1396 (HY000): Operation CREATE USER failed for ‘username’@'ipaddress’
Apologies, I should have specified. Replace these values in that:
username: Replace with the username you want to log in with
ipaddress: Replace with the IP address of the system you will be connecting from
password: Replace with the password you will use to authenticate
I can’t rightly say what’s wrong then, I followed these steps on the same image. Maybe carefully type it out and make sure not to copy/paste, make sure you’re using a single quote (’) and not a backtick (`).
“Query OK, 0 rows affected (0.00 sec)”
It’s really odd. On a brand new droplet…
I’m pretty sure that’s the right output, user should be created now.
Hmm. I think you’re right — apologies. Odd how 0 rows affected… but it’s there.