如何从远程主机获取制表符分隔的 MySQL 转储?
如下所示的 mysqldump 命令:
mysqldump -u<username> -p<password> -h<remote_db_host> -T<target_directory> <db_name> --fields-terminated-by=,
将为每个表写出两个文件(一个是架构,另一个是 CSV 表数据)。要获取 CSV 输出,您必须指定目标目录(使用 -T)。当 -T 传递给 mysqldump 时,它将数据写入运行 mysqld 的服务器的文件系统 - 而不是发出命令的系统。 有没有一种简单的方法可以从远程系统转储 CSV 文件?
注意:我熟悉使用简单的 mysqldump 和处理 STDOUT 输出,但我不知道如何在不进行大量解析的情况下获取 CSV 表数据。在本例中,我将使用 -X 选项并转储 xml。
A mysqldump command like the following:
mysqldump -u<username> -p<password> -h<remote_db_host> -T<target_directory> <db_name> --fields-terminated-by=,
will write out two files for each table (one is the schema, the other is CSV table data). To get CSV output you must specify a target directory (with -T). When -T is passed to mysqldump, it writes the data to the filesystem of the server where mysqld is running - NOT the system where the command is issued.
Is there an easy way to dump CSV files from a remote system ?
Note: I am familiar with using a simple mysqldump and handling the STDOUT output, but I don't know of a way to get CSV table data that way without doing some substantial parsing. In this case I will use the -X option and dump xml.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我想添加到 codeman 的答案中。它有效,但需要大约 30 分钟的调整才能满足我的需求。
我的网络服务器使用 centos 6/cpanel,codeman 上面使用的标志和序列对我不起作用,我必须重新排列和使用不同的标志等。
另外,我将其用于本地文件转储,它不仅对远程有用DB,因为我在 SELECT INTO OUTFILE 命令的 selinux 和 mysql 用户权限方面遇到了太多问题,等等。
在我的 Centos+Cpanel 服务器上起作用的
注意事项
无列名称
我无法让列名称显示在顶部。我尝试添加标志:
但没有什么区别。我还停留在这一点上。我目前将其添加到处理后的文件中。
选择数据库
由于某种原因,我无法在命令行中包含数据库名称。我尝试在命令行中使用
,但不断收到权限错误,因此我最终使用了以下 query.sql 顶部的内容:
I want to add to codeman's answer. It worked but needed about 30 minutes of tweaking for my needs.
My webserver uses centos 6/cpanel and the flags and sequence which codeman used above did not work for me and I had to rearrange and use different flags, etc.
Also, I used this for a local file dump, its not just useful for remote DBs, because I had too many issues with selinux and mysql user permissions for SELECT INTO OUTFILE commands, etc.
What worked on my Centos+Cpanel Server
Caveats
No Column Names
I cant get column names to appear at the top. I tried adding the flag:
but it made no difference. I am still stuck on this one. I currently add it to the file after processing.
Selecting a Database
For some reason, I couldn't include the database name in the commandline. I tried with
in the commandline but I kept getting permission errors, so I ended using the following the top of my query.sql:
在许多系统上,MySQL 作为不同的用户(例如用户“mysql”)运行,如果 MySQL 用户在转储目录中没有写权限,则 mysqldump 将失败 - 无论您自己的写权限是什么,都没有关系目录。将您的目录(至少暂时)更改为全局可写(777)通常会解决您的导出问题。
On many systems, MySQL runs as a distinct user (such as user "mysql") and your mysqldump will fail if the MySQL user does not have write permissions in the dump directory - it doesn't matter what your own write permissions are in that directory. Changing your directory (at least temporarily) to world-writable (777) will often fix your export problem.