1. Home
  2. Knowledge Base
  3. Pre-migration Preparation
  4. How to enable remote access to MySQL Database server

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 or ifconfig 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:
    1. Allow incoming connections on the machine with MySQL server using request:
      iptables -A INPUT -i eth0 -p tcp --dport 3306 -j ACCEPT
    2. Set up the ports redirection for your NAT (please check the documentation of your NAT)
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’;
  • 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’;

Looking for more data migration details?

Let’s talk now
Was this article helpful?
%s
— %s