patternsqlMinor
Exporting/Importing a large MySQL database without mysqldump
Viewed 0 times
withoutexportingmysqldumpdatabasemysqllargeimporting
Problem
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?
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?
Solution
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.
-
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.
Context
StackExchange Database Administrators Q#18001, answer score: 4
Revisions (0)
No revisions yet.