Delphi7+Zeos7.1.4+libmysql55.dll迁移PostgreSQL至MySQL遇权限错误

Hey there! Let's work through that "Access denied" error you're facing while trying to migrate data from a remote PostgreSQL database to your local MySQL using Zeos. That error SQL Error: Access denied for user 'xxxx'@'localhost' (using password: YES) tells us MySQL is rejecting your connection even though you're providing a password—let's break down the most likely fixes:

1. Double-check MySQL User Permissions #

First, make sure your MySQL user xxxx is actually allowed to connect from localhost. Fire up your MySQL console (using a root or admin-level account) and run this query:

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

• If you don't see an entry where host is localhost or % (which allows connections from any host), you'll need to create or update the user's permissions:

  -- Create the user if it doesn't exist (for local connections)
  CREATE USER 'xxxx'@'localhost' IDENTIFIED BY 'your_actual_password';
  -- Grant full access to your target database (adjust the db name as needed)
  GRANT ALL PRIVILEGES ON your_target_mysql_db.* TO 'xxxx'@'localhost';
  -- Refresh permissions to apply changes immediately
  FLUSH PRIVILEGES;
  

• Heads up: Some MySQL versions treat localhost as a socket connection, while 127.0.0.1 uses TCP/IP. If the above doesn't work, repeat the steps for 'xxxx'@'127.0.0.1' too.

2. Tweak Your Zeos Connection Settings #

Let's make sure your ZConPropro component is configured correctly:

• Verify the Protocol property is set to mysql or mysql-5.5 (matching your libmysql55.dll).
• Try switching your HostName from localhost to 127.0.0.1—this forces Zeos to use a TCP/IP connection instead of a local socket, which can bypass permission issues related to socket paths.
• Rule out issues with your input fields: Temporarily hardcode your password in the code (just for testing!) to make sure the input box isn't truncating or escaping special characters:

  ZConPropro.Password := 'your_raw_password_here';
  

3. Verify MySQL Server Status & Config #

• Confirm your local MySQL service is running:

  # On Windows
  net start mysql
  # On Linux/macOS
  systemctl status mysql
  

• Check your MySQL config file (my.ini on Windows, my.cnf on Linux/macOS):
  • Make sure skip-networking is not enabled (this blocks TCP connections).
  • Ensure bind-address is set to 127.0.0.1 (to allow local TCP connections) or 0.0.0.0 (if you need remote access too).

4. Check libmysql55.dll Compatibility #

If your local MySQL server is version 8.0 or newer, the older libmysql55.dll (built for MySQL 5.5) might not support the modern caching_sha2_password authentication plugin. To fix this:

• Either switch your user to use the older mysql_native_password auth method:

  ALTER USER 'xxxx'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
  FLUSH PRIVILEGES;
  

• Or upgrade your Zeos components and corresponding libmysql.dll to a version that supports MySQL 8.0.

Start with the permission checks first—they're the most common culprit for this error. Let me know if any of these steps get you past the access denied message!

内容的提问来源于stack exchange，提问作者Wellington Telles Cunha