Home / Questions / 230368
Accepted
Miguel
Asked: 2020-01-18 02:30:35 +0800 CST 2020-01-18 02:30:35 +0800 CST

Configure external MySql access [closed]

  • 772

I have done mysql installation with sudo apt-get install mysql-server. By ssh yes I can enter but by navicat it won't let me.

Can someone help me with Mysql configuration? because I do everything I think is necessary, but through ssh I do connect but not through navicat (for example).

mysql

1 Answers

  1. Best Answer

    To configure external access to your MySQL server you must follow these steps:

    1. Make the MySQL server listen for external connections (and not just from localhost, 127.0.0.1).
    2. Enable access to the MySQL server in the firewall.
    3. rootCreate the remote user and give it privileges.

    Below I will detail each of the steps.

    Make MySQL server listen for external connections

    You have to edit the file my.cnfto change bind-addressto 0.0.0.0.

    In this particular case the file was located in /etc/mysql/mysql.conf.d/mysqld.cnf.

    Enable access to the MySQL server in the firewall

    Since it was being used as a firewall ufw, you have to enable access to port 3306 used by the MySQL server by typing:

    # ufw allow 3306

    Create the remote user rootand give it privileges

    To create a rootremote user the following SQL queries must be used:

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

    If the user 'root'@'%'already exists, but the password is unknown, it can be changed using:

    ALTER USER 'root'@'%' IDENTIFIED BY 'tu_clave_de_root';
    • 4