使用mysqldump格式化每一行一个插入? [英] Using mysqldump to format one insert per line?

问题描述

已经问过几次了，但是我找不到解决问题的方法.基本上，当使用mysqldump(这是MySQL Workbench管理工具的内置工具)时，当我使用扩展插入来转储数据库时，会得到大量的长数据行.我理解为什么要这样做，因为它通过将数据作为一个命令插入(特别是在InnoDB上)来加快插入速度，但是格式设置使得真正很难真正查看转储文件中的数据或使用差异工具比较两个文件如果您将它们存储在版本控制等中.就我而言，我将它们存储在版本控制中，因为我们使用转储文件来跟踪我们的集成测试数据库.

This has been asked a few times but I cannot find a resolution to my problem. Basically when using mysqldump, which is the built in tool for the MySQL Workbench administration tool, when I dump a database using extended inserts, I get massive long lines of data. I understand why it does this, as it speeds inserts by inserting the data as one command (especially on InnoDB), but the formatting makes it REALLY difficult to actually look at the data in a dump file, or compare two files with a diff tool if you are storing them in version control etc. In my case I am storing them in version control as we use the dump files to keep track of our integration test database.

现在，我知道我可以关闭扩展插入，因此每行将获得一个插入，这行得通，但是任何时候使用转储文件进行还原时，速度都会变慢.

Now I know I can turn off extended inserts, so I will get one insert per line, which works, but any time you do a restore with the dump file it will be slower.

我的核心问题是在我转储文件时使用的OLD工具(MySQL Administrator)中，它的作用基本相同，但是它的格式是INSERT语句每行插入一个插入，同时仍在进行大容量插入.所以代替这个:

My core problem is that in the OLD tool we used to use (MySQL Administrator) when I dump a file, it does basically the same thing but it FORMATS that INSERT statement to put one insert per line, while still doing bulk inserts. So instead of this:

INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES (887,'0.0000'),191607,'1.0300');

您得到了:

INSERT INTO `coupon_gv_customer` (`customer_id`,`amount`) VALUES 
 (887,'0.0000'),
 (191607,'1.0300');

无论我尝试哪种选择，似乎都无法获得这样的转储，这确实是两全其美.是的，它需要更多的空间，但是在需要人工阅读文件的情况下，它会更加有用.

No matter what options I try, there does not seem to be any way of being able to get a dump like this, which is really the best of both worlds. Yes, it take a little more space, but in situations where you need a human to read the files, it makes it MUCH more useful.

我是否缺少某些东西，并且可以通过MySQLDump做到这一点，还是我们都倒退了，而旧的(现已过时)的MySQL Administrator工具中的此功能不再可用?

Am I missing something and there is a way to do this with MySQLDump, or have we all gone backwards and this feature in the old (now deprecated) MySQL Administrator tool is no longer available?

推荐答案

使用默认的mysqldump格式，每个转储的记录将在转储文件(即sql文件)中生成一个单独的INSERT命令，每个命令都在其自己的行上.这非常适合源代码控制(例如svn，git等)，因为它可以使diff和delta分辨率更精细，并最终导致更有效的源代码控制过程.但是，对于尺寸很大的表，执行所有这些INSERT查询可能会导致从sql文件还原的速度过慢.

With the default mysqldump format, each record dumped will generate an individual INSERT command in the dump file (i.e., the sql file), each on its own line. This is perfect for source control (e.g., svn, git, etc.) as it makes the diff and delta resolution much finer, and ultimately results in a more efficient source control process. However, for significantly sized tables, executing all those INSERT queries can potentially make restoration from the sql file prohibitively slow.

使用--extended-insert选项通过将所有记录包装到转储的sql文件的一行上的单个INSERT命令中，从而解决了多个INSERT问题.但是，源代码控制过程效率很低.整个表的内容在sql文件的一行中表示，并且如果单个字符在该表中的任何地方更改，则源代码管理会将整个行(即整个表)标记为版本之间的差异.而且，对于大型表，这抵消了使用正式的源代码控制系统的许多好处.

Using the --extended-insert option fixes the multiple INSERT problem by wrapping all the records into a single INSERT command on a single line in the dumped sql file. However, the source control process becomes very inefficient. The entire table contents is represented on a single line in the sql file, and if a single character changes anywhere in that table, source control will flag the entire line (i.e., the entire table) as the delta between versions. And, for large tables, this negates many of the benefits of using a formal source control system.

因此，理想情况下，为了有效地恢复数据库，在sql文件中，我们希望每个表都由单个INSERT表示.为了进行有效的源代码控制，在sql文件中，我们希望该INSERT命令中的每个记录都驻留在自己的行中.

So ideally, for efficient database restoration, in the sql file, we want each table to be represented by a single INSERT. For an efficient source control process, in the sql file, we want each record in that INSERT command to reside on its own line.

我的解决方案是以下备份脚本:

My solution to this is the following back-up script:

#!/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 ($VALUES\n($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

结果是一个sql文件INSERT命令格式，如下所示:

The result is a sql file INSERT command format that looks like:

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文件格式与您选择的源代码控件结合使用时，您会发现，当用户更新其工作副本时，他们只需要在Internet上移动增量(即，更改的记录)即可，并且他们可以利用diff实用程序轻松查看数据库中的哪些记录已更改.
• 如果您要转储驻留在远程服务器上的数据库，请尽可能在该服务器上运行此脚本，以避免每次转储在网络上推送数据库的全部内容.
• 如果可能，请在运行此脚本的同一台服务器上为您的sql文件建立一个有效的源代码控制存储库；从那里将它们检入存储库.这也将有助于避免每次转储时都必须通过网络推送整个数据库.

这篇关于使用mysqldump格式化每一行一个插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！