Q: How to set --set-gtid-purged=OFF as a default Export parameter in Mysql workbench?

Question

Ive recently been playing with Mysql Workbench and the Export function has one little annoying feature that is not needed (by me of course). The set-gtid-purged function is set to AUTO by default in the GUI and it seems everytime you want to export without this parameter - you have to change it to OFF in Data Exports - Advanced options. To make it worse - the setting is not remembered.

Wanted to check if anyone has found a way to set it to OFF permanently for every DB/conenction i make?

I even checked the wb_options.xml file and the feature is set to OFF but this doesn't seem to stop it for some reason.

<value type="string" key="wb.admin.export.option:set-gtid-purged">OFF</value>

Thanks.

Answer 1

I want to do exactly what you are looking for but in AWS RDS. so for your case what you must do is:

Solution

1. Update config file Update /etc/my.cnf and add

   [mysqldump] set-gtid-purged=OFF

2. No need to restart. If you dont have access to that .cnf file then update the update directadmin.conf with the following instruction:

   extra_mysqldump_options=--set-gtid-purged=OFF

3. Is possible to run the following command in shell to achieve point number 2:

   echo "extra_mysqldump_options=--set-gtid-purged=OFF" >> /usr/local/directadmin/conf/directadmin.conf service directadmin restart

Official and useful references

MySQL Dump

MySQL enable/disable GTID (Global Transaction Ids)

MySQL extra options during DUMP

Answer 2

I've found a temporary solution for this on Linux/Ubuntu

1. Open /usr/lib/mysql-workbench/modules/wb_admin_export.py
2. Search for "--set-gtid-purged=OFF"
3. Delete or comment that line (#) and the if statement above it.
4. Restart MySQL Workbench

Using windows, the path to the file will vary and I will update my answer if someone can give it to me.

Answer 3

If you are using only mysqldump in Ubuntu Server

Try append this to etc/mysql/conf.d/mysqldump.cnf

set-gtid-purged=OFF
column-statistics=0

May looks like this:

Answer 4

On a Mac, running Mysql 5.7, I found the following to work:

In Workbench, select:

Server -> Data Export -> Advanced Options

change "set-gtid-purged - Add 'SET @@GLOBAL.GTID_PURGED' to the output." from AUTO to OFF

Then export your data.

The exported SQL files should no longer have the line:

"SET @@SESSION.SQL_LOG_BIN= 0;"

and now it imports successfully.