Configuring Remote Access for MySQL on Linux

By default, MySQL is set up to accept connections only from localhost (127.0.0.1). If you want to allow connections from external IP addresses, you need to modify MySQL’s configuration and grant appropriate privileges. Will guide you through the process of configuring MySQL to accept remote connections, from editing the configuration file to adding users with the appropriate permissions.

Step 1: Modify MySQL Configuration

1. Edit MySQL Configuration File:

   First, you need to edit the MySQL configuration file mysqld.cnf to change the bind-address setting. This specifies which IP address MySQL will listen to for connections.

   Open the terminal and run the following command to edit the configuration file using nano:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

2. Change bind-address Setting:

   Locate the line containing bind-address and change its value from 127.0.0.1 (localhost) to 0.0.0.0. This will allow MySQL to listen for connections from any IP address.

   bind-address = 0.0.0.0

3. Save and Exit:

   After making the changes, save the configuration file by pressing CTRL + O, press Enter to confirm, and exit nano by pressing CTRL + X.

Step 2: Restart MySQL

To apply the configuration changes, you need to restart the MySQL service. Run the following command:

sudo systemctl restart mysql

Step 3: Configure User Access in MySQL

1. Log in to MySQL:

   Run the following command to access the MySQL prompt:

   mysql -u root -p

   Enter the MySQL root password when prompted.

2. Create a New User and Grant Privileges:

   At the MySQL prompt, create a new user and grant the necessary privileges. Replace PASSWORD with the desired password for the user:

   CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
   FLUSH PRIVILEGES;

   • CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';: Creates a new user root that can connect from any IP address (%).
   • GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';: Grants full privileges to the root user for all databases and tables.
   • FLUSH PRIVILEGES;: Applies the privilege changes immediately.

3. Exit MySQL:

   After adding the user and granting privileges, exit the MySQL prompt with the command:

   EXIT;

Conclusion

By following the steps above, you have configured MySQL to accept connections from external IP addresses and created a new user with appropriate privileges. This allows you to access the MySQL database remotely, which is useful for development or remote administration.

Always remember to maintain database security by using strong passwords and monitoring allowed access to prevent potential security issues.