Connexió remota a mysql (OVHCloud)

create user 'pepito'@'%' identified by 'password';
drop user 'pepito';

192.168.48.123
create user 'pepito'@'192.168.48.123' identified by 'password';

mysql> select User,Host from mysql.user;
+------------------+----------------+
| User             | Host           |
+------------------+----------------+
| alumne           | %              |
| pepito           | 192.168.48.123 |

/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


GRANT SELECT ON municipis.municipis TO pepito@192.168.48.123;
GRANT all ON municipis.* TO 'pepito'@'192.168.48.123';
flush privileges;

mysql -h 192.168.48.123 -u pepito -ppassword municipis

sudo ufw enable
sudo ufw status
sudo ufw allow 3306/tcp

$ sudo ufw allow 3306/tcp
Regla afegida
Regla afegida (v6)

$ sudo ufw status numbered
Status: active

     A                          Acció       Des de
     -                          -----       ------
[ 1] 3306/tcp                   ALLOW IN    Anywhere                  
[ 2] 3306/tcp (v6)              ALLOW IN    Anywhere (v6) 

sudo ufw disable


$ sudo lsof -i -P -n | grep LISTEN
[sudo] contrasenya per a joan:         
systemd-r   671 systemd-resolve   13u  IPv4  27340      0t0  TCP 127.0.0.53:53 (LISTEN)
apache2    1067            root    4u  IPv6  34737      0t0  TCP *:80 (LISTEN)
...
mysqld    47753           mysql   24u  IPv6 315423      0t0  TCP *:3306 (LISTEN)

o bé:
$ sudo netstat -tulpn | grep LISTEN
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      671/systemd-resolve 
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      6730/cupsd          
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      47753/mysqld        
tcp6       0      0 :::80                   :::*                    LISTEN      1067/apache2        
tcp6       0      0 ::1:631                 :::*                    LISTEN      6730/cupsd          
tcp6       0      0 :::443                  :::*                    LISTEN      1067/apache2        
tcp6       0      0 :::3306                 :::*                    LISTEN      47753/mysqld

If you’re using iptables, you need to add an entry to your firewall rule for Transmission Control Protocol (TCP) port 3306. You can use the name mysql for the port number.

$ sudo ufw allow from 192.168.0.66 to any port 3306
Regles actualitzades
joan@joan-Len1:~$ sudo ufw status
Estat: inactiu
joan@joan-Len1:~$ sudo ufw enable
Firewall is active and enabled on system startup
joan@joan-Len1:~$ sudo ufw status
Status: active

A                          Acció       Des de
-                          -----       ------
3306/tcp                   ALLOW       Anywhere                  
3306                       ALLOW       192.168.0.66              
3306/tcp (v6)              ALLOW       Anywhere (v6)

https://phoenixnap.com/kb/mysql-remote-connection

You can also use the telnet or nc command to connect to port 3306 for testing purpose:
$ echo X | telnet -e X 192.168.48.123 3306
o bé:
$ nc -z -w1 192.168.48.123 3306


/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
====
mysql -h vps-f5d83567.vps.ovh.net -u bdquest -pkeiL2lai municipis

Per fer el bind de totes les ips es fa:
bind-address    = 0.0.0.0

$ 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.

$ nc -z -w1 vps-f5d83567.vps.ovh.net 3306


$ mysql -h vps-f5d83567.vps.ovh.net -u bdquest -pkeiL2lai
mysql> 
funciona, però encara no puc connectar-me a mysql

select User,Host from mysql.user;
CREATE USER 'provisional'@'%' identified by 'password';
GRANT SELECT ON municipis.* TO 'provisional'@'%';
SHOW GRANTS FOR 'provisional'@'%';

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

municipis de Canarias

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';

o bé:

$ 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


$ 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
...

des de dins del docker:

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

# 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

des del mysql-workbench:

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

mysql-workbench

---

creat per Joan Quintana Compte, febrer 2022