Allow Remote Connections to MySQL (CentOS)

Step 1: Changing MySQL Configuration

Why: MySQL, by default, doesn’t allow remote connections for security reasons. In this step, we’ll walk through configuring MySQL to safely accept remote connections from trusted sources. Here’s how to do it:

1. Find the MySQL Configuration File

   • Why: MySQL has a configuration file where settings are stored. You need to locate this file to make changes to how MySQL handles remote connections.
   • Where: The default location of the MySQL configuration file on CentOS is usually /etc/my.cnf or /etc/mysql/my.cnf.

2. Edit the Configuration File

   • Why: By default, MySQL is configured to only allow connections from the server itself, for security reasons. To enable connections from other computers, you must change this setting.
   • What:
     
     • In the MySQL configuration file, you’ll typically find a section called [mysqld] which contains server-related settings. Here’s a sample [mysqld] section:
     plaintextCopy code

     [mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
bind-address=0.0.0.0
# skip-networking


     In this example, we’ve added the bind-address=0.0.0.0 line to allow connections from all IP addresses, and we’ve commented out skip-networking to enable networking.

3. Restart MySQL

   • Why: After making changes to the MySQL configuration, you need to restart the MySQL service to apply these changes and allow remote connections.
   • How: You can restart MySQL on CentOS using the following command:
     
     bashCopy code

     systemctl restart mysqld


     This command stops and then starts the MySQL service, ensuring that your configuration changes take effect.

Step 2: Opening the Required Port

1. Open Port 3306 in the Firewall

   • Why: The MySQL server communicates through a specific port (3306 by default). By default, CentOS may block incoming connections to this port for security. You need to allow access to this port to enable remote connections.

   • What:

     • To open port 3306 for all IPs, use the following command:
       
       bashCopy code

       iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT


       This command appends a rule to the input chain that allows incoming TCP connections on port 3306.

     • To open port 3306 for a specific IP (replace 10.5.1.3 with your IP), use this command:
       
       bashCopy code

       iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT


       This command allows incoming TCP connections on port 3306 from a specific IP address.

   • Remember: Don’t forget to change 10.5.1.3 in the second command example to your real IP address.

   • Save the iptables configuration: After adding the rule, save the iptables configuration to make the changes permanent:

     bashCopy code

     service iptables save


2. Update Firewall Rules (Alternative Method)

   • Why: This is an alternative method to open port 3306 in the firewall using firewall-cmd.
   • What:
     
     • To open port 3306 for all IPs, use the following commands:
       
       bashCopy code

       firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload


       The first command adds port 3306 for TCP connections to the public zone and makes the rule permanent. The second command reloads the firewall rules to apply the changes.

   • Remember: If you want to allow connections from a specific IP address using firewall-cmd, you can replace --zone=public with --zone=your-custom-zone and use --add-source=YOUR.IP.ADDRESS in the first command.

Step 3: Creating a MySQL User for Remote Access

1. Create a MySQL User

   • Why: To connect to MySQL remotely, you need a user account with appropriate privileges. This step creates a new user.
   • What:
     
     • To create a user named ‘daniel’ that can connect from a specific IP address (replace 10.10.10.10 with your desired IP) with the specified password, use the following command:
       
       sqlCopy code

       CREATE USER 'daniel'@'10.10.10.10' IDENTIFIED BY 'password';


2. Grant Privileges

   • Why: Creating a user alone isn’t enough; you also need to grant that user the necessary privileges to interact with the MySQL server.
   • What:
     
     • To grant full privileges to the ‘daniel’ user when connecting from ‘10.10.10.10’ and allow ‘daniel’ to grant those privileges to other users, use these commands:
       
       sqlCopy code

       GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'10.10.10.10' WITH GRANT OPTION;
FLUSH PRIVILEGES;

     • Alternatively, if you want ‘daniel’ to connect from any IP address, you can use:
       
       sqlCopy code

       GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'%';
FLUSH PRIVILEGES;

     • Lastly, if you want to grant all privileges to ‘daniel’ from any IP address, use:
       
       sqlCopy code

       GRANT ALL PRIVILEGES ON *.* TO daniel@'%';
FLUSH PRIVILEGES;


These steps help you configure MySQL to accept remote connections securely, open the necessary port in the firewall, and set up a user with the right permissions to access your MySQL database. Enjoy secure and remote MySQL management!