MySQL Workbench：无法导出数据库

MySQL Workbench: Cannot export a database

提问by Dimitra Micha

I am encountering a problem concerning the export of a database. First of all, I have to clarify that I am using MySQL Workbench 5.2.47. The procedure that I followed so far is the following:

我遇到了有关数据库导出的问题。首先，我必须澄清我使用的是MySQL Workbench 5.2.47。到目前为止，我遵循的程序如下：

1. I followed the instructions of http://mysqlworkbench.org/2012/07/migrating-from-ms-sql-server-to-mysql-using-workbench-migration-wizard/in order to create a connection with my MSSQL DBin order to transform it into MySQL DB.

2. Then I also checked that my data is imported in the database.

3. Now I want to export it into an sql file / or preferably to frm,myi,myd files in order to place them in my server.

1. 我按照http://mysqlworkbench.org/2012/07/migrating-from-ms-sql-server-to-mysql-using-workbench-migration-wizard/的说明创建与我的连接MSSQL DB，以便将其转换为MySQL DB.

2. 然后我还检查了我的数据是否导入到数据库中。

3. 现在我想将它导出到一个 sql 文件/或者最好是 frm,myi,myd 文件，以便将它们放在我的服务器中。

I have tried to export them from

我试图将它们从

• Server Administration -> Data Export

• Changed already the password from the security (Users and Privileges)

• 服务器管理 -> 数据导出

• 已经更改了安全性（用户和权限）的密码

but I encounter the problem

但我遇到了问题

Dumping test (all tables)
Running: mysqldump.exe --defaults-extra-file="c:\users\d_micha\appdata\local\temp\tmpgtwa_m.cnf"  --user=root --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events --no-data "test"

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

Operation failed with exitcode 2

I don't know what I might be doing wrong and I have searched in google to find the solution, but it should be normally exported.

我不知道我可能做错了什么，我已经在谷歌中搜索了解决方案，但它应该正常导出。

Any other information will be given upon request.

任何其他信息将根据要求提供。

Thank you.

谢谢你。

回答by ewhitmire

I've seen this issue when you don't have the LOCK TABLESpermission. You'll see this error before the rest of the access denied errors in the log. Try disabling LOCK TABLESin advanced settings in the Data Export panel of the workbench.

当您没有LOCK TABLES权限时，我已经看到了这个问题。您将在日志中的其余访问被拒绝错误之前看到此错误。尝试LOCK TABLES在工作台的数据导出面板中的高级设置中禁用。

mysqldump: Got error: 1044: Access denied for user 'XXX'@'%' to database 'XXX' when doing LOCK TABLES

回答by n.r.

Solution 1 - Provide correct config file to each mysqldump-call

解决方案 1 - 为每个 mysqldump 调用提供正确的配置文件

This is more a workaround, but it will get you to the desired result. Just use the provided information to get a dump of your MySQL-Table from the CLI - basically it's just copy & paste:

这更像是一种解决方法，但它会让您获得所需的结果。只需使用提供的信息从 CLI 获取 MySQL 表的转储 - 基本上它只是复制和粘贴：

As you can see from the log mysqldumphas the parameter --defaults-file. This file can and will contain connection credentials, like the password. Apparently MySQLWorkbench is not providing the password with this file ("using password: NO").

从日志中可以看出mysqldump有参数--defaults-file。此文件可以并且将包含连接凭据，例如密码。显然 MySQLWorkbench 没有提供此文件的密码（“使用密码：否”）。

So just create a file named database.cnf and put it somewhere to your computer (e.g. c:\temp\database.cnf) containing the credentials like this:

因此，只需创建一个名为 database.cnf 的文件并将其放在您计算机的某个位置（例如 c:\temp\database.cnf），其中包含如下凭据：

[client]
user=root
password=your-root-password
single-transaction=TRUE
host=localhost
port=3306
default-character-set=utf8
max_allowed_packet=1G

As this also works with any other parameter from the command line, you may also add all your other stuff like, --single-transactionetc. Now take your log file entry:

由于这也适用于命令行中的任何其他参数，您还可以添加所有其他内容，例如--single-transaction等。现在获取您的日志文件条目：

Running: mysqldump.exe --defaults-extra-file="c:\users\d_micha\appdata\local\temp\tmpgtwa_m.cnf"  --user=root --max_allowed_packet=1G --host=localhost --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events --no-data "test"

And replace the --defaults-extra-fileparameter to point to your database.cnf- also remove the "Running:" info and every parameter you are already providing in your database.cnf:

并替换--defaults-extra-file参数以指向您的database.cnf- 同时删除“运行：”信息和您已经在 database.cnf 中提供的每个参数：

mysqldump.exe --defaults-extra-file="c:\temp\database.cnf" --routines --events --no-data "test"

Then open a Shell, go to your MySQLWorkbench-Folder and run the command, e.g:

然后打开一个 Shell，转到你的 MySQLWorkbench-Folder 并运行命令，例如：

cd c:\Program Files\MySQL\MySQL Workbench 6.3 CE\
mmysqldump.exe --defaults-extra-file="c:\temp\database.cnf" --routines --events --no-data "test" > c:\Users\user\Downloads\table1.sql

Do not forget to route the output to a file!

不要忘记将输出路由到文件！

Long story short: Use the CLI tool mysqldump, MySQLWorkbench is doing the same, but not the correct way.

长话短说：使用 CLI 工具 mysqldump，MySQLWorkbench 也是这样做的，但不是正确的方法。

Solution 2 - Provide a global correct config file

解决方案 2 - 提供全局正确的配置文件

mysqldumpalso reads a global config file, if it exists in one of those locations:

mysqldump还会读取全局配置文件，如果它存在于这些位置之一：

• C:\WINDOWS\my.ini
• C:\WINDOWS\my.cnf
• C:\my.ini
• C:\my.cnf
• c:\Program Files\MySQL\my.ini
• c:\Program Files\MySQL\my.cnf

• C:\WINDOWS\my.ini
• C:\WINDOWS\my.cnf
• C:\my.ini
• C:\my.cnf
• c:\Program Files\MySQL\my.ini
• c:\Program Files\MySQL\my.cnf

So you can just put the information from the above edited cnf-file to one of this locations and run the mysqldump-command without the --defaults-file-parameter

所以你可以把上面编辑过的 cnf 文件中的信息放到这个位置之一，然后运行 ​​mysqldump-command 而没有--defaults-file-parameter

Solution 3 Just call mysqldump with no parameters

解决方案 3 只调用 mysqldump 不带参数

This is maybe the most sophisticated solution: The my.cnf will will work with any parameter that mysqldump accepts. So why don't just use this to configure your dump? Just add all parameters to your my.cnf

这可能是最复杂的解决方案：my.cnf 将使用 mysqldump 接受的任何参数。那么为什么不直接使用它来配置您的转储呢？只需将所有参数添加到您的 my.cnf

[client]
user=root
password=secretPassword
single-transaction=TRUE
host=localhost
protocol=tcp
port=3306
default-character-set=utf8
skip-triggers=TRUE
all-databases=TRUE
all-tablespaces=TRUE

Now run mysqldump on the shell / command line, without any parameters, and your good:

现在在 shell / 命令行上运行 mysqldump，不带任何参数，你的好：

cd c:\Program Files\MySQL\MySQL Workbench 6.3 CE\
mysqldump.exe  > c:\Users\user\Downloads\dump.sql

回答by Ndrik7

Please try this solution https://bugs.mysql.com/bug.php?id=91640.

请尝试此解决方案https://bugs.mysql.com/bug.php?id=91640。

Actually we can use an advanced option in Workbench to disable column statistics as per https://stackoverflow.com/a/52944315/1694902- see below:

1. Go to Management/Data export
2. Choose the schema to export in the 'Tables to export' list
3. Click the 'Advanced Options...' button (top right)
4. Search for the option 'Other/column-statistics'
5. Set the value to 0
6. Click the 'Return' button (top right)

According to the author of this post, "Unfortunately, you'll have to do that every time you start MySQL Workbench."

实际上，我们可以根据https://stackoverflow.com/a/52944315/1694902使用 Workbench 中的高级选项禁用列统计信息- 见下文：

1. 转到管理/数据导出
2. 在“要导出的表”列表中选择要导出的架构
3. 单击“高级选项...”按钮（右上角）
4. 搜索选项“其他/列统计”
5. 将值设置为 0
6. 单击“返回”按钮（右上角）

根据这篇文章的作者的说法，“不幸的是，每次启动 MySQL Workbench 时都必须这样做。”

It's work for me, may be it can help others.

这对我有用，也许可以帮助其他人。

回答by SharpC

Operation failed with exitcode 2

操作失败，退出代码 2

also occurs with column statistic issues, which I fixed this by amending the MySQL Workbench config file wb_admin_export.py.

列统计问题也会发生，我通过修改 MySQL Workbench 配置文件解决了这个问题wb_admin_export.py。

See my answer here.

在这里看到我的答案。

回答by RandomSeed

MySQL workbench is trying to access your database without a password (notice the using password: NOin the error). Surprisingly you managed to get access to the server instance. Recreate the server instance, or at least try with a newly created instance.

MySQL 工作台试图在没有密码的情况下访问您的数据库（注意using password: NO错误中的 ）。令人惊讶的是，您设法访问了服务器实例。重新创建服务器实例，或者至少尝试使用新创建的实例。