mysqldump privileges required

July 23, 2013

mysqldump privileges required

Filed under: MySQL FAQ — snoyes @ 10:12 am

“mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.”
– http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Format	Default	Privileges Required
–add-drop-database	Off
–add-drop-table	On in –opt
–add-drop-trigger	Off
–add-locks	On in –opt
–all-databases	Off	SELECT, SHOW DATABASES ON .
–allow-keywords	Off
–apply-slave-statements	Off
–bind-address=ip_address	Off
–comments	On
–compact	Off
–compatible=name[,name,…]	Off
–complete-insert	Off
–create-options	On in –opt
–databases	Off
–debug[=debug_options]	Off
–debug-check	Off
–debug-info	Off
–default-auth=plugin	Off
–default-character-set=charset_name	utf8/latin1
–delayed-insert	Off
–delete-master-logs	Off	SUPER ON .
–disable-keys	On in –opt
–dump-date	On in –comments
–dump-slave[=value]	Off	SUPER or REPLICATION CLIENT ON .
–events	Off	EVENT
–extended-insert	On in –opt
–fields-enclosed-by=string	” in –tab
–fields-escaped-by	‘\\’ in –tab
–fields-optionally-enclosed-by=string	Off
–fields-terminated-by=string	‘\t’ in –tab
–flush-logs	Off	RELOAD ON .
–flush-privileges	Off
–help	Off
–hex-blob	Off
–host	localhost
–ignore-table=db_name.tbl_name	Off
–include-master-host-port	Off
–insert-ignore	Off
–lines-terminated-by=string	‘\n’ in –tab
–lock-all-tables	Off	LOCK TABLES ON .
–lock-tables	On in –opt	LOCK TABLES
–log-error=file_name	Off
–login-path=name	Off	controlled at OS level
–master-data	Off	RELOAD ON . SUPER or REPLICATION CLIENT ON .
–max_allowed_packet=value	24MB
–net_buffer_length=value	1022KB
–no-autocommit	Off
–no-create-db	Off
–no-create-info	Off
–no-data	Off
–no-set-names	Off
–no-tablespaces	Off
–opt	On
–order-by-primary	Off
–password[=password]	Off
–pipe	Off
–plugin-dir=path	Off
–port=port_num	3306
–quick	On in –opt
–quote-names	On
–replace	Off
–result-file=file	Off
–routines	Off	SELECT ON mysql.proc
–set-charset	On
–set-gtid-purged=value	Auto
–single-transaction	Off
–skip-add-drop-table	Off in –opt
–skip-add-locks	Off in –opt
–skip-comments	Off
–skip-compact	On
–skip-disable-keys	Off in –opt
–skip-extended-insert	Off in –opt
–skip-opt	Off
–skip-quick	Off in –opt
–skip-quote-names	Off
–skip-set-charset	Off
–skip-triggers	Off
–skip-tz-utc	Off
–ssl-ca=file_name	Off
–ssl-capath=dir_name	Off
–ssl-cert=file_name	Off
–ssl-cipher=cipher_list	Off
–ssl-crl=file_name	Off
–ssl-crlpath=dir_name	Off
–ssl-key=file_name	Off
–ssl-verify-server-cert	Off
–tab=path	Off
–tables	Off
–triggers	On	TRIGGER since 5.1.23, SUPER ON . earlier
–tz-utc	On
–user=user_name	system user on Linux, ‘ODBC’ on Windows
–verbose	Off
–version	Off
–where=’where_condition’	Off
–xml	Off

Comments (3)

3 Comments »

The chart says –triggers (default on) requires “SUPER ON *.* or TRIGGER” but this is ambiguous. In 5.0, you need SUPER. In later releases, triggers are silently skipped if you lack TRIGGER

Comment by Trig — July 23, 2013 @ 4:38 pm
Thanks Trig, corrected.

Comment by snoyes — August 12, 2013 @ 10:02 am
Thanks. I was having problem with mysqldump.

Comment by sathorn — October 1, 2014 @ 2:07 am