Enabling remote connections to a MySQL server involves a few steps:
1. Configure MySQL Server
By default, MySQL server is configured to listen to the loopback IP address 127.0.0.1, which means it only accepts connections from the local machine. To allow remote connections, you need to modify the MySQL configuration file (my.cnf or my.ini or 50-server.cnf), typically located in /etc/mysql/ or /usr/local/mysql/etc/ or /etc/mysql/mariadb.conf.d.
Look for the following line in the configuration file:
bind-address = 127.0.0.1
And change it to:
bind-address = 0.0.0.0
This allows the MySQL server to listen on all available network interfaces.
2. Grant remote access to MySQL user
By default, MySQL server creates a user root with full administrative privileges, but it only allows access from the local machine. To enable remote access for this user, you need to grant it permission to connect from a remote IP address.
Log in to the MySQL server as root user and execute the following command:
GRANT ALL ON . TO 'root'@'%' IDENTIFIED BY 'yourpassword';
This grants the user root access to all databases and tables from any IP address (%). Replace ‘yourpassword‘ with a secure password of your choice.
3. Restart MySQL server
After modifying the configuration file and granting remote access to the user, you need to restart the MySQL server to apply the changes.
On Ubuntu or Debian, use the following command:
sudo service mysql restart
On CentOS or Fedora, use:
sudo systemctl restart mysqld
4. Open MySQL port on firewall
If you have a firewall running on the MySQL server, you need to open the port that MySQL server is listening on (usually port 3306) to allow incoming connections.
On Ubuntu or Debian, use the following command to open the port:
sudo ufw allow 3306/tcp
On CentOS or Fedora, use:
sudo firewall-cmd --add-port=3306/tcp --permanent
sudo firewall-cmd --reload
Once you have completed these steps, you should be able to connect to the MySQL server remotely using the root user and the password you set in step 2.
However, note that allowing remote access to the MySQL server can pose a security risk if not properly secured.
Therefore, it is recommended to only allow connections from trusted IP addresses and to use a secure password.