Setting up a remote connection to MySQL

    The material of the article is intended for beginners; Habr’s professionals for the most part do not need to present this issue. However, those who take the first steps in working with MySQL often ask similar questions. This article is written for those who first encountered the need to establish a remote connection to a MySQL database. The article describes the difficulties that may arise when setting up a remote connection and how to overcome them.

    How to establish a connection with a remote database?



    To establish a remote connection, you must specify the parameters that characterize the established connection. it
    • --host
    • --protocol
    • --port


    Of the four possible protocols, only TCP / IP allows a remote connection, so the first requirement is that the computer be accessible from the network via TCP / IP.
    Next, you need to add the host name (or ip address of the machine on which the database is located) in the connection string:
    mysql --host = host_name
    Note that specifying the host name as localhost (or the absence of such a parameter, which is the same, so as this default value) leads to the appeal to the local machine.


    Note that the parameters have two forms of writing: long and short. In turn, if the parameter matters (as, for example, in the case of a host, you must specify the host name to which you want to connect), then the short form can be used with or without a space (the exception to this rule is the password).
    Thus, the following three entries are equivalent:
    • mysql --host = myhost.ru
    • mysql -h myhost.ru
    • mysql -hmyhost.ru


    How to configure a connection with a remote database?


    In MySQL, a user is characterized by two parameters: a name and a host from which he can access. By default, access is allowed only from the local machine, i.e. for user user @ localhost. Access rights to users are given using the GRANT command . The command is executed under the root.


    For example, if I want to create a user who can connect from any host with full rights, then I should run the following command:
    GRANT ALL PRIVILEGES ON `database_name`. * TO myuser @% IDENTIFIED BY 'password';

    Note. Note that this command gives the user myuser access from all IP except 127.0.0.1 corresponding to localhost.
    For the user myuser @ localhost, you must give rights as a separate GRANT command.


    If you decide to refer to the local machine as remote using the TCP / IP protocol, then do not forget that myuser @ localhost and myuser @ ip_ own_comp are different users and each of them needs to be given a separate command.


    The second example shows how to give the right to read the time_zone table in the mysql database to the user myuser from machine 192.168.0.76
    with the password mypassy:
    GRANT SELECT ON mysql.time_zone TO myuser@192.168.0.76 IDENTIFIED BY 'mypassy';

    Emerging difficulties


    If after all the steps you get an error
    (Can't connect to Mysql Server on 'your IP' (10061)),
    then the connection is blocking the firewall (windows firewall, outpost, antivirus or something else).


    If an error occurs
    (Client does not support autentification protocol request by server; consider upgrading MySQL client), the client (i.e. your program) does not support the protocol required by the server.
    This error can be fixed by setting the old format password:
    SET PASSWORD FOR user @ host = OLD_PASSWORD ('password');

    Krosspost Setting up a remote connection to MySQL with Webew.

    Also popular now: