How to fix CR_DNS_SRV_LOOKUP_FAILED (2070): DNS SRV lookup failed in MySQL

# Query for SRV record using dig
dig _mysql._tcp.example.com SRV
dig _mysqlx._tcp.example.com SRV  # For X Protocol (port 33060)

# Alternative: use nslookup
nslookup -type=SRV _mysql._tcp.example.com

# Expected output should show:
# _mysql._tcp.example.com. 86400 IN SRV 0 5 3306 server1.example.com.
# _mysql._tcp.example.com. 86400 IN SRV 0 10 3306 server2.example.com.

# Check with full domain resolution
dig _mysql._tcp.example.com SRV +short
nslookup -type=srv _mysql._tcp.example.com

# Verify the A records for the target hosts resolve correctly
dig server1.example.com
dig server2.example.com

# Check if DNS resolver is available
resolvectl status  # On systemd systems
cat /etc/resolv.conf  # Check nameserver configuration

# Test general DNS connectivity
ping 8.8.8.8  # Google DNS
ping 1.1.1.1   # Cloudflare DNS

# Check which DNS servers are being used
cat /etc/resolv.conf
resolvectl status
nslookup -debug

# Try resolving a known public domain first
dig google.com
dig 8.8.8.8  # Reverse DNS

# Verify the exact SRV name resolves
dig _mysql._tcp.example.com SRV

# Increase verbosity to see what's happening
dig _mysql._tcp.example.com SRV +v
dig _mysql._tcp.example.com SRV +trace  # Shows full resolution path

# Test from specific DNS server
dig @ns1.example.com _mysql._tcp.example.com SRV
dig @8.8.8.8 _mysql._tcp.example.com SRV

# Check DNS timeout settings
cat /etc/resolv.conf  # Look for timeout setting
# Add timeout if too short:
# options timeout:5 attempts:2

# Correct format for DNS SRV connections:
mysql --dns-srv-name=_mysql._tcp.example.com -u username -p

# Correct format with user and password:
mysql --dns-srv-name=_mysql._tcp.example.com \
      --user=root \
      --password=mypassword

# For X Protocol (requires mysqlx instead of mysql):
mysqlsh --uri 'mysqlx+srv://_mysqlx._tcp.example.com'

# IMPORTANT: Do NOT include:
# - Port number: --dns-srv-name=_mysql._tcp.example.com:3306  [WRONG]
# - Multiple hosts: --dns-srv-name=server1.example.com,server2.example.com  [WRONG]
# - Additional host specifications: --host=_mysql._tcp.example.com  [WRONG]

# Correct: Service and protocol prefixes with underscores
Correct:   _mysql._tcp.example.com
Wrong:     mysql.example.com
Wrong:     tcp.example.com
Wrong:     example.com

# Verify no typos in the domain name
# Compare your SRV name with dig output exactly

# Check MySQL server version
mysql -V
mysql --version
mysql -e "SELECT VERSION();"  # From within MySQL

# For MySQL 5.7 and earlier:
# DNS SRV is NOT supported - you must use direct hostname/IP

# Verify client library version supports SRV
# For MySQL 8.0+, check library version matches
mysql --help | grep -i dns

# For compiled applications using C API:
# Ensure you're using MySQL client library 8.0 or newer
ldd $(which mysql) | grep libmysql
pkg-config --modversion mysqlclient

# Check if DNS SRV function is available
# This requires checking C API documentation/source
# Function: mysql_real_connect_dns_srv()

# Test DNS server connectivity
nc -zv 8.8.8.8 53  # Google DNS
nc -zv 1.1.1.1 53  # Cloudflare DNS
telnet 8.8.8.8 53

# Check firewall rules allowing DNS
sudo ufw status | grep 53
sudo iptables -L -n | grep 53

# Verify DNS queries are not being blocked
sudo tcpdump -i any -n port 53 -c 10  # Capture DNS traffic

# Check if DNS service is running on the server
sudo systemctl status bind9    # Ubuntu/Debian
sudo systemctl status named    # CentOS/RHEL
sudo systemctl status unbound  # Alternative DNS service

# For Docker/Kubernetes environments
# Verify DNS service is accessible from container:
docker exec <container_id> nslookup -type=SRV _mysql._tcp.example.com

# Check Kubernetes DNS
kubectl run -it --rm debug --image=busybox --restart=Never -- \
  nslookup -type=SRV _mysql._tcp.example.com

# Verify corporate network/proxy DNS handling
# Some proxies intercept DNS queries
nslookup _mysql._tcp.example.com 8.8.8.8  # Bypass local DNS

# Standard format for MySQL classic protocol (port 3306)
_mysql._tcp.example.com. 300 IN SRV 10 60 3306 mysql-server1.example.com.
_mysql._tcp.example.com. 300 IN SRV 10 40 3306 mysql-server2.example.com.
_mysql._tcp.example.com. 300 IN SRV 20 100 3306 mysql-server3.example.com.

# For X Protocol (port 33060)
_mysqlx._tcp.example.com. 300 IN SRV 10 60 33060 mysql-server1.example.com.
_mysqlx._tcp.example.com. 300 IN SRV 10 40 33060 mysql-server2.example.com.

# Explanation:
# Priority 10: servers 1 & 2 (preferred)
# Weight 60/40: server1 gets 60% load, server2 gets 40%
# Priority 20: server 3 (used if 10s are down)
# Weight 100: server3 gets all load from priority 20

aws route53 change-resource-record-sets \
  --hosted-zone-id Z1234567890ABC \
  --change-batch file://change-batch.json

{
  "Changes": [
    {
      "Action": "CREATE",
      "ResourceRecordSet": {
        "Name": "_mysql._tcp.example.com",
        "Type": "SRV",
        "TTL": 300,
        "ResourceRecords": [
          {
            "Value": "10 60 3306 mysql-server1.example.com"
          },
          {
            "Value": "10 40 3306 mysql-server2.example.com"
          }
        ]
      }
    }
  ]
}

# MySQL command-line with DNS SRV
mysql --dns-srv-name=_mysql._tcp.example.com \
      --user=root \
      --password \
      --protocol=TCP

# Disable connection compression and verify SRV usage
mysql --dns-srv-name=_mysql._tcp.example.com \
      -u root -p \
      --disable-local-infile \
      -v  # Verbose output shows connection details

# Test without specifying individual hosts
# When using DNS SRV, do NOT use:
# --host=
# --port=
# -h

# For X Protocol (MySQL 8.0.27+):
mysqlsh --uri 'mysqlx+srv://root@_mysqlx._tcp.example.com'

# For Java applications using MySQL Connector/J:
# jdbc:mysql+srv://user:password@_mysql._tcp.example.com/dbname

# For Node.js applications:
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
  host: '_mysql._tcp.example.com',
  // Do NOT set port - SRV lookup provides ports
  user: 'root',
  password: 'password',
  database: 'mydb'
});

# For Python:
import mysql.connector
connection = mysql.connector.connect(
    host='_mysql._tcp.example.com',
    user='root',
    password='password',
    database='mydb'
    # Do NOT specify port with DNS SRV
)

# Enable verbose MySQL client output
mysql --dns-srv-name=_mysql._tcp.example.com \
      -u root -p \
      -v -v -v  # Multiple -v for more verbosity

# Check MySQL client library debugging (if compiled with debug)
# Set debug environment variable
export MYSQL_DEBUG=d:t:o,/tmp/mysql.log
mysql --dns-srv-name=_mysql._tcp.example.com -u root -p

# Check DNS resolution with strace
strace -e openat,connect,sendto,recvfrom \
  mysql --dns-srv-name=_mysql._tcp.example.com -u root -p

# Monitor network traffic during connection
sudo tcpdump -i any -n 'port 53 or port 3306' -w dns-srv.pcap
# Analyze with wireshark:
wireshark dns-srv.pcap

# For containerized applications:
docker exec <container> mysql --dns-srv-name=_mysql._tcp.example.com -u root -p

# For Kubernetes:
kubectl exec -it <pod> -- mysql --dns-srv-name=_mysql._tcp.example.com -u root -p

# Check application logs for connection errors
tail -f /var/log/mysql/general.log
tail -f application.log | grep -i dns

# For Node.js applications, add debug logging:
process.env.NODE_DEBUG = 'mysql*';

# For Python:
import logging
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger()