Exporting/Importing a large MySQL database without mysqldump

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.