Mysql – Exporting/Importing a large MySQL database without thesqldump

exportimportMySQLmysqldump

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?

Best Answer

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

Related Solutions

MySQL and PostgreSQL – Resolving Invalid Byte Sequence for UTF8 Encoding

One or more of those character/text fields MAY have 0x00 for its content.

Try the following:

SELECT * FROM rt3 where some_text_field = 0x00 LIMIT 1;

If this returns any single row then try updating those character/text fields with:

UPDATE rt3 SET some_text_field = '' WHERE some_text_field = 0x00;

Afterwards, try another MYSQLDUMP ... ( and PostgreSQL import method ).

Mysql – Sensible data management tool for importing and exporting of large data files

It sounds like you should check out MySQL's load data infile .... The docs claim that it is a very fast way to load rows. (I personally have never loaded millions of rows at once).

Once the data is loaded, you may need/want to add appropriate indexes to your tables to help speed up query performance.

For getting data out of MySQL, check out select ... into outfile.

Best Answer

Related Solutions

MySQL and PostgreSQL – Resolving Invalid Byte Sequence for UTF8 Encoding

Mysql – Sensible data management tool for importing and exporting of large data files

Related Question