I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy.

After running a simple --all-databases dump succeeds, but the import always encounters random (and frequent) errors. These are generally syntax errors in the SQL statements because of incorrectly escaped quotation marks within the dump file.

I've done raw data transfers before by copying the actual data files, however the destination server I seek to import into does not allow me to access the file system.

Are there any other tools that are less buggy than mysqldump for very large exports/imports?

  • What version of MySQL server and MySQL client you are using?
    – rkosegi
    Commented May 16, 2012 at 3:59
  • You should try using the --hex-blob option for mysqldump Commented May 16, 2012 at 17:46

4 Answers 4


I was having the same problem, solved with Percona Xtrabackup. It's free, faster and consistent.


For MySQL, you have these methods (beside mysqldump):

  • Select ... into outfile: support all engines, in can be use for a warm backup, it's slow for backup and recovery.

  • mk-parallel-backup: support all engines, used it for warm, backup and recovery speed is medium.

  • ibbackup: all engines, warm (if you use this method for only INNODB it can be hot), and it's fast for backup and recovery

here is a good image about backups.

enter image description here

  • 1
    that's a great table, where did you get it from?
    – knocte
    Commented Jun 28, 2016 at 2:15

If both the source and target database are MySQL, you could try exporting to .csv format first and then importing that file.

You can create the .csv file with something like:

SELECT * FROM table INTO OUTFILE '/home/tables_csv/table' FIELDS ENCLOSED BY '"' TERMINATED BY ', ';

I realize that is going to be of limited use if you have 100s of tables though.

Another option is to make sure your are on the latest MySQL version on both systems as that may include corrections to the bugs in mysqldump you are seeing. I haven't done 150GB before, but moving databases up to 2GB I haven't encountered the issues you describe - that's using MySQL v5.5. Hope that helps.

  • also: ESCAPED BY 'some_char' Commented May 16, 2012 at 17:33

I would work out a procedure per table and put that into a small bash script. I'd try to export to XML - should cause less of an issue with unescaped quotes (I don't understand why they're there at all though).

Do the migration per-table, and if it works for one table, you can write another small bash script to replicate the invocation of your first script for as many tables as you have. Export the output of the SHOW TABLES; command to get a list of tables. With a little bit of scripting, you can generate a a file with a bunch of executable mysql calls, then just execute that file.

This way, at least the non-problematic tables get migrated, then you can focus on the problematic ones if needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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