无法远程连接到MariaDB服务器

Question

我知道大家都在讨论这个问题，对你们中的一些人来说，这可能是一件很简单的事情，但作为一名n00b并试图自己解决这个问题让我抓狂。我有两个VPS运行的Ubuntu服务器20.04LTS。

VPS #1运行MariaDB服务器10.3.34。没有防火墙运行(没有iptable，没有ufw，什么都没有)；

/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

在终端输出中执行netstat -ant _ grep 3306：

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

我确实向MariaDB添加了一个用户，授予远程访问的所有权限，一切看起来都很好！

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运行Ubuntu服务器20.04LTS，我还安装了mariadb客户端和telnet。

尝试: 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")

试图对产出进行电信：

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

请给我任何可能的帮助/支持。一切都会被感激的！谢谢!

Answer 1

如果无法与root用户登录

执行下列步骤：

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;

和出口

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

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