Ubuntu虚拟机间MySQL远程连接失败问题排查求助

Alright, let's break down the remaining configurations you need to check—you’ve already nailed the basics (user host grant, UFW rules, ping connectivity), so let’s dive into the trickier spots:

• Check MySQL’s bind-address setting
  By default, MySQL is often configured to only listen on 127.0.0.1 (local connections only). To allow remote access, you need to update this to bind to all interfaces or the specific IP of your target VM:

  1. Open the MySQL config file (path may vary by version, common locations are /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/mysql/my.cnf) with sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
  2. Find the line starting with bind-address and change it to bind-address = 0.0.0.0 (to allow all IPs) or the static IP of your second VM (192.168.1.80)
  3. Restart MySQL to apply changes: sudo systemctl restart mysql
  4. Verify it’s listening correctly with sudo netstat -tulpn | grep mysqld—you should see 0.0.0.0:3306 or your target IP in the output.

• Confirm user privileges were properly applied
  Even if you updated the root user’s host to %, you might have forgotten to flush privileges, or the grant statement was incomplete:

  1. Log into MySQL locally on the target VM: mysql -u root -p
  2. Run FLUSH PRIVILEGES; to ensure your grant changes take effect
  3. Verify the user’s host with SELECT user, host FROM mysql.user;—you should see a row for root with host set to %
  4. Double-check your grant statement was correct (e.g., GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_secure_password';—omitting the database/syntax can cause connection failures)

• Check if skip-networking is enabled
  Some MySQL configurations include the skip-networking parameter, which completely disables TCP/IP connections. Open your MySQL config file and look for this line—if it exists, comment it out with a # and restart the MySQL service.

• Validate port connectivity beyond ping
  Ping only confirms ICMP traffic works, not TCP port 3306. Test if the port is actually reachable from your first VM:

  • Use nc -zv 192.168.1.80 3306 (install netcat first if needed with sudo apt install netcat)
  • If it returns Connection to 192.168.1.80 3306 port [tcp/mysql] succeeded!, the port is open; if not, double-check UFW rules and MySQL’s listening config.

• Inspect MySQL’s error logs for clues
  The error log will tell you exactly why connections are failing (e.g., permission denied, binding issues). Access it with sudo tail -f /var/log/mysql/error.log, then attempt your remote connection again. Look for lines like:

  Host '192.168.x.x' is not allowed to connect to this MySQL server
  Can't connect to MySQL server on '192.168.1.80'

• Verify your remote connection command
  It’s easy to miss the -h flag (which specifies the remote host). Make sure you’re using:

  mysql -h 192.168.1.80 -u root -p
  

  If you changed the default MySQL port (3306), add --port=your_port_number to the command.

Start with checking the bind-address and error logs—those are the most common culprits here. Work through each step one by one, and you should track down the issue.

内容的提问来源于stack exchange，提问作者Log