Setting up a remote connection to MySQL

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

How to connect to a remote database?

To establish a remote connection, you must specify the parameters characterizing the connection being established. it

• --host
• --protocol
• --port

Of the four possible protocols, a remote connection allows only TCP / IP, so the first requirement is the availability of a computer 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 localhost as the host name (or the absence of such a parameter, which is the same, since this is the default value) results in a call to the local machine.
')

Note that the parameters have two forms of recording: long and short. In turn, if the parameter has a value (for example, in the case of a host, you must specify the host name to which you want to connect), then the short form of the record can be used with or without a space (an 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 set up 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. User access rights are granted 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. Please note that this command gives access to the user myuser from all IP except 127.0.0.1, corresponding to localhost.
For the user myuser @ localhost it is necessary to give rights to a separate command GRANT.


If you decide to access the local machine as remote using TCP / IP, then do not forget that myuser @ localhost and myuser @ ip_private_kompa are different users and each of them must 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 a machine 192.168.0.76
with mypassy password:
GRANT SELECT ON mysql.time_zone TO myuser@192.168.0.76 IDENTIFIED BY 'mypassy';

Emerging difficulties

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


If an error occurs
(Client does not support authentication request server by server; consider upgrading MySQL client), the client (that is, 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');

Crosspost Setting up a remote connection to MySQL with Webew.