используя mysqldump для форматирования одной вставки на строку?

<p>об этом спрашивали несколько раз, но я не могу найти решение своей проблемы. в основном, при использовании mysqldump, который является встроенным инструментом для инструмента администрирования mysql workbench, когда я выгружаю базу данных, используя расширенные вставки, я получаю огромные длинные строки данных. я понимаю, почему он это делает, поскольку он ускоряет вставки, вставляя данные в виде одной команды (особенно в innodb), но форматирование делает действительно затруднительным просмотр данных в файле дампа или сравнение двух файлов с помощью инструмента сравнения. если вы храните их в системе управления версиями и т. д. в моем случае я храню их в системе управления версиями, поскольку мы используем файлы дампа для отслеживания нашей базы данных тестов интеграции.</p><p>теперь я знаю, что могу отключить расширенные вставки, поэтому я получу по одной вставке на строку, что работает, но каждый раз, когда вы выполняете восстановление с использованием файла дампа, это будет медленнее.</p><p>моя основная проблема заключается в том, что в old-инструменте, который мы использовали (mysql administrator), когда я выгружаю файл, он делает в основном то же самое, но форматирует, что оператор insert помещает одну вставку в строку, в то же время выполняя массовые вставки. итак, вместо этого:</p><pre><code>insert into `coupon\_gv\_customer` (`customer\_id`,`amount`) values (887,'0.0000'),191607,'1.0300'); </code></pre><p>вы получаете это:</p><pre><code>insert into `coupon\_gv\_customer` (`customer\_id`,`amount`) values (887,'0.0000'), (191607,'1.0300'); </code></pre><p>независимо от того, какие варианты я пробую, похоже, нет никакого способа получить подобный дамп, который действительно лучший из обоих миров. да, это занимает немного больше места, но в ситуациях, когда вам нужен человек для чтения файлов, это делает его намного более полезным.</p><p>я что-то упустил, и есть способ сделать это с mysqldump, или мы все пошли в обратном направлении, и эта функция в старом (теперь устаревшем) инструменте администратора mysql больше не доступна?</p>

<p>при использовании формата mysqldump по умолчанию каждая сохраненная запись будет генерировать отдельную команду insert в файле дампа (то есть в файле sql), каждая в отдельной строке. это идеально подходит для управления источником (например, svn, git и т.д.), поскольку делает разностное и дельта-разрешение намного более точным, и в конечном итоге приводит к более эффективному процессу управления источником. однако для таблиц значительного размера выполнение всех этих запросов insert может потенциально сделать восстановление из файла sql чрезмерно медленным.</p><p>использование параметра --extended-insert устраняет проблему множественных insert, заключая все записи в одну команду insert в одну строку в выгруженном файле sql. однако процесс управления исходным кодом становится очень неэффективным. все содержимое таблицы представлено одной строкой в файле sql, и, если где-то в этой таблице изменяется один символ, система управления исходным кодом помечает всю строку (т. е. всю таблицу) как дельту между версиями. а для больших таблиц это сводит на нет многие преимущества использования формальной системы контроля версий.</p><p>поэтому в идеале для эффективного восстановления базы данных в файле sql мы хотим, чтобы каждая таблица была представлена одним insert. для эффективного управления исходным кодом в файле sql мы хотим, чтобы каждая запись в этой команде insert находилась в отдельной строке.</p><p>мое решение этого заключается в следующем сценарии резервного копирования:</p><pre><code>#!/bin/bash cd my\_git\_directory/ args="--host=myhostname --user=myusername --password=mypassword --opt --skip-dump-date" /usr/bin/mysqldump $args --database mydatabase | sed 's$values ($valuesn($g' | sed 's$),($),\n($g' > mydatabase.sql git fetch origin master git merge origin/master git add mydatabase.sql git commit -m "daily backup." git push origin master в результате получается формат команды insert для файла sql:insert into mytable values (r1c1value, r1c2value, r1c3value), (r2c1value, r2c2value, r2c3value), (r3c1value, r3c2value, r3c3value); некоторые заметки: пароль в командной строке ... знаю, небезопасно, другое обсуждение. --opt: помимо прочего, включает опцию --extended-insert (т.е. один insert на таблицу). --skip-dump-date: mysqldump обычно помещает отметку даты/времени в файл sql при создании. это может стать раздражающим в системе контроля версий, когда единственная разница между версиями - это отметка даты/времени. ос и система контроля версий будут указывать дату и время файла и версии. это не очень нужно в файле sql. команды git не являются центральными в фундаментальном вопросе (форматирование файла sql), но показывают, как я возвращаю свой файл sql обратно в систему контроля версий, нечто подобное можно сделать с помощью svn. комбинируя этот формат файла sql с выбранным вами источником контроля, вы обнаружите, что когда ваши пользователи обновляют свои рабочие копии, им нужно только перемещать дельты (то есть измененные записи) через интернет, и они могут использовать преимущества утилит diff. чтобы легко увидеть, какие записи в базе данных изменились. если вы создаете дамп базы данных, находящейся на удаленном сервере, по возможности, запустите этот сценарий на этом сервере, чтобы избежать отправки всего содержимого базы данных по сети с каждым дампом. если возможно, создайте рабочий репозиторий контроля версий для ваших файлов sql на том же сервере, на котором вы запускаете этот скрипт; проверьте их в хранилище оттуда. это также поможет избежать необходимости проталкивать всю базу данных по сети с каждым дампом.  

попробуйте использовать следующую опцию: <strong> --skip-extended-insert </strong>это сработало для меня.

как говорили другие, использование sed для замены "), (" небезопасно, так как это может отображаться как содержимое в базе данных . однако есть способ сделать это: если ваша база данных называется my_database, запустите следующий:$ mysqldump -u my\_db\_user -p -h 127.0.0.1 --skip-extended-insert my\_database > my\_database.sql $ sed ':a;n;$!ba;s/)\;\ninsert into `[a-za-z0-9$_] values /),n/g' my_database.sql > my_database2.sql вы также можете использовать «sed -i» для замены in-line.вот что делает этот код: --skip-extended-insert создаст один insert into для каждой вашей строки. теперь мы используем sed для очистки данных. обратите внимание, что обычный поиск/замена на sed применяется для одной строки, поэтому мы не можем обнаружить символ " n", так как sed работает по одной строке за раз. вот почему мы ставим «: a; n; $! ba;» который в основном говорит sed искать много строк и буферизовать следующую строку.</li> </ol><p>надеюсь это поможет</p>

<p>как насчет сохранения дампа в файл csv с помощью mysqldump, используя такую опцию <code>--tab</code>?</p><pre><code>mysqldump --tab=/path/to/serverlocaldir --single-transaction <database> table\_a </code></pre><p>это производит два файла:</p><ul> <li><code>table\_a.sql</code>, который содержит только оператор создания таблицы; а также</li> <li><code>table\_a.txt</code>, который содержит данные, разделенные табуляцией.</li> </ul><h2>восстановление</h2><p>вы можете восстановить свою таблицу с помощью <code>load data</code>:</p><pre><code>load data infile '/path/to/serverlocaldir/table\_a.txt' into table table\_a fields terminated by '\t' ... </code></pre><p>load data обычно в 20 раз быстрее, чем использование операторов insert.</p><p>если вам нужно восстановить данные в другую таблицу (например, для проверки или тестирования), вы можете создать «зеркальную» таблицу:</p><pre><code>create table table\_for\_test like table\_a; </code></pre><p>затем загрузите csv в новую таблицу:</p><pre><code>load data infile '/path/to/serverlocaldir/table\_a.txt' into table table\_for\_test fields terminated by '\t' ... </code></pre><h2>сравнить</h2><p>csv-файл является самым простым для сравнения или для поиска внутри, или для не-технических пользователей sql, которые могут использовать общие инструменты, такие как <code>excel</code>, <code>access</code> или командная строка (<code>diff</code>, <code>comm</code> и т.д.)</p>

<p>боюсь, это будет невозможно. в старом mysql administrator я написал код для выгрузки объектов db, который был полностью независим от инструмента mysqldump и, следовательно, предлагал ряд дополнительных опций (таких как форматирование или обратная связь о ходе выполнения). в mysql workbench было решено использовать инструмент mysqldump, который, помимо того, что является шагом назад в некоторых отношениях и порождает проблемы с версиями, имеет преимущество в том, чтобы всегда быть в курсе событий на сервере.</p><p>таким образом, короткий ответ: форматирование в настоящее время невозможно с mysqldump.</p>

<p>я нашел этот инструмент очень полезным для работы с расширенными вставками: <a href="http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html">http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html</a> </p><p>он анализирует вывод mysqldump и вставляет разрывы строк после каждой записи, но все еще использует более быстрые расширенные вставки. в отличие от сценария sed, не должно быть риска разбить строки в неправильном месте, если регулярное выражение совпадает внутри строки.</p>

<p>мне нравилось решение ace.di с sed, пока я не получил эту ошибку: sed: не удалось перераспределить память</p><p>поэтому мне пришлось написать небольшой php скрипт</p><pre><code>mysqldump -u my\_db\_user -p -h 127.0.0.1 --skip-extended-insert my\_database | php mysqlconcatinserts.php > db.sql </code></pre><p>скрипт php также генерирует новый insert для каждых 10.000 строк, опять же, чтобы избежать проблем с памятью.</p><p>mysqlconcatinserts.php:</p><pre><code>#!/usr/bin/php <?php /* assuming a mysqldump using --skip-extended-insert */ $last = ''; $count = 0; $maxinserts = 10000; while($l = fgets(stdin)){ if ( preg\_match('/^(insert into .* values) (.*);/',$l,$s) ) { if ( $last != $s[1] || $count > $maxinserts ) { if ( $count > $maxinserts ) // limit the inserts echo ";\n"; echo "$s[1] "; $comma = ''; $last = $s[1]; $count = 0; } echo "$comma$s[2]"; $comma = ",\n"; } elseif ( $last != '' ) { $last = ''; echo ";\n"; } $count++; } </code></pre>