无法远程连接到MariaDB服务器

Question

我知道这已经被讨论过很多次了，这对你们中的一些人来说可能是一件简单的事情，但是作为一个新手，试图自己解决这个问题让我发疯。我有两台运行Ubuntu服务器20.04 LTS的VPS。

VPS #1运行MariaDB服务器10.3.34。没有防火墙在运行(没有iptables，没有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.04 LTS，我也安装了mariadb-client和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输出:

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

请给我任何你可以帮助/支持。我很感激!谢谢你！

Answer 1

如果无法使用root用户登录

执行以下步骤:

1. 停止mariadb服务

   2. 使用以下命令运行mysqld安全版本:sudo mysqld_safe --skip-grant-tables&

   3. 在新终端上运行mysql client:mysql -u root

   4. 设置mysql数据库用户表密码:

      use mysql;

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

      flush privileges;

      退出和

   5. 用新密码登录，在上面的命令中设置:

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