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

You still have the client for mysql version 5.1.72. You may need to install it from a third party repository. Some of the official repositories can be slow updating to new versions. — datasage
Hi, I did try updating the client from a third party repository such that it was at version 5.6 however I got the same error message. I then reverted back to 5.1.72 thinking perhaps the client was too far ahead in version compared to the server. — Huw
You do need a new version of mysqldump, and unless a release has a bug, the rule is that the client should be newer than the server, not older... but that isn't what's breaking this. You used -h with mysqldump but --host= with mysql. Does switching the way you supplied the option change the behavior? Also, check your my.cnf file for anything in the [mysqldump] section. — Michael - sqlbot
OK I upgraded the client back up to 5.6.14 and also checked the /etc/my.cnf file however no sign of any [mysqldump] section. I also checked in the other usual places for a my.cnf file however found no others. I tried the mysqldump command using the '--host' argument instead of just '-h' however still no joy. — Huw
It makes no sense at all that mysql works and mysqldump doesn't, although if you aren't using official Oracle binaries from the same version for both, all bets for sanity are off, because some 3rd party builds have strange "helpful" things compiled-in and active by default (like looking in strange places for config files and sockets). What if you add --protocol=TCP to your mysqldump invocation? Or, what if you get the IP address by pinging your RDS instance's name and use --host=ip.add.re.ss? Anything strange/interesting in the [client] or [mysql] sections of your my.cnf? — Michael - sqlbot

slayedbylucifer slayedbylucifer · Accepted Answer · 2013-12-13T10:45:14

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

Amazon RDS mysqldump Issues

1 Answers