Diferència entre revisions de la pàgina «Connexió remota a mysql (OVHCloud)»

De wikijoan
Salta a la navegació Salta a la cerca
(Es crea la pàgina amb « {{Autor}}, febrer 2022».)
 
 
(Hi ha 8 revisions intermèdies del mateix usuari que no es mostren)
Línia 1: Línia 1:
 +
=Professor: connexió a OVHCloud=
 +
OVHCloud és una empresa amb la que podem contractar un VPS (Virtual Private Server). Aquesta wiki i l'aplicació ''bdquest'', entre d'altres aplicacions, estan allotjades en aquest VPS.
 +
==creació de l'usuari de MySQL==
 +
El professor es connecta a la màquina al núvol, on hi ha un servidor mysql:
 +
<pre>
 +
$ ssh ubuntu@vps-f5d83567.vps.ovh.net
 +
</pre>
 +
El professor entra dins el mysql, i crea l'usuari ''provisional'':
 +
<pre>
 +
$ mysql -u root -p
 +
mysql> create user 'provisional'@'%' identified by 'password';
  
 +
mysql> select User,Host from mysql.user;
 +
+------------------+-----------+
 +
| User            | Host      |
 +
+------------------+-----------+
 +
| bdquest          | %        |
 +
| provisional      | %        |
 +
...
 +
</pre>
 +
Es dóna permisos de ''select'' sobre les tres taules de la base de dades de ''municipis'':
 +
<pre>
 +
GRANT SELECT ON municipis.* TO 'provisional'@'%';
 +
 +
SHOW GRANTS FOR 'provisional'@'%';
 +
+----------------------------------------------------+
 +
| Grants for provisional@%                          |
 +
+----------------------------------------------------+
 +
| GRANT USAGE ON *.* TO `provisional`@`%`            |
 +
| GRANT SELECT ON `municipis`.* TO `provisional`@`%` |
 +
+----------------------------------------------------+
 +
</pre>
 +
==MySQL: habilitar connexions remotes==
 +
El servidor MySQL té fitxers de configuració a ''/etc/mysql''. Concretament:
 +
<pre>
 +
$ cd /etc/mysql/mysql.conf.d
 +
$ sudo joe mysqld.cnf
 +
 +
# Instead of skip-networking the default is now to listen only on
 +
# localhost which is more compatible and is not less secure.
 +
# bind-address = 127.0.0.1
 +
bind-address = 0.0.0.0
 +
</pre>
 +
Per defecte, un servidor MySQL només accepta connexions locals (127.0.0.1), i per acceptar connexions remotes des de qualsevol IP ho canviem per ''0.0.0.0''.
 +
 +
Reiniciem el servidor MySQL:
 +
<pre>
 +
$ sudo /etc/init.d/mysql restart
 +
</pre>
 +
 +
El professor comproca que el servidor està escoltant pel port 3306, que és el port del MySQL:
 +
<pre>
 +
$ sudo lsof -i -P -n | grep LISTEN
 +
...
 +
mysqld    3274835          mysql  32u  IPv6 170198035      0t0  TCP *:33060 (LISTEN)
 +
mysqld    3274835          mysql  56u  IPv4 170198037      0t0  TCP *:3306 (LISTEN)
 +
...
 +
</pre>
 +
o bé:
 +
<pre>
 +
$ sudo netstat -tulpn | grep LISTEN
 +
tcp        0      0 0.0.0.0:3306            0.0.0.0:*              LISTEN      3274835/mysqld     
 +
...
 +
tcp6      0      0 :::33060                :::*                    LISTEN      3274835/mysqld     
 +
...
 +
</pre>
 +
 +
=Alumne: connexió remota=
 +
==Des de dins del docker==
 +
Entrem a dins del docker:
 +
<pre>
 +
$ docker exec -it mysql_m02bd /bin/bash
 +
bash-4.4#
 +
</pre>
 +
Primer comprovem que podem accedir al servidor pel port 3306. Una manera de fer-ho és amb un telnet:
 +
<pre>
 +
# echo X | telnet -e X vps-f5d83567.vps.ovh.net 3306
 +
Telnet escape character is 'X'.
 +
Trying 51.210.179.7...
 +
Connected to vps-f5d83567.vps.ovh.net.
 +
Escape character is 'X'.
 +
 +
telnet> Connection closed.
 +
</pre>
 +
Ha respost amb ''connected'', vol dir que s'ha fet la connexió.
 +
 +
Només tenim permisos per connectar-nos a la base de dades ''municipis'':
 +
<pre>
 +
# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword langtrainer
 +
mysql: [Warning] Using a password on the command line interface can be insecure.
 +
ERROR 1044 (42000): Access denied for user 'provisional'@'%' to database 'langtrainer'
 +
 +
# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword municipis
 +
 +
mysql> show databases;
 +
+--------------------+
 +
| Database          |
 +
+--------------------+
 +
| information_schema |
 +
| municipis          |
 +
+--------------------+
 +
</pre>
 +
 +
Només tenim permisos per fer selects. Per exemple:
 +
<pre>
 +
mysql> select * from comunitats;
 +
+--------+----------------------+---------+
 +
| id_com | comunitat            | abr_com |
 +
+--------+----------------------+---------+
 +
|      1 | Catalunya            | CAT    |
 +
|      2 | Aragón              | ARG    |
 +
|      3 | Comunidad Valenciana | VAL    |
 +
...
 +
</pre>
 +
No podem fer un insert:
 +
<pre>
 +
mysql> insert into municipis (id_mun,municipi,id_prov) values(9000,'nou municipi',3);
 +
ERROR 1142 (42000): INSERT command denied to user 'provisional'@'79.153.33.102' for table 'municipis'
 +
</pre>
 +
 +
Municipis de Canarias:
 +
<pre>
 +
mysql> select concat(municipi,';',provincia) from municipis m
 +
inner join provincies p using (id_prov)
 +
inner join comunitats c using (id_com)
 +
where comunitat = 'Islas Canarias';
 +
</pre>
 +
Anem a executar aquesta select, no des de la consola del mysql, sinó des de la consola del sistema operatiu. Per fer-ho, utilitzem l'opció '''-e''' (''--execute''), l'opció '''-s''' (''--silent'') i redirigim la sortida a un fitxer:
 +
<pre>
 +
# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword -D municipis -s
 +
-e "select concat(municipi,';',provincia) from municipis m inner join provincies p using (id_prov) inner join comunitats c using (id_com) where comunitat = 'Euskadi'" > municipis_euskadi.txt
 +
</pre>
 +
S'ha generat el fitxer ''municipis_euskadi.txt'':
 +
<pre>
 +
# cat municipis_euskadi.txt
 +
Alegría-Dulantzi;Álava
 +
Amurrio;Álava
 +
Añana;Álava
 +
Aramaio;Álava
 +
Armiñón;Álava
 +
Arraia-Maeztu;Álava
 +
Arrazua-Ubarrundia;Álava
 +
Artziniega;Álava
 +
Asparrena;Álava
 +
Ayala/Aiara;Álava
 +
...
 +
</pre>
 +
Concretament, la sentència que ha d'executar l'alumne i que haurà d'entregar és:
 +
<pre>
 +
# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword -D municipis -s
 +
-e "select concat(municipi,';',provincia) as 'nom_alumne' from municipis m inner join provincies p using (id_prov) inner join comunitats c using (id_com) where comunitat = 'Euskadi' order by rand() limit 10" > municipis_euskadi_nom_alumne.txt
 +
</pre>
 +
 +
==Des del mysql-workbench:==
 +
[[Fitxer:Connexio mysql remota ovhcloud.png | thumbnail]]
 +
Veure la imatge. Si ens ha funcionat des del docker, també ens funcioanrà des d'una eina gràfica com el mysql-workbench.
 +
=Professor. Veure les connexions remotes=
 +
[[Fitxer:Connexions remotes.png | thumbnail]]
 +
Per veure les connexions al servidor:
 +
<pre>
 +
select id,
 +
      user,
 +
      host,
 +
      db,
 +
      command,
 +
      time,
 +
      state,
 +
      info
 +
from information_schema.processlist;
 +
 +
...
 +
| 542 | provisional    | 79.153.33.102:47082 | municipis | Sleep  |    6 |                        | NULL
 +
</pre>
 +
Podem veure tots els processor que han creat els alumnes, és a dir, les connexions que estan obertes.
 +
 +
=Tasques a realitzar=
 +
Aquesta pràctica s'entrega al final de la classe, doncs l'usuari amb què hem treballat (''provisional'') quedarà deshabilitat (''mysql> drop user 'provisional'@'%';'').
 +
 +
Entregaràs un pdf amb dues captures de pantalla:
 +
 +
1. la teva llista de 10 municipis d'Euskadi o de qualsevol altra comunitat (en la capçalera ha de sortir el teu nom)
 +
 +
2. Captura de pantalla de la connexió correcta del mysql-workbench, on hi hagi una evidència de què ho has fet amb la teva màquina.
  
 
{{Autor}}, febrer 2022
 
{{Autor}}, febrer 2022

Revisió de 16:30, 2 març 2022

Professor: connexió a OVHCloud

OVHCloud és una empresa amb la que podem contractar un VPS (Virtual Private Server). Aquesta wiki i l'aplicació bdquest, entre d'altres aplicacions, estan allotjades en aquest VPS.

creació de l'usuari de MySQL

El professor es connecta a la màquina al núvol, on hi ha un servidor mysql: 

$ ssh ubuntu@vps-f5d83567.vps.ovh.net

El professor entra dins el mysql, i crea l'usuari provisional: 

$ mysql -u root -p
mysql> create user 'provisional'@'%' identified by 'password';

mysql> select User,Host from mysql.user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| bdquest          | %         |
| provisional      | %         |
...

Es dóna permisos de select sobre les tres taules de la base de dades de municipis: 

GRANT SELECT ON municipis.* TO 'provisional'@'%';

SHOW GRANTS FOR 'provisional'@'%';
+----------------------------------------------------+
| Grants for provisional@%                           |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `provisional`@`%`            |
| GRANT SELECT ON `municipis`.* TO `provisional`@`%` |
+----------------------------------------------------+

MySQL: habilitar connexions remotes

El servidor MySQL té fitxers de configuració a /etc/mysql. Concretament: 

$ cd /etc/mysql/mysql.conf.d
$ sudo joe mysqld.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
bind-address = 0.0.0.0

Per defecte, un servidor MySQL només accepta connexions locals (127.0.0.1), i per acceptar connexions remotes des de qualsevol IP ho canviem per 0.0.0.0.

Reiniciem el servidor MySQL: 

$ sudo /etc/init.d/mysql restart

El professor comproca que el servidor està escoltant pel port 3306, que és el port del MySQL: 

$ sudo lsof -i -P -n | grep LISTEN
...
mysqld    3274835           mysql   32u  IPv6 170198035      0t0  TCP *:33060 (LISTEN)
mysqld    3274835           mysql   56u  IPv4 170198037      0t0  TCP *:3306 (LISTEN)
...

o bé: 

$ sudo netstat -tulpn | grep LISTEN
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3274835/mysqld      
...
tcp6       0      0 :::33060                :::*                    LISTEN      3274835/mysqld      
...

Alumne: connexió remota

Des de dins del docker

Entrem a dins del docker: 

$ docker exec -it mysql_m02bd /bin/bash
bash-4.4#

Primer comprovem que podem accedir al servidor pel port 3306. Una manera de fer-ho és amb un telnet: 

# echo X | telnet -e X vps-f5d83567.vps.ovh.net 3306
Telnet escape character is 'X'.
Trying 51.210.179.7...
Connected to vps-f5d83567.vps.ovh.net.
Escape character is 'X'.

telnet> Connection closed.

Ha respost amb connected, vol dir que s'ha fet la connexió.

Només tenim permisos per connectar-nos a la base de dades municipis: 

# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword langtrainer
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'provisional'@'%' to database 'langtrainer'

# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword municipis

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| municipis          |
+--------------------+

Només tenim permisos per fer selects. Per exemple: 

mysql> select * from comunitats;
+--------+----------------------+---------+
| id_com | comunitat            | abr_com |
+--------+----------------------+---------+
|      1 | Catalunya            | CAT     |
|      2 | Aragón               | ARG     |
|      3 | Comunidad Valenciana | VAL     |
...

No podem fer un insert: 

mysql> insert into municipis (id_mun,municipi,id_prov) values(9000,'nou municipi',3);
ERROR 1142 (42000): INSERT command denied to user 'provisional'@'79.153.33.102' for table 'municipis'

Municipis de Canarias: 

mysql> select concat(municipi,';',provincia) from municipis m
inner join provincies p using (id_prov)
inner join comunitats c using (id_com)
where comunitat = 'Islas Canarias';

Anem a executar aquesta select, no des de la consola del mysql, sinó des de la consola del sistema operatiu. Per fer-ho, utilitzem l'opció -e (--execute), l'opció -s (--silent) i redirigim la sortida a un fitxer: 

# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword -D municipis -s
-e "select concat(municipi,';',provincia) from municipis m inner join provincies p using (id_prov) inner join comunitats c using (id_com) where comunitat = 'Euskadi'" > municipis_euskadi.txt

S'ha generat el fitxer municipis_euskadi.txt: 

# cat municipis_euskadi.txt 
Alegría-Dulantzi;Álava
Amurrio;Álava
Añana;Álava
Aramaio;Álava
Armiñón;Álava
Arraia-Maeztu;Álava
Arrazua-Ubarrundia;Álava
Artziniega;Álava
Asparrena;Álava
Ayala/Aiara;Álava
...

Concretament, la sentència que ha d'executar l'alumne i que haurà d'entregar és: 

# mysql -h vps-f5d83567.vps.ovh.net -u provisional -ppassword -D municipis -s
-e "select concat(municipi,';',provincia) as 'nom_alumne' from municipis m inner join provincies p using (id_prov) inner join comunitats c using (id_com) where comunitat = 'Euskadi' order by rand() limit 10" > municipis_euskadi_nom_alumne.txt

Des del mysql-workbench:

Connexio mysql remota ovhcloud.png

Veure la imatge. Si ens ha funcionat des del docker, també ens funcioanrà des d'una eina gràfica com el mysql-workbench.

Professor. Veure les connexions remotes

Connexions remotes.png

Per veure les connexions al servidor: 

select id,
       user,
       host,
       db,
       command,
       time,
       state,
       info
from information_schema.processlist;

...
| 542 | provisional     | 79.153.33.102:47082 | municipis | Sleep   |    6 |                        | NULL

Podem veure tots els processor que han creat els alumnes, és a dir, les connexions que estan obertes.

Tasques a realitzar

Aquesta pràctica s'entrega al final de la classe, doncs l'usuari amb què hem treballat (provisional) quedarà deshabilitat (mysql> drop user 'provisional'@'%';).

Entregaràs un pdf amb dues captures de pantalla:

1. la teva llista de 10 municipis d'Euskadi o de qualsevol altra comunitat (en la capçalera ha de sortir el teu nom)

2. Captura de pantalla de la connexió correcta del mysql-workbench, on hi hagi una evidència de què ho has fet amb la teva màquina.

creat per Joan Quintana Compte, febrer 2022

Obtingut de «http://wiki.joanillo.org/index.php?title=Connexió_remota_a_mysql_(OVHCloud)&oldid=298612»