Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to deal with enormous line lengths created by mysqldump

I'm using mysqldump in a cron job to backup a database with over 2 million rows.

It creates a text file which can be used to restore the datalog from the command line.

I thought it would be useful to edit the dump before a restore as a quick way of changing values and table or column names - at least until I learn more and become confident about doing it with ALTER and UPDATE.

Editing large text files does not bother me, but I was surprised to find that in a 250 megabyte dump of my database, there were only about 300 lines. Each line was something like 800k characters long.

Is there another way of generating dumps with more control over line length?

Or should I post-process the dump with tools like sed or Perl?

like image 339
pavium Avatar asked Aug 18 '09 12:08

pavium


People also ask

What permissions are needed for mysqldump?

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.

What is the output of mysqldump?

mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.

How to dump MySQL database?

To dump/export a MySQL database, execute the following command in the Windows command prompt: mysqldump -u username -p dbname > filename. sql . After entering that command you will be prompted for your password.

What is dump file in MySQL?

Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.


1 Answers

By default, mysqldump generates only one INSERT command per table, resulting in one (very long) line of inserted data for each table that got dumped. This is essentially because the "batch" inserts are much faster than if it generated a separate INSERT query for every record in every table.

So, it's not that mysqldump has created arbitrarily long lines, and you can just impose some other cutoff length. The lines are long for a reason.

If it's really important to get the INSERTs broken down onto multiple lines, you can indicate that with:

mysqldump --extended-insert=FALSE --complete-insert=TRUE ... 

Note, however, that restoring tables will take longer in this format.

like image 54
VoteyDisciple Avatar answered Sep 21 '22 18:09

VoteyDisciple