CODEWITHSUNDEEP
mysqlmysql-workbench
Wes
Wes

Reputation: 844

How do I run an import or export command from mysql workbench outside of mysql workbench?

I see that workbench shows the command being run for imports and exports, and I'd like to run that command on another machine in a standalone script. Unfortunately, the script appears to reference a "defaults file" that is temporary and not accessible after the command runs... it seems that workbench is dynamically creating this extraparams.cnf file each time the command is run.. therefore it makes it difficult for me to reproduce this command elsewhere

13:45:52 Restoring /Users/wes/dumps/Dump20170907.sql
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysql --defaults-file="/var/folders/x1/8vgyglcd0hv44_5fdlqj1zssm53dzs/T/tmpsmXKI3/extraparams.cnf" --protocol=tcp --host=server.edu --user=riuser --port=3306 --default-character-set=utf8 --comments --database=prd2 < "/Users/wes/dumps/Dump20170907.sql"
13:46:02 Import of /Users/wes/dumps/Dump20170907.sql has finished

Any suggestions?

Thank you.

BTW: I posted this a month ago on the mysql forums with no response: https://forums.mysql.com/read.php?152,660219

Upvotes: 0

Views: 1244

Answers (2)

Matthias
Matthias

Reputation: 11

Although I agree with the accepted answer given by Kevin Bott here is what I did on my Mac as a brutal hack to spy the contents of the defaults file extraparams.cnf.

create a folder writable by any user

cd /tmp
mkdir hack
chmod a+xw hack

execute the MySQL Workbench export once and leave the Export Progress tab open

replace the mysqldump binary used by the export with a fake that dumps the contents of the defaults file (see Export Progress log for the correct paths to use in your case)

cd /private/var/folders/ks/vmng3tc51nb43xkklk7jjxxh0000gn/T/
mv AppTranslocation AppTranslocation.orig
mkdir -p AppTranslocation/10D5E741-FD2F-4247-9CEE-E8A82D8EE23F/d/MySQLWorkbench.app/Contents/MacOS
cd AppTranslocation/10D5E741-FD2F-4247-9CEE-E8A82D8EE23F/d/MySQLWorkbench.app/Contents/MacOS

vim mysqldump

  1 #!/bin/bash
  2 if [ $1 = '--version' ] 
  3 then 
  4     echo 'mysqldump  Ver 10.13 Distrib 5.6.23, for osx10.8 (x86_64)'
  5     exit 0
  6 fi  
  7 
  8 VALUE=`echo $1 | sed 's/^[^=]*=//g'`
  9 cat $VALUE > /tmp/hack/extraparams.cnf

chmod +x mysqldump

click 'Export Again' in the Export Progress tab

inspect the contents of the captured .cnf file

cd /tmp/hack
cat extraparams.cnf

Upvotes: 1

Kevin Bott
Kevin Bott

Reputation: 733

The defaults file usually holds the username and password. Ignore what workbench is doing and it will be easier to learn from scratch.

Assuming the commands run on the host local to mysql... 

mysqldump prd2 --single-transaction --routines --master-data=2 > prd2.sql
mysql prd2 < prd2.sql

Add other options as needed.

If on Linux/Unix, and it's a big database, consider nohup <command> &, to put it in the background, so you don't have to watch an idle terminal.

Upvotes: 1

Related Questions