MySQL Workbench: Cannot export a database

Question

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:

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 DB in 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.

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.

Solution 1

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

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

Solution 2

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

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:

As you can see from the log mysqldump has 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").

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:

[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-transaction etc. Now take your log file entry:

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-file parameter to point to your database.cnf - also remove the "Running:" info and every parameter you are already providing in your 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:

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.

Solution 2 - Provide a global correct config file

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

• 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

Solution 3 Just call mysqldump with no parameters

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

[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 you're good:

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