MySQL remote access

Discussion in 'Systems Administration' started by DanHraje770, May 30, 2017.

  1. Hello,

    I have problem with MySQL remote access. I have mysql server on VPS. I'm tried many tutorials and none worked. Please help me with this.

    Thanks, have a nice day :)
     
  2. I have phpmyadmin.
     
  3. mysql listens on the local address only not for external connections, if you want to remote you need to make sure that your server is listening on an external ip address and that you have the proper firewall in place so that the wrong people cant access it
     
    • Like Like x 1
  4. I have port for mysql open. How to set remote access?
     
  5. yes but the mysql server has to be listening on the external ip, port doesnt do jack shit, check your mysql server bind address in the config and set it to the ip you wish to use, then restart it

    MAKE SURE YOU FIREWALL SO THAT ONLY THE RIGHT PEOPLE CAN CONNECT OTHERWISE YOU COULD GET INTO TROUBLE
     
  6. Thanks for information. I have correct mysql config and not working.
    Code (Text):

    #bind-address           = 127.0.0.1
    #skip-networking
     
  7. electronicboy

    IRC Staff

    you need to uncomment the bind address and actually put an address in there, or 0.0.0.0 so that it binds on all interfaces
     
    • Agree Agree x 1
  8. How is that correct?

    Bind-address is commented out to begin with, it's not setting any IP address now. By default it binds to localhost.
    Uncomment it to 0.0.0.0 to make it public, do make sure you password protect the root user (or disallow public root access)
     
    • Agree Agree x 1
  9. I'm change to bind-adress 0.0.0.0 and restart mysql - service mysql restart MySQL still not working. Can you help me please via TeamViewer?
     
  10. electronicboy

    IRC Staff

    define "still not working", what exactly are you seeing when you try to connect? did you set it properly? e.g. uncommented and changed 127... to 0.0.0.0? Do you have iptables configured, if so, have you allowed mysql through it? have you double checked these rules?
     
  11. I think that i iptables configurated. How to allow iptables for mysql?

    Actually config file:

    Code (Text):

    bind-address            = 0.0.0.0
    #skip-networking
     
  12. sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

    Assuming you run it on the default port.
     
    • Like Like x 1
  13. @MrDienns @electronicboy With mariadb on Ubuntu when you comment the bind-address line it will listen to 0.0.0.0 but it might be different with a different distrubution and variant of MySQL/MariaDB
     
  14. electronicboy

    IRC Staff

    it will listen to whatever is set in the config.
    Ubuntu might have updated their config at some point so it points to 0.0.0.0, but in the past and last I checked it was 127... Ubuntu isn't exactly known for having config files that are actually solid and don't change over versions for stupid reasons.
     
    • Agree Agree x 1
  15. I've installed MariaDB AND MySQL like a few weeks ago, both were on 127.0.0.1 by default :p
     
  16. Are you trying to remote connect to it with minecraft..
    or are you trying to remote manage the database yourself from your own computer?
    If its' the latter, perhaps don't turn on remote management for mysql and instead use something like this https://www.digitalocean.com/commun...-a-mysql-server-remotely-with-mysql-workbench
    if it is the former, then limit the remote connection to the minecraft server's IP, so others can't try to brute force their way in since their IP doesn't match.
     
  17. Anyway, that said, let's assume you just want to open up your mysql server to the world and allow a remote connection, despite everybody recommending against it.

    You have to not use the loopback, so instead, bind it to the ip of the server. Or use 0.0.0.0 to not specify an ip

    Don't forget to restart the mysql's my.cnf and then type:
    lsof -i -P | grep :3306
    to check if it is running.
    It should say something like
    mysqld 1234 mysql 10u IPv4 5203 0t0 TCP the.binded.ip.here:3306 (LISTEN)

    yay, then:
    Then connect to the mysql locally and add a new user with the create user command .. add one for [email protected] and [email protected]%
    and of course grant them all on *.* for [email protected] and [email protected]%
    if you dunno how, it's something like this: obviously replace the db's username you added above
    GRANT ALL ON *.* TO 'myuser'@'localhost';
    GRANT ALL ON *.* TO 'myuser'@'%';

    Then finally flush the privileges to make it all work:

    THEN try the remote connection again.
    if it works, limit it down by perhaps changing the mysql default port to prevent at least the auto-script port scans that try to exploit remotely. and secondly, limit it to the hosts that are allowed to connect.
     
  18. Hello, it don't working.

    1. My MySQL part of config:
    bind-address = 0.0.0.0
    #skip-networking

    2. I restart MySQL with: service apache2 restart
    3. I check MySQL with: lsof -i -P | grep :3306
    mysqld 30688 mysql 10u IPv4 322462 0t0 TCP *:3306 (LISTEN)

    4. I login to MySQL with: mysql -u root -p
    5. I wrote: GRANT ALL ON *.* TO 'root'@'localhost';
    6. I wrote: GRANT ALL ON *.* TO 'root'@'localhost';
    7. I wrote: flush privileges;
    8. I exit MySQL with: exit

    And don't working this. Please help me.
     
  19. 2. I restart MySQL with: service apache2 restart

    You are restarting apache, but that's not the mysql database service.

    You are using a numbered list, which implies you're doing the apache restart first, then edit mysql ..

    You edit mysql configuration
    restart mysql
    and sure, restart apache too.