AWS EC2上MySQL/Aurora 1.14本地连接及权限异常咨询

Hey there, let's work through your MySQL/Aurora issues on AWS EC2—this mix of local connection failures, missing root users, and permission grant errors is tricky but totally fixable. Here's how to tackle each part step by step:

1. Fixing Local Connection Failure (When Remote Works) #

Remote connections working but local ones failing almost always boils down to user host restrictions or socket path mismatches:

• First, check what host your localtest user is tied to. Log in remotely as admin and run:

  SELECT user, host FROM mysql.user WHERE user = 'localtest';
  

  If the host value is % (all remote IPs) or a specific external IP, MySQL won't let you connect via localhost (which uses a Unix socket instead of TCP).
• Fix this by creating a localtest user specifically for local connections:

  -- For socket-based local connections
  CREATE USER 'localtest'@'localhost' IDENTIFIED BY 'your-secure-password';
  -- Or for TCP-based local connections (127.0.0.1)
  CREATE USER 'localtest'@'127.0.0.1' IDENTIFIED BY 'your-secure-password';
  

• If you're still having issues, verify the MySQL socket path on your EC2 instance. Run this to find the socket location:

  ps aux | grep mysql | grep socket
  

  Then specify it explicitly when connecting locally:

  mysql -u localtest -p --socket=/path/to/your/mysql.sock
  

2. Handling the Missing Root User in mysql.user #

Aurora often replaces the traditional root user with the admin user as the default superuser—this is expected behavior for managed Aurora instances. If you need a local root user for convenience, you can create one via your remote admin session:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'very-strong-password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Note: Avoid creating a remote root user for security reasons—stick to localhost for local administration.

3. Troubleshooting the Permission Grant Error #

Even without the full error message, here are the most common fixes for failed grant attempts from your remote admin user:

• Double-check your grant syntax: Make sure you're matching the user's host correctly. For example, to grant full permissions to the local localtest user:

  GRANT ALL PRIVILEGES ON *.* TO 'localtest'@'localhost' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
  

  If you're granting to a remote localtest user, replace localhost with % or the specific IP they'll connect from.
• Verify admin's grant permissions: Ensure your admin user has the ability to grant permissions. Run this to check:

  SHOW GRANTS FOR 'admin'@'%';
  

  Look for WITH GRANT OPTION in the output—if it's missing, you may need to reach out to AWS support to adjust your Aurora cluster's superuser permissions (though this is rare for default setups).
• Avoid syntax mistakes: Don't mix up user creation and grant statements. If you're creating a user and granting permissions in one go, include the password:

  GRANT ALL PRIVILEGES ON *.* TO 'localtest'@'localhost' IDENTIFIED BY 'your-password' WITH GRANT OPTION;
  

4. Debugging Local Connections in Depth #

If you're still stuck, run your local connection command with verbose output to get specific error details:

mysql -u localtest -p -v

The verbose logs will tell you exactly if the issue is a bad password, non-existent user, socket mismatch, or permission denial—this is the fastest way to narrow down the problem.

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