How to enable remote access to MySQL Database server
1. Connect to the machine with MySQL server (usually through SSH)
2. Find the main configuration file of the MySQL server - my.cnf:
- Debian/Ubuntu -
/etc/mysql/my.cnf
- RedHat/CentOS/Fedora -
/etc/my.cnf
- FreeBSD -
/var/db/mysql/my.cnf
3. Open this file (using admin rights) to edit it with any text editor (for example nano or vi/vim).
4. Go to MySQL daemon settings section (starts with [mysqld]
)
5. Comment or delete row with skip-networking.
6. Check and if needed update bind-address (default value - 127.0.0.1)
- Change to the IP address of the main machine interface (you can find out the IP address using
ip addr show
orifconfig
request)
OR
- set
0.0.0.0
- allows connecting from any address and machine interface
7. Save changes and reboot MySQL server:
- Debian/Ubuntu:
systemctl restart mysql
OR/etc/init.d/mysql restart
- RHEL/CentOS/Fedora:
systemctl restart mysqld
OR/etc/init.d/mysqld restart
- FreeBSD:
service mysql-server restart
OR/usr/local/etc/rc.d/mysql-server restart
8. On the firewall (usually iptables
), allow incoming connections via TCP port 3306 (MySQL) from our IP address - 18.198.164.195:
- If the machine is available on the Internet (has a public IP address), please make the following request
iptables -A INPUT -i eth0 -p tcp -s 18.198.164.195 --dport 3306 -j ACCEPT
- If the machine is using NAT:
- Allow incoming connections on the machine with MySQL server using request:
iptables -A INPUT -i eth0 -p tcp --dport 3306 -j ACCEPT
- Set up the ports redirection for your NAT (please check the documentation of your NAT)
- Allow incoming connections on the machine with MySQL server using request:
eth0
is the name of the machine network interface (often: enp2s0
)9. Set up the connection to the necessary database:
- Connect to MySQL server using admin credentials (by default it is -
root
) -mysql -u root -p
- Create a new MySQL user and grant access to the needed database:
- for full rights:
mysql> GRANT ALL PRIVILEGES ON database.* TO ‘user’@’18.198.164.195’ IDENTIFIED BY ‘StrongPassword’;
- for read-only rights
mysql> GRANT SELECT, SHOW VIEW ON database.* TO ‘user’@’18.198.164.195’ IDENTIFIED BY ‘StrongPassword’;
- for full rights:
- database - database name to which you want to grant the access
- user - username
The user will be created automatically by request:
GRANT SELECT, SHOW VIEW ON database.* TO ‘user’@’18.198.164.195’ IDENTIFIED BY ‘StrongPassword’;