Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 16 '12 at 17:05

This question came from our site for professional and enthusiast programmers.

    
What version of MySQL server and MySQL client you are using? –  rkosegi May 16 '12 at 3:59
    
You should try using the --hex-blob option for mysqldump –  RolandoMySQLDBA May 16 '12 at 17:46
add comment

4 Answers

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
also: ESCAPED BY 'some_char' –  ypercube May 16 '12 at 17:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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