skip to Main Content

I am trying to export a database which is protected by a SSH key and a private network, meaning I can’t login to the host itself to run mysqldump locally and somehow the ssh -i "key" doesn’t work for me to run it remotely too.

The only option I see is to connect via Workbench and dumpt it from there, but when I try to dump, I get a PROCESS privelage error. As I researched it can be circumvented by using a –no-tablespaces flag on mysqldump.exe, so I am looking for a way to add it so that Workbench would call mysqldump.exe with that flag.

I found that MySQL can read configuration files my.cnf and my.ini, but I don’t know what to write in them for a –no-tablespaces alternative.

2

Answers


  1. The "MySQL Programs / Using MySQL Programs / Specifying Program Options / Using Option Files" page includes:

    Most MySQL programs can read startup options from option files (sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

    To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

    In the syntax section, you can see a [mysqldump] header.

    So check if adding the header+option to your my.cnf or my.ini would be enough:

    [mysqldump]
    no-tablespaces
    
    Login or Signup to reply.
  2. Abandon Workbench (since it seems to be in the way).

    • From a commandline prompt, use mysqldump directly.

    • Try ssl from the other machine (with the suitable flag to reverse the direction). If neither direction works, elaborate on what complaint it provides.

    • What version of MySQL?

    mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the –single-transaction option is not used, PROCESS (as of MySQL 8.0.21) if the –no-tablespaces option is not used, and (as of MySQL 8.0.32) the RELOAD or FLUSH_TABLES priviledge with –single-transaction if both gtid_mode=ON and –set-gtid=purged=ON|AUTO.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search