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.

Mesomorph

simi-related... I went through this troubleshooting thinking I had a legacy MySQL server that didn't support set-gtid-purged because it kept throwing an error. Found out that I had configured MySQL Workbench to use the mysql binary as mysqldump, so of course it complained. – Erymanthus

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

Bussell

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.

Input

Mac file location is /Applications/MySQLWorkbench.app/Contents/Resources/plugins/wb_admin_export.py, line number 1861 and 1862. – Erymanthus

In my case y have only commented the line 1883 (MySQL Workbench 8.0.13) #if self._compatibility_params: – Athwart

Answer 3

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.

Busey

Answer 4

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:

Briony