Mysql – Using thesqldump on RDS to export a large file in TSV format

mysqldump

I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica.

However, RDS doesn't provide a way to use mysqldump with the –tab=path option, so we need to parse/convert the INSERT statements to TAB seperated files.

I would like to convert lines from the dump file like this:

INSERT INTO MyTAble VALUES ('data_1','data_2', 'date_3');

to this
'data_1' 'data_2' 'data_3'

Since I need to parse through a 30G file, I would like to find the most efficient way of doing this?

Thanks for any help.

Best Answer

Assuming RDS with MySQL 5.6 is being used, mydumper may be a better option. The newer versions have the RDS grants limitation into account and you will get all the advantages of mydumper over mysqldump (compression, parallel execution, overall faster speed, etc).

This wouldn't provide TSV data, but by using the --chunk-filesize option you can split the data into multiple files in parallel and then process them in parallel afterwards

option:: --chunk-filesize -F Split tables into chunks of this output file size. This value is in MB

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

Mysql – Why is thesqldump file so large

Something tells me you are either using a version of MySQL prior to 5.0, you called an older version of mysqldump, or you messed with the settings of the dump.

What usually blows up a mysqldump past the size of its dataset is the option --skip-extended-insert.

In older versions of MySQL, there was no extended insert. That means each and every row in a table had an INSERT command to itself. If a table had 2,000 rows, the mysqldump output will have 2,000 INSERT commands. That's a whole lot of commas, parentheses, single quotes, and "INSERT INTO" tags to place in a dump file.

In newer versions of MySQL, --extended-insert was added to group together dozens (or even hundreds) of rows in a single INSERT. SO, instead of...

INSERT INTO tbname VALUES (1);
INSERT INTO tbname VALUES (2);
INSERT INTO tbname VALUES (3);
INSERT INTO tbname VALUES (4);
INSERT INTO tbname VALUES (5);

You could have this:

INSERT INTO tbname VALUES (1),(2),(3),(4),(5);

For mysqldump, --opt includes --extended-insert. If you use --skip-opt when doing mysqldump, it disables --skip-extended-insert. Here are the options of mysqldump that affect extended insert:

  -e, --extended-insert
                      Use multiple-row INSERT syntax that include several
                      VALUES lists.
                      (Defaults to on; use --skip-extended-insert to disable.)
      --opt               Same as --add-drop-table, --add-locks, --create-options,
                          --quick, --extended-insert, --lock-tables, --set-charset,
                          and --disable-keys. Enabled by default, disable with
                          --skip-opt.

CAVEAT

Run these at the Linux command line

mysql --version
mysqldump --version
which mysqldump

If these do not match the version of mysql you are running on the server, or multiple versions of MySQL exist on the same machine, get that straightened out. Until then, make sure you call the correct version of mysqldump and don't use --skip-opt.

Best Answer

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Mysql – Why is thesqldump file so large

Related Question