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:
      plaintext
      [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:
      bash
      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:
        bash
        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:
        bash
        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:

      bash
      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:
        bash
        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:
        sql
        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:
        sql
        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:
        sql
        GRANT ALL PRIVILEGES ON *.* TO 'daniel'@'%';
        FLUSH PRIVILEGES;
      • Lastly, if you want to grant all privileges to ‘daniel’ from any IP address, use:
        sql
        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!

By Tony

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.