0

I know this was discussed and over discussed and it might be a simple thing for some of you, but being a n00b and trying to fix it by myself drives me crazy. I got two VPS's running Ubuntu server 20.04 LTS.

VPS #1 runs MariaDB server 10.3.34. There is NO firewall running (not iptables, not ufw, nothing);

/etc/mysql/mariadb.conf.d/50-server.cnf looks like this:

    
#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking

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

Doing netstat -ant | grep 3306 in terminal outputs:

netstat -ant | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:3306          127.0.0.1:46154         TIME_WAIT
tcp        0      0 127.0.0.1:3306          127.0.0.1:59448         TIME_WAIT
tcp        0      0 127.0.0.1:3306          127.0.0.1:46174         TIME_WAIT
tcp        0      0 127.0.0.1:3306          127.0.0.1:46184         TIME_WAIT
tcp        0      0 127.0.0.1:3306          127.0.0.1:59432         TIME_WAIT
tcp        0      0 127.0.0.1:3306          127.0.0.1:59442         TIME_WAIT
tcp        0      0 127.0.0.1:46162         127.0.0.1:3306          TIME_WAIT

I did add an user to MariaDB, grant all privileges with remote access and everything seems ok!

MariaDB [(none)]> CREATE DATABASE xxxdb;
MariaDB [(none)]> CREATE USER  'xxxuser'@'%' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT ALL ON *.* to 'xxxuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

VPS #2 runs Ubuntu server 20.04 LTS and also I got mariadb-client installed and telnet.

Trying: mariadb -u xxxuser -h SERVERIP -P 3306 -p

    mariadb -u xxxuser -h SERVERIP -P 3306 -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'SERVERIP' (110 "Connection timed out")

Trying to telnet outputs:

telnet SERVERIP 3306
Trying SERVERIP...
telnet: Unable to connect to remote host: Connection timed out

Please give me any help / support that you can. Anything will be appreciated! Thank you!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
DooMFeaR
  • 11
  • 7
  • You did: `CREATE USER 'xxxuser'@'localhost'`, but did you also do: `CREATE USER 'xxxuser'@'%'` ? (Or you could use the ip-address of server2, and not use `%`) – Luuk Nov 27 '22 at 17:13
  • Tried both... same thing! – DooMFeaR Nov 27 '22 at 17:20
  • Can you ping your server? (using: `ping SERVERIP`) ? If not, then check the ip address of the server using `ip addr`, executed on the server). – Luuk Nov 27 '22 at 17:55
  • `ping SERVERIP PING SERVERIP (SERVERIP) 56(84) bytes of data. 64 bytes from SERVERIP (SERVERIP): icmp_seq=1 ttl=63 time=0.854 ms 64 bytes from SERVERIP (SERVERIP): icmp_seq=2 ttl=63 time=0.752 ms ` – DooMFeaR Nov 27 '22 at 17:58
  • It must be a firewall, if it's not on VPS#1, then it's still running on VPS#2. – Luuk Nov 27 '22 at 18:23
  • It's a clean install of Ubuntu on both VPS's. None got a firewall installed! – DooMFeaR Nov 27 '22 at 18:31
  • If it's a fresh install, then: "Why Mariadb 10.3.34 ?" (It's the oldest still supported version, see: https://mariadb.com/kb/en/mariadb-server-release-dates/ ) – Luuk Nov 27 '22 at 18:35
  • This is what Ubuntu installed under "sudo apt install mariadb-server". I didn't even noticed why! – DooMFeaR Nov 27 '22 at 18:50
  • The firewall could be by the VPS provider. `tcptraceroute SERVERIP 3306` ([traceroute](https://packages.ubuntu.com/focal/traceroute) package). – danblack Nov 27 '22 at 22:20
  • `FLUSH PRIVILEGES` is NOT required to make [CREATE USER](https://mariadb.com/kb/en/create-user/) or [GRANT](https://mariadb.com/kb/en/grant/) take effect. – danblack Nov 27 '22 at 22:25
  • `traceroute to SERVERIP (SERVERIP), 64 hops max 1 23.XX.XXX.XX 11.131ms 12.758ms 1.250ms 2 * * * 3 * * * 4 * * * 5 * * * 6 * * * 7 * * * 8 * * * 9 * * * 10 * * * 11 * * * 12 * * * ` – DooMFeaR Nov 28 '22 at 09:40

1 Answers1

0

If not able to login with root user

Execute the following steps:

  1. Stop mariadb services

    1. Run mysqld safe version by below command: sudo mysqld_safe --skip-grant-tables &

    2. Run mysql client with below command on new terminal: mysql -u root

    3. Set password in user table of mysql database:

      use mysql;

      update user SET PASSWORD=PASSWORD("<new_password>") WHERE USER='root';

      flush privileges;

      and exit

    4. Login with new password, set in above command:

      mysql -u root -p Enter password: <new_password>

Ankit Dabhi
  • 48
  • 9
  • Doesn't make any difference! – DooMFeaR Nov 27 '22 at 17:44
  • [Stop changing passwords like this](https://stackoverflow.com/questions/64841185/error-1356-hy000-view-mysql-user-references-invalid-tables-or-columns-o/64841540#64841540), its very broken, and wasn't the user's problem anyway. – danblack Nov 27 '22 at 22:14