I have server with db(36Gb data), only mysql access. When i'm trying to use mysqldump utility, i get connection timeout(or something like this).

I doesn't have ssh access for copy /var/mysql/data. It's a production server and i want copy data without any loads on server CPU/IOdisk.

Now i have only one solution. Read batch of data (1000-10000 lines) with script, insert in local database, wait xxx ms.

May be exists ready solutions?

  • QUESTIONS : Is the entire database 36GB, or it one table 36GB ? Is the database/table all MyISAM, all InnoDB, or a mixture of both ? Are you dumping one table out of a database, one database, or multiple databases ? – RolandoMySQLDBA Oct 29 '14 at 15:14
  • 1
    "Connection timed out" or "No route to host" when you try to connect usually indicates that you cannot reach the server from the host you're running the mysqldump client on. Sounds like you have no access to the server at all, possibly because of a firewall. You should talk to the network administrator to find out if you can get a VPN connection or access to another client host on the same network. – Bill Karwin Oct 29 '14 at 16:18
  • I have full access to mysql server. I can run queries, modify tables. 36Gb is single table - InnoDB. I've already created dump other small tables of my database. And left this one table by size reason. – Kein Oct 30 '14 at 10:39

You might try mydumper which is multi-threaded whereas mysqldump is single-threaded. Percona have also improved the less locking feature in the most recent version. The excellent Mysql Performance Blog has this useful article.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.