This MySQL client error occurs when DNS SRV record lookup fails during connection attempts using the mysql_real_connect_dns_srv() function or --dns-srv-name option. The error indicates the DNS server either cannot find the SRV record, the SRV record is misconfigured, or DNS resolution is failing. This commonly affects MySQL 8.0+ clients using DNS SRV for failover and load balancing scenarios.
The CR_DNS_SRV_LOOKUP_FAILED (2070) error occurs when MySQL attempts to resolve a DNS SRV record and the lookup fails. DNS SRV records enable MySQL clients to specify a single DNS name that resolves to multiple MySQL servers, providing automatic failover and load balancing capabilities. Key aspects of this error: 1. **DNS SRV Protocol**: DNS SRV records allow a DNS administrator to map a single DNS domain to multiple servers, enabling clients to connect to any available server in the cluster. The format is `_service._proto.name.` (e.g., `_mysql._tcp.example.com.`). 2. **Connection Methods**: This error occurs when using mysql_real_connect_dns_srv() in the C API or the --dns-srv-name option in the mysql command-line client. Standard hostname/IP connections do not use DNS SRV records. 3. **Lookup Failure Causes**: The lookup can fail due to the DNS SRV record not existing, DNS server being unreachable, incorrect SRV record format, or network DNS resolution issues. 4. **MySQL 8.0+ Feature**: DNS SRV record support was introduced in MySQL 8.0 for both classic protocol and X Protocol connections. Older MySQL versions do not support this feature. 5. **Failover and Load Balancing**: When a DNS SRV lookup succeeds, MySQL automatically tries each server listed in the SRV record in priority/weight order, enabling cluster connections without hardcoding individual server addresses. This error is specific to advanced deployment scenarios using clustered or replicated MySQL servers.
Check that the DNS SRV record is correctly configured using dig or nslookup:
# 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 configurationSRV record format requirements:
- Service prefix: _mysql (for classic protocol) or _mysqlx (for X Protocol)
- Protocol prefix: _tcp (must be present)
- Domain: your registered domain name
- Priority: determines preferred servers (lower = higher priority)
- Weight: determines load distribution among same-priority servers
- Port: 3306 for classic protocol, 33060 for X Protocol
- Target: fully qualified hostname of MySQL server
Test DNS resolution on the client system to ensure it can reach the DNS server:
# 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:2If SRV record queries timeout or receive SERVFAIL, DNS resolution is failing.
Ensure the correct SRV record name format is used:
# 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 exactlyCommon mistakes:
- Forgetting the underscore prefixes (_mysql._tcp)
- Including port number in SRV name
- Using service name without underscore
- Specifying individual hostnames instead of SRV domain
DNS SRV support requires MySQL 8.0 or later:
# 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()If using MySQL 5.7 or earlier, upgrade to MySQL 8.0+ for DNS SRV support, or use direct hostname/IP addresses.
Ensure the client can reach DNS servers on port 53:
# 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 DNSIf DNS port 53 is blocked, queries will fail. Verify network and firewall rules.
Configure the SRV record in your DNS provider (Route53, Cloudflare, GoDaddy, etc.):
# 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 20Using AWS Route 53:
aws route53 change-resource-record-sets \
--hosted-zone-id Z1234567890ABC \
--change-batch file://change-batch.jsonchange-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"
}
]
}
}
]
}Using Cloudflare:
1. Go to DNS settings
2. Add new record: Type = SRV
3. Name: _mysql._tcp
4. Service: _mysql
5. Protocol: _tcp
6. Priority: 10
7. Weight: 60
8. Port: 3306
9. Target: mysql-server1.example.com
Repeat for additional servers. Allow 5-15 minutes for DNS propagation.
After DNS records are created, test the connection:
# 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
)Verify the connection succeeds and you see which server was connected to.
Debug DNS SRV connection issues with verbose output:
# 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()Debug output will show which servers are found via DNS SRV and which is being connected to.
DNS SRV record configuration for MySQL involves several advanced considerations:
1. Priority and Weight Algorithm: SRV records use priority (lower = preferred) and weight (distribution percentage among same priority) to determine connection order. MySQL follows RFC 2782 for server selection, attempting all servers with lowest priority before moving to next priority tier.
2. TTL and DNS Caching: The TTL (Time To Live) on SRV records controls caching duration. Lower TTL (60-300 seconds) enables faster updates when servers change, but increases DNS query load. Longer TTL (3600+) improves performance but delays propagation.
3. Kubernetes Service Discovery: In Kubernetes, MySQL clusters using StatefulSets automatically create DNS SRV records. Connection string would be _mysql._tcp.mysql-service.default.svc.cluster.local (format: _service._protocol.service-name.namespace.svc.cluster.local).
4. Docker Compose Networks: Docker DNS doesn't support SRV records natively. Service discovery uses different mechanisms - either custom DNS servers or explicit host/port configuration.
5. Multi-Region Deployments: When using DNS SRV across multiple regions/datacenters, DNS servers may return region-specific servers based on geolocation. Implement proper health checks to ensure returned servers are actually available.
6. Connection Pooling Behavior: Connection pooling interacts with DNS SRV - pools may cache connections to specific servers. When DNS SRV records change, existing pool connections remain but new connections honor updated SRV records.
7. SecondLevel Delegation and CNAME Records: DNS SRV records cannot be created for CNAME targets. SRV records must point to A/AAAA records directly. If using CNAMEs, resolve them first before creating SRV records.
8. IPv4 vs IPv6: SRV records can target both IPv4 (A records) and IPv6 (AAAA records). Ensure MySQL clients support both and your network infrastructure can route both address families.
9. DNSSEC Validation: Signed DNS zones (DNSSEC) may cause SRV lookup failures if validation fails. Some clients don't handle DNSSEC-signed responses correctly. Test with and without DNSSEC if issues occur.
10. Fallback Strategies: When DNS SRV lookup fails, applications should fall back to direct hostname/IP connection. MySQL connectors vary in fallback behavior - check your driver documentation.
EE_WRITE (3): Error writing file
How to fix "EE_WRITE (3): Error writing file" in MySQL
CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters
How to fix "CR_PARAMS_NOT_BOUND (2031): No data supplied for parameters" in MySQL
ERROR 1146: Table 'database.table' doesn't exist
How to fix "ERROR 1146: Table doesn't exist" in MySQL
ERROR 1040: Too many connections
How to fix "ERROR 1040: Too many connections" in MySQL
EE_READ (2): Error reading file
How to fix "EE_READ (2): Error reading file" in MySQL