When setting up MySQL user permissions with hostname restrictions, many developers encounter unexpected connection failures. The core issue stems from how MySQL determines client hostnames during authentication - a process more nuanced than simple DNS resolution.

MySQL follows this sequence to identify connecting clients:

1. TCP/IP connection: Extracts the client's IP address
2. Reverse DNS lookup: Performs PTR record query for the IP
3. Forward confirmation: Verifies the DNS result with forward lookup
4. Hostname matching: Compares against user@host specifications

The critical detail is that MySQL requires bidirectional DNS resolution - the reverse lookup (PTR) and forward lookup (A/AAAA records) must match.

In your specific AWS environment with appserver-lan.mydomain.com, several factors complicate hostname resolution:

# Typical EC2 hostname resolution chain
ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com (PTR)
→ 10.xxx.xxx.xxx (A record)
→ appserver-lan.mydomain.com (CNAME)

The mismatch occurs because MySQL sees the intermediate EC2 hostname during resolution, not your final CNAME alias.

Option 1: Use IP-based permissions

GRANT ALL ON db_name.* TO 'user_name'@'10.xxx.xxx.xxx' 
IDENTIFIED BY 'some_passwd';

Option 2: Configure skip-name-resolve

Add to my.cnf:

[mysqld]
skip-name-resolve

This makes MySQL use only IP addresses for authentication.

Option 3: Wildcard host patterns

GRANT ALL ON db_name.* TO 'user_name'@'%.mydomain.com'
IDENTIFIED BY 'some_passwd';

To diagnose exactly what hostname MySQL sees:

SELECT host FROM information_schema.processlist 
WHERE user = 'user_name';

Or check the connection attempt log:

sudo tail -f /var/log/mysql/mysql.log

For environments with complex DNS, add entries to /etc/hosts:

10.xxx.xxx.xxx appserver-lan.mydomain.com appserver.mydomain.com

This ensures consistent resolution on the database server.

---

When MySQL authenticates a client connection, it follows a specific sequence to determine the client's hostname:

1. Initial TCP connection establishment
2. Server performs reverse DNS lookup on client's IP
3. Forward DNS verification of the resolved hostname
4. Hostname comparison against mysql.user table

In your case with AWS infrastructure, we frequently see these patterns:

# AWS EC2 instance with multiple DNS names
ec2-xxx-xxx-xxx-xxx.compute-1.amazonaws.com (public)
ip-10-xxx-xxx-xxx.ec2.internal (private)
custom-domain.com (attached alias)

Here's how to diagnose the exact hostname MySQL sees:

# On the MySQL server:
mysql> SHOW PROCESSLIST;
+----+----------+------------------------------+------+---------+------+-------+------------------+
| Id | User     | Host                         | db   | Command | Time | State | Info             |
+----+----------+------------------------------+------+---------+------+-------+------------------+
| 5  | user_name| appserver.mydomain.com       | NULL | Query   | 0    | init  | SHOW PROCESSLIST |
+----+----------+------------------------------+------+---------+------+-------+------------------+

For reliable host-based authentication in AWS:

# Option 1: Use IP-based authentication
CREATE USER 'user_name'@'10.xxx.xxx.xxx' IDENTIFIED BY 'password';

# Option 2: Use wildcard for domain
CREATE USER 'user_name'@'%.mydomain.com' IDENTIFIED BY 'password';

# Option 3: Configure skip-name-resolve (recommended for AWS)
[mysqld]
skip-name-resolve=1

To understand exactly how MySQL sees your client hostname:

# On MySQL server:
mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS client_host;
+-----------------------+
| client_host           |
+-----------------------+
| appserver.mydomain.com|
+-----------------------+

# Compare with system DNS:
$ dig -x 10.xxx.xxx.xxx
$ host appserver.mydomain.com

For stable authentication in cloud environments:

1. Prefer IP-based authentication when possible
2. Use AWS VPC endpoints for internal traffic
3. Maintain consistent DNS naming conventions
4. Monitor authentication failures in MySQL error log