MySQL CentOS allow remote connections

Step 1. Changing MySQL Configuration

By default, MySQL is not listening for external connections.

You need to change that by adding an extra option in the configuration file.

Here are the steps:

1. Log in to your server and run this command to determine the location of the MySQL configuration file:
   
   mysql --help | grep "Default options" -A 1
   
2. edit the config file
   
3. Locate the line that contains [mysqld]
   
   add the following code below:
   
   bind-address=YOUR.SERVER.IP
   Note that you have to replace YOUR.SERVER.IP with your actual dedicated IP address.

or add the line:

bind-address = 0.0.0.0

Comment out the line “skip-networking” by placing a # in front

———————
[mysqld]

bind-address = 0.0.0.0

# skip-networking
——————————

The first line defines that this is for the MySql daemon.

The second line tells MySql to bind to a network interface, 0.0.0.0 means all interfaces.

The third line tells MySql to not skip networking configuration routines.

For changes to take effect, restart your MySQL daemon by running this command:

>systemctl restart mysqld

Step 2. Opening The Required Port

1. By default, MySQL is set to use TCP 3306 port for external connections.
   
   Thus, you need to open this port in the firewall by executing the command below:>iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
   Alternatively, you can grant access to just one IP:
   iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT
   
   Don’t forget to change 10.5.1.3 in this example to your real IP address.
   
2. Save the iptables configuration by entering:
   
   service iptables save
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

create an user who can access from the remote machine, if doesn’t exist

replace 10.10.10.10 by the IP number of the MySQL Server machine

> CREATE USER ‘daniel’@’10.10.10.10’ IDENTIFIED BY ‘password’;

then give the privileges

> GRANT ALL PRIVILEGES ON *.* TO ‘daniel’@’10.10.10.10’ WITH GRANT OPTION;

> FLUSH PRIVILEGES;

or

>GRANT ALL PRIVILEGES ON *.* TO daniel@’%’;

> FLUSH PRIVILEGES;

or

>GRANT ALL PRIVILEGES ON . TO root@’%’;

> FLUSH PRIVILEGES;