port 3306 is closed on my website how can i open it?

Question

I have a vb.net desktop app.. and I need to insert some data to database on my website. when it's try to add data I get this Error: " Unable to connect to any of the specified MySQL hosts "

when I try Other IP shared IP from Other website, it's work. but it's not for my website..

I Checked it on this https://www.yougetsignal.com/tools/open-ports/ it's give me that Port 3306 is closed on "My IP Address"

this is My connection string look like:

Server=Mywebsite IP;Port=3306;Database=databasename;Uid=username;Pwd=password;

can you help me with that.. thanks..

Answer 1

As @pmdba has mentioned, opening your database to the internet is a very bad idea.

Since you haven't mentioned which GNU/Linux distribution you are using and whether you use MySQL or MariaDB, I am generalizing here:

1. Configure your MySQL/MariaDB server to listen on your IP address by setting the following parameter in my.cnf: bind-address=IP_address or bind-address=0.0.0.0 for all IP interfaces on your server.
2. Restart your database server
3. Make sure your firewall is allowing connections to tcp/3306. A command that would work is: iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

Check your distro documentation on how to properly add a rule for tcp/3306 in your firewall. 4. Make sure the database user is allowed to connect remotely. A generic command that would work is: grant all privileges on database.* to 'username'@'%' identified by 'password' for all IPs or grant all privileges on database.* to 'username'@'DesktopIP' identified by 'password' for enabling access only for your desktop app IP address.

Answer 2

/etc/mysql/mysql.conf.d/mysqld.cnf must have localhost bind-address commented out so:

#bind-address           = 127.0.0.1

Create and grant permissions for user root connecting from machine 192.168.0.2 for example:

mysql -u root -p
Enter password: <enter password>
mysql>CREATE USER 'root'@'192.168.0.2' IDENTIFIED BY 'root';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.2';
mysql>FLUSH PRIVILEGES;
mysql>exit