Connexió remota a mysql (OVHCloud)

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:

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

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