Amazon RDS mysqldump Issues

Question

I'm trying to retrieve a database from my Amazon RDS instance using mysqldump e.g.

mysqldump –h myDBname.cra3xhjrgj69.eu-west-1.rds.amazonaws.com -u root –p  --port 3306 --single-transaction --databases  DBname > ~/tt.sql

However I'm getting the error:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

I can connect to the database using the client however:

mysql --host=myDBname.cra3xhjrgj69.eu-west-1.rds.amazonaws.com -u root -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 85250
Server version: 5.1.69-log MySQL Community Server (GPL)

I checked the root privileges as well:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*abcabcabc' WITH GRANT OPTION

These looked OK.

Reading up on this issue I thought it may have been a version mismatch between my EC2 mysqldump and the RDS MySQL server version from this article:

Mysqldump 5.6 version needed on EC2 with MySQL 5.6

So I tried updating the client to version 5.6 however I still got the same error. I've tried various different versions now and have currently reverted to:

mysqldump -V
mysqldump  Ver 10.13 Distrib 5.1.72, for unknown-linux-gnu (x86_64)

But I still get the same error. I've also tried connecting from another machine however the issue persists.

I'd appreciate it if anyone had any suggestions on what to try next. Seems like a workaround, if there was one wouldn't be too bad either right now.

Many thanks

Answer 1

Man, I can't believe I spend 2 hrs to make this work whereas it was an issue of client version.

DB Info: RDS MySQL version: 5.6.13

when I was running mysqldump from my RHEL6.4 Server, I got the below error:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

I checked hte version of mysqldump and it was:

# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.1.71, for redhat-linux-gnu (x86_64)

I tried yo update it with yum but it says that the most recent in available in yum repo.

So I created a Ubuntu 12.04 instance and installed MySQL client on it.

Now the version on my Ubuntu instance is ;

# mysqldump --version
mysqldump  Ver 10.13 Distrib 5.5.34, for debian-linux-gnu (x86_64)

Then I ran my SQL dump and it wokred like a charm:

# mysqldump -hRDS_END_POINT -uUSERNAME -pPASWORD dbname> dbname.sql