How to allow remote connection to mysql [Music no 11]

In the previous blog post, I show how to create MySQL database on linux server. I was able to access that MySQL, create db, create users and so on. The music application which is no the server is able to use this db. BUT! Nobody can access that database from outside. I am not able to connect from my localhost to my database on Linux server. How I can handle that?

So my application on server has a pretty straightforad connection string:

Server=localhost; Port=3306; Database=prod; Uid=prod; Pwd=prodpass;

But the very same connection won’t work on my localhost. So what I have to do? First I go /etc/mysql/mysql.conf.d/mysqld.cnf and edit line with:

bind-address = 127.0.0.1

And just commend this out:

#bind-address = 127.0.0.1

Bind – address means who which ip address can access mysql. By commeting this out we made our MySql database avaliable for everyone.

In previose blog post we created users.

CREATE DATABASE prod;
CREATE DATABASE test;
 
GRANT ALL PRIVILEGES ON prod.* TO 'prod'@'localhost' IDENTIFIED BY 'prodpass';
 
GRANT ALL PRIVILEGES ON test.* TO 'test'@'localhost' IDENTIFIED BY 'testpass'; 

Now we need to allow then to work also from every single place in the world :). We allow accessing our db by a specific user from any IP address.

CREATE USER 'prod'@'%' IDENTIFIED BY 'prodpass';
GRANT ALL PRIVILEGES ON *.* TO 'prod'@'%' WITH GRANT OPTION;

CREATE USER 'test'@'%' IDENTIFIED BY 'testpass';
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

At the moment to access database from localhost I can use connecition string

Server=<server_IP_address>; Port=3306; Database=prod; Uid=prod; Pwd=prodpass;

Links:
https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql